本文为您介绍输出到动态分区的基本语法和使用示例。

背景信息

在使用INSERT OVERWRITE语句将数据插入到分区表时,MaxCompute提供了如下两种方式:
  • 输出到静态分区:在INSERT语句中直接指定分区值,将数据插入指定的分区。
  • 输出到动态分区:在INSERT语句中不直接指定分区值,只指定分区列名。分区列的值在SELECT子句中提供,系统自动根据分区字段的值将数据插入到相应分区。

动态分区语法

命令格式
INSERT OVERWRITE|INTO TABLE tablename PARTITION (partcol1, partcol2 ...) 
select_statement FROM from_statement;
参数说明
  • tablename

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

  • partcol1, partcol2 ...

    目标表分区列列名。

  • select_statement

    源表的查询语句。select_statement子句中的字段将提供目标表的动态分区值。

    如果目标表只有一级动态分区,则select_statement的最后一个字段值即为目标表的动态分区值。源表SELECT的值和输出分区的值的关系仅仅是通过位置来确定的,和字段名称没有关系。

注意事项

  • 使用INSERT INTO最多可以生成10000个动态分区,使用INSERT OVERWRITE最多可以生成60000个动态分区。
  • 分布式环境下,使用动态分区功能的SQL中,单个进程最多只能输出512个动态分区,否则会引发运行时异常。
  • 动态生成的分区值不允许为NULL,也不支持含特殊字符和中文,否则会引发运行时异常FAILED: ODPS-0123031:Partition exception - invalid dynamic partition value: province=xxx
  • 如果目标表有多级分区,在运行INSERT语句时允许指定部分分区为静态,但是静态分区必须是高级分区。
  • 如果目标表为Hash Clustering Table,则不支持动态分区。
  • 动态分区中,select_statement字段和目标表动态分区的对应是按字段顺序决定,并不是按照列名称决定的。当源表的字段与目标表字段顺序不一致时,建议您按照目标表顺序在select_statement语句中指定字段。
    例如,创建目标表sale_detail_dypart
    create table sale_detail_dypart like sale_detail; 
    • 执行如下语句将源表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;

      上述语句中,决定目标表sale_detail_dypart动态分区的字段sale_date为源表sale_detail的字段sale_date;决定目标表sale_detail_dypart动态分区的字段region为源表sale_detail的字段region

    • 执行如下语句将源表sale_detail中的数据插入到目标表sale_detail_dypart
      insert overwrite table sale_detail_dypart partition (sale_date, region)
      select shop_name,customer_id,total_price,region,sale_date from sale_detail;

      上述语句中,决定目标表sale_detail_dypart动态分区的字段sale_date为源表sale_detail的字段region;决定目标表sale_detail_dypart动态分区的字段region为源表sale_detail的字段sale_date

动态分区示例

  • 示例1:将源表中的数据插入到目标表中。在SQL运行之前,您无法得知会产生哪些分区。只有在语句运行结束后,才能通过region字段产生的值确定产生的分区。
    --创建目标表total_revenues。
    create table total_revenues (revenue double) partitioned by (region string);
    
    --将源表sale_detail中的数据插入到目标表total_revenues。
    insert overwrite table total_revenues partition(region)
    select total_price as revenue, region from sale_detail;
  • 示例2:将源表中的数据插入到目标表中。多级分区,指定一级分区sale_date
    insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
    select shop_name,customer_id,total_price,region from sale_detail;
  • 示例3:动态分区插入时,动态分区列必须在SELECT列表中,否则会执行失败,例如下面语句。
    insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
    select shop_name,customer_id,total_price from sale_detail;
  • 示例4:动态分区插入时,不能仅指定低级子分区,而动态插入高级分区,否则会执行失败,例如下面语句。
    insert overwrite table sales partition (region='china', sale_date)
    select shop_name,customer_id,total_price,sale_date from sale_detail;
  • 示例5:MaxCompute 1.0在进行动态分区时,如果分区列的类型与对应SELECT列表中列的类型不严格一致,会发生报错。MaxCompute 2.0则支持隐式类型转换,示例如下。
    --创建目标表parttable。
    create table parttable(a int, b double) partitioned by (p string);
    --将源表src数据插入目标表parttable。
    insert into parttable partition(p) select key, value, current_timestmap() from src;
    --查询目标表parttable。
    select * from parttable;
    返回结果如下。
    a b p
    0 NULL 2017-01-23 22:30:47.130406621
    0 NULL 2017-01-23 22:30:47.130406621
    说明 如果您的数据是有序的,动态分区插入会把数据随机打散,导致压缩率较低。推荐您使用Tunnel命令上传数据到动态分区,以获取较好的压缩率。使用该命令的详细示例请参见RDS迁移至MaxCompute实现动态分区