TIME TRAVEL
描述
除了标准的 SELECT 查询子句外，算场还支持按数据历史版本或时间记录查询表的历史数据。
语法
table_identifier(version_expression = integer_expression)
table_identifier(time_travel_key operator_expression timestamp_expression)参数
version_expression
version， 指定按表的某一数据历史版本查询， 可以简写为 v
integer_expression
一个返回整数的文本表达式, 指定数据表的历史版本号, 可以从 DESCRIBE HISTORY table_spec 的输出中获取的 long 值。
time_travel_key
算场支持三种 time travel 查询 key，分别是 time， days， second
operator_expression
运算符表达式，支持 =，>，<，>=，<=
timestamp_expression
timestamp_expression 写法如下：
- '20241023110150.390'，形如 yyyyMMddHHmmss.SSS 的时间戳格式，
可以仅写到其中一部分截断长度，但是必须如 2024， 202410， 2024101023 等合法的年月日分秒等表示形式。
- '20241023110150.390'，形如 yyyyMMddHHmmss.SSS 的时间戳格式，
version 和 timestamp 可以并列使用，用 AND 连接，当 time travel 表达式存在多个 version 和 timestamp 条件时，
查询结果集为所有条件的交集。
示例
示例 1
CREATE TABLE person (name STRING, age INT, salary DOUBLE);
INSERT INTO person VALUES ('tom', 1, 11.0);
INSERT INTO person VALUES ('jack', 2, 12.0);
INSERT INTO person VALUES ('john', 3, 13.0);
DESCRIBE HISTORY person;
+-------+------+----------------------+----+----+--------------------+----------+---------+------+
|version|action| description|size|rows| commitTime|timeTravel|worksheet| user|
+-------+------+----------------------+----+----+--------------------+----------+---------+------+
| 2|insert|运行sql: CREATE TAB...| 727| 1|2024-10-22 18:59:...| 8小时前| 19988| you|
| 1|insert|运行sql: CREATE TAB...| 727| 1|2024-10-22 18:59:...| 8小时前| 19988| you|
| 0|insert|运行sql: CREATE TAB...| 725| 1|2024-10-22 18:59:...| 8小时前| 19988| you|
+-------+------+----------------------+----+----+--------------------+----------+---------+------+
SELECT * FROM person(version = 2);
+----+---+------+
|name|age|salary|
+----+---+------+
|jack| 2| 12.0|
+----+---+------+
|john| 3| 13.0|
+----+---+------+
| tom| 1| 11.0|
+----+---+------+
SELECT * FROM person(version = 1) where age > 1;
+----+---+------+
|name|age|salary|
+----+---+------+
|jack| 2| 12.0|
+----+---+------+
SELECT * FROM person(version = 0);
+----+---+------+
|name|age|salary|
+----+---+------+
| tom| 1| 11.0|
+----+---+------+
SELECT * FROM person(version = 0 AND version = 1 AND version = 2);
+----+---+------+
|name|age|salary|
+----+---+------+
| tom| 1| 11.0|
+----+---+------+示例 2
CREATE TABLE person (name STRING, age INT, salary DOUBLE);
INSERT INTO person VALUES ('tom', 1, 11.0);
-- formatter.format(new Date()) 20241023110150.390
INSERT INTO person VALUES ('jack', 2, 12.0);
-- formatter.format(new Date()) 20241023110151.373
INSERT INTO person VALUES ('john', 3, 13.0);
-- formatter.format(new Date()) 20241023110152.260
SELECT * FROM person(time < 20241023110152.260) order by age;
+----+---+------+
|name|age|salary|
+----+---+------+
| tom| 1| 11.0|
+----+---+------+
|jack| 2| 12.0|
+----+---+------+
|john| 3| 13.0|
+----+---+------+
SELECT * FROM person(time < 20241023110150.390);
+----+---+------+
|name|age|salary|
+----+---+------+
| tom| 1| 11.0|
+----+---+------+示例 3
CREATE TABLE person (name STRING, age INT, salary DOUBLE);
INSERT INTO person VALUES ('tom', 1, 11.0) -- version 0
INSERT INTO person VALUES ('jack', 2, 12.0) -- version 1
-- formatter.format(new Date()) 20241023110150.390
INSERT INTO person VALUES ('john', 3, 13.0) -- version 2
INSERT INTO person VALUES ('remilia', 4, 14.0) -- version 3
INSERT INTO person VALUES ('flandre', 5, 15.0) -- version 4
-- formatter.format(new Date()) 20241023110152.260
SELECT * FROM person(version = 3 AND time > 20241023110150.390) where age > 1 order by age;
+--------+---+------+
| name|age|salary|
+--------+---+------+
| john| 3| 13.0|
+--------+---+------+
| remilia| 4| 14.0|
+--------+---+------+示例 4
CREATE TABLE person (name STRING, age INT, salary DOUBLE);
INSERT INTO person VALUES ('tom', 1, 11.0);
INSERT INTO person VALUES ('jack', 2, 12.0);
INSERT INTO person VALUES ('john', 3, 13.0);
SELECT * FROM person(days > 1); -- 查询一天前的数据
+--------+---+------+
| name|age|salary|
+--------+---+------+
+--------+---+------+
SELECT * FROM person(days <= 1); -- 查询一天内的数据
+----+---+------+
|name|age|salary|
+----+---+------+
| tom| 1| 11.0|
+----+---+------+
|jack| 2| 12.0|
+----+---+------+
|john| 3| 13.0|
+----+---+------+
SELECT * FROM person(seconds <= 10); -- 查询10s内的表中新增的数据
SELECT * FROM person(seconds > 10); -- 查询10s内的表中新增的数据示例 5
CREATE TABLE person (name STRING, age INT, salary DOUBLE);
CREATE TABLE student (name STRING, age INT, salary DOUBLE, grade INT);
INSERT INTO person VALUES ('p1', 1, 1.0), ('p2', 2, 2.0);
INSERT INTO person VALUES ('p4', 4, 4.0), ('p3', 3, 3.0);
INSERT INTO person VALUES ('p6', 6, 6.0), ('p5', 5, 5.0);
INSERT INTO student VALUES ('p4', 4, 4.0, 4), ('p5', 5, 5.0, 5), ('p6', 6, 6.0, 6);
INSERT INTO student VALUES ('p7', 7, 7.0, 7), ('p8', 8, 8.0, 8), ('p9', 9, 9.0, 9);
SELECT person.name FROM person(version = 1) INNER JOIN student(version = 0) ON person.age = student.age
+--------+
| name|
+--------+
| p4|
+--------+示例 6
CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (1, 1), (2, 2);
INSERT INTO t2 VALUES (2, 5), (3, 3);
INSERT INTO t2 VALUES (1, 4), (3, 5);
MERGE INTO t1 USING t2(version = 0) ON t1.c1 = t2.c1
WHEN MATCHED THEN
UPDATE SET *
WHEN NOT MATCHED THEN
INSERT *
SELECT * FROM t1;
+---------+-----------+
| c1| c2|
+---------+-----------+
| 1| 2|
+---------+-----------+
| 2| 5|
+---------+-----------+
| 3| 3|
+---------+-----------+