从雪花历史记录中获取DDL类型SQL [英] Get ddl type sqls from Snowflake History

问题描述

我对在Snowflake数据库架构上执行DDL感兴趣，此DDL类似于CREATE/ALTER TYPE SQL语句。是否有办法使用Snowflke.Account_Usage架构或任何其他方法来捕获此元数据。

推荐答案

如果要使用snowflake共享数据库，则可以使用snowflake.account_usage.query_history视图来执行此操作。它有一个名为query_type的列，该列显示运行的是哪种类型的查询。下面是一个示例：

-- Run some sample queries
create or replace database sample_database;
create or replace table sample_database.public.test_table (col1 varchar);
alter table sample_database.public.test_table add column col2 varchar;
insert overwrite into sample_database.public.test_table values ('row 1 col 1', 'row 1 col 2'), ('row 2 col 1', 'row 2 col 2');


-- Use the snowflake.account_usage.query_history view to see what the query types were
select 
    query_text, 
    database_name, 
    schema_name, 
    query_type
from snowflake.account_usage.query_history 
where database_name = 'SAMPLE_DATABASE' order by start_time;

-- results
+------------------------------------------+-----------------+-------------+------------------------+
|                QUERY_TEXT                |  DATABASE_NAME  | SCHEMA_NAME |       QUERY_TYPE       |
+------------------------------------------+-----------------+-------------+------------------------+
| create or replace table sample_databa... | SAMPLE_DATABASE | PUBLIC      | CREATE_TABLE           |
| alter table sample_database.public.te... | SAMPLE_DATABASE | PUBLIC      | ALTER_TABLE_ADD_COLUMN |
| insert overwrite into sample_database... | SAMPLE_DATABASE | PUBLIC      | INSERT                 |
| select * from snowflake.account_usage... | SAMPLE_DATABASE | PUBLIC      | SELECT                 |
+------------------------------------------+-----------------+-------------+------------------------+

请注意，account_usage视图have a slight delay因此您的查询可能不会立即显示。特别是query_history视图，根据我刚才链接的文档，它的延迟大约为45分钟。

另一种方法是使用QUERY_HISTORY, QUERY_HISTORY_BY_*表函数，据我所知它们没有延迟。这里有一个这样的示例：

-- Run some sample queries
create or replace database sample_database;
create or replace table sample_database.public.test_table (col1 varchar);
alter table sample_database.public.test_table add column col2 varchar;
insert overwrite into sample_database.public.test_table values ('row 1 col 1', 'row 1 col 2'), ('row 2 col 1', 'row 2 col 2');

-- Use the table functions to see the history and view the query types run in the last hour
select 
    query_text, 
    database_name, 
    schema_name, 
    query_type
from table(information_schema.query_history(dateadd('hours',-1,current_timestamp()),current_timestamp()))
where database_name = 'SAMPLE_DATABASE'
order by start_time;

-- results
+------------------------------------------+-----------------+-------------+------------------------+
|                QUERY_TEXT                |  DATABASE_NAME  | SCHEMA_NAME |       QUERY_TYPE       |
+------------------------------------------+-----------------+-------------+------------------------+
| create or replace table sample_databa... | SAMPLE_DATABASE | PUBLIC      | CREATE_TABLE           |
| alter table sample_database.public.te... | SAMPLE_DATABASE | PUBLIC      | ALTER_TABLE_ADD_COLUMN |
| insert overwrite into sample_database... | SAMPLE_DATABASE | PUBLIC      | INSERT                 |
| select * from snowflake.account_usage... | SAMPLE_DATABASE | PUBLIC      | SELECT                 |
+------------------------------------------+-----------------+-------------+------------------------+

这篇关于从雪花历史记录中获取DDL类型SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！