在使用INSERT OVERWRITE语句将数据插入到一张分区表时,您可以在语句中指定分区的值,也可以在分区中指定一个分区列名(但不提供具体的值),并在SELECT子句中提供对应分区列的值。这种指定分区列名的数据插入方式即为输出到动态分区。本文为您介绍输出到动态分区的基本语法和使用示例。

动态分区语法

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

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

  • partcol1, partcol2 ...

    目标表分区列列名。

  • select_statement

    源表的查询语句。select_statement字段中,后面的字段将提供目标表动态分区值。如果目标表只有一级动态分区,则select_statement最后一个字段值即为目标表的动态分区值。

    说明
    • 在使用动态分区功能的SQL中,在分布式环境下,单个进程最多只能输出512个动态分区,否则会引发运行时异常。
    • 任意动态分区SQL不允许生成超过2000个动态分区,否则会引发运行时异常。
    • 动态生成的分区值不允许为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

说明 新创建的项目(project)默认禁止分区表全表扫描,即对该项目的分区表执行全表扫描语句时会返回失败,必须指定分区条件。因此,执行需要对分区表全表扫描的SQL语句时,要在SQL语句前加一个set语句set odps.sql.allow.fullscan=true;,执行时,set语句和SQL语句一起提交执行。

动态分区示例

  • 示例一
    --创建目标表total_revenues。
    create table total_revenues (revenue bigint) partitioned by (region string);
    
    --将源表sale_detail中的数据插入到目标表total_revenues。
    insert overwrite table total_revenues partition(region)
      select total_price as revenue, region from sale_detail;

    上述语句,在SQL运行之前,您无法得知会产生哪些分区。只有在select语句运行结束后,才能通过region字段产生的值确定会产生哪些分区。

  • 示例二:将源表中的数据插入到目标表中。多级分区,指定一级分区sale_date
    insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
      select shop_name,customer_id,total_price,region from sale_detail;
  • 示例三:动态分区插入时,动态分区列必须在select列表中,否则会执行失败,例如下面语句。
    insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
      select shop_name,customer_id,total_price from sale_detail;
  • 示例四 :动态分区插入时,不能仅指定低级子分区,而动态插入高级分区,否则会执行失败,例如下面语句。
    insert overwrite table sales partition (region='china', sale_date)
      select shop_name,customer_id,total_price,sale_date from sale_detail;
  • 示例五: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实现动态分区