Objects used in queries

0

Hello, is there any way to find out the list of all the tables (including external tables) used in the sqls executed in redshift. What I am looking is if there is any view or table which says this query has accessed these tables, if there is such view/table it would be easy to extract the info I am looking for. Otherwise I would need to go query history and manually get the objects accessed in each query.

nmakb
asked 5 years ago350 views
1 Answer
0
Accepted Answer

Hi nmakb,

I believe you can construct query or view for what you're looking by basing a query off of a UNION of query system log table pg_catalog.stl_scan jointed to a few other table for all table in Redshift storage UNIONEed with a query of svl_s3query smilarly joined to a few other tables to get the query text and the table and schema name.

Basically stl_scan tracks all table scan operators against table in Redshift storage for in all query plans and svl_s3query effectively does the same because all Spectrum sub-queries are single table in nature and produce equivalent scan results returned to the slices from the Spectrum layer.

I hope this helps you develop the result set you're looking for.

Regards,
-Kurt

klarson
answered 5 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions