MaxCompute:Insert or overwrite data into dynamic partitions (DYNAMIC PARTITION)

Insert into a single-level dynamic partition

Create a destination table with one partition level and let MaxCompute assign partition values from the source data:

-- Create destination table
CREATE TABLE total_revenues (revenue DOUBLE) PARTITIONED BY (region STRING);

-- Insert with dynamic partition
SET odps.sql.allow.fullscan=true;
INSERT OVERWRITE TABLE total_revenues PARTITION(region)
SELECT total_price AS revenue, region FROM sale_detail;

-- Verify partitions created
SHOW PARTITIONS total_revenues;

SHOW PARTITIONS output:

region=china

Data in total_revenues:

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

+------------+------------+
| revenue    | region     |
+------------+------------+
| 100.1      | china      |
| 100.2      | china      |
| 100.3      | china      |
+------------+------------+

Mix static and dynamic partitions

When a table has multiple partition levels, fix the high-level partition as static and leave lower levels dynamic:

-- Create destination table with same schema as sale_detail
CREATE TABLE sale_detail_dypart LIKE sale_detail;

-- Fix sale_date as static, leave region dynamic
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;

-- Verify
SET odps.sql.allow.fullscan=true;
SELECT * FROM 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      |
+------------+-------------+-------------+------------+------------+

Understand column order in dynamic partition mapping

Dynamic partition values are mapped by column position, not column name. The last columns in SELECT fill partition slots in the order declared in PARTITION(...).

Correct column order — sale_date maps to sale_date, region maps to region:

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;

Swapped column order — region maps to sale_date, sale_date maps to region:

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;

With swapped columns, the partition values are reversed. Each source row appears twice — once per original partition — with partition values swapped:

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

Use implicit type conversion for partition values

If the partition key column type doesn't match the trailing SELECT column type, MaxCompute converts the value automatically. In this example, a TIMESTAMP value from CURRENT_TIMESTAMP() is stored in a STRING partition column:

-- Create source table
CREATE TABLE src (c INT, d STRING) PARTITIONED BY (e INT);
ALTER TABLE src ADD IF NOT EXISTS PARTITION (e=201312);
INSERT INTO src PARTITION (e=201312) VALUES (1,100.1),(2,100.2),(3,100.3);

-- Create destination table with STRING partition
CREATE TABLE parttable(a INT, b DOUBLE) PARTITIONED BY (p STRING);

-- Insert with implicit conversion: TIMESTAMP to STRING
SET odps.sql.allow.fullscan=true;
INSERT INTO parttable PARTITION (p) SELECT c, d, CURRENT_TIMESTAMP() FROM src;

-- Verify
SET odps.sql.allow.fullscan=true;
SELECT * FROM parttable;

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

Missing partition columns in SELECT

Error:

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

Cause: The SELECT list doesn't include the dynamic partition column. For a table with 3 data columns and 1 dynamic partition, SELECT must return 4 columns total.

Fix: Add the partition key column to SELECT:

-- Incorrect: SELECT returns only 3 columns, but 4 are required
INSERT OVERWRITE TABLE sale_detail_dypart PARTITION (sale_date='2013', region)
SELECT shop_name, customer_id, total_price FROM sale_detail;

-- Correct: include region as the 4th column
INSERT OVERWRITE TABLE sale_detail_dypart PARTITION (sale_date='2013', region)
SELECT shop_name, customer_id, total_price, region FROM sale_detail;

Static partition specified at sub-partition level

Error:

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

Cause: A static value was assigned to a lower-level partition while a higher-level partition was left dynamic.

Fix: Declare static partitions at a higher level than dynamic ones:

-- Incorrect: high-level partition (sale_date) is dynamic, low-level (region) is static
INSERT OVERWRITE TABLE sale_detail_dypart PARTITION (sale_date, region='china')
SELECT shop_name, customer_id, total_price, sale_date FROM sale_detail_dypart;

-- Correct: high-level partition (sale_date) is static, low-level (region) is dynamic
INSERT OVERWRITE TABLE sale_detail_dypart PARTITION (sale_date='2013', region)
SELECT shop_name, customer_id, total_price, region FROM sale_detail;