デルタテーブルでの使用に関する注意事項とクエリの制限 - MaxCompute

MaxComputeでは、デルタテーブルに対してタイムトラベルクエリと増分クエリを実行できます。タイムトラベルのクエリを実行する場合は、過去の時点または過去のバージョンのソーステーブルのスナップショットに基づいて、履歴データをクエリできます。増分クエリを実行すると、履歴期間内、またはソーステーブルの2つのバージョン間の履歴増分データをクエリできます。このトピックでは、デルタテーブルに対するクエリの使用方法と制限について説明します。

構文

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

説明

ほとんどの場合、すべてのシナリオでデータクエリ言語 (DQL) ステートメントを実行してDeltaテーブルにクエリを実行できます。 DQL文の構文と制限は、基本的にMaxCompute DQLの構文と制限に準拠しています。唯一の違いは、FROM TABLE句の構文がDQLステートメントで最適化されていることです。最適化されたFROM TABLE句は、タイムトラベルクエリの履歴時点または履歴バージョンを指定したり、増分クエリの時間範囲またはバージョン範囲を指定したりできる固定形式の式を提供します。

タイムトラベルクエリのパラメータと制限

デルタテーブルでタイムトラベルクエリを実行する場合、履歴時点または履歴バージョンのソーステーブルのスナップショットに基づいて履歴データをクエリできます。 [TIMESTAMP | VERSION AS OF expr] 構文を使用して、タイムトラベルクエリを設定できます。

exprのようなタイムスタンプ

パラメーター
- TIMESTAMP AS OF: 固定された構文フォーマットであり、その後に履歴時点が続く。履歴時点より前に保存された履歴データは、スナップショットに基づいて照会されます。
- expr: MaxComputeでサポートされている標準のTIMESTAMP、DATETIME、またはDATE型の値。値は、次のいずれかの形式にすることができます。
  - 日付文字列定数:
    次の表に、TIMESTAMP、DATETIME、またはDATE型の文字列定数の例を示します。
    データ型
    例
    TIMESTAMP
    '2023-01-01 00:00:00.123 '
    日付時刻
    '2023-01-01 00:00:00 '
    日付
    '2023-01-01 '
  - MaxComputeの組み込み時間関数: current_timestamp() 、getDate() + N: current_timestamp() 、getDate() 。
    前述の関数では、Nは秒で表される。 Nが負の数に設定されている場合、現在の時刻のN秒前の時刻が指定されます。 Nを正の数に設定すると、現在の時刻からN秒後の時刻が指定されます。
  - デルタテーブルの構文: get_latest_timestamp(string tablename [, bigint <number>])
    - クロスプロジェクトアクセスの場合、tablenameパラメーターの値はProjectName.TableNameとしてフォーマットする必要があります。
    - 3層モデルの場合、tablenameパラメーターの値はProjectName.SchemaName.TableNameとしてフォーマットする必要があります。
    - numberパラメーターはオプションです。デフォルト値は 1 です。このパラメータは、データ操作のシーケンス番号を逆時系列で指定します。たとえば、最後のデータ操作がコミットされた時刻を取得するには、このパラメーターを1に設定します。データ操作には、ユーザによって開始されるデータ修正と、システムによって開始されるデータのソートおよび配布とが含まれる。 numberパラメーターが異なる値に設定されていても、返されるTIMESTAMPは同じである可能性があります。
制限事項
- 履歴スナップショットデータのクエリ範囲は [CreateTableTimestamp, expr] です。 exprパラメーターはDML操作がコミットされる時刻を指定し、CreateTableTimestampパラメーターはテーブル作成操作がコミットされる時刻を指定します。
- exprパラメーターで指定された時刻がN時間以上前、またはDeltaテーブルが作成された時刻より前の場合、特定の履歴データが存在しない可能性があるため、エラーが返されます。 Nは、テーブルの作成時に設定されるacid.data.retain.hoursパラメーターで指定します。たとえば、acid.data.retain.hoursパラメーターが72に設定され、exprパラメーターが72時間以上前に設定されている場合、エラーが返されます。
- exprパラメーターで指定された時刻がN時間前の場合、エラーが返される可能性があります。 Nは、テーブルの作成時に設定されるacid.data.retain.hoursパラメーターで指定します。これは、内部システムが相互作用するときに第2レベルのレイテンシが発生する可能性があるためです。したがって、TIMESTAMP AS OF current_timestamp() - time travel periodに似たエラーが発生しやすい構文を使用しないことをお勧めします。

exprのようなバージョン

パラメーター
- VERSION AS OF: 履歴データ操作バージョンが続く固定構文フォーマット。履歴データ操作バージョンがスナップショットに基づいて照会される前のバージョンに格納された履歴データ。
- expr: MaxComputeでサポートされているBIGINT型の値。この値は、次の形式をサポートします。
  - BIGINT型の定数:
    例えば、定数3。
  - デルタテーブルの構文: get_latest_version(string tablename [, bigint <number>])
    - クロスプロジェクトアクセスの場合、tablenameパラメーターの値はProjectName.TableNameとしてフォーマットする必要があります。
    - 3層モデルの場合、tablenameパラメーターの値はProjectName.SchemaName.TableNameとしてフォーマットする必要があります。
    - numberパラメーターはオプションです。デフォルト値は 1 です。このパラメータは、データ操作のシーケンス番号を逆時系列で指定します。たとえば、最後のデータ操作のバージョンを取得するには、このパラメーターを1に設定します。データ操作には、ユーザによって開始されるデータ修正と、システムによって開始されるデータのソートおよび配布とが含まれる。返されるVERSIONは、numberパラメーターの値によって異なります。
制限事項
- 各DMLオペレーションは、厳密にインクリメントするバージョン番号を生成する。 SHOW HISTORY FOR TABLE/PARTITIONを使用して、すべてのDML操作情報を表示し、対応するバージョンを取得できます。
- タイムトラベルクエリの履歴時間範囲は [CreateTableVersion, expr] です。 exprパラメーターは、DML操作のバージョンを指定します。 CreateTableVersionパラメーターは、テーブル作成操作のバージョンを指定します。デフォルト値は 1 です。
- システムは、exprパラメーターで指定されたバージョンに基づいて、DML操作がコミットされた時刻を取得します。時刻がN時間以上前の場合、またはバージョンが1未満の場合は、エラーが返されます。 Nは、テーブルの作成時に設定されるacid.data.retain.hoursパラメーターで指定します。
- exprパラメーターで指定されたバージョンが最後のDML操作のバージョンよりも新しい場合は、エラーが返されます。 get_latest_version関数を使用してバージョンを取得することを推奨します。

増分クエリのパラメータと制限

増分クエリをサポートするのはDeltaテーブルのみです。増分クエリを実行すると、履歴期間内、またはソーステーブルの2つのバージョン間の履歴増分データをクエリできます。 [TIMESTAMP | VERSION BETWEEN start_expr AND end_expr] 構文を使用して、増分クエリを設定できます。

start_exprとend_exprの間のタイムスタンプ

パラメーター
- TIMESTAMP BETWEEN AND: 履歴時間範囲を指定する固定構文形式。時間範囲内に生成された履歴増分データが照会されます。
- start_exprおよびend_expr: 2つのパラメーターの使用方法と制限は、TIMESTAMP AS of構文のexprパラメーターの使用方法と一致しています。
制限事項
- (start_expr, end_expr] で指定される時間範囲は、左オープン、右クローズの間隔です。 start_exprおよびend_exprパラメーターは、DML操作がコミットされる時刻を指定します。
- start_exprパラメーターで指定された時刻がN時間以上前、またはテーブルの作成時刻より前の場合、エラーが返されます。 Nは、acid.data.retain.hoursパラメーターで指定します。
- end_exprパラメーターで指定された時刻が、最後のDML操作がコミットされた時刻よりも遅い場合、クエリ結果は、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");
```

start_exprとend_exprの間のバージョン

パラメーター
- VERSION BETWEEN AND: 履歴DML操作のバージョン範囲を指定する固定構文形式。バージョン範囲内で生成された履歴の増分データが返されます。
- start_exprおよびend_expr: 2つのパラメーターの使用方法と制限は、VERSION AS of構文のexprパラメーターの使用方法と一致しています。
制限事項
- (start_expr, end_expr) で指定されたバージョン範囲は、左オープン、右クローズの間隔です。 start_exprおよびend_exprパラメーターは、DML操作のバージョンを指定します。
- システムは、start_exprパラメーターで指定されたバージョンに基づいて、DML操作がコミットされた時刻を取得します。時刻がN時間以上前の場合、またはバージョンが1未満の場合は、エラーが返されます。 Nは、タイムトラベルクエリ用に設定されたacid.data.retain.hoursパラメーターによって指定されます。
- end_exprパラメーターで指定されたバージョンが最後のDML操作のバージョンよりも新しい場合、クエリ結果はacid.incremental.query.out.of.time.range.enabledパラメーターの値によって異なります。このパラメーターがデフォルト値falseに設定されている場合、エラーが返されます。このパラメーターをtrueに設定すると、(start_expr, end_expr) で指定されたバージョン範囲内で生成されたすべての履歴増分データが返されます。

その他

同じキーを持つレコードの複数の行が照会された場合、最新の行のみが返されます。最新の行がDELETE状態にある場合、この行は除外されます。変更データキャプチャ (CDC) と同様の形式でデータの更新ステータスを照会する機能は、将来提供される予定です。
存在しない履歴テーブルを照会することはできません。たとえば、テーブルに対してドロップまたは名前変更操作を実行した後は、元のテーブルの履歴データを照会することはできません。
このタイプのテーブルの履歴データをクエリする場合は、テーブルを復元してからクエリを実行できます。
デルタテーブルのみがタイムトラベルクエリと増分クエリをサポートしています。
SQL文の同じテーブルに対してタイムトラベルクエリまたは増分クエリを実行する場合は、クエリのタイムスタンプまたはバージョンを同じ値に設定する必要があります。
パーティションテーブルでクエリを実行する場合は、クエリにパーティションを指定することを推奨します。このようにして、指定されたパーティションのみがクエリされ、消費時間が短縮されます。
マルチバージョン同時実行制御 (MVCC) モデルは、同時読み取りおよび書き込みトランザクションに使用されます。これにより、読み取り操作と書き込み操作が分離され、互いに影響しないようになります。 ReadCommittedレベルがサポートされています。
コンパクション操作によって生成されたデータは、新しいデータとは見なされません。したがって、増分クエリを実行しても、このタイプのデータは返されません。

例

データの例。

-- The version of the following table creation operation is 1. You can execute show history for table mf_tt2 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);

テーブルデータを照会します。

過去の時点を指定するときに参照として使用されるテーブル作成時刻を照会します。

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

タイムトラベルのクエリの例。

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

定数で指定されたバージョンより前に格納されたすべての履歴データを照会します。

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

10秒前までに保存されたすべての履歴データを照会します。

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

最後の2番目の操作がコミットされた時刻までに保存されたすべての履歴データを照会します。

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

2番目に最近の操作のバージョンまで保存されたすべての履歴データを照会します。

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

増分クエリの例

指定された期間内の履歴増分データを照会します。たとえば、操作がコミットされた時刻に基づいて、開始時刻と終了時刻を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 |
+------------+------------+----+----+

指定した2つのバージョン間の履歴増分データを照会します。

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

直近の300秒以内の履歴増分データを照会します。

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

3番目に最近のコミットと1番目に最近のコミット時間の範囲内のすべての履歴データを照会します。

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

3番目の最新操作バージョンと1番目の最新操作バージョンの範囲内のすべての履歴データを照会します。

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

データ型	例
TIMESTAMP	`'2023-01-01 00:00:00.123 '`
日付時刻	`'2023-01-01 00:00:00 '`
日付	`'2023-01-01 '`