MaxCompute:Time travel queries and incremental queries

Syntax

[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>]

Parameters and limits of time travel queries

If you perform a time travel query on Delta tables, you can query historical data based on snapshots of source tables at a historical point in time or of a historical version. You can use the [timestamp | version as of expr] syntax to configure time travel queries.

Parameters and limits of incremental queries

Only Delta tables support incremental queries. If you perform an incremental query, you can query historical incremental data within a historical period of time or between two versions of a source table. You can use the [timestamp | version between start_expr and end_expr] syntax to configure incremental queries.

Others

• If multiple rows of records that have the same key are queried, only the latest row is returned. If the latest row is in the DELETE state, this row is filtered out. The feature of querying the update status of data in formats similar to Change Data Capture (CDC) is planned to be provided in the future.

• You cannot query the historical tables that do not exist. For example, after you perform the drop or rename operation on a table, you cannot query the historical data of the original table.

  If you want to query the historical data in this type of table, you can restore a table and then perform queries.

• Only Delta tables support time travel queries and incremental queries.

• If you want to perform a time travel query or an incremental query on the same table in an SQL statement, you must set the timestamps or versions for the queries to the same values.

• If you want to perform queries on a partitioned table, we recommend that you specify a partition for the query. This way, only the specified partition is queried, and less time is consumed.

• The Multiversion Concurrency Control (MVCC) model is used for concurrent read and write transactions. This ensures that read and write operations are isolated and do not affect each other. The ReadCommitted level is supported.

• Data that is generated by the compaction operation is not considered new data. Therefore, this type of data is not returned when you perform incremental queries.

Examples

• Test tables

  -- Create a table. The version of this operation is 1. You can execute the show history for table mf_tt2 statement to query the version.
  create table mf_tt2 (pk bigint not null primary key, 
                    val bigint not null) 
                    partitioned by (dd string, hh string) 
                    tblproperties ("transactional"="true"); 
  -- The version of the following DML operation is 2.
  insert overwrite table mf_tt2 partition(dd='01', hh='01') 
                   values (1, 1), (2, 2), (3, 3);
  -- The version of the following DML operation is 3.
  insert into table mf_tt2 partition(dd='01', hh='01') 
              values (3, 30), (4, 4), (5, 5);

• Query table data.

  • Query the table creation time, which is used as a reference when you specify a historical point in time.

    desc extended mf_tt2;

    The following result is returned:

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

  • Query the version of a historical data operation.

    show history for table mf_tt2 partition(dd='01',hh='01');

    The following result is returned:

    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

• Examples of time travel queries

  • Query all historical data that was stored before a specific point in time, such as the time specified by a string constant of the DATETIME type.

    select * from mf_tt2 timestamp as of '2023-06-26 09:33:00' 
             where dd = '01' and hh = '01';

    The following result is returned:

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

  • Query all historical data that was stored before a version specified by a constant.

    select * from mf_tt2 version as of 2 where dd = '01' and hh = '01';

    The following result is returned:

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

  • Query all historical data that was stored until the current time.

    select * from mf_tt2 timestamp as of current_timestamp() where dd = '01' and hh = '01';

    The following result is returned:

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

  • Query all historical data that was stored up to 10 seconds ago.

    select * from mf_tt2 timestamp as of current_timestamp() - 10 where dd = '01' and hh = '01';

    The following result is returned:

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

  • Query all historical data that was stored up to the time when the last second operation was committed.

    • Sample statement 1

      select * from mf_tt2 timestamp as of get_latest_timestamp('mf_tt2', 2) where dd = '01' and hh = '01';

    • Sample statement 2

      select * from mf_tt2 version as of get_latest_version('mf_tt2', 2) where dd = '01' and hh = '01';

      The following result is returned:

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

• Examples of incremental queries

  • Query the historical incremental data within a specified period of time. For example, set the start time and end time to string constants of the DATETIME type based on the time when operations are committed.

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

    The following result is returned:

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

  • Query historical incremental data between two specified versions.

    select * from mf_tt2 version between 2 and 3 where dd = '01' and hh = '01';

    The following result is returned:

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

  • Query historical incremental data within the most recent 300 seconds.

    • Use the default value false for the acid.incremental.query.out.of.time.range.enabled property.

      select * from mf_tt2 timestamp between current_timestamp() - 301 and current_timestamp() where dd = '01' and hh='01';

      An error is returned:

      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

    • Set the acid.incremental.query.out.of.time.range.enabled property to true.

      alter table mf_tt2 set tblproperties("acid.incremental.query.out.of.time.range.enabled"="true");

    • Perform the query again.

      select * from mf_tt2 timestamp between current_timestamp() - 301 and current_timestamp() where dd = '01' and hh='01';

      The following result is returned:

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

  • Query the historical incremental data between the points in time when the last two operations are committed.

    • Sample statement

      select * from mf_tt2 timestamp between get_latest_timestamp('mf_tt2', 3) and get_latest_timestamp('mf_tt2') where dd = '01' and hh = '01';

    • Sample statement

      select * from mf_tt2 version between get_latest_version('mf_tt2', 3) and get_latest_version('mf_tt2') where dd = '01' and hh = '01';

    • Returned result

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