MaxCompute:Inserir ou substituir dados em uma tabela ou partição estática (INSERT INTO | INSERT OVERWRITE)

Plataformas suportadas:

Pré-requisitos

Para executar INSERT INTO ou INSERT OVERWRITE, você precisa da permissão Update na tabela de destino e da permissão Select nos metadados da tabela source. Para mais informações, consulte Permissões do MaxCompute.

Visão geral

Para informações sobre como inserir dados em partições dinâmicas, consulte Inserir ou substituir dados em partições dinâmicas (DYNAMIC PARTITION).

Limitações

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

Parâmetros:

ZORDER BY versus SORT BY:

• ZORDER BY: Suporta modos local (padrão) e global. O modo local ordena dentro de um único arquivo. O modo global (requer set odps.sql.default.zorder.type=global;) ordena entre todos os arquivos, otimizando o salto de dados (data skipping).

  Limitações do ZORDER BY:

  • Ordena apenas uma partição por vez.

  • Suporta de 2 a 4 campos.

  • Indisponível para tabelas clusterizadas.

  • Compatível com DISTRIBUTE BY, mas incompatível com ORDER BY, CLUSTER BY ou SORT BY.

  Nota

  O Z-Ordering consome mais recursos e tempo durante a inserção.

• SORT BY: Ordena dados dentro de um único arquivo. Se SORT BY não for especificado, o Z-Ordering local é aplicado por padrão.

Exemplos: Tabelas padrão

• Exemplo 1: Adicionar dados a uma tabela não particionada

  -- Create tables
  CREATE TABLE IF NOT EXISTS websites (id INT, name STRING, url STRING);
  CREATE TABLE IF NOT EXISTS apps (id INT, app_name STRING, url STRING);
  
  -- Append data to the apps table
  INSERT INTO apps (id, app_name, url) VALUES (1, 'Aliyun', 'https://www.aliyun.com');
  
  -- Copy data from apps to websites
  INSERT INTO websites (id, name, url) SELECT id, app_name, url FROM apps;
  
  -- Verify data
  SELECT * FROM websites;

  Resultado:

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

• Exemplo 2: Adicionar dados a uma tabela particionada

  -- Create table
  CREATE TABLE IF NOT EXISTS sale_detail (
    shop_name     STRING,
    customer_id   STRING,
    total_price   DOUBLE
  ) PARTITIONED BY (sale_date STRING, region STRING);
  
  -- Add partition (optional, can be created automatically)
  ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');
  
  -- Append data
  INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') 
  VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
  
  -- Verify data
  SET odps.sql.allow.fullscan=true; 
  SELECT * FROM sale_detail;

  Resultado:

  +------------+-------------+-------------+------------+------------+
  | 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: Substituir dados

  -- Create table with same schema
  CREATE TABLE sale_detail_insert LIKE sale_detail;
  
  -- Add partition (optional)
  ALTER TABLE sale_detail_insert ADD PARTITION (sale_date='2013', region='china');
  
  -- Overwrite data. Partition columns (sale_date, region) are specified in PARTITION(), so do not include them in SELECT.
  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;
  
  -- Verify data
  SELECT * FROM sale_detail_insert;

  Resultado:

  +------------+-------------+-------------+------------+------------+
  | 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: Substituir dados com ordem de colunas ajustada

  O mapeamento de dados segue a ordem das colunas, não seus nomes.

  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;    
  
  SELECT * FROM sale_detail_insert;                  

  Resultado (Observe as colunas trocadas):

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

• Exemplo 5: Uso incorreto (Colunas de partição no SELECT)

  Não inclua colunas de chave de partição na cláusula SELECT ao inserir dados em uma partição estática.

  -- This will fail because sale_date and region are partition keys
  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: Uso incorreto (Expressões em PARTITION)

  Os valores em PARTITION() devem ser constantes.

  -- This will fail because datepart() is an expression
  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: Z-Ordering global

  -- Create source table and insert data
  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);
  
  -- Create target table
  CREATE TABLE mf_zorder_src LIKE mf_src;
  
  -- Insert with global Z-Ordering
  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:

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

• Exemplo 8: Substituição com Z-Ordering

  SET odps.sql.default.zorder.type=global;
  INSERT OVERWRITE TABLE target
  SELECT key, value FROM target 
  ZORDER BY key, value;

Exemplos: Tabelas Delta

Operações de INSERT em tabelas Delta com comportamento automático de deduplicação.

-- Create a Delta table
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 OVERWRITE (Auto-deduplication: keeps one row for pk=1, 2, 3)
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';
-- Result:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

-- INSERT INTO (Appends data, no deduplication by default)
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';
-- Result (Note: pk=3 is duplicated):
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
| 3          | 3          | 01 | 01 |
+------------+------------+----+----+

-- INSERT OVERWRITE (Overwrites partition, removes duplicates)
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';
-- Result: 
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

Melhores práticas para Z-Ordering

Avalie o custo de ordenação em relação aos benefícios nas consultas. O Z-Ordering não é uma solução universal.