Inserir ou sobrescrever dados (INSERT INTO | INSERT OVERWRITE) - MaxCompute: Central de Documentação do Alibaba Cloud

O MaxCompute permite usar INSERT INTO ou INSERT OVERWRITE para inserir ou sobrescrever dados em uma tabela de destino ou partição estática.

Execute os comandos deste tópico nas seguintes plataformas:

Pré-requisitos

Para executar operações INSERT INTO e INSERT OVERWRITE, é necessária a permissão Update na tabela de destino e a permissão Select na tabela source. Para mais informações sobre como conceder permissões, consulte MaxCompute Permissions.

Visão geral

Ao processar dados com MaxCompute SQL, use a instrução INSERT INTO ou INSERT OVERWRITE para salvar o resultado de uma consulta SELECT em uma tabela de destino. As diferenças são:

INSERT INTO: Adiciona dados a uma tabela ou partição estática. Especifique valores de partição na instrução INSERT para inserir dados em uma partição específica. Para inserir um pequeno volume de dados de teste, combine esta instrução com VALUES.
INSERT OVERWRITE: Limpa os dados existentes em uma tabela ou partição estática antes de inserir os novos dados.
Nota
- A sintaxe INSERT do MaxCompute difere da sintaxe INSERT do MySQL ou Oracle. Omita a palavra-chave TABLE tanto nas instruções INSERT INTO quanto nas INSERT OVERWRITE.
- Ao executar repetidamente uma operação INSERT OVERWRITE na mesma partição, o Size da partição retornado pelo comando DESC pode variar. Isso ocorre porque, ao executar SELECT dos dados de uma partição e depois usar INSERT OVERWRITE para gravá-los de volta na mesma partição, a lógica de divisão de arquivos muda. Consequentemente, o Size dos dados também se altera. O comprimento total dos dados permanece o mesmo antes e depois da operação INSERT OVERWRITE, sem gerar custos extras de armazenamento.

Para obter informações sobre como inserir dados em partições dinâmicas, consulte Insert or overwrite data in dynamic partitions (DYNAMIC PARTITION).

Limitações

As seguintes limitações se aplicam ao usar INSERT INTO e INSERT OVERWRITE para atualizar dados em uma tabela ou partição estática:
- INSERT INTO: Não é possível adicionar dados a uma tabela clusterizada.
- INSERT OVERWRITE: Não oferece suporte à especificação de colunas para inserção. Para especificar colunas, use INSERT INTO. Por exemplo, a instrução CREATE TABLE t(a STRING, b STRING); INSERT INTO t(a) VALUES ('1'); insere '1' na coluna a e define a coluna b como NULL ou seu valor padrão.
- O MaxCompute não implementa bloqueios de tabela. Evite executar operações INSERT INTO ou INSERT OVERWRITE simultaneamente na mesma tabela.

As restrições abaixo se aplicam a uma Delta Table.
- Ao usar INSERT OVERWRITE para gravar dados em uma Delta Table, várias linhas com o mesmo valor de PK são deduplicadas antes da gravação. Apenas a primeira linha é gravada. O resultado final depende da ordem dos registros durante o processo de computação, que não pode ser especificada manualmente. Como essa operação grava todo o conjunto de dados, essa deduplicação padrão ajuda a garantir a unicidade da chave primária.
- Ao gravar dados em uma Delta Table usando INSERT INTO, várias linhas com o mesmo valor de chave primária (PK) não são deduplicadas por padrão; todas são gravadas na tabela. No entanto, se você definir set odps.sql.insert.acidtable.deduplicate.enable = true, os dados serão deduplicados antes da gravação.

Sintaxe

INSERT {INTO|OVERWRITE} TABLE <table_name> [PARTITION (<pt_spec>)] [(<col_name> [,<col_name> ...)]]
<select_statement>
FROM <from_statement>
[ZORDER BY <zcol_name> [, <zcol_name> ...]];

A tabela a seguir descreve os parâmetros.

Parâmetro	Obrigatório	Descrição
table_name	Sim	Nome da tabela de destino onde os dados serão inseridos.
pt_spec	Não	Partição na qual os dados serão inseridos. Especifique uma constante. Funções e expressões não são permitidas. O formato é `(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`.
col_name	Não	Nome da coluna na tabela de destino onde os dados serão inseridos. `INSERT OVERWRITE` não oferece suporte à especificação de `[(<col_name> [,<col_name> ...)]`.
select_statement	Sim	Cláusula `SELECT` que consulta os dados a serem inseridos da tabela source. Para mais informações sobre instruções `SELECT`, consulte SELECT Syntax. Nota O mapeamento entre as tabelas source e de destino depende da ordem das colunas na cláusula `SELECT`, e não dos nomes das colunas. Ao inserir dados em uma partição estática, as colunas de partição não podem aparecer na cláusula `SELECT`.
from_statement	Sim	Cláusula `FROM` que especifica a fonte de dados, como o nome da tabela source.
ZORDER BY <zcol_name> [, <zcol_name> ...]	Não	Ao gravar dados em uma tabela ou partição, classifique os dados por uma ou mais colunas especificadas (colunas na tabela correspondentes ao select_statement) para agrupar linhas com dados semelhantes. Isso melhora o desempenho de filtragem de consultas e pode reduzir custos de armazenamento. Observe que `ORDER BY x, y` classifica estritamente os dados primeiro por x e depois por y, enquanto `ZORDER BY x, y` agrupa pares <x, y> semelhantes. Quando as condições de filtro de uma consulta SQL incluem as colunas de classificação, os dados classificados por `ORDER BY` fornecem filtragem eficaz apenas para expressões que contêm x. Em contraste, os dados classificados por `ZORDER BY` oferecem filtragem eficaz para expressões que contêm x ou ambos x e y, além de alcançar uma taxa de compressão de coluna maior.

As diferenças entre ZORDER BY e SORT BY são:

ZORDER BY possui dois modos: zorder local e zorder global. O modo padrão é local zorder. O modo local classifica os dados por z-order apenas dentro de arquivos individuais e não redistribui os dados globalmente. Portanto, se os dados estiverem distribuídos por vários arquivos, o clustering de dados pode ser baixo, o que impede o Data Skipping eficaz. Para resolver esse problema, versões mais recentes oferecem suporte a global zorder. Para usar o modo global zorder, defina o seguinte parâmetro: SET odps.sql.default.zorder.type=global;.

ZORDER BY apresenta as seguintes limitações:
- Para tabelas particionadas, execute uma classificação ZORDER BY em apenas uma partição por vez.
- A contagem de colunas do ZORDER BY deve estar entre 2 e 4.
- Se a tabela de destino for clusterizada, a cláusula ZORDER BY não será suportada.
- ZORDER BY pode ser usado com DISTRIBUTE BY, mas não pode ser combinado com ORDER BY, CLUSTER BY ou SORT BY.
Nota
Ao gravar dados usando a cláusula ZORDER BY, a operação consome mais recursos e leva mais tempo do que sem a classificação.
A instrução SORT BY especifica como os dados são classificados dentro de um único arquivo. Se você não especificar SORT BY, os dados dentro de um único arquivo serão classificados por local zorder.

Exemplos: tabelas regulares

Exemplo 1: Execute o comando INSERT INTO para adicionar dados à tabela não particionada websites. Comando:

--Create a non-partitioned table named websites.
CREATE TABLE IF NOT EXISTS websites
(id INT,
 name STRING,
 url STRING
);
--Create a non-partitioned table named apps.
CREATE TABLE IF NOT EXISTS apps
(id INT,
 app_name STRING,
 url STRING
);
--Append data to the apps table. The keyword TABLE in `INSERT INTO TABLE ` is optional.
INSERT INTO apps (id,app_name,url) VALUES 
(1,'Aliyun','https://www.aliyun.com');
--Copy data from the apps table and append it to the websites table.
INSERT INTO websites (id,name,url) SELECT id,app_name,url
FROM apps;
--Run the SELECT statement to view the data in the websites table.
SELECT * FROM websites;

Resultado retornado:

-- The result.
+------------+------------+------------+
| id         | name       | url        |
+------------+------------+------------+
| 1          | Aliyun     | https://www.aliyun.com |
+------------+------------+------------+

Exemplo 2: Use o comando INSERT INTO para adicionar dados à tabela particionada sale_detail. Exemplo de comando:

-- Create a partitioned table named sale_detail.
CREATE TABLE IF NOT EXISTS sale_detail
(
shop_name     STRING,
customer_id   STRING,
total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

-- Add a partition to the source table. This step is optional. If the partition does not exist, it is automatically created when you write data.
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');

-- Append data to the source table. The TABLE keyword after INSERT INTO and INSERT OVERWRITE is optional.
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);

-- Enable a full table scan for the current session only. Run a SELECT statement to view data in the sale_detail table.
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail;

Resultado retornado:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Exemplo 3: Execute o comando INSERT OVERWRITE para sobrescrever dados na tabela sale_detail_insert. Exemplo de comando:

-- Create a target table named sale_detail_insert with the same schema as sale_detail.
CREATE TABLE sale_detail_insert LIKE sale_detail;

-- Add a partition to the target table. This step is optional. If the partition does not exist, it is automatically created when you write data.
ALTER TABLE sale_detail_insert ADD PARTITION (sale_date='2013', region='china');

-- Overwrite a static partition. For static partitions, partition columns are specified in the PARTITION() clause and must not be in the SELECT list. The columns in the SELECT list are mapped to the target table's columns by position.
SET odps.sql.allow.fullscan=true;
INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date='2013', region='china')
  SELECT 
  shop_name, 
  customer_id,
  total_price 
  FROM sale_detail
  ZORDER BY customer_id, total_price;

-- Enable a full table scan for the current session only. Run a SELECT statement to view data in the sale_detail_insert table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_insert;

Resultado retornado:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Exemplo 4: Execute o comando INSERT OVERWRITE para sobrescrever dados na tabela sale_detail_insert e alterar a ordem das colunas na cláusula SELECT. O mapeamento entre a tabela source e a tabela de destino baseia-se na ordem das colunas na cláusula SELECT, e não nos nomes das colunas. Comando:

SET odps.sql.allow.fullscan=true;
INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date='2013', region='china')
    SELECT customer_id, shop_name, total_price FROM sale_detail;    

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_insert;

Resultado retornado:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| c1         | s1          | 100.1       | 2013       | china      |
| c2         | s2          | 100.2       | 2013       | china      |
| c3         | s3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Quando a tabela sale_detail_insert foi criada, a ordem das colunas era:

+-------------------+--------------------+-------------------+
| shop_name STREING | customer_id  STRING| total_price BIGINT| 
+-------------------+--------------------+-------------------+

A ordem de inserção de dados de sale_detail em sale_detail_insert é a seguinte:

+---------------------+--------------------+-------------------+
| customer_id  STRING | shop_name STREING  | total_price BIGINT| 
+---------------------+--------------------+-------------------+

Neste caso, os dados de sale_detail.customer_id são inseridos em sale_detail_insert.shop_name, e os dados de sale_detail.shop_name são inseridos em sale_detail_insert.customer_id.

Exemplo 5: Ao inserir dados em uma partição, as colunas de partição não podem aparecer na cláusula SELECT. A instrução a seguir retorna um erro porque sale_date e region são colunas de partição, proibidas na cláusula SELECT para uma partição estática. Exemplo de comando incorreto:
```
INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date='2013', region='china')
   SELECT shop_name, customer_id, total_price, sale_date, region FROM sale_detail;
```

Exemplo 6: O valor de PARTITION deve ser uma constante, não uma expressão. Exemplo de comando incorreto:

INSERT OVERWRITE TABLE sale_detail_insert PARTITION (sale_date=datepart('2016-09-18 01:10:00', 'yyyy') , region='china')
   SELECT shop_name, customer_id, total_price FROM sale_detail;

Exemplo 7: Execute o comando INSERT OVERWRITE para sobrescrever dados nas tabelas mf_src e mf_zorder_src e classificar a tabela mf_zorder_src no modo zorder global. Exemplo de comando:

-- Create the target table mf_src.
CREATE TABLE mf_src (key STRING, value STRING);

INSERT OVERWRITE TABLE mf_src
SELECT a, b FROM VALUES ('1', '1'),('3', '3'),('2', '2')
AS t(a, b);

SELECT * FROM mf_src;

-- The result is returned:
+-----+-------+
| key | value |
+-----+-------+
| 1   | 1     |
| 3   | 3     |
| 2   | 2     |
+-----+-------+

-- Create the target table mf_zorder_src with the same schema as mf_src.
CREATE TABLE mf_zorder_src LIKE mf_src;

-- Use the global z-order mode for sorting.
SET odps.sql.default.zorder.type=global;
INSERT OVERWRITE TABLE mf_zorder_src
SELECT key, value FROM mf_src 
ZORDER BY key, value;

SELECT * FROM mf_zorder_src;

Resultado retornado:

+-----+-------+
| key | value |
+-----+-------+
| 1   | 1     |
| 2   | 2     |
| 3   | 3     |
+-----+-------+

Exemplo 8: Execute o comando INSERT OVERWRITE para sobrescrever os dados na tabela target existente. Comando:

-- The 'target' table is an existing table.
SET odps.sql.default.zorder.type=global;
INSERT OVERWRITE TABLE target
SELECT key, value FROM target 
ZORDER BY key, value;

Exemplos: Delta Table

Exemplo: Crie a Delta Table mf_dt e execute o comando INSERT para inserir e sobrescrever dados.

-- Create a Delta Table named mf_dt.
CREATE TABLE IF NOT EXISTS mf_dt (pk BIGINT NOT NULL PRIMARY KEY, 
                  val BIGINT NOT NULL) 
                  PARTITIONED BY (dd STRING, hh STRING) 
                  tblproperties ("transactional"="true");
                  
-- Insert test data into the partition where dd='01' and hh='01' in the mf_dt table.
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='01') 
                 VALUES (1, 1), (2, 2), (3, 3);

-- Query data in the target partition of the mf_dt table.                
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
-- The result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- Use 'INSERT INTO' to append data to the target partition of the mf_dt table.
INSERT INTO TABLE mf_dt PARTITION(dd='01', hh='01') 
            VALUES (3, 30), (4, 4), (5, 5);
            
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
-- The result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- Use 'INSERT OVERWRITE' to overwrite data in the target partition of the mf_dt table.
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='01') 
                 VALUES (1, 1), (2, 2), (3, 3);
SELECT * FROM mf_dt WHERE dd='01' AND hh='01';
-- The result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- Use 'INSERT OVERWRITE' to write data to the partition where dd='01' and hh='02' in the mf_dt table.
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') 
                 VALUES (1, 11), (2, 22), (3, 32);
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
-- The result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 11         | 01 | 02 |
| 3          | 32         | 01 | 02 |
| 2          | 22         | 01 | 02 |
+------------+------------+----+----+

-- Enable a full table scan for the current session only. Run a SELECT statement to view data in the mf_dt table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM mf_dt;
-- The result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 11         | 01 | 02 |
| 3          | 32         | 01 | 02 |
| 2          | 22         | 01 | 02 |
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

Melhores práticas

O Z-ordering não é adequado para todos os cenários. Teste seu caso de uso para determinar se os benefícios de armazenamento e desempenho de consulta justificam o custo computacional adicional de gravar dados com Z-order. As seções a seguir fornecem recomendações gerais.

Escolher um índice clusterizado em vez de Z-order

Se suas condições de filtro forem tipicamente baseadas em um prefixo de colunas, como a, a and b ou a and b and c, usar um índice clusterizado (por exemplo, ORDER BY a, b, c) é mais eficaz. Não use ZORDER BY neste caso. Isso ocorre porque ORDER BY fornece excelente classificação para a primeira coluna, com menor impacto nas colunas subsequentes. Em contraste, ZORDER BY dá peso igual a todas as colunas especificadas, tornando a classificação em qualquer coluna individual menos eficiente do que a classificação na primeira coluna de uma cláusula ORDER BY.
Caso certas colunas apareçam frequentemente em uma chave JOIN, Hash ou Range Clustering é mais adequado. A implementação de Z-order do MaxCompute classifica dados apenas dentro de arquivos, e o mecanismo SQL não tem conhecimento da distribuição de dados Z-order. No entanto, o mecanismo SQL reconhece um índice clusterizado e pode otimizar melhor o desempenho do JOIN durante a fase de planejamento da consulta.
Se você executa frequentemente operações GROUP BY e ORDER BY em determinadas colunas, usar um índice clusterizado pode oferecer melhor desempenho.

Recomendações de Z-order

Selecione colunas que aparecem frequentemente nas condições de filtro, especialmente aquelas filtradas em conjunto.
Quanto mais colunas você incluir no ZORDER BY, menos eficaz será a classificação para cada coluna individual. Portanto, não especifique mais de quatro colunas. Se houver apenas uma coluna, use um índice clusterizado em vez de Z-ordering.
Prefira colunas com cardinalidade equilibrada (número de valores distintos). Colunas de baixa cardinalidade, como uma coluna de gênero, oferecem benefício mínimo de classificação. Colunas de alta cardinalidade com valores majoritariamente únicos aumentam os custos de classificação, pois a implementação de Z-order do MaxCompute precisa armazenar em cache todos os valores distintos na memória para calcular o valor Z.
O tamanho da tabela não deve ser nem muito pequeno nem muito grande. Se o volume de dados for muito pequeno, os benefícios do Z-ordering não ficam evidentes. Se o volume de dados for muito grande, o custo de geração de dados com Z-order pode ser alto, impactando significativamente o tempo de conclusão das tarefas de linha de base.