CODEWITHSUNDEEP
snowflake-cloud-data-platform
Puspa Bista
Puspa Bista

Reputation: 85

Enable Change Tracking through Stream from Query History in Snowflake

I am trying to create a stream on view that looks for the change in information_schema.query_history().

But getting an error below:

SQL compilation error: line 8 at position 11: Change tracking is not supported on queries with 'Built-in view'.

How to track the changes from built in view, any ideas please?

Upvotes: 0

Views: 1087

Answers (2)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

As an alternative, you could create a table and a task that keeps updating it with changes in the view:

create or replace table track_queries
change_tracking = true
as
select *
from table(information_schema.query_history())
limit 0;


merge into track_queries using (
    select * 
    from table(information_schema.query_history())
) as b 
on track_queries.query_id = b.query_id
and track_queries.start_time = b.start_time
when matched and track_queries.end_time != b.end_time
then update 
set BYTES_SCANNED=b.BYTES_SCANNED, COMPILATION_TIME=b.COMPILATION_TIME, CREDITS_USED_CLOUD_SERVICES=b.CREDITS_USED_CLOUD_SERVICES, DATABASE_NAME=b.DATABASE_NAME, END_TIME=b.END_TIME, EXECUTION_STATUS=b.EXECUTION_STATUS, EXECUTION_TIME=b.EXECUTION_TIME, EXTERNAL_FUNCTION_TOTAL_INVOCATIONS=b.EXTERNAL_FUNCTION_TOTAL_INVOCATIONS, EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES=b.EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES, EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS=b.EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS, EXTERNAL_FUNCTION_TOTAL_SENT_BYTES=b.EXTERNAL_FUNCTION_TOTAL_SENT_BYTES, EXTERNAL_FUNCTION_TOTAL_SENT_ROWS=b.EXTERNAL_FUNCTION_TOTAL_SENT_ROWS, INBOUND_DATA_TRANSFER_BYTES=b.INBOUND_DATA_TRANSFER_BYTES, IS_CLIENT_GENERATED_STATEMENT=b.IS_CLIENT_GENERATED_STATEMENT, LIST_EXTERNAL_FILE_TIME=b.LIST_EXTERNAL_FILE_TIME, OUTBOUND_DATA_TRANSFER_BYTES=b.OUTBOUND_DATA_TRANSFER_BYTES, QUERY_ID=b.QUERY_ID, QUERY_TAG=b.QUERY_TAG, QUERY_TEXT=b.QUERY_TEXT, QUERY_TYPE=b.QUERY_TYPE, QUEUED_OVERLOAD_TIME=b.QUEUED_OVERLOAD_TIME, QUEUED_PROVISIONING_TIME=b.QUEUED_PROVISIONING_TIME, QUEUED_REPAIR_TIME=b.QUEUED_REPAIR_TIME, RELEASE_VERSION=b.RELEASE_VERSION, ROLE_NAME=b.ROLE_NAME, ROWS_PRODUCED=b.ROWS_PRODUCED, SCHEMA_NAME=b.SCHEMA_NAME, SESSION_ID=b.SESSION_ID, START_TIME=b.START_TIME, TOTAL_ELAPSED_TIME=b.TOTAL_ELAPSED_TIME, TRANSACTION_BLOCKED_TIME=b.TRANSACTION_BLOCKED_TIME, USER_NAME=b.USER_NAME, WAREHOUSE_NAME=b.WAREHOUSE_NAME, WAREHOUSE_TYPE=b.WAREHOUSE_TYPE
when not matched 
then insert (BYTES_SCANNED, COMPILATION_TIME, CREDITS_USED_CLOUD_SERVICES, DATABASE_NAME, END_TIME, EXECUTION_STATUS, EXECUTION_TIME, EXTERNAL_FUNCTION_TOTAL_INVOCATIONS, EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES, EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS, EXTERNAL_FUNCTION_TOTAL_SENT_BYTES, EXTERNAL_FUNCTION_TOTAL_SENT_ROWS, INBOUND_DATA_TRANSFER_BYTES, IS_CLIENT_GENERATED_STATEMENT, LIST_EXTERNAL_FILE_TIME, OUTBOUND_DATA_TRANSFER_BYTES, QUERY_ID, QUERY_TAG, QUERY_TEXT, QUERY_TYPE, QUEUED_OVERLOAD_TIME, QUEUED_PROVISIONING_TIME, QUEUED_REPAIR_TIME, RELEASE_VERSION, ROLE_NAME, ROWS_PRODUCED, SCHEMA_NAME, SESSION_ID, START_TIME, TOTAL_ELAPSED_TIME, TRANSACTION_BLOCKED_TIME, USER_NAME, WAREHOUSE_NAME, WAREHOUSE_TYPE) values (BYTES_SCANNED, COMPILATION_TIME, CREDITS_USED_CLOUD_SERVICES, DATABASE_NAME, END_TIME, EXECUTION_STATUS, EXECUTION_TIME, EXTERNAL_FUNCTION_TOTAL_INVOCATIONS, EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES, EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS, EXTERNAL_FUNCTION_TOTAL_SENT_BYTES, EXTERNAL_FUNCTION_TOTAL_SENT_ROWS, INBOUND_DATA_TRANSFER_BYTES, IS_CLIENT_GENERATED_STATEMENT, LIST_EXTERNAL_FILE_TIME, OUTBOUND_DATA_TRANSFER_BYTES, QUERY_ID, QUERY_TAG, QUERY_TEXT, QUERY_TYPE, QUEUED_OVERLOAD_TIME, QUEUED_PROVISIONING_TIME, QUEUED_REPAIR_TIME, RELEASE_VERSION, ROLE_NAME, ROWS_PRODUCED, SCHEMA_NAME, SESSION_ID, START_TIME, TOTAL_ELAPSED_TIME, TRANSACTION_BLOCKED_TIME, USER_NAME, WAREHOUSE_NAME, WAREHOUSE_TYPE);

In this way the table track_queries enables change tracking - as long as the task periodically brings in the latest queries from the view.

Check the options to get more than 100 rows from query_history:

Upvotes: 1

Gokhan Atil
Gokhan Atil

Reputation: 10039

It's not possible. Please take a look at the limitations on Streams on Views:

https://docs.snowflake.com/en/user-guide/streams-intro.html#streams-on-views

Streams are limited to views that satisfy the following requirements:

  • Underlying Tables All of the underlying tables must be native tables.

  • The view may only apply the following operations:

    • Projections

    • Filters

    • Inner or cross joins

    • UNION ALL

Upvotes: 1

Related Questions