MaxCompute:Inserir ou sobrescrever dados em partições dinâmicas

Execute os comandos deste tópico nas seguintes plataformas:

• MaxCompute client (odpscmd)

• SQL Analysis

• Use Cloud Shell (odpscmd)

• DataWorks

• Develop and submit an SQL script

Pré-requisitos

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

Como funciona

Ao usar uma instrução INSERT INTO ou INSERT OVERWRITE para gravar em partições dinâmicas, especifique apenas as colunas de chave de partição, e não seus valores. O MaxCompute determina a partição de destino para cada linha com base nos valores correspondentes na cláusula SELECT.

Para saber mais sobre como inserir dados em partições estáticas, consulte Insert or overwrite data (INSERT INTO | INSERT OVERWRITE).

Limites

Notas de uso

Sintaxe

INSERT {INTO|OVERWRITE} TABLE <table_name> PARTITION (<ptcol_name>[, <ptcol_name> ...]) 
<select_statement> FROM <from_statement>;

Parâmetros

Dados 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 you do not create the partition in advance, it is created automatically when data is written to it.
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');

-- Append data to the source table. The TABLE keyword after INSERT INTO or INSERT OVERWRITE can be omitted.
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 scan. This setting is valid only for the current session. Run a SELECT statement to view the data in the sale_detail table.
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail;

-- The following result is returned:
+------------+-------------+-------------+------------+------------+
| 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      |
+------------+-------------+-------------+------------+------------+

Exemplos

Os dados da tabela sale_detail usados nos exemplos a seguir vêm de Sample data.

• Exemplo 1: Este exemplo insere dados de uma tabela de origem em uma tabela de destino, com as partições de destino determinadas dinamicamente pelos valores da coluna region.

  -- Create a target table named total_revenues.
  CREATE TABLE total_revenues (revenue DOUBLE) PARTITIONED BY (region string);
  
  -- Insert data from the sale_detail source table into the total_revenues target table.
  SET odps.sql.allow.fullscan=true; 
  INSERT OVERWRITE TABLE total_revenues PARTITION(region) SELECT total_price AS revenue, region FROM sale_detail;
  
  -- Run the SHOW PARTITIONS statement to view the partitions of the total_revenues table.
  SHOW PARTITIONS total_revenues;
     
  -- The following result is returned:
  region=china  
  
  -- Enable a full scan. This setting is valid only for the current session. Run a SELECT statement to view the data in the total_revenues table.  
  SET odps.sql.allow.fullscan=true; 
  SELECT * FROM total_revenues;    
  
  -- The following result is returned:
  +------------+------------+
  | revenue    | region     |
  +------------+------------+
  | 100.1      | china      |
  | 100.2      | china      |
  | 100.3      | china      |
  +------------+------------+        

• Exemplo 2: Este exemplo insere dados em uma tabela particionada de vários níveis, especificando estaticamente a partição de nível superior sale_date.

  -- Create a target table named sale_detail_dypart. 
  CREATE TABLE sale_detail_dypart LIKE sale_detail; 
  
  -- Specify the high-level partition and insert data into the target table.
  SET odps.sql.allow.fullscan=true; 
  INSERT OVERWRITE TABLE sale_detail_dypart PARTITION (sale_date='2013', region)
  SELECT shop_name,customer_id,total_price,region FROM sale_detail;
  
  -- Enable a full scan. This setting is valid only for the current session. Run a SELECT statement to view the data in the sale_detail_dypart table.
  SET odps.sql.allow.fullscan=true; 
  SELECT * FROM sale_detail_dypart;
  
  -- The following result is returned:
  +------------+-------------+-------------+------------+------------+
  | 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: Este exemplo demonstra que o mapeamento de partições dinâmicas depende da ordem das colunas no select_statement, e não de seus nomes.

  -- Insert data from the sale_detail source table into the sale_detail_dypart target table.
  SET odps.sql.allow.fullscan=true; 
  INSERT OVERWRITE TABLE sale_detail_dypart PARTITION (sale_date, region)
  SELECT shop_name,customer_id,total_price,sale_date,region FROM sale_detail;
  
  -- Enable a full scan. This setting is valid only for the current session. Run a SELECT statement to view the data in the sale_detail_dypart table.
  SET odps.sql.allow.fullscan=true; 
  SELECT * FROM sale_detail_dypart;
  
  -- The following result is returned:
  -- The sale_date column of the source table sale_detail determines the sale_date dynamic partition of the target table sale_detail_dypart.
  -- The region column of the source table sale_detail determines the region dynamic partition of the target table sale_detail_dypart.
  +------------+-------------+-------------+------------+------------+
  | 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      |
  +------------+-------------+-------------+------------+------------+
  
  -- Insert data from the sale_detail source table into the sale_detail_dypart target table and adjust the column order in the SELECT statement.
  SET odps.sql.allow.fullscan=true; 
  INSERT OVERWRITE TABLE sale_detail_dypart PARTITION (sale_date, region)
  SELECT shop_name,customer_id,total_price,region,sale_date FROM sale_detail;
  
  -- Enable a full scan. This setting is valid only for the current session. Run a SELECT statement to view the data in the sale_detail_dypart table.
  SET odps.sql.allow.fullscan=true; 
  SELECT * FROM sale_detail_dypart;
  
  -- The following result is returned:
  -- The region column of the source table sale_detail determines the sale_date dynamic partition of the target table sale_detail_dypart.
  -- The sale_date column of the source table sale_detail determines the region dynamic partition of the target table sale_detail_dypart.
  +------------+-------------+-------------+------------+------------+
  | shop_name  | customer_id | total_price | sale_date  | region     |
  +------------+-------------+-------------+------------+------------+
  | s1         | c1          | 100.1       | china      | 2013       |
  | s2         | c2          | 100.2       | china      | 2013       |
  | s3         | c3          | 100.3       | china      | 2013       |
  | s1         | c1          | 100.1       | 2013       | china      |
  | s2         | c2          | 100.2       | 2013       | china      |
  | s3         | c3          | 100.3       | 2013       | china      |
  +------------+-------------+-------------+------------+------------+

• Exemplo 4 (Incorreto): Esta instrução falha porque as colunas de partição dinâmica não estão incluídas na lista SELECT.

  INSERT OVERWRITE TABLE sale_detail_dypart PARTITION (sale_date='2013', region) 
  SELECT shop_name,customer_id,total_price FROM sale_detail;

  O MaxCompute retorna o seguinte resultado:

  FAILED: ODPS-0130071:[1,24] Semantic analysis exception - wrong columns count 3 in data source, requires 4 columns (includes dynamic partitions if any)

• Exemplo 5 (Incorreto): Esta instrução falha porque as partições estáticas devem estar em um nível superior ao de quaisquer partições dinâmicas.

  INSERT OVERWRITE TABLE sale_detail_dypart PARTITION (sale_date, region='china')
  SELECT shop_name,customer_id,total_price,sale_date FROM sale_detail_dypart;

  O MaxCompute retorna o seguinte resultado:

  FAILED: ODPS-0130071:[1,72] Semantic analysis exception - static partition region must be a high level partition than any dynamic partitions

• Exemplo 6: Este exemplo mostra que, se o tipo de dados de uma coluna de partição não corresponder ao tipo do valor na cláusula SELECT, o MaxCompute executa uma implicit conversion.

  -- Create a source table named src.
  CREATE TABLE src (c INT, d STRING) PARTITIONED BY (e INT);
  
  -- Add a partition to the source table src.
  ALTER TABLE src ADD IF NOT EXISTS PARTITION (e=201312);
  
  -- Append data to the source table src.
  INSERT INTO src PARTITION (e=201312) VALUES (1,100.1),(2,100.2),(3,100.3);
  
  -- Create a target table named parttable.
  CREATE TABLE parttable(a INT, b DOUBLE) PARTITIONED BY (p STRING);
  
  -- Insert data from the source table src into the target table parttable.
  SET odps.sql.allow.fullscan=true; 
  INSERT INTO parttable PARTITION (p) SELECT c, d, CURRENT_TIMESTAMP() FROM src;
  
  -- Query the target table parttable.
  SET odps.sql.allow.fullscan=true;
  SELECT * FROM parttable;
  
  -- The following result is returned:
  +------------+------------+------------+
  | a          | b          | p          |
  +------------+------------+------------+
  | 1          | 100.1      | 2024-12-10 15:59:34.492 |
  | 2          | 100.2      | 2024-12-10 15:59:34.492 |
  | 3          | 100.3      | 2024-12-10 15:59:34.492 |
  +------------+------------+------------+

  Nota

  Inserir dados ordenados em partições dinâmicas pode quebrar a ordenação e reduzir a taxa de compressão. Para melhorar a taxa de compressão, use o Tunnel command para fazer upload de dados ordenados. Para ver um exemplo detalhado de como usar esse comando, consulte Migrate data from ApsaraDB RDS to MaxCompute based on dynamic partitioning.