全部產品
Search

搜尋本產品

    MaxCompute：Time travel查詢與增量查詢

    文件中心

    MaxCompute：Time travel查詢與增量查詢

    更新時間：Dec 06, 2024

    對於Delta Table類型的表，MaxCompute支援查詢回溯到源表某個歷史時間或者版本進行歷史Snapshot查詢（Time travel查詢），也支援指定源表某個歷史時間區間或者版本區間進行歷史增量查詢（Incremental查詢）。本文為您介紹Delta Table的查詢使用說明和使用限制。

    命令格式

    [WITH <cte>[, ...] ]
SELECT [ALL | DISTINCT] <select_expr>[, <except_expr>)][, <replace_expr>] ...
       FROM <table_reference>
       [TIMESTAMP | VERSION AS OF expr]
       [TIMESTAMP | VERSION BETWEEN start_expr AND end_expr] 
       [WHERE <where_condition>]
       [GROUP BY {<col_list> | ROLLUP(<col_list>)}]
           [HAVING <having_condition>]
       [ORDER BY <order_condition>]
       [DISTRIBUTE BY <distribute_condition> [SORT BY <sort_condition>]|[ CLUSTER BY <cluster_condition>] ]
       [LIMIT <number>]
       [WINDOW <window_clause>]
    說明

    SQL DQL文法基本支援了查詢Delta Table的所有情境，並基本遵循MaxCompute DQL文法以及對應的約束。只增加了From Table子句的文法增強，可以通過固定格式的運算式指定源表某個歷史時間或者版本進行歷史Snapshot查詢，也可以指定源表某個歷史時間區間或者版本區間進行歷史增量查詢。

    Time travel查詢參數與使用限制

    Time travel查詢Delta Table，回溯到源表某個歷史時間或者版本進行歷史Snapshot查詢。您可通過[TIMESTAMP | VERSION AS OF expr]來配置具體的查詢方式。

    TIMESTAMP AS OF expr

    • 參數說明

      • TIMESTAMP AS OF為固定文法格式，代表根據歷史時間查詢歷史snapshot資料 。

      • expr的取實值型別可為MaxCompute標準的TIMESTAMP、DATETIME或DATE類型，目前可支援的形式有：

        • 日期文字常量：

          TIMESTAMP、DATETIME和DATE類型的字串常量，樣本如下。

          資料類型

          樣本

          TIMESTAMP

          '2023-01-01 00:00:00.123'

          DATETIME

          '2023-01-01 00:00:00'

          DATE

          '2023-01-01'

        • MaxCompute內建時間函數：current_timestamp()、getDate() + N: current_timestamp()和getDate() 。

          其中N的單位是秒，N為負數，表示目前時間往前N秒。N為正數，表示目前時間往後N秒。

        • Delta Table特殊文法：get_latest_timestamp(string tablename [, bigint <number>])。

          • 如果是跨Project訪問，tablename需寫為ProjectName.TableName。

          • 如果是三層模型，tablename需寫為ProjectName.SchemaName.TableName。

          • number可不填，預設值為1，表示時間從後往前序列中第number次資料操作的Commit時間，比如1表示最後一次操作，其中資料操作包括使用者側主動發送的資料修改操作和系統內部發起的資料排布操作。不同的number參數返回的TIMESTAMP可能相同。

    • 使用限制

      • 查詢的歷史快照資料範圍為[CreateTableTimestamp, expr]，比較對象為每次DML操作產生的Commit時間，CreateTableTimestamp為表建立操作產生的Commit時間。

      • expr返回的時間早於Time travel時間周期（即建立Delta Table時配置的acid.data.retain.hours），或早於Delta Table表建立時間，會直接報錯，因為對應的歷史資料狀態可能不存在了，比如acid.data.retain.hours=72小時，expr為72小時之前的時間，就會報錯。

      • expr返回的時間如果正好處於Time travel時間周期（即建立Delta Table時配置的acid.data.retain.hours）的下限，由於內部系統之間互動也有延時，所以有機率出現秒級的誤差，導致報錯，所以盡量不要使用類似於 (TIMESTAMP AS OF current_timestamp() - time travel時間周期) 的文法，容易觸發報錯。

    VERSION AS OF expr

    • 參數說明

      • VERSION AS OF 為固定文法格式，代表根據歷史資料操作的版本號碼（version）查詢歷史Snapshot資料。

      • expr傳回型別為MaxCompute的bigint整型，目前可支援的形式有：

        • 整型常量：

          比如常量3。

        • Delta Table特殊文法：get_latest_version(string tablename [, bigint <number>])

          • 如果是跨Project訪問，tablename需寫為ProjectName.TableName。

          • 如果是三層模型，tablename需寫為ProjectName.SchemaName.TableName。

          • number可不填，預設值為1，表示時間從後往前序列中第number次資料操作的版本號碼，比如1表示最後一次操作，其中資料操作包括使用者側主動發送的資料修改操作和系統內部發起的資料排布操作。不同的number參數返回的VERSION不相同。

    • 使用限制

      • 每次DML操作會產生嚴格遞增的version，您可通過SHOW HISTORY FOR TABLE/PARTITION顯示所有DML操作資訊，從中擷取對應操作的version。

      • 查詢的歷史快照資料範圍為[CreateTableVersion, expr]，比較對象為每次DML操作對應的version。CreateTableVersion為表建立操作產生的version，預設為1。

      • expr返回的version，系統會擷取它對應的DML Commit時間，如果早於配置的Time travel時間周期（即建立Delta Table時配置的acid.data.retain.hours），或者version值小於1，會直接報錯。

      • expr返回的version大於最近一次DML操作的version，直接報錯，推薦通過get_latest_version函數來擷取所需的版本號碼。

    Incremental查詢參數與使用限制

    Incremental查詢Delta Table，指定源表某個歷史時間區間或者版本區間進行歷史增量查詢。您可通過[TIMESTAMP | VERSION BETWEEN start_expr AND end_expr]來配置具體的查詢方式。

    TIMESTAMP BETWEEN start_expr AND end_expr

    • 參數說明

      • TIMESTAMP BETWEEN AND：為固定文法格式，代表根據歷史時間區間查詢歷史增量資料。

      • start_expr和end_expr用法以及約束同TIMESTAMP AS OF文法的EXPR保持一致。

    • 使用限制

      • 查詢的歷史增量資料範圍為(start_expr，end_expr]，即左開右閉區間，比較對象為每次DML操作產生的Commit時間。

      • start_expr早於配置的Time travel時間周期（acid.data.retain.hours），或者早於表建立時間，會直接報錯。

      • end_expr晚於最近一次DML操作的Commit時間時，查詢行為結果根據表屬性（acid.incremental.query.out.of.time.range.enabled）的取值來決定：

        • 設定為false（預設值）時，會直接報錯。

        • 設定為true時，會查詢包含(start_expr，end_expr]範圍內的所有增量歷史資料。

          您可通過ALTER TABLE修改此參數的取值，例如：

          ALTER TABLE mf_tt2 SET tblproperties("acid.incremental.query.out.of.time.range.enabled"="true");

    VERSION BETWEEN start_expr AND end_expr

    • 參數說明

      • VERSION BETWEEN AND為固定文法格式，代表根據歷史DML操作的version區間查詢歷史增量資料。

      • start_expr和end_expr用法以及約束同VERSION AS OF文法的expr保持一致，參考上面描述。

    • 使用限制

      • 查詢的歷史增量資料範圍是（start_expr，end_expr]，即左開右閉區間，比較對象為每次DML操作產生的version。

      • start_expr返回的version，系統會擷取它對應的DML Commit時間，如果早於配置的Time travel時間周期（acid.data.retain.hours）或者version值小於1，會直接報錯。

      • end_expr返回的version晚於最近一次DML操作的version的行為通過表屬性（acid.incremental.query.out.of.time.range.enabled）來決定，預設值為false，會直接報錯。如果設定為true，會查詢包含（start_expr，end_expr]範圍內的所有增量歷史資料。

    其他使用說明

    • 相同Key的多行記錄，只返回最近的一行記錄，如果最後一行是Delete狀態，直接過濾掉。後續版本考慮支援類似CDC格式的資料更新狀態查詢。

    • 不支援表對象不存在的錶的歷史資料查詢，例如對錶進行Drop、Rename等刪除操作。

      此種情況下您可先通過Restore操作恢複表對象，然後再繼續查詢。

    • 目前只支援Delta Table的Time travel/Incremental查詢，其他表不支援。

    • 同一個SQL中的同一張表，對於Time travel和Incremental查詢，timestamp或者version必須相同。

    • 分區表查詢最好指定partition，避免查詢所有partition的歷史動作記錄導致耗時不穩定。

    • 讀寫並發事務採用MVCC模型，可保障讀寫隔離，相互不影響，支援ReadCommited層級。

    • Compaction操作產生資料不認為是新增資料，因此增量查詢出來的資料不會包含。

    使用樣本

    • 樣本資料。

      --建立表操作，產生的version為1，執行show history for table mf_tt2, 可查詢version;
CREATE TABLE mf_tt2 ( 
  pk bigint NOT NULL PRIMARY KEY,    
  val bigint NOT NULL) 
PARTITIONED BY (dd string, hh string) 
tblproperties ("transactional"="true"); 

--DML操作，產生的version為2
INSERT OVERWRITE TABLE mf_tt2 PARTITION (dd='01', hh='01') VALUES (1, 1), (2, 2), (3, 3);
                 
--DML操作，產生的version為3
INSERT INTO TABLE mf_tt2 PARTITION (dd='01', hh='01') VALUES (3, 30), (4, 4), (5, 5);

    • 表相關資料查詢。

      • 查詢表建立時間，作為後續設定查詢歷史時間的參考。

        DESC EXTENDED mf_tt2;

        返回結果如下。

        +------------------------------------------------------------------------------------+
| Owner: ALIYUN$****_doctest@test.aliyunid.com | Project: doc_test_prod                               |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2023-06-26 09:31:38                                      |
| LastDDLTime:              2023-06-26 09:31:38                                      |
| LastModifiedTime:         2023-06-26 09:32:31                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 8541                                               |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
+------------------------------------------------------------------------------------+
| pk       | bigint |       |               | false    | NULL         |              |
| val      | bigint |       |               | false    | NULL         |              |
+------------------------------------------------------------------------------------+
| Partition Columns:                                                                 |
+------------------------------------------------------------------------------------+
| dd              | string     |                                                     |
| hh              | string     |                                                     |
+------------------------------------------------------------------------------------+
| Extended Info:                                                                     |
+------------------------------------------------------------------------------------+
| TableID:                  bec515a56cc9492c8f906a224c62****                         |
| IsArchived:               false                                                    |
| PhysicalSize:             25623                                                    |
| FileNum:                  9                                                        |
| StoredAs:                 AliOrc                                                   |
| CompressionStrategy:      normal                                                   |
| ClusterType:              hash                                                     |
| BucketNum:                16                                                       |
| ClusterColumns:           [pk]                                                     |
| SortColumns:              [pk ASC]                                                 |
+------------------------------------------------------------------------------------+

      • 查詢歷史資料操作的版本號碼。

        SHOW HISTORY FOR TABLE  mf_tt2 PARTITION (dd='01',hh='01');

        返回結果如下。

        ID = 20230626021756157ghict5k****
ObjectType      ObjectId                                ObjectName              VERSION(LSN)            Time                    Operation
PARTITION       4764c8e1cb634a4fb9c21f3fc850****        dd=01/hh=01             0000000000000002        2023-06-26 09:31:56     CREATE
PARTITION       4764c8e1cb634a4fb9c21f3fc850****        dd=01/hh=01             0000000000000003        2023-06-26 09:32:32     APPEND

    • Time travel查詢樣本。

      • 查詢截止到指定時間（例如datetime格式的字串常量）的所有歷史資料。

        SELECT * FROM mf_tt2 TIMESTAMP AS OF '2023-06-26 09:33:00' WHERE dd = '01' AND hh = '01';

        返回結果如下。

        +------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

      • 查詢截止到指定version常量的所有歷史資料。

        SELECT * FROM mf_tt2 VERSION AS OF 2 WHERE dd = '01' AND hh = '01';

        返回結果如下。

        +------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

      • 查詢截止到目前時間的所有歷史資料，即全量查詢。

        SELECT * FROM mf_tt2 TIMESTAMP AS OF current_timestamp() WHERE dd = '01' AND hh = '01';

        返回結果如下。

        +------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

      • 查詢截止到10s前的所有歷史資料。

        SELECT * FROM mf_tt2 TIMESTAMP AS OF current_timestamp() - 10 WHERE dd = '01' AND hh = '01';

        返回結果如下。

        +------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

      • 查詢截止到最近第二次Commit時間的所有歷史資料。

        SELECT * FROM mf_tt2 TIMESTAMP AS OF get_latest_timestamp('mf_tt2', 2) WHERE dd = '01' AND hh = '01';

        返回結果如下。

        +------------+------------+------------+------------+
| pk         | val        | dd         | hh         |
+------------+------------+------------+------------+
| 1          | 1          | 01         | 01         |
| 3          | 3          | 01         | 01         |
| 2          | 2          | 01         | 01         |
+------------+------------+------------+------------+

      • 查詢截止到最近第二次操作的版本的所有歷史資料。

        SELECT * FROM mf_tt2 VERSION AS OF get_latest_version('mf_tt2', 2) WHERE dd = '01' AND hh = '01';

        返回結果如下。

        +------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

    • Incremental查詢樣本。

      • 查詢指定時間（例如datetime格式的字串常量）區間的歷史增量資料，常量值需要根據具體操作的時間來配置。

        SELECT * FROM mf_tt2 TIMESTAMP BETWEEN '2023-06-26 09:31:40' AND '2023-06-26 09:32:00' WHERE dd = '01' AND hh = '01';

        返回結果如下。

        +------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 3          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

      • 查詢指定version區間的歷史增量資料。

        SELECT * FROM mf_tt2 version BETWEEN 2 AND 3 WHERE dd = '01' AND hh = '01';

        返回結果如下。

        +------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
+------------+------------+----+----+

      • 查詢最近300s內的歷史增量資料。

        • 樣本：當mf_tt2表acid.incremental.query.out.of.time.range.enabled屬性的預設值為false。

          SELECT * FROM mf_tt2 TIMESTAMP BETWEEN current_timestamp() - 301 AND current_timestamp() WHERE dd = '01' AND hh='01';

          傳回值結果報錯。

          FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: 
com.aliyun.odps.meta.exception.MetaException: com.aliyun.odps.meta.exception.MetaException: com.aliyun.odps.metadata.common.MetastoreServerException: 
Incremental query can't exceed current version. Current version timestamp: 2023-06-26 09:32:32, input timestamp is: 2023-06-26 10:47:55

        • 樣本：修改表acid.incremental.query.out.of.time.range.enabled屬性值為true。

          ALTER TABLE mf_tt2 SET tblproperties("acid.incremental.query.out.of.time.range.enabled"="true");

        • 樣本：再次進行查詢。

          SELECT * FROM mf_tt2 TIMESTAMP BETWEEN current_timestamp() - 301 AND current_timestamp() WHERE dd = '01' AND hh='01';

          傳回值為：

          +------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
+------------+------------+----+----+

      • 查詢最近第三次Commit和最近第一次Commit時間範圍內的所有歷史資料。

        SELECT * FROM mf_tt2 TIMESTAMP BETWEEN get_latest_timestamp('mf_tt2', 3) AND get_latest_timestamp('mf_tt2') WHERE dd = '01' AND hh = '01';

        返回結果如下。

        +------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+

      • 查詢最近第三次和最近第一次操作版本範圍內的所有歷史資料。

        SELECT * FROM mf_tt2 VERSION BETWEEN get_latest_version('mf_tt2', 3) AND get_latest_version('mf_tt2') WHERE dd = '01' AND hh = '01';

        返回結果如下。

        +------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 01 |
| 3          | 30         | 01 | 01 |
| 4          | 4          | 01 | 01 |
| 5          | 5          | 01 | 01 |
| 2          | 2          | 01 | 01 |
+------------+------------+----+----+