MaxCompute:Insert or overwrite data in dynamic partitions

Prerequisites

To perform INSERT INTO and INSERT OVERWRITE operations, you must have Update permission on the target table and Select permission on the source table. For more information about how to grant permissions, see MaxCompute Permissions.

How it works

When using an INSERT INTO or INSERT OVERWRITE statement to write to dynamic partitions, you specify only the partition key columns, not their values. MaxCompute determines the target partition for each row based on the corresponding values in the SELECT clause.

For information about how to insert data into static partitions, see Insert or overwrite data (INSERT INTO | INSERT OVERWRITE).

Limits

Usage notes

Syntax

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

Parameters

Sample data

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

Examples

The data in the sale_detail table used in the following examples is from Sample data.

• Example 1: This example inserts data from a source table into a target table, where the target partitions are determined dynamically by the values in the region column.

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

• Example 2: This example inserts data into a multi-level partitioned table, specifying the high-level partition sale_date statically.

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

• Example 3: This example demonstrates that dynamic partition mapping relies on column order in the select_statement, not on column names.

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

• Example 4 (Incorrect): This statement fails because the dynamic partition columns are not included in the SELECT list.

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

  The following result is returned:

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

• Example 5 (Incorrect): This statement fails because static partitions must be higher-level than any dynamic partitions.

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

  The following result is returned:

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

• Example 6: This example shows that if a partition column's data type does not match the type of the corresponding value in the SELECT clause, MaxCompute performs an 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 |
  +------------+------------+------------+

  Note

  If your data is sorted, inserting it into dynamic partitions can disrupt the sort order, reducing the compression ratio. For a better compression ratio, use the Tunnel command to upload sorted data. For a detailed example of how to use this command, see Migrate data from ApsaraDB RDS to MaxCompute based on dynamic partitioning.