RedShift Reconstructing SQL from STL_QUERYTEXT
If you are managing the RedShift clusters then STL_QUERY and STL_QUERYTEXT tables are not new to you. STL_Query can’t hold the complete SQL query instead we can use STL_QueryText to read the complete query. But there is a challenge, we can’t read that table as it is. Since your queries are saved in multiple rows. So we need to combine all these rows into a single row with LISTAGG function which is well documented here.
What is the real challenge?
From the AWS documentation example, they are showing to reconstruct the SQL for a single query. But in a production system, you may have 50k+ queries where the LISTAGG function will through some errors related to its limitation. Let’s simulate this. I ran the below query on my production RedShift Cluster.
Why this error?
If you read the LISTAGG function details, If the result set is larger than the maximum VARCHAR size (64K – 1, or 65535), then LISTAGG returns the error. Here I have more than 50k+ rows. So the overall characters will not fit in the result set.
What is the solution?:
If we run the reconstruct query one by one instead of all then the resultset will fit into the LISTAGG’s limitation. Lets say one of my queries split into 2 rows in the STL_QUERYTEXT table.
First, process these two rows and then process another query and then the next one.
query columns are common between these two rows. So partition your resultset by query wise. Then do the LISTAGG.
Replace \n and \r characters:
If you are going to run this query from psql client most of the queries starts with
\r\nselect * from mytbl or any GUI based tools will return
- \r actually a carriage return
- \n is not a new line its a sting. So we have to replace twice.
For east management, Im going to create a view on top of the STL_QUERYTEXT table to return the reconstructed SQL for all the queries.
Now you can view the proper data from this view.
I hope this helps you to get the overall consolidated view on the query history. If you are interested in how to Audit RedShift Historical Queries With pgbadger please click here.