MaxCompute:Auto partition table based on time computing function

Syntax

Create an automatic partition table based on time computing functions.

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: Truncate time/date type column data by day to calculate and generate automatic partitions.

  Create an automatic partition table sale_detail that includes a TIMESTAMP type time data column sale_time. In the DDL statement, truncate this column data by day, and generate AUTO PARTITION table partitions based on the calculation results.

  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 sale_detail auto-partitioned table created by the statement contains four columns: shop_name, total_price, sale_time, and _pt_col_0_. The _pt_col_0_ column is the automatically generated partition column, and its data type is STRING. You can run the DESC sale_detail; statement to view the table information. The following is an example of the result:

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

  Note

  To view the partition generation calculation logic of the AUTO PARTITION column, you can use SHOW CREATE TABLE.

• Example 2: Explicitly specify the name of the generated partition column.

  Create an automatic partition table sale_detail2, adding an explicitly specified partition column name sale_date based on Example 1.

  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: If the input data column of the TRUNC_TIME function is of DATE data type and partitioned by day, the following simplified syntax is supported.

  CREATE TABLE IF NOT EXISTS sale_detail3(
   shop_name     STRING,
   total_price   DOUBLE,
   sale_date    DATE )
  AUTO PARTITIONED BY (TRUNC_TIME(sale_date , 'day'));
  
  -- The above SQL statement can be simplified to
  CREATE TABLE IF NOT EXISTS sale_detail3(
   shop_name     STRING,
   total_price   DOUBLE,
   sale_date    DATE )
  AUTO PARTITIONED BY (sale_date);

  Important

  This simplified syntax only supports DATE data type, not DATETIME/TIMESTAMP/TIMESTAMP_NTZ data types.

Example: Write data to the partition table sale_detail2 created based on time computing functions.

-- Set the project local time zone to East Eight Zone.
set odps.sql.timezone=Asia/Shanghai;

-- Same as the creation method of the partition table sale_detail2 above.
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);


INSERT INTO sale_detail2 VALUES
  ('chongqin_shop',101101,timestamp '2025-02-04 01:15:30'),
  ('shenzhen_shop',202202,timestamp '2024-03-29 15:30:30'),
  ('hangzhou_shop',303303,timestamp '2025-02-04 08:30:30'),
  ('shanghai_shop',404404,timestamp '2025-02-28 01:50:30');
  

In this example, the correspondence between the values of the sale_time time data column and the partition values generated after TRUNC_TIME calculation is as follows:

• Example 1: When executing select * query, the generated partition column is not displayed by default.

  -- Enable full table scan 
  set odps.sql.allow.fullscan=true;
  
  SELECT * FROM sale_detail2;
  
  -- Result
  +------------+-------------+------------+
  | shop_name  | total_price | sale_time  | 
  +------------+-------------+------------+
  | hangzhou_shop | 303303.0    | 2025-02-04 08:30:30 | 
  | shanghai_shop | 404404.0    | 2025-02-28 01:50:30 | 
  | shenzhen_shop | 202202.0    | 2024-03-29 15:30:30 | 
  | chongqin_shop | 101101.0    | 2025-02-04 01:15:30 | 
  +------------+-------------+------------+

• Example 2: To display the generated partition column in the output, run SELECT *,sale_date from sale_detail2;. The sale_date column is the name of the partition column that was specified when the table was created.

  SELECT *,sale_date from sale_detail2;
  
  -- Result
  +------------+-------------+------------+------------+
  | shop_name  | total_price | sale_time  | sale_date  | 
  +------------+-------------+------------+------------+
  | shanghai_shop | 404404.0    | 2025-02-28 01:50:30 | 2025-02-27 | 
  | hangzhou_shop | 303303.0    | 2025-02-04 08:30:30 | 2025-02-04 | 
  | shenzhen_shop | 202202.0    | 2024-03-29 15:30:30 | 2024-03-29 | 
  | chongqin_shop | 101101.0    | 2025-02-04 01:15:30 | 2025-02-03 | 
  +------------+-------------+------------+------------+

Partition pruning for automatic partition tables based on time computing functions

When querying automatic partition tables, partition pruning is supported only in the following scenarios.

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

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

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

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