Automate Partitioning and Optimize Queries with Ingestion-Time Tables - MaxCompute

Ingestion-time partitioned tables automatically assign incoming rows to partitions based on when they are written to MaxCompute. Instead of specifying a partition key manually, you declare a _partitiontime pseudo-column and a TRUNC_TIME expression. MaxCompute captures the write timestamp, truncates it to the chosen granularity (hour, day, month, or year), and generates the partition key column for you.

For example, if you create a table partitioned by the hour and write rows at these times:

Data ingestion time (UTC+8)	Generated `_partitiontime` (TIMESTAMP)	Generated partition key (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

TRUNC_TIME converts the local timestamp to UTC before truncating, so the STRING partition key reflects UTC time. See Timezone behavior for details.

For an overview of all partitioned table types in MaxCompute, see Partitioned table overview.

Limitations

Only a single partition column is supported. Multi-level partitioning is not supported.
The partition column always has the STRING data type. This cannot be changed.
Auto-partitioning based on ingestion time works only for MaxCompute SQL writes. Other ingestion methods, such as the Flink connector, are not supported.
Only the TRUNC_TIME function is supported in the AUTO PARTITIONED BY expression.

Timezone behavior

TRUNC_TIME converts the ingestion timestamp from the project's local time zone to UTC before truncating. The STRING partition key value is always in UTC, even if your project runs in a different time zone.

Important

Set your project's time zone to UTC to avoid unexpected partition key values: If your project uses a non-UTC time zone (for example, Asia/Shanghai, UTC+8), the truncated UTC value differs from the local time by the UTC offset. The query examples in this topic use Asia/Shanghai to illustrate this effect — this is why _partitiontime and the partition key column show an 8-hour difference in the results.

SET odps.sql.timezone=UTC;

Create an ingestion-time partitioned table

Syntax

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

Parameter	Required	Description
`table_name`	Yes	The name of the table to create.
`_partitiontime`	Yes	A pseudo-column that stores the truncated ingestion timestamp. The column name is fixed and cannot be changed. Supported data types: `TIMESTAMP` and `TIMESTAMP_NTZ`.
`col_name`	Yes	A regular column name.
`data_type`	Yes	The data type of the column.
`table_comment`	No	A comment for the table.
`TBLPROPERTIES('ingestion_time_partition'='true')`	Yes	Marks the table as an ingestion-time partitioned table.
`auto_partition_expression`	Yes	The expression that derives the partition key. Only `TRUNC_TIME` is supported. MaxCompute automatically captures the ingestion time and passes it to `TRUNC_TIME` to generate the partition key value.
`auto_partition_column_name`	No	The name for the generated partition column. Defaults to `_pt_col_0_`. If that name is already taken, the system uses `_pt_col_1_`, `_pt_col_2_`, and so on. The generated column has the STRING data type and cannot be modified directly.

Examples

Example 1: Partition by hour

The following statement creates ingestion_sale_detail_hourly. When data is written, MaxCompute truncates the ingestion time to the hour, populates _partitiontime, and stores the UTC result as the sale_time partition column.

-- The system captures the write time, truncates it to the hour,
-- and stores the UTC value as the sale_time partition key.
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');

Run DESC ingestion_sale_detail_hourly; to inspect the table. The output shows sale_time as a STRING partition column:

+------------------------------------------------------------------------------------+
| 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: Partition by day

When the granularity is day, MaxCompute also generates a _partitiondate pseudo-column of the DATE type alongside _partitiontime.

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

To view _partitiondate, explicitly include it in the query: SELECT *, _partitiondate FROM ingestion_sale_detail_daily; To inspect the partition generation logic, use the SHOW CREATE TABLE statement.

Write data to an ingestion-time partitioned table

All examples below write to the ingestion_sale_detail_hourly and ingestion_sale_detail_daily tables created in Create an ingestion-time partitioned table.

If you omit _partitiontime from the INSERT statement, MaxCompute uses the current write time. If you provide a value, it must be truncated to the same granularity defined in the CREATE TABLE statement.

Example 1: Let the system assign the partition

Omit _partitiontime. MaxCompute uses the current write timestamp and truncates it to the hour.

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: Specify the ingestion time explicitly

The value must be truncated to the declared granularity (hourly in this case).

Valid — 2025-06-15 14:00:00 is on an hour boundary:

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 — 2025-06-15 14:30:00 is not on an hour boundary:

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

Error 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: Daily-partitioned table — specify `_partitiontime` only

For daily-partitioned tables, provide _partitiontime only. _partitiondate cannot be specified in an INSERT statement.

Valid — 2025-06-18 00:00:00 is the start of the day:

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

Invalid — 2025-06-18 14:00:00 is not the start of the day:

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

Error 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

Invalid — specifying _partitiondate directly:

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

Error returned:

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

Query an ingestion-time partitioned table

All examples below query the tables created in Create an ingestion-time partitioned table.

Two behaviors to keep in mind:

SELECT * does not return _partitiontime, _partitiondate, or the generated partition column. Specify them explicitly to include them in the result.

Use _partitiontime in WHERE clauses to trigger partition pruning. The following filter expressions trigger partition pruning on ingestion-time partitioned tables: For the full list of supported pruning expressions, see Auto-partitioned tables based on time functions.

Granularity	Example filter
Hour	`WHERE _partitiontime >= TIMESTAMP '2025-06-18 07:00:00' AND _partitiontime < TIMESTAMP '2025-06-18 08:00:00'`
Month	`WHERE _partitiontime >= TIMESTAMP '2025-06-01 00:00:00' AND _partitiontime < TIMESTAMP '2025-07-01 00:00:00'`
Year	`WHERE _partitiontime >= TIMESTAMP '2025-01-01 00:00:00' AND _partitiontime < TIMESTAMP '2026-01-01 00:00:00'`

Example 1: Basic SELECT — pseudo-columns are hidden by default

SELECT * FROM ingestion_sale_detail_hourly;

-- Result:
+---------------+-------------+
| 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: Include pseudo-columns and partition columns explicitly

The project time zone is set to Asia/Shanghai (UTC+8). Because TRUNC_TIME converts to UTC before truncating, sale_time (the partition key) is 8 hours behind _partitiontime.

Query the hourly-partitioned table:

SELECT *, _partitiontime, sale_time FROM ingestion_sale_detail_hourly;

-- Result:
+---------------+-------------+---------------------+---------------------+
| 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 daily-partitioned table:

SELECT *, _partitiontime, _partitiondate, sale_date FROM ingestion_sale_detail_daily;

-- Result:
+--------------+-------------+---------------------+----------------+-----------+
| shop_name    | total_price | _partitiontime      | _partitiondate | sale_date |
+--------------+-------------+---------------------+----------------+-----------+
| beijing_shop | 50005.5     | 2025-06-18 00:00:00 | 2025-06-18     | 2025-06-18|
+--------------+-------------+---------------------+----------------+-----------+

MaxCompute:Ingestion-time partitioned tables

Limitations

Timezone behavior

Create an ingestion-time partitioned table

Syntax

Examples

Write data to an ingestion-time partitioned table

Query an ingestion-time partitioned table

See also