AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生大数据计算服务 MaxCompute 操作指南 开发参考：SQL SQL参考 DML操作 插入或覆写动态分区数据（DYNAMIC PARTITION）

插入或覆写动态分区数据（DYNAMIC PARTITION）

更新时间：
产品详情
我的收藏

MaxCompute支持通过INSERT INTO或INSERT OVERWRITE操作向动态分区中插入数据。

前提条件

执行insert into和insert overwrite操作前需要具备目标表的更新权限（Update）及源表的元信息读取权限（Select）。授权操作请参见MaxCompute权限。

功能介绍

在使用MaxCompute SQL处理数据时，INSERT INTO或INSERT OVERWRITE语句中不直接指定分区值，只指定分区列名（分区字段）。分区列的值在SELECT子句中提供，系统自动根据分区列的值将数据插入到相应分区。

向静态分区插入数据的操作请参见插入或覆写数据（INSERT INTO | INSERT OVERWRITE）。

使用限制

通过INSERT INTO和INSERT OVERWRITE操作向动态分区中插入数据的使用限制如下：

  • INSERT INTO最多可以生成10000个动态分区，INSERT OVERWRITE最多可以生成60000个动态分区。

  • 分布式环境下，在使用动态分区功能的SQL语句中，单个进程最多只能输出512个动态分区，否则会运行异常。

  • 动态生成的分区值不允许为NULL，也不支持特殊字符和中文，否则会报错FAILED: ODPS-0123031:Partition exception - invalid dynamic partition value: province=xxx。

    说明

    分区值不能包含双字节字符（如中文），必须以字母开头，包含字母、数字和允许的字符，长度不超过255字节。允许的字符包括空格、冒号（:）、下划线（_）、美元符号（$）、井号（#）、英文句点（.）、感叹号（!）和at（@），其他字符的行为未定义，例如转义字符\t、\n和/。

  • 聚簇表不支持动态分区。

注意事项

如果您需要更新表数据到动态分区，需要注意：

  • INSERT INTO PARTITION时，如果分区不存在，会自动创建分区。

  • 多个INSERT INTO PARTITION作业并发时，如果分区不存在，优先执行成功的作业会自动创建分区，但只会成功创建一个分区。

  • 如果不能控制INSERT INTO PARTITION作业并发，建议您通过ALTER TABLE命令提前创建分区，详情请参见分区操作。

  • 如果目标表有多级分区，在执行INSERT操作时，允许指定部分分区为静态分区，但是静态分区必须是高级分区。

  • 向动态分区插入数据时，动态分区列必须在SELECT列表中，否则会执行失败。

命令格式

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

参数说明

参数名

是否必填

描述

table_name

是

需要插入数据的目标表名。

ptcol_name

是

目标表分区列的名称。

select_statement

是

SELECT子句，从源表中查询需要插入目标表的数据。

如果目标表只有一级动态分区，则SELECT子句的最后一个字段值即为目标表的动态分区值。源表SELECT的值和输出分区的值的关系是由字段顺序决定，并不是由列名称决定的。当源表的字段与目标表字段顺序不一致时，建议您按照目标表顺序在select_statement语句中指定字段。

from_statement

是

FROM子句，表示数据来源。例如，源表名称。

示例数据

--创建一张分区表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);

--向源表增加分区。非必需操作，如果不提前创建，写入时会自动创建该分区。
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china');

--向源表追加数据。其中：insert into table table_name可以简写为insert into table_name，但insert overwrite table table_name不可以省略table关键字。
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);

--开启全表扫描，仅此Session有效。执行select语句查看表sale_detail中的数据。
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail;

--返回结果。
+------------+-------------+-------------+------------+------------+
| 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      |
+------------+-------------+-------------+------------+------------+

使用示例

下述示例中的sale_detail表数据均来自于示例数据。

  • 示例1：将源表中的数据插入到目标表中。在运行SQL语句之前，您无法得知会产生哪些分区。只有在语句运行结束后，才能通过region字段产生的值确定产生的分区。命令示例如下：

    --创建目标表total_revenues。
CREATE TABLE total_revenues (revenue DOUBLE) PARTITIONED BY (region string);

--将源表sale_detail中的数据插入到目标表total_revenues。
SET odps.sql.allow.fullscan=true; 
INSERT OVERWRITE TABLE total_revenues PARTITION(region) SELECT total_price AS revenue, region FROM sale_detail;

--执行SHOW PARTITIONS语句查看表total_revenues的分区。
SHOW PARTITIONS total_revenues;
   
--返回结果。
region=china  

--开启全表扫描，仅此Session有效。执行SELECT语句查看表total_revenues中的数据。  
SET odps.sql.allow.fullscan=true; 
SELECT * FROM total_revenues;    

--返回结果。
+------------+------------+
| revenue    | region     |
+------------+------------+
| 100.1      | china      |
| 100.2      | china      |
| 100.3      | china      |
+------------+------------+

  • 示例2：将源表中的数据插入到目标表中。多级分区，指定一级分区sale_date。命令示例如下： 

    --创建目标表sale_detail_dypart。 
CREATE TABLE sale_detail_dypart LIKE sale_detail; 

--指定一级分区，将数据插入目标表。
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;

--开启全表扫描，仅此Session有效。执行select语句查看表sale_detail_dypart中的数据。
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      |
+------------+-------------+-------------+------------+------------+

  • 示例3：动态分区中，select_statement字段和目标表动态分区的对应关系是由字段顺序决定，并不是由列名称决定的。命令示例如下：

    --将源表sale_detail中的数据插入到目标表sale_detail_dypart。
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;

--开启全表扫描，仅此Session有效。执行select语句查看表sale_detail_dypart中的数据。
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail_dypart;

--返回结果。决定目标表sale_detail_dypart动态分区的字段sale_date为源表sale_detail的字段sale_date；决定目标表sale_detail_dypart动态分区的字段region为源表sale_detail的字段region。
+------------+-------------+-------------+------------+------------+
| 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      |
+------------+-------------+-------------+------------+------------+


--将源表sale_detail中的数据插入到目标表sale_detail_dypart，调整select字段顺序。
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;

--开启全表扫描，仅此Session有效。执行select语句查看表sale_detail_dypart中的数据。
SET odps.sql.allow.fullscan=true; 
SELECT * FROM sale_detail_dypart;

--返回结果。决定目标表sale_detail_dypart动态分区的字段sale_date为源表sale_detail的字段region；决定目标表sale_detail_dypart动态分区的字段region为源表sale_detail的字段sale_date。
+------------+-------------+-------------+------------+------------+
| 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      |
+------------+-------------+-------------+------------+------------+

  • 示例4（错误示例）：向动态分区插入数据时，动态分区列必须在SELECT列表中，否则会执行失败。错误命令示例如下： 

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

    返回结果如下：

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

  • 示例5（错误示例）：向动态分区插入数据时，不能仅指定低级子分区，而动态插入高级分区，否则会执行失败。错误命令示例如下： 

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

    返回结果如下：

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

  • 示例6：MaxCompute在向动态分区插入数据时，如果分区列的类型与对应SELECT中列的类型不严格一致，会进行隐式转换，命令示例如下： 

    --创建源表src。
CREATE TABLE src (c INT, d STRING) PARTITIONED BY (e INT);

--向源表src增加分区。
ALTER TABLE src ADD IF NOT EXISTS PARTITION (e=201312);

--向源表src追加数据。
INSERT INTO src PARTITION (e=201312) VALUES (1,100.1),(2,100.2),(3,100.3);

--创建目标表parttable。
CREATE TABLE parttable(a INT, b DOUBLE) PARTITIONED BY (p STRING);

--将源表src数据插入目标表parttable。
SET odps.sql.allow.fullscan=true; 
INSERT INTO parttable PARTITION (p) SELECT c, d, CURRENT_TIMESTAMP() FROM src;

--查询目标表parttable。
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 |
+------------+------------+------------+
    说明

    如果您的数据是有序的，动态分区插入会把数据随机打散，导致压缩率较低。推荐您使用Tunnel命令上传数据到动态分区，以获取较好的压缩率。使用该命令的详细示例请参见RDS迁移至MaxCompute实现动态分区。

上一篇：插入或覆写数据（INSERT INTO | INSERT OVERWRITE）下一篇：更新或删除数据（UPDATE | DELETE）
文档推荐