Snowflake - View what tables and columns are queried the most

user_7587176 0
Viewed 1110

is there any way within snowflake/sql query to view what tables are being queried the most as well as what columns? I want to know what data is of most value to my users and not sure how to do this programatically. Any thoughts are appreciated - thank you!

snowflake-cloud-data-platform
1 Answers

2021 update

The new ACCESS_HISTORY view has this information (in preview right now, enterprise edition).

For example, if you want to find the most used columns:

select obj.value:objectName::string objName
    , col.value:columnName::string colName
    , count(*) uses
    , min(query_start_time) since
    , max(query_start_time) until
from snowflake.account_usage.access_history 
    , table(flatten(direct_objects_accessed)) obj
    , table(flatten(obj.value:columns)) col
group by 1, 2
order by uses desc

Ref: https://docs.snowflake.com/en/sql-reference/account-usage/access_history.html

2020 answer

The best I found (for now):

  • For any given query, you can find what tables are scanned through looking at the plan generated for it:
SELECT *, "objects"
FROM TABLE(EXPLAIN_JSON(SYSTEM$EXPLAIN_PLAN_JSON('SELECT * FROM a.b.any_table_or_view')))
WHERE "operation"='TableScan'
  • You can find all of your previous ran queries too:
select QUERY_TEXT 
from table(information_schema.query_history())

So the natural next step would be combine both - but that's not straightforward, as you'll get an error like:

SQL compilation error: argument 1 to function EXPLAIN_JSON needs to be constant, found 'SYSTEM$EXPLAIN_PLAN_JSON('SELECT * FROM a.b.c')'

The solution would be to combine the queries from the query_history() with the SYSTEM$EXPLAIN_PLAN_JSON outside (to make the strings constant), and then you will be able to find out the most queried tables.

user_1324380
Related