Snowflake is a fully managed cloud data warehouse solution. It is fast, reliable and comes with near zero management. Similar to many relational databases such as AWS Redshift, Azure Synapse, Snowflake support many system tables and views. Snowflake system tables store more rich and important information such as table usage, records count, constraint details, etc. In this article, we will check How to get most queried table in Snowflake using system tables present in the information schema.
How to Get Most Queried Table in Snowflake?
Snowflake maintains the important information in system tables. One of such view is access_history view.
Snowflake ACCESS_HISTORY View
This Account Usage view can be used to query the access history of Snowflake objects. You can get account usage information about tables, view, columns, etc. You must be a ACCOUNTADMIN or should have ACCOUNTADMIN role assigned to access Snowflake account usage views. Note that, ACCESS_HISTORY view returns object used within 365 days or 1 year.
Query to Get Most Queried Table in Snowflake
You can use the information stored in access_history view to get most queried object in Snowflake.
For example, Consider following query to get most queried object in ‘TEST_DB’ database.
USE ROLE ACCOUNTADMIN;
select obj.value:objectName::string TableName
, count(*) uses
from snowflake.account_usage.access_history
, table(flatten(direct_objects_accessed)) obj
group by 1
order by uses DESC;Following is the output.
+----------------------------------------+------+
| TABLENAME | USES |
|----------------------------------------+------|
| SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY | 3 |
| TEST_DB.PUBLIC.TEST_SCRIPTS | 2 |
+----------------------------------------+------+
The direct_objects_accessed column stores the information in the form of an array of JSON hence we are using FLATTEN function to get column name.
Related articles,
- Snowflake Convert Array to Rows – Methods and Examples
- What is Snowflake Lateral Join and How to use it?
- How to combine two arrays in Snowflake?
Hope this helps 🙂