Does redshift store failed queries?

0
votes

I wish to analyze the queries executed on certain redshift warehouse (not mine). In order to do so I'm using a query with a join on stl_querytext and stl_query.

My question is how come I'm also getting illegal queries (I.E queries with wrong sql syntax)? When I've tried it in my local redshift I haven't seen those. Also, couldn't find relevant documentation.

Is this a configuration issue? And in case I'm supposed to those queries is there a way to know those are illegal ones?

Thanks, Nir.

1
sqlamazon-redshift
I wonder whether you could use SVL_QUERY_METRICS_SUMMARY - Amazon Redshift to determine whether the query failed? – John Rotenstein
@JohnRotenstein - umm, don't see any indication there whether the query was completed successfully or not. – Nir99
Correct, but you might be able to use some of the statistics (eg number of rows) to make an assumption about whether it was successful. – John Rotenstein
well, this doesn't sound very deterministic... – Nir99

1 Answers

0
votes

So stl_querytext breaks long queries into parts identified by sequence number. I hope you are reconstructing the parts into the original query as a first step. This can be done with listagg() function as long as the resulting query doesn't over the max tex field (about 320 parts).

Now this is not enough to get valid SQL back in all cases because you need to treat combining the parts differently depending if the section of the query is inside or outside a text string in the query. (Is white space needed between parts or not)

I've done this exact process a bunch so this is doable. I don't have a perfect process on the whitespace question, I get close. Maybe others know the expression to get an exact recreation of the query from stl_querytext.