Get list of all CustomSQL values across datasets

I’m looking to pull the CustomSQL values for our all our active datasets, as we have some field names changing in underlying tables. I want to look across all custom sql queries to see where we need to make this change in our QuickSight datasets. We have >50 datasets and I am limited to using CloudShell.

I am able to pull this manually for each dashboard using:
aws quicksight describe-data-set --aws-account-id 000000000000 --data-set-id 00000000-0000-0000-0000-000000000000 --query ‘DataSet.{DataSetID:DataSetId,SQL:PhysicalTableMap.[].|[0].SqlQuery}’

Is there any way to pull values for multiple data-set-ids in CloudShell? Would this be possible outside of CloudShell?

You would need to run the list-datasets command, loop through all datasets and run the describe-data-set for each id.

You can do this outside of CloudShell with API’s / locally using the cli.

Here is the python api to list datasets.

https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/quicksight/client/list_data_sets.html

2 Likes

Unfortunately I’m not able to run CLI locally but that would be ideal. Appreciate the response!