How to go from a redshift SQL query back to the QS dataset that generated that query

Question & Answer
,
1

When I look at the redshift console at the running and completed queries, many of these SQL statements start with:

/* QuickSight   1a2b3c4d-5e6f-7a8b-9c0d-1e2f3a4b5c6d */
select a from b.c

How can I tie back that ARN in the SQL statement back to the QuickSight dataset that generated/invoked this SQL/ingestion?

2

Hi @cgreenacre

not sure what ID is showing, but you could check if it is the dataset id.

BR

3

It’s not the dataset id:

image
image1228×429 95.7 KB

4

working on a different query (not the one from above), but the arn (or guid or hash or whatever you want to call it) that is the first that you see, may not be traceable; but in the SQL (at least in this complex SQL with multiple with clauses aka CTE), there is an ARN in the SQL that maps back to something in the dataset. The SQL in redshift in the QS built portion of the SQL (that wraps our custom SQL), there is an arn that maps back to a PhyscialTableMap component as per the CLI. I used the following CLI command to generate the file datasetoutput.txt:
aws quicksight describe-data-set --aws-account-id 150970770365 --data-set-id f8f5ec22-a01b-44a8-90dc-c030a2f1af1b >datasetoutput.txt

image
image1154×576 45.4 KB

I need confirm that dataset built without custom SQL (what I call the visual approach) also follow this pattern.
If this holds true, then one step is dump all of datasets via cli using python and cross reference dataset to physical table map ARN’s, and then step two is to interrogate redshift sql history and parse the SQL looking for ARNs and match those to the ones found in the first step.

1 Like