This topic describes the basic syntax and provides examples on how to insert data in dynamic partition mode.

Background information

MaxCompute provides the following methods for you to insert data into a partitioned table by using the INSERT OVERWRITE statement:
  • Static partition mode: Specify a partition value in the statement.
  • Dynamic partition mode: Specify only the name of a partitioning column in the statement. The partitioning column value is provided in the SELECT clause. The system automatically inserts data into the required partitions based on the value.

Syntax

Syntax
INSERT OVERWRITE|INTO TABLE tablename PARTITION (partcol1, partcol2 ...) 
select_statement FROM from_statement;
Parameters
  • tablename

    The name of the destination table into which you want to insert data.

  • partcol1, partcol2 ...

    The names of partitioning columns in the destination table into which you want to insert data.

  • select_statement

    The statement used to query data in the source table. The fields in the select_statement clause provide the dynamic partition values in the destination table.

    If the destination table has only level-1 dynamic partitions, the value of the last field in the select_statement clause is the dynamic partition value in the destination table. In dynamic partition mode, the mapping between the fields in the select_statement clause and dynamic partitions in a destination table is determined by field locations, not by field names.

Usage notes

  • A maximum of 10,000 dynamic partitions can be generated by using the INSERT INTO statement. A maximum of 60,000 dynamic partitions can be generated by using the INSERT OVERWRITE statement.
  • An SQL statement that supports the dynamic partition feature in a distributed environment can generate a maximum of 512 dynamic partitions in a single process. If the number of dynamic partitions exceeds this limit, an exception occurs.
  • A dynamic partition value cannot be NULL or contain special characters. Otherwise, FAILED: ODPS-0123031:Partition exception - invalid dynamic partition value: province=xxx is returned.
  • If a destination table has multi-level partitions, you can specify some partitions as static partitions in an INSERT statement. However, the static partitions must be high-level partitions.
  • If a destination table is a hash clustering table, the dynamic partition feature is not supported.
  • In dynamic partition mode, the mapping between the fields in the select_statement clause and dynamic partitions in a destination table is determined by the field order, not by the column name. If the field order in a source table is different from that in a destination table, we recommend that you specify the fields in the select_statement clause based on the order in the destination table.
    For example, create destination table sale_detail_dypart.
    create table sale_detail_dypart like sale_detail; 
    • Execute the following statements to insert data from source table sale_detail into destination table 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;

      In these statements, sale_date in sale_detail determines the value of the sale_date dynamic partition in sale_detail_dypart, and region in sale_detail determines the value of the region dynamic partition in sale_detail_dypart.

    • Execute the following statements to insert data from sale_detail into 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;

      In these statements, region in sale_detail determines the value of the sale_date dynamic partition in sale_detail_dypart, and sale_date in sale_detail determines the value of the region dynamic partition in sale_detail_dypart.

Examples

  • Example 1: Insert data from a source table into a destination table. You can obtain the partitions generated based on the region field only after the required statement is executed.
    -- Create destination table total_revenues.
    create table total_revenues (revenue double) partitioned by (region string);
    
    -- Insert data from sale_detail to total_revenues.
    insert overwrite table total_revenues partition(region)
    select total_price as revenue, region from sale_detail;
  • Example 2: Insert data from a source table into a destination table. If the destination table has multi-level partitions, level-1 partition sale_date must be specified.
    insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
    select shop_name,customer_id,total_price,region from sale_detail;
  • Example 3: To insert data in dynamic partition mode, the dynamic partition column must be included in the SELECT clause. Otherwise, the execution fails.
    insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
    select shop_name,customer_id,total_price from sale_detail;
  • Example 4: If you specify only low-level sub-partitions when you insert data in dynamic partition mode, the insertion of data into high-level partitions will fail.
    insert overwrite table sales partition (region='china', sale_date)
    select shop_name,customer_id,total_price,sale_date from sale_detail;
  • Example 5: If the types of values in partitioning columns are inconsistent with those in the SELECT clause, an error is returned when data is inserted in dynamic partition mode in MaxCompute V1.0. MaxCompute V2.0 supports implicit conversion of data types.
    -- Create destination table parttable.
    create table parttable(a int, b double) partitioned by (p string);
    -- Insert data from source table src into parttable.
    insert into parttable partition(p) select key, value, current_timestmap() from src;
    -- Query data in parttable.
    select * from parttable;
    The following table lists the results.
    a b p
    0 NULL 2017-01-23 22:30:47.130406621
    0 NULL 2017-01-23 22:30:47.130406621
    Note If your data is ordered, it is randomly scattered when it is inserted in dynamic partition mode, which reduces the compression ratio. In this case, we recommend that you use Tunnel commands to upload the data to dynamic partitions to increase the compression ratio. For more information, see Migrate data from ApsaraDB RDS to MaxCompute based on dynamic partitioning.