基于数据写入时间的自动分区表

MaxCompute支持在数据写入时自动获取写入时间,并结合用户指定的时间计算函数(TRUNC_TIME),根据计算结果生成分区列的值,从而实现对表的分区。这种基于数据写入时间的自动分区表又称为Ingestion Time Partition表,本文将介绍基于数据写入时间的自动分区表的使用方法。

功能介绍

MaxCompute支持普通分区表和自动分区表(AUTO PARTITION)两种分区表类型,您可根据不同场景下预期的分区列生成方式,选择不同的分区表类型。详情请参见分区表概述

使用Ingestion Time Partition表时,需要指定一个名为_partitiontime、数据类型为时间类型的列。系统会按照一定的时间粒度,对每行数据的写入时间进行截取(如:按小时、按天、按月、按年),然后自动生成_partitiontime列值。

例如,用户创建了一个按小时截取生成分区的Ingestion Time Partition表,并在以下时间写入数据:

数据写入时间(UTC+8)

生成的 _PARTITIONTIME列(TIMESTAMP类型)

生成的分区(STRING类型)

2025-06-18 17:22:00

2025-06-18 17:00:00

2025-06-18 09:00:00

2025-06-18 17:40:00

2025-06-18 17:00:00

2025-06-18 09:00:00

2025-06-18 18:31:00

2025-06-18 18:00:00

2025-06-18 10:00:00

上述示例中,Ingestion Time Partition表指定按小时截取生成分区,因此,_partitiontime列会根据数据写入时间进行截取并生成对应的值,并将该值转换为UTC时间后的STRING类型值,作为数据所在的分区。

使用限制

  • 基于数据写入时间的自动分区表当前仅支持一个分区列,且不支持多级分区。

  • 基于数据写入时间的自动分区表的分区列,字段类型默认为STRING,不支持修改。

  • 按照数据写入时间进行截断时,当前仅支持TRUNC_TIME函数,该函数会将本地时间转换为UTC时间再进行截断。因此最终生成的STRING类型分区列的值会受到时区影响。建议执行set odps.sql.timezone=UTC;语句调整您项目的本地时区为UTC时区。

使用说明

创建Ingestion Time Partition

语法结构

创建基于数据写入时间的自动分区表。

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

参数说明如下:

参数

是否必填

描述

table_name

待创建的表名。

_partitiontime

按数据写入时间截取生成的时间伪列,列名不支持修改,数据类型仅支持TIMESTAMP、TIMESTAMP_NTZ。

col_name

列名。

data_type

列数据类型。

table_comment

表注释。

TBLPROPERTIES('ingestion_time_partition'='true')

指定所创建的表为基于数据写入时间的自动分区表(Ingestion Time Partition表),按数据写入时间生成分区列。

auto_partition_expression

定义如何进行分区列计算的表达式。当前仅支持使用TRUNC_TIME函数计算_partitiontime时间伪列,以生成分区列。

说明

当创建基于数据写入时间的自动分区表时,系统会自动获取数据写入时间,并通过TRUNC_TIME函数对写入时间进行截取,从而生成分区列的值。

auto_partition_column_name

生成分区列的列名,若未指定分区列的列名,则系统会以_pt_col_0_为默认列名,依次检查表中是否存在该列名,若存在则自动递增后缀(如_pt_col_1__pt_col_2_等),直到找到未被占用的列名为止。

说明

使用分区表达式进行分区计算后,会根据计算结果生成一个STRING类型的分区列,列的类型和列的取值均不支持直接操作

使用示例

  • 示例1

    创建一张按小时分区,表名为ingestion_sale_detail_hourlyIngestion 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_dailyIngestion 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进行查看。

写入Ingestion Time Partition

当您使用SQL语句向Ingestion Time Partition表中写入数据时,支持指定_partitiontime时间伪列的值,但需保证_partitiontime列值的时间截取粒度与建表语句中定义的一致。

若不指定_partitiontime,则系统会自动获取数据写入MaxCompute的时间,结合用户指定的时间计算函数,根据计算结果生成_partitiontime时间伪列和分区列的值,对数据进行分区。

示例如下:

说明

下述示例基于使用示例中已创建的ingestion_sale_detail_hourlyingestion_sale_detail_daily表,进行数据写入。

  • 示例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 ?

查询Ingestion Time Partition

  • 执行SELECT *的查询语句时,系统默认不显示_partitiontime_partitiondate伪列及生成的分区列。若您希望显示,则需在语句中显式指定对应的列,例如SELECT *, _partitiontime FROM xxx;

  • 对于按小时、按月、按年粒度进行截取生成分区的表,可以使用时间类型的伪列_partitiontime进行查询过滤和分区裁剪。Ingestion Time Partition表作为一种特殊的自动分区表,支持的分区裁剪能力与普通自动分区表一致,详情请参见基于时间计算函数的自动分区表

示例如下:

说明

下述示例基于使用示例中已创建的ingestion_sale_detail_hourlyingestion_sale_detail_daily表,进行数据查询。

  • 示例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_timesale_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|
      +--------------+-------------+---------------------+----------------+-----------+