CODEWITHSUNDEEP
amazon-redshiftaws-cli
Siddhant Jain
Siddhant Jain

Reputation: 549

Unable to see all the queries on AWS Redshift console

I am not able to see all my queries that i run from command-line tool or some other dbhelper on the aws console. Especially am not seeing any drop commands in the dashboard. Does aws redshift filters/sample out the queries which it shows on the dashboard ?

Can we use aws-cli to list all the queries which got run on the cluster ? Or is there any other place from where i can get list of all the queries which got run ?

Upvotes: 1

Views: 5997

Answers (2)

Yahia
Yahia

Reputation: 1339

You can run the following query to list the completed queries:

select pid, user_name, starttime, query,status
from stv_recents where status = 'Done';

Upvotes: 0

Nicolas
Nicolas

Reputation: 2231

The AWS console works a bit strangely for me: by default, it has a filter set to display only the queries issued the last 24 hours, but in fact, it forgets some. If I specify a narrower date range (like the last hour), I get more results. Try that, I guess that Amazon removes the relatively fast queries compared to the other ones when there are too many to return. And I think that I've never seen DDL statements in the "Queries" tab.

If you want to see all of them all the time, connect to the cluster via SQL instead. There are several tables inside the cluster itself that log the queries issued by whatever tool to the cluster:

  • STL_DDLTEXT (CREATE, DROP, ALTER table, view and schema).
  • STL_QUERYTEXT (all the typical SQL queries, like SELECT, INSERT, and also COPY)
  • STL_UTILITYTEXT (utility queries, like EXPLAIN or TRUNCATE).

SVL_STATEMENTTEXT is a view that groups all of them, so you should be able to see all your queries there.

Upvotes: 5

Related Questions