MaxCompute：基於資料寫入時間的自動分區表

文法結構

建立基於資料寫入時間的自動分區表。

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');

參數說明如下：

使用樣本

• 樣本1

  建立一張按小時分區，表名為ingestion_sale_detail_hourly的Ingestion Time Partition表，表中指定一個TIMESTAMP類型時間偽列_partitiontime。當資料寫入時，系統會自動擷取資料寫入時間，並根據DDL語句中按小時作為截取粒度產生_partitiontime列的值，然後基於計算結果產生表的分區sale_time。

  -- 表建立完成後，當資料寫入時系統會自動擷取資料寫入時間，按小時為粒度進行截取計算_partitiontime，並產生名為sale_time的分區列，並根據該分區列進行表的分區
  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');

  上述建立的ingestion_sale_detail_hourly表包含4列，其中 sale_time是自動產生的一個STRING類型的分區列。可執行DESC ingestion_sale_detail_hourly;語句查看錶資訊，返回結果樣本如下：

  +------------------------------------------------------------------------------------+
  | 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     |                                                     |
  +------------------------------------------------------------------------------------+

• 樣本2

  建立一張按天分區，表名為ingestion_sale_detail_daily的Ingestion Time Partition表。當表的分區產生截取粒度為按天時，除了_partitiontime之外，會額外產生一個DATE類型的偽列_partitiondate。

  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');

  說明

  • 可通過SELECT *, _partitiondate FROM ingestion_sale_detail_daily;命令查看偽列_partitiondate。

  • 如需查看Ingestion Time Partition分區列的分區產生計算邏輯，可以通過SHOW CREATE TABLE進行查看。

樣本如下：

• 樣本1

  插入資料時，不指定_partitiontime時間偽列的值。

  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);

• 樣本2

  插入資料時，指定 _partitiontime時間偽列的值，需保證寫入的_partitiontime列值與分區產生的指定時間截取粒度（hour）一致。

  • 正確樣本：指定_partitiontime列的值為'2025-06-15 14:00:00'。

    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');

  • 錯誤樣本：指定_partitiontime列的值為'2025-06-15 14:30:00'，與分區產生的按小時截取粒度不一致。

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

    返回的報錯資訊如下：

    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

• 樣本3

  當分區產生的截取粒度為按天時，仍然僅支援指定_partitiontime時間偽列的值，不支援指定 _partitiondate日期偽列值。

  • 正確樣本：指定_partitiontime列的值為'2025-06-18 00:00:00'。

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

  • 錯誤樣本：

    • 指定_partitiontime列的值為'2025-06-15 14:00:00'，與分區產生的按天截取粒度不一致。

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

      返回的報錯資訊如下：

      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

    • 直接指定_partitiondate偽列的值。

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

      返回的錯誤資訊如下：

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

樣本如下：

• 樣本1

  執行SELECT *的查詢語句時，系統預設不顯示_partitiontime或_partitiondate偽列及產生的分區列。

  SELECT *  FROM ingestion_sale_detail_hourly;
  
  -- 返回結果
  +------------+-------------+
  | 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     |
  +---------------+-------------+

• 樣本2

  查詢時指定顯示_partitiontime或_partitiondate偽列及產生的分區列，其中sale_time和sale_date是在建立該表時指定的產生分區列的列名。

  說明

  本樣本中通過set odps.sql.timezone=Asia/Shanghai;設定專案本地時區為東八區時區，在TRUNC_TIME截取產生的分區值時，會受到時區影響。

  • 查詢按小時分區的ingestion_sale_detail_hourly表。

    SELECT * ,_partitiontime,sale_time FROM ingestion_sale_detail_hourly;
    
    --返回結果如下
    +---------------+-------------+---------------------+---------------------+
    | 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 |
    +---------------+-------------+---------------------+---------------------+

  • 查詢按天分區的ingestion_sale_detail_daily表。

    SELECT * ,_partitiontime,_partitiondate,sale_date FROM ingestion_sale_detail_daily;
    
    --返回結果如下
    +--------------+-------------+---------------------+----------------+-----------+
    | shop_name    | total_price | _partitiontime      | _partitiondate | sale_date |
    +--------------+-------------+---------------------+----------------+-----------+
    | beijing_shop | 50005.5     | 2025-06-18 00:00:00 | 2025-06-18     | 2025-06-18|
    +--------------+-------------+---------------------+----------------+-----------+