基於時間計算函數的自動分區表 - MaxCompute

MaxCompute支援自動分區（AUTO PARTITION）表，自動分區表的分區列支援基於時間計算函數或基於資料寫入時間兩種方式自動產生。本文介紹基於時間計算函數產生的自動分區表的使用方式。

功能介紹

MaxCompute支援普通分區表和自動分區表（AUTO PARTITION）兩種分區表類型，可以根據不同情境下預期的分區列產生方式，選擇不同的分區表類型。詳細介紹請參考分區表概述。

相比於其他巨量資料產品支援基於日期/時間類型的列進行分區，或對其進行截取處理後進行分區，MaxCompute具備更靈活的分區能力。MaxCompute支援對錶中的時間/日期類型（DATE、DATETIME、TIMESTAMP、TIMESTAMP_NTZ）的資料列使用特定的時間計算函數，自動根據資料列計算後的結果產生分區列的值，從而實現表分區。

在此基礎上，MaxCompute也支援自動擷取資料寫入的時間，結合使用者指定的時間計算函數，根據計算後的結果產生分區列的值，對錶進行分區，詳細介紹請參考基於資料寫入時間的自動分區表。

使用限制

基於時間計算函數的自動分區表當前僅支援一個分區列，暫不支援多級分區。
基於時間計算函數的自動分區表的分區列，會按照表建立時指定的分區產生運算式自動產生，分區欄位類型預設為STRING，不支援修改。
基於時間計算函數和資料寫入時間的Auto Partition能力僅在基於MaxCompute SQL寫入時支援，不支援其他資料寫入方式如Flink Connector。

使用說明

建立基於時間計算函數的自動分區表

文法結構

建立基於時間計算函數的自動分區表。

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

參數說明

參數	是否必填	描述
table_name	是	待建立的表名。
col_name	是	列名。
data_type	是	列資料類型。
table_comment	否	表注釋。
auto_partition_expression	是	定義如何進行分區列計算的運算式。當前僅支援使用TRUNC_TIME函數來進行分區列的產生計算。說明當建立基於資料寫入時間的自動分區表時，系統會自動擷取資料寫入時間，並通過TRUNC_TIME函數對寫入時間進行截取，從而產生分區列的值。
auto_partition_column_name	否	產生分區列的列名，若未指定分區列的列名，則系統會以`_pt_col_0_`為預設列名，依次檢查表中是否存在該列名，若存在則自動遞增尾碼（如`_pt_col_1_`、`_pt_col_2_`等），直到找到未被佔用的列名為止。說明使用分區運算式進行分區計算後，會根據計算結果產生一個STRING類型的分區列，列的類型和列的取值均不支援直接操作。

使用樣本

樣本1：以天為粒度截取時間/日期類型的列資料計算並產生自動分區。

建立一張自動分區表 sale_detail，表中包含一個TIMESTAMP類型的時間資料列sale_time ，DDL語句中按天為粒度對該列資料進行截取計算，並基於計算後的結果產生AUTO PARTITION表的分區。

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

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

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

說明

如需查看AUTO PARTITION分區列的分區產生計算邏輯，可以通過SHOW CREATE TABLE查看。

樣本2：顯式指定產生的分區列的名稱。

建立一張自動分區表 sale_detail2，在樣本1基礎上增加了顯式指定的分區列名稱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);

樣本3：若TRUNC_TIME函數的輸入資料列為DATE資料類型，且按天截取計算產生分區列時，支援如下簡化寫法。

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

-- 上述sql語句可以簡化寫成
CREATE TABLE IF NOT EXISTS sale_detail3(
 shop_name     STRING,
 total_price   DOUBLE,
 sale_date    DATE )
AUTO PARTITIONED BY (sale_date);

重要

這種簡化寫法只支援DATE資料類型，不支援DATETIME/TIMESTAMP/TIMESTAMP_NTZ資料類型。

寫入基於時間計算函數的自動分區表

當使用者使用SQL語句向AUTO PARTITION表中寫入資料時，系統會根據使用者建立表時定義的分區自動產生計算運算式，再根據表中資料決定其對應的分區取值，不支援使用者顯式地指定分區取值。

樣本：將資料寫入基於時間計算函數建立的分區表sale_detail2。

-- 設定專案本地時區為東八區時區。
set odps.sql.timezone=Asia/Shanghai;

-- 同上文分區表sale_detail2的建立方式。
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');

在該樣本中，sale_time時間資料列的值，經過TRUNC_TIME計算後產生的分區值對應關係如下：

sale_time列-Asia/Shanghai時區	分區列sale_date值（對時間列截取到天后產生，代表UTC時間）
2025-02-04 01:15:30	2025-02-03
2024-03-29 15:30:30	2024-03-29
2025-02-04 08:30:30	2025-02-04
2025-02-28 01:50:30	2025-02-27

說明

注意，對於時區相關的資料類型如DATETIME/TIMESTAMP，TRUNC_TIME會將本地時間轉換為UTC時間再進行截斷。
本樣本中通過set odps.sql.timezone=Asia/Shanghai;設定專案本地時區為東八區時區，在TRUNC_TIME截取產生的分區值時，會受到時區影響。

查詢基於時間計算函數的自動分區表

樣本1：當執行`select *` 查詢的時候，預設不顯示產生分區列。

-- 開啟全表掃描 
set odps.sql.allow.fullscan=true;

SELECT * FROM sale_detail2;

-- 返回結果
+------------+-------------+------------+
| 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 | 
+------------+-------------+------------+

樣本2：若希望輸出產生的分區列，可執行`SELECT *,sale_date from sale_detail2;`，其中`sale_date`是在該表建立時指定的產生分區列的列名。

SELECT *,sale_date from sale_detail2;

-- 返回結果
+------------+-------------+------------+------------+
| 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 | 
+------------+-------------+------------+------------+

基於時間計算函數的自動分區表分區裁剪

對自動分區表進行查詢過濾時，僅在以下幾種情境中支援分區裁剪。

說明

可以通過Logview或使用EXPLAIN命令查看SQL執行計畫，判斷分區裁剪是否生效，參考文檔：分區剪裁合理性評估。

情境1：使用產生的分區列作為過濾條件，查詢語句執行時支援分區裁剪。

對於表sale_detail2，查詢時可以通過產生的分區列sale_date過濾資料。使用WHERE sale_date > '2025-02-03';過濾條件，此時僅掃描滿足過濾條件sale_date > '2025-02-03'的分區。

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

-- 返回結果
+------------+-------------+------------+
| 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 | 
+------------+-------------+------------+

情境2：使用產生分區列的時間/日期類型資料列（不對該列進行函數運算）作為過濾條件，查詢語句執行時支援分區裁剪。

對於表sale_detail2，查詢時可以通過表中的時間資料列sale_time過濾資料。使用WHERE sale_time > '2025-02-04 08:00:00';過濾條件，此時系統會判斷哪些分區資料滿足掃描 sale_time 的過濾條件，並僅掃描對應滿足條件的分區。

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

-- 返回結果
+------------+-------------+------------+
| 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 | 
+------------+-------------+------------+

情境3：對建表時產生分區列的時間/日期類型資料列進行Function Compute，並將計算結果作為過濾條件，目前在部分Function Compute情境支援分區裁剪。

支援分區裁剪的函數	分區裁剪生效條件
TRUNC_TIME	時間截取粒度與建表語句相同時，支援分區裁剪。
DATETRUNC	時間截取粒度與建表語句相同時，支援分區裁剪。
CAST AS DATE	建表時分區產生的截取粒度為天時，支援分區裁剪。
TO_DATE	建表時分區產生的截取粒度為天時，支援分區裁剪。

樣本1：對sale_time時間列使用TRUNC_TIME或不帶時區參數的DATETRUNC函數進行過濾，且時間截取粒度與建表語句相同，此時分區裁剪生效。

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

-- 返回結果
+------------+-------------+------------+
| shop_name  | total_price | sale_time  | 
+------------+-------------+------------+
| shanghai_shop | 404404.0    | 2025-02-28 01:50:30 | 
+------------+-------------+------------+

樣本2：對sale_time時間列使用CAST AS DATE進行過濾或對不帶時區參數的TO_DATE函數進行過濾，且建表時的截取粒度為天，此時分區裁剪生效。

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';
-- 返回結果
+------------+-------------+------------+
| shop_name  | total_price | sale_time  | 
+------------+-------------+------------+
| hangzhou_shop | 303303.0    | 2025-02-04 08:30:30 | 
+------------+-------------+------------+