CODEWITHSUNDEEP
snowflake-cloud-data-platform
SungJoon
SungJoon

Reputation: 65

How can I get information about table usage?

I'm new to Snowflake and trying to get a ranking of table usage which means how many queries requested in a certain period of time for each table. I found out this link but could get query text not a table name. Should I parse the query text and extract table names or find another fancy way? If the first one is true, is there a good SQL parser library for python?

Upvotes: 3

Views: 2777

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11086

You can use the SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY view to see object usage.

This will get you started. The flatten function turns an array in BASE_OBJECTS_ACCESSED into rows so you can aggregate, count, etc. Check the VALUE column for what you can count, aggregate, etc.

select * from 
"SNOWFLAKE"."ACCOUNT_USAGE"."ACCESS_HISTORY",
   table(flatten(BASE_OBJECTS_ACCESSED))
;

Upvotes: 3

Related Questions