本文介绍了从雪花历史记录中获取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 | +------------------------------------------+-----------------+-------------+------------------------+更多推荐
从雪花历史记录中获取DDL类型SQL
发布评论