全部产品
Search

搜索本产品

    云原生大数据计算服务 MaxCompute：Time travel查询与增量查询

    文档中心

    云原生大数据计算服务 MaxCompute：Time travel查询与增量查询

    更新时间：Dec 05, 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 |
+------------+------------+----+----+