MaxCompute:Ingestion-time partitioned tables

Syntax

Use the following syntax to create an ingestion-time partitioned table.

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table_name> (_partitiontime <data_type>, <col_name><data_type>... )
[COMMENT <table_comment>]
AUTO PARTITIONED BY (<auto_partition_expression> [AS <auto_partition_column_name>]);
TBLPROPERTIES('ingestion_time_partition'='true');

Parameters:

Examples

• Example 1

  This example creates an ingestion-time partitioned table named ingestion_sale_detail_hourly that is partitioned by the hour. The table definition specifies a pseudo-column named _partitiontime of the TIMESTAMP type. When data is written to the table, the system automatically obtains the data ingestion time. It then populates the _partitiontime pseudo-column based on the hourly granularity specified in the DDL statement. The value of the partition key column sale_time is then generated based on this result.

  -- After the table is created, the system automatically obtains the data ingestion time when data is written. It truncates _partitiontime by the hour, generates a partition key column named sale_time, and partitions the table based on this column.
  CREATE TABLE IF NOT EXISTS ingestion_sale_detail_hourly(
   shop_name     STRING,
   total_price   DOUBLE,
   _partitiontime TIMESTAMP)
  AUTO PARTITIONED BY (TRUNC_TIME(_partitiontime, 'hour') AS sale_time)
  TBLPROPERTIES('ingestion_time_partition'='true');

  The ingestion_sale_detail_hourly table created in the preceding example contains four columns. The sale_time column is an automatically generated partition key column of the STRING type. You can run the DESC ingestion_sale_detail_hourly; statement to view the table information. The following is a sample result:

  +------------------------------------------------------------------------------------+
  | Owner:                    ALIYUN$test****                                          |
  | Project:                   xxxxxxx                                                 |
  | Schema:                    default                                                 |
  | TableComment:                                                                      |
  +------------------------------------------------------------------------------------+
  | CreateTime:               2025-06-16 14:59:40                                      |
  | LastDDLTime:              2025-06-16 14:59:40                                      |
  | LastModifiedTime:         2025-06-16 14:59:40                                      |
  +------------------------------------------------------------------------------------+
  | InternalTable: YES      | Size: 0                                                  |
  +------------------------------------------------------------------------------------+
  | Native Columns:                                                                    |
  +------------------------------------------------------------------------------------+
  | Field           | Type       | Label | Comment                                     |
  +------------------------------------------------------------------------------------+
  | shop_name       | string     |       |                                             |                                        |
  | total_price     | double     |       |                                             |
  | _partitiontime  | timestamp  |       |                                             |
  +------------------------------------------------------------------------------------+
  | Partition Columns:                                                                 |
  +------------------------------------------------------------------------------------+
  | sale_time       | string     |                                                     |
  +------------------------------------------------------------------------------------+

• Example 2

  This example creates an ingestion-time partitioned table named ingestion_sale_detail_daily that is partitioned by the day. If the partition granularity is by day, an additional pseudo-column named _partitiondate of the DATE type is also generated, along with the _partitiontime pseudo-column.

  CREATE TABLE IF NOT EXISTS ingestion_sale_detail_daily(
   shop_name     STRING,
   total_price   DOUBLE,
   _partitiontime TIMESTAMP)
  AUTO PARTITIONED BY (TRUNC_TIME(_partitiontime, 'day') AS sale_date)
  TBLPROPERTIES('ingestion_time_partition'='true');

  Note

  • You can run the SELECT *, _partitiondate FROM ingestion_sale_detail_daily; command to view the _partitiondate pseudo-column.

  • To view the partition generation logic for the ingestion-time partition key column, you can use the SHOW CREATE TABLE statement.

Examples:

• Example 1

  When you insert data, do not specify a value for the _partitiontime pseudo-column.

  INSERT INTO ingestion_sale_detail_hourly (shop_name,total_price) 
  VALUES 
    ('shanghai_shop',10001.1), 
    ('chongqin_shop',20002.2), 
    ('hangzhou_shop',30003.3), 
    ('shenzhen_shop',40004.4);

• Example 2

  When you insert data, you can specify a value for the _partitiontime pseudo-column. The time granularity of the _partitiontime value must match the specified partition granularity, which is `hour`.

  • Valid example: Specify '2025-06-15 14:00:00' as the value for the _partitiontime column.

    INSERT INTO ingestion_sale_detail_hourly (shop_name,total_price,_partitiontime)
    VALUES 
      ('beijing_shop',50005.5,TIMESTAMP '2025-06-15 14:00:00'),
      ('chengdu_shop',60006.6,TIMESTAMP '2025-06-14 05:00:00');

  • Invalid example: Specify '2025-06-15 14:30:00' as the value for the _partitiontime column. This value does not match the hourly partition granularity.

    INSERT INTO ingestion_sale_detail_hourly (shop_name,total_price,_partitiontime)
    VALUES ('beijing_shop',50005.5,TIMESTAMP '2025-06-15 14:30:00');

    The following error message is returned:

    FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: SQL Runtime Unretryable Error: ODPS-0121095:Invalid argument - illegal ingestion time: 2025-06-15 14:30:00

• Example 3

  When the partition granularity is by day, you can specify a value only for the _partitiontime pseudo-column. You cannot specify a value for the _partitiondate pseudo-column.

  • Valid example: Specify '2025-06-18 00:00:00' as the value for the _partitiontime column.

    INSERT INTO ingestion_sale_detail_daily (shop_name,total_price,_partitiontime) 
    VALUES ('beijing_shop',50005.5,TIMESTAMP '2025-06-18 00:00:00');

  • Invalid examples:

    • Specify '2025-06-15 14:00:00' as the value for the _partitiontime column. This value does not match the daily partition granularity.

      INSERT INTO ingestion_sale_detail_daily (shop_name,total_price,_partitiontime) 
      VALUES ('beijing_shop',50005.5,TIMESTAMP '2025-06-18 14:00:00');

      The following error message is returned:

      FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: SQL Runtime Unretryable Error: ODPS-0121095:Invalid argument - illegal ingestion time: 2025-06-18 14:00:00

    • Directly specify a value for the _partitiondate pseudo-column.

      INSERT INTO ingestion_sale_detail_daily (shop_name,total_price,_partitiondate) 
      VALUES ('beijing_shop',50005.5,DATE '2025-06-15');

      The following error message is returned:

      FAILED: ODPS-0130071:[1,64] Semantic analysis exception - column _partitiondate cannot be resolved; Did you mean _partitiontime ?

Examples:

• Example 1

  When you run a SELECT * query, the system does not return the _partitiontime or _partitiondate pseudo-columns or the generated partition key column by default.

  SELECT *  FROM ingestion_sale_detail_hourly;
  
  -- The result is returned.
  +------------+-------------+
  | shop_name  |  total_price |
  +---------------+-------------+
  | shanghai_shop | 10001.1     |
  | chongqin_shop | 20002.2     |
  | hangzhou_shop | 30003.3     |
  | shenzhen_shop | 40004.4     |
  | chengdu_shop  | 60006.6     |
  | beijing_shop  | 50005.5     |
  +---------------+-------------+

• Example 2

  You can specify the _partitiontime or _partitiondate pseudo-columns and the generated partition key column in your query. In this example, sale_time and sale_date are the names of the partition key columns that were specified when the tables were created.

  Note

  In this example, the project's local time zone is set to `Asia/Shanghai` (UTC+8) by running the set odps.sql.timezone=Asia/Shanghai; command. The time zone affects the partition values generated by the `TRUNC_TIME` function.

  • Query the ingestion_sale_detail_hourly table, which is partitioned by the hour.

    SELECT * ,_partitiontime,sale_time FROM ingestion_sale_detail_hourly;
    
    --The following result is returned:
    +---------------+-------------+---------------------+---------------------+
    | shop_name     | total_price | _partitiontime      | sale_time           |
    +---------------+-------------+---------------------+---------------------+
    | shanghai_shop | 10001.1     | 2025-06-18 15:00:00 | 2025-06-18 07:00:00 |
    | chongqin_shop | 20002.2     | 2025-06-18 15:00:00 | 2025-06-18 07:00:00 |
    | hangzhou_shop | 30003.3     | 2025-06-18 15:00:00 | 2025-06-18 07:00:00 |
    | shenzhen_shop | 40004.4     | 2025-06-18 15:00:00 | 2025-06-18 07:00:00 |
    | beijing_shop  | 50005.5     | 2025-06-15 14:00:00 | 2025-06-15 06:00:00 |
    | chengdu_shop  | 60006.6     | 2025-06-14 05:00:00 | 2025-06-13 21:00:00 |
    +---------------+-------------+---------------------+---------------------+

  • Query the ingestion_sale_detail_daily table, which is partitioned by the day.

    SELECT * ,_partitiontime,_partitiondate,sale_date FROM ingestion_sale_detail_daily;
    
    --The following result is returned:
    +--------------+-------------+---------------------+----------------+-----------+
    | shop_name    | total_price | _partitiontime      | _partitiondate | sale_date |
    +--------------+-------------+---------------------+----------------+-----------+
    | beijing_shop | 50005.5     | 2025-06-18 00:00:00 | 2025-06-18     | 2025-06-18|
    +--------------+-------------+---------------------+----------------+-----------+