在Snowflake中，有没有一种方法可以找出表格何时被截断

Question

我已经搜索了文档并查看了信息模式视图，但是，我不确定如何确定表何时被截断，有什么想法吗？

Answer 1

给出几个使用QUERY_HISTORY的例子。信息模式query_history是一个表函数。

在过去1小时内搜索历史记录以检查被截断的表-

select query_id,query_text,start_time,end_time from table(information_schema.query_history(dateadd('hours'
,-1,current_timestamp()),
current_timestamp())) where query_text like 'TRUNCATE%test%';
+--------------------------------------+---------------------------+-------------------------------+-------------------------------+
| QUERY_ID                             | QUERY_TEXT                | START_TIME                    | END_TIME                      |
|--------------------------------------+---------------------------+-------------------------------+-------------------------------|
| 01a397da-3200-5a32-0000-00006ca565d1 | TRUNCATE table test_hash; | 2022-04-13 20:38:48.536 -0700 | 2022-04-13 20:38:48.951 -0700 |
+--------------------------------------+---------------------------+-------------------------------+-------------------------------+

以下是将被截断的示例表-

select count(*) from player;
+----------+
| COUNT(*) |
|----------|
|        4 |
+----------+

给出截断命令-

truncate table player;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

将在QUERY_HISTORY中搜索表截断。查询是区分大小写的，所以在使用谓词进行搜索时需要小心。下面不会给出任何结果，因为我们在小写(截断(中使用了查询

select query_id,query_text,start_time,end_time from table(information_schema.query_history(dateadd('hours'
,-1,current_timestamp()),current_timestamp())) where query_text like 'TRUNCATE%player%';
+----------+------------+------------+----------+
| QUERY_ID | QUERY_TEXT | START_TIME | END_TIME |
|----------+------------+------------+----------|
+----------+------------+------------+----------+

使用小写再次搜索，或者您可以使用函数将query_text转换为小写/大写，并在谓词中给定相同的大小写。例如，大写(query_text(，如"TRUNCATE%PLAYER%"；

select query_id,query_text,start_time,end_time from table(information_schema.query_history(dateadd('hours'
,-1,current_timestamp()),current_timestamp())) where query_text like 'truncate%player%';
+--------------------------------------+------------------------+-------------------------------+-------------------------------+
| QUERY_ID                             | QUERY_TEXT             | START_TIME                    | END_TIME                      |
|--------------------------------------+------------------------+-------------------------------+-------------------------------|
| 01a397e2-3200-5a1a-0000-00006ca5560d | truncate table player; | 2022-04-13 20:46:22.083 -0700 | 2022-04-13 20:46:22.471 -0700 |
+--------------------------------------+------------------------+-------------------------------+-------------------------------+