CREATE TABLE birds (
id INT,
name VARCHAR(50),
wingspan_cm FLOAT,
colors STRING
);
INSERT INTO birds VALUES
(1, 'Sparrow', 15.5, 'Brown'),
(2, 'Blue Jay', 20.2, 'Blue');
-- 插入更多数据
INSERT INTO birds VALUES
(3, 'Cardinal', 22.1, 'Red'),
(4, 'Robin', 18.7, 'Red,Brown');
-- 查看版本历史
DESC HISTORY birds;
+---------+-------------------------+------------+-------------+----------+----+
| version | time | total_rows | total_bytes | user | op |
+---------+-------------------------+------------+-------------+----------+----+
| 3 | 2024-12-23 16:41:47.831 | 4 | 5786 | UAT_TEST | IN |
| 2 | 2024-12-23 16:36:04.426 | 2 | 2859 | UAT_TEST | IN |
| 1 | 2024-12-23 16:36:04.233 | 0 | 0 | UAT_TEST | CR |
+---------+-------------------------+------------+-------------+----------+----+
-- 查询 version 2 时的数据（2 条记录）
SELECT * FROM birds TIMESTAMP AS OF '2024-12-23 16:36:04.426';
+----+----------+-------------+---------+
| id | name | wingspan_cm | colors |
+----+----------+-------------+---------+
| 1 | Sparrow | 15.5 | Brown |
| 2 | Blue Jay | 20.2 | Blue |
+----+----------+-------------+---------+
示例 2：历史数据 JOIN
-- 当前版本查询（4 个学生）
SELECT s.name, s.class, sc.score
FROM students s
INNER JOIN scores sc ON s.name = sc.name;
-- 查询历史版本（2 个学生）
SELECT s.name, s.class, sc.score
FROM students TIMESTAMP AS OF '2024-12-23 16:15:22.957' s
INNER JOIN scores sc ON s.name = sc.name;
示例 3：恢复被 TRUNCATE 的数据
-- 创建表并插入数据
CREATE TABLE birds (id INT, name VARCHAR(50));
INSERT INTO birds VALUES (1, 'Sparrow'), (2, 'Blue Jay');
-- 清空数据
TRUNCATE TABLE birds;
-- 通过 Time Travel 查询删除前的数据
SELECT * FROM birds TIMESTAMP AS OF '2024-01-15 10:00:00';
-- 恢复数据
INSERT OVERWRITE TABLE birds
SELECT * FROM birds TIMESTAMP AS OF '2024-01-15 10:00:00';
示例 4：备份历史数据到新表
CREATE TABLE birds_backup AS
SELECT * FROM birds TIMESTAMP AS OF '2024-01-15 10:00:00';
