CODEWITHSUNDEEP
snowflake-cloud-data-platform
Pratyush Tripathi
Pratyush Tripathi

Reputation: 11

Is there a way to figure out the tables a snowflake query is accessing?

I looked up the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY table but the information it provides is the database name. Is there a way in snowflake to get the table names a query is accessing ? I am not looking for a solution which involves parsing the query string, as that is really complicated.

Upvotes: 1

Views: 1292

Answers (2)

josescuderoh
josescuderoh

Reputation: 85

Snowflake users with Enterprise Edition can obtain the tables used by a query using the ACCESS_HISTORY view (currently in preview), inside the DIRECT_OBJECTS_ACCESSED or BASE_OBJECTS_ACCESSED fields.

Upvotes: 0

Greg Pavlik
Greg Pavlik

Reputation: 11046

To see what tables an historical query accessed, you can go to the History tab, click on the query ID for the query, and then click on the profile. For queries that you are about to run, you can see what table(s) it will access by typing "explain" before the query. That will produce a metadata result set with a list of tables the query will read from in addition to other information.

Edit: If the explain produces a very long result set and you want to filter it down to just the affected tables, you can do something like this:

-- Generate the explain metadata reult set
explain select * from MY_VIEW;

-- Filter to just affected tables
select distinct "objects" as TABLE_NAME
from table(result_scan(last_query_id())) 
where "operation" ilike '%table%' and "objects" is not null;

Upvotes: 1

Related Questions