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

前提条件

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

功能介绍

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

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

使用限制

注意事项

命令格式

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 子句，表示数据来源。例如，源表名称。

使用示例

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

  --创建目标表total_revenues。
  create table total_revenues (revenue double) partitioned by (region string);
  --将源表sale_detail中的数据插入到目标表total_revenues。源表信息请参见插入或覆写数据（INSERT INTO | INSERT OVERWRITE）。
  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; 
  --指定一级分区，将数据插入目标表。
  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。
  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字段顺序。
  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       |
  +------------+-------------+-------------+------------+------------+

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

  insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
  select shop_name,customer_id,total_price from sale_detail;

• 示例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;

• 示例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。
  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      | 2020-11-25 15:13:28.686 |
  | 2          | 100.2      | 2020-11-25 15:13:28.686 |
  | 3          | 100.3      | 2020-11-25 15:13:28.686 |
  +------------+------------+------------+

  说明

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