MaxCompute:Auto partition table based on time computing function

Syntax

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

Parameters

Examples

Example 1: Partition by day using a TIMESTAMP column (default partition column name)

Create an auto-partitioned table sale_detail with a TIMESTAMP column sale_time, partitioned by day:

CREATE TABLE IF NOT EXISTS sale_detail(
  shop_name    STRING,
  total_price  DOUBLE,
  sale_time    TIMESTAMP)
AUTO PARTITIONED BY (TRUNC_TIME(sale_time, 'day'));

The table contains four columns: shop_name, total_price, sale_time, and _pt_col_0_. The _pt_col_0_ column is the automatically generated partition column with STRING type.

Run DESC sale_detail; to view the table structure:

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$***                             |
| Project:                  ***                                    |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-06-26 11:21:55                                      |
| LastDDLTime:              2025-06-26 11:21:55                                      |
| LastModifiedTime:         2025-06-26 11:21:55                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| shop_name       | string     |       |                                             |
| total_price     | double     |       |                                             |
| sale_time       | timestamp  |       |                                             |
+------------------------------------------------------------------------------------+
| Partition Columns:                                                                 |
+------------------------------------------------------------------------------------+
| _pt_col_0_      | string     |                                                     |
+------------------------------------------------------------------------------------+

To view the partition generation expression for an AUTO PARTITION column, use SHOW CREATE TABLE.

Example 2: Partition by day with an explicit partition column name

Specify an explicit partition column name sale_date:

CREATE TABLE IF NOT EXISTS sale_detail2(
  shop_name    STRING,
  total_price  DOUBLE,
  sale_time    TIMESTAMP)
AUTO PARTITIONED BY (TRUNC_TIME(sale_time, 'day') AS sale_date);

Example 3: Simplified syntax for DATE columns

When the source column is DATE type and the truncation granularity is day, you can omit TRUNC_TIME:

-- Full syntax
CREATE TABLE IF NOT EXISTS sale_detail3(
  shop_name   STRING,
  total_price DOUBLE,
  sale_date   DATE)
AUTO PARTITIONED BY (TRUNC_TIME(sale_date, 'day'));

-- Simplified syntax (equivalent)
CREATE TABLE IF NOT EXISTS sale_detail3(
  shop_name   STRING,
  total_price DOUBLE,
  sale_date   DATE)
AUTO PARTITIONED BY (sale_date);

Filter on the generated partition column

Filtering directly on the partition column (sale_date) triggers partition pruning:

SELECT * FROM sale_detail2 WHERE sale_date > '2025-02-03';

-- Result: only partitions where sale_date > '2025-02-03' are scanned
+------------+-------------+---------------------+
| shop_name  | total_price | sale_time           |
+------------+-------------+---------------------+
| shanghai_shop | 404404.0 | 2025-02-28 01:50:30 |
| hangzhou_shop | 303303.0 | 2025-02-04 08:30:30 |
+------------+-------------+---------------------+

Filter on the source time column (without functions)

Filtering directly on the source time column (sale_time), without wrapping it in a function, also triggers partition pruning. The system maps the filter range to the corresponding partition boundaries:

SELECT * FROM sale_detail2 WHERE sale_time > '2025-02-04 08:00:00';

-- Result: only partitions containing matching sale_time values are scanned
+------------+-------------+---------------------+
| shop_name  | total_price | sale_time           |
+------------+-------------+---------------------+
| shanghai_shop | 404404.0 | 2025-02-28 01:50:30 |
| hangzhou_shop | 303303.0 | 2025-02-04 08:30:30 |
+------------+-------------+---------------------+

Filter using a function on the source time column

When you apply a function to the source time column in the filter condition, partition pruning works only if the function's truncation granularity matches the granularity used when the table was created. This is required because the system can map a filter condition to partition boundaries only when both express time at the same resolution.

Example 1: Filter using `TRUNC_TIME` or `DATETRUNC` (without a time zone parameter)

Both queries trigger partition pruning because the truncation granularity (day / dd) matches the day granularity used to create sale_detail2, and neither function is called with a time zone parameter:

SELECT * FROM sale_detail2 WHERE TRUNC_TIME(sale_time, 'day') > '2025-02-04';

SELECT * FROM sale_detail2 WHERE DATETRUNC(sale_time, 'dd') > '2025-02-04 00:00:00';

-- Result
+------------+-------------+---------------------+
| shop_name  | total_price | sale_time           |
+------------+-------------+---------------------+
| shanghai_shop | 404404.0 | 2025-02-28 01:50:30 |
+------------+-------------+---------------------+

Example 2: Filter using `CAST AS DATE` or `TO_DATE` (without a time zone parameter)

Both queries trigger partition pruning because sale_detail2 was created with day granularity:

SELECT * FROM sale_detail2 WHERE CAST(sale_time AS DATE) = '2025-02-04';

SELECT * FROM sale_detail2 WHERE TO_DATE(sale_time) = '2025-02-04';

-- Result
+------------+-------------+---------------------+
| shop_name  | total_price | sale_time           |
+------------+-------------+---------------------+
| hangzhou_shop | 303303.0 | 2025-02-04 08:30:30 |
+------------+-------------+---------------------+