Monitoring and profiling redshift query

Question

What tables (svl, stl, etc) should I consider in order to filter out which local tables are used in sql executed in redshift? I am thinking of using regex to query stl_ddltext for local tables but it does not seem to work

MilindOke · Answer

You can use [`PG_TABLE_DEF`](https://docs.aws.amazon.com/redshift/latest/dg/r_PG_TABLE_DEF.html) for listing all tables and its columns by schema. And use [`STL_QUERYTEXT`](https://docs.aws.amazon.com/redshift/latest/dg/r_STL_QUERYTEXT.html) to get query text for SQL commands. Building such string parsing functionality can be challenging so one option is to consider [monitoring tools from the AWS Marketplace](https://aws.amazon.com/marketplace/search?searchTerms=monitoring).

**Edit:** Kindly refer to [`SVV_EXTERNAL_TABLES`](https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_EXTERNAL_TABLES.html) for external tables in Redshift.

An Nguyen · Answer

Hi Milind,

Thank you for your answer, I'm still not clear on how to identify which tables are local or external since this info does not seem to be present in the suggested table. Can you advise me on this?

Monitoring and profiling redshift query

Add your answer

Relevant content