从雪花历史记录中获取DDL类型SQL

本文介绍了从雪花历史记录中获取DDL类型SQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我对在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 | +------------------------------------------+-----------------+-------------+------------------------+