功能描述

DESCRIBE HISTORY命令用于获取表、动态表或物化视图的历史记录信息。通过这些历史记录，用户可以执行回滚操作，或者查询特定时间点的表数据。是否可以查看的对象操作历史依赖于数据的保留周期。

语法格式

DESCRIBE HISTORY object_name;

参数说明：

• object_name：支持的类型包括表（table）、动态表（dynamic table）和物化视图（materialized view）。

使用示例

示例1:查看表的历史记录操作

DESC HISTORY students;
+---------+-------------------------+------------+-------------+----------+-----------------+-------------------------------+
| version |          time           | total_rows | total_bytes |   user   |    operation    |            job_id             |
+---------+-------------------------+------------+-------------+----------+-----------------+-------------------------------+
| 2       | 2024-06-14 11:18:43.238 | 37         | 1387        | UAT_TEST | CREATE_TABLE_AS | 2024061403184277861pl5i9616ul |
+---------+-------------------------+------------+-------------+----------+-----------------+-------------------------------+

示例2：查看动态表的历史记录

DESC HISTORY public.event_gettime;

执行该命令后，将返回public.event_gettime动态表的历史记录，如下所示：

+---------+-------------------------+------------+-------------+----------+-----------+-------------------------------+------------------------------------------------------------------------------------+
| version |          time           | total_rows | total_bytes |   user   | operation |            job_id             |                                                      source_tables                 |
+---------+-------------------------+------------+-------------+----------+-----------+-------------------------------+------------------------------------------------------------------------------------+
| 3       | 2024-01-31 17:49:28.814 | 5          | 7578        |          | REFRESH   | 202401310949284215k3g3xp367rm | [{"table_name":"event_tb","workspace":"ql_ws","schema":"public","version":"3",com  |
| 2       | 2024-01-31 17:48:11.862 | 4          | 3820        |          | REFRESH   | 2024013109481169177wieyw9xl51 | [{"table_name":"event_tb","workspace":"ql_ws","schema":"public","version":"2",com  |
| 1       | 2024-01-31 17:48:11.656 | 0          | 0           |          | CREATE    | 2024013109481148777wieyw9xkqo | [{"table_name":"event_tb","workspace":"ql_ws","schema":"public"}]                  |
+---------+-------------------------+------------+-------------+----------+-----------+-------------------------------+------------------------------------------------------------------------------------+

SELECT * FROM public.event_gettime TIMESTAMP AS OF '2024-01-31 17:48:11.862';

+---------+---------+------------+-------------+-----------+------------+--------------+
|  event  | process | event_year | event_month | event_day | event_hour | event_minute |
+---------+---------+------------+-------------+-----------+------------+--------------+
| event-0 | 20.0    | 2023       | 9           | 20        | 14         | 43           |
| event-0 | 20.0    | 2023       | 9           | 19        | 11         | 40           |
| event-1 | 21.0    | 2023       | 9           | 19        | 14         | 30           |
| event-1 | 22.0    | 2023       | 9           | 20        | 14         | 20           |
+---------+---------+------------+-------------+-----------+------------+--------------+

示例3：查看物化视图的历史记录

DESC HISTROY mv_inventory_basic;
+---------+-------------------------+------------+-------------+---------+-----------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------+
| version |          time           | total_rows | total_bytes |  user   | operation |            job_id             |                                                      source_tables                                                       |
+---------+-------------------------+------------+-------------+---------+-----------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------+
| 2       | 2024-12-26 15:18:21.626 | 1          | 2915        | qiliang | CREATE    | 202412261518212641gmspelya5ne | [{"table_name":"inventory","workspace":"ql_ws","schema":"public","version":"2","commit_time":"2024-12-26 14:38:14.258"}] |
+---------+-------------------------+------------+-------------+---------+-----------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------+

注意事项

• 使用DESCRIBE HISTORY命令时，请确保object_name是有效的表、动态表或物化视图名称。
• 在执行查询特定时间点的数据时，请确保提供的时间格式正确。