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.

Você pode executar os comandos deste tópico nas seguintes plataformas:

Pré-requisitos

Para executar operações de INSERT INTO e INSERT OVERWRITE, é necessário ter 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 o 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 as seguintes:

INSERT INTO: Adiciona dados a uma tabela ou a uma partição estática. Especifique os valores de partição na instrução INSERT para inserir dados em uma partição específica. Para inserir uma pequena quantidade de dados de teste, use 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 do INSERT no MaxCompute difere da sintaxe do INSERT no MySQL ou Oracle. Você pode omitir a palavra-chave TABLE nas instruções INSERT INTO e INSERT OVERWRITE.
- Ao executar repetidamente uma operação de INSERT OVERWRITE na mesma partição, o Size da partição retornado pelo comando DESC pode ser diferente. Isso ocorre porque, ao usar SELECT para ler dados de uma partição e, em seguida, usar INSERT OVERWRITE para gravar os dados de volta na mesma partição, a lógica de divisão de arquivos muda. Como resultado, o Size dos dados também muda. O comprimento total dos dados permanece o mesmo antes e depois da operação de INSERT OVERWRITE, e nenhuma taxa extra de armazenamento é cobrada.

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. Atualize dados em uma tabela ou partição estática observando as regras abaixo:
- INSERT INTO: Não é possível adicionar dados a uma tabela clusterizada.
- INSERT OVERWRITE: Não há suporte para especificar 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. Não execute operações de INSERT INTO ou INSERT OVERWRITE simultaneamente na mesma tabela.

As limitações a seguir 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, e 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 de serem gravados na tabela.

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	O nome da tabela de destino na qual você deseja inserir dados.
pt_spec	Não	A partição na qual inserir dados. Você deve especificar 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	O nome da coluna na tabela de destino na qual você deseja inserir dados. `INSERT OVERWRITE` não oferece suporte para especificar `[(<col_name> [,<col_name> ...)]`.
select_statement	Sim	A 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	A 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, você pode classificar os dados por uma ou mais colunas especificadas (colunas na tabela que correspondem ao select_statement) para agrupar linhas com dados semelhantes. Isso melhora o desempenho de filtragem de consultas e pode reduzir os 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. Por outro lado, os dados classificados por `ZORDER BY` fornecem filtragem eficaz para expressões que contêm x ou tanto x quanto y, e alcançam uma taxa de compactação de coluna maior.

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

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

O ZORDER BY tem as seguintes limitações:
- Para tabelas particionadas, você pode executar 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.
- Quando a tabela de destino é uma tabela clusterizada, a cláusula ZORDER BY não é suportada.
- O ZORDER BY pode ser usado com DISTRIBUTE BY, mas não pode ser usado 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 não classificar.
A instrução SORT BY é usada para especificar como os dados são classificados dentro de um único arquivo. Se você não especificar o SORT BY, os dados dentro de um único arquivo serão classificados por local zorder.

Exemplos: Tabela regular

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

--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;

O seguinte resultado é retornado:

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

Exemplo 2: Execute o comando INSERT INTO para adicionar dados à tabela particionada sale_detail. A seguir, um comando de exemplo:

-- 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;

O seguinte 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 os dados na tabela sale_detail_insert. A seguir, um comando de exemplo:

-- 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;

O seguinte 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 os 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. O comando é o seguinte:

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;

O seguinte 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| 
+---------------------+--------------------+-------------------+

Nesse 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, que não são permitidas na cláusula SELECT para uma partição estática. Um exemplo de comando incorreto é o seguinte:
```
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 pode ser apenas uma constante, não uma expressão. Um exemplo de comando incorreto é o seguinte:

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 os dados nas tabelas mf_src e mf_zorder_src e classificar a tabela mf_zorder_src no modo global zorder. A seguir, um comando de exemplo:

-- 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;

O seguinte 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. O comando é o seguinte:

-- 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. Pode ser necessário testar seu caso de uso para determinar se os benefícios de desempenho de armazenamento e consulta justificam o custo computacional extra de gravar dados com Z-order. As seções a seguir fornecem recomendações gerais.

Como escolher um índice clusterizado em vez do Z-order

Se as 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 o ORDER BY fornece uma excelente classificação para a primeira coluna, com menos impacto nas colunas subsequentes. Por outro lado, o ZORDER BY dá peso igual a todas as colunas especificadas, portanto, a classificação em qualquer coluna individual é menos eficiente do que a classificação na primeira coluna de uma cláusula ORDER BY.
Se determinadas colunas aparecerem frequentemente em uma chave JOIN, o Hash ou Range Clustering é mais adequado. A implementação de Z-order do MaxCompute classifica os dados apenas dentro dos arquivos, e o mecanismo SQL não tem conhecimento da distribuição de dados do 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ê executar frequentemente operações de GROUP BY e ORDER BY em determinadas colunas, o uso de um índice clusterizado pode oferecer um desempenho melhor.

Recomendações de Z-order

Selecione colunas que aparecem frequentemente nas condições de filtro, especialmente aquelas que costumam ser filtradas juntas.
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 você tiver apenas uma coluna, use um índice clusterizado em vez do Z-ordering.
Selecione colunas com uma cardinalidade equilibrada (número de valores distintos). Colunas de baixa cardinalidade, como uma coluna de gênero, oferecem um 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 muito pequeno nem muito grande. Se o volume de dados for muito pequeno, os benefícios do Z-ordering não serão evidentes. Se o volume de dados for muito grande, o custo de geração de dados com Z-order pode ser alto, o que pode impactar significativamente o tempo de conclusão das tarefas de linha de base.