Get list of all CustomSQL values across datasets

Question & Answer
, ,
1

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?

2

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

3 Likes
3

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