Overriding the default Athena schema with AWS Quicksight start-asset-bundle-import-job API via CLI

Question & Answer
, , , ,
1

I have two aws accounts with an AWS QuickSight dashboard in my source environment and am trying to copy that dashboard to my target environment on the other account. I have identical Athena databases/tables in both environments with the exception of the schema name. I can’t figure out how to issue or format the --override-parameters argument in the CLI to supply the appropriate schema name (or otherwise), so when I execute the start-asset-bundle-import-job. However, if I run start-asset-bundle-export-job on my source environment and download the output .qs bundle, and edit the schema name in the datasets .json file, and then simply run start-asset-bundle-import-job, everything works. If I attempt to update the dataset via the CLI using the --physical-table-map, the dashboards are unable to connect to the data. I’ve also tried running the --override-parameters option with no luck. Does anyone know how to issue change the schema of the Athena database upon import with the API?

For reference here is some toy code that explains what I have tried:

Export the bundle from the source environment, called “dev”:

aws quicksight start-asset-bundle-export-job --profile dev --aws-account-id XXXXXXXXXXXX --asset-bundle-export-job-id="migration_6_export" --resource-arns='arn:aws:quicksight:us-east-1:XXXXXXXXXXXX:dashboard/redacted-redacted-redacted-9817-redacted' --include-all-dependencies --export-format "QUICKSIGHT_JSON"

Then after running describe-asset-bundle-export-job I obtain the migration_6_export.js file and upload it to an s3 bucket my target environment “val”. Next, I imported the bundle successfully:

aws quicksight start-asset-bundle-import-job --profile dev --aws-account-id YYYYYYYYYYYY --asset-bundle-import-job-id="migration_6_export" --asset-bundle-import-source S3Uri="s3://val-quicksight-migration-files/migration_6_export.qs"

I can now see the dashboard in my target environment. I figured this would require a simple updating of the dataset schema using the following code, since the Athena database schema “my-new-schema-database-in-athena-db” already exists in my target enviroment, but this does not work:

   aws quicksight update-data-set --profile ocse-val-dev --aws-account-id YYYYYYYYYYYY --data-set-id 'redacted-c5ec-redacted-redacted-redacted' --name "migration_dummy_table" --import-mode "SPICE" --physical-table-map='{"redacted-513e-redacted-redacted-redacted": {
                    "RelationalTable": {
                        "DataSourceArn": "arn:aws:quicksight:us-east-1:YYYYYYYYYYYY :datasource/redacted-e621-redactedredacted-redacted",
                        "Catalog": "AwsDataCatalog",
                        "Schema": "my-new-schema-database-in-athena-db",
                        "Name": "migration_dummy_table",
                        "InputColumns": [
                            {
                                "Name": "id",
                                "Type": "INTEGER"
                            },
                            {
                                "Name": "total_cases",
                                "Type": "DECIMAL",
                                "SubType": "FLOAT"
                            }
                        ]
                    }
                }
            }'

If I now review the dashboard, the visuals are not displayed. Instead I get an error on each visual that reads, “Fields for this Visual no longer in Analysis.”

Can anyone help or provide some suggestions? I’d really appreciate some sample api commands if anyone can assist. For reference, I’ve been following the recording here:

https://www.youtube.com/watch?v=VhAumX35-X0 as well as the blog blost here on this topic:
https://aws.amazon.com/blogs/business-intelligence/automate-and-accelerate-your-amazon-quicksight-asset-deployments-using-the-new-apis/

1 Like
2

Hello @MJL, my first suggestion would be to run that same bundle-assets command on the new dashboard that you created. Then you can compare it with the source dashboard bundle to see what does not match. That would allow us to troubleshoot this together a little more easily. We will be able to see if the issue is just in the update dataset syntax.

3

We hope this solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking this answer as a “Solution.” (click the check box under the reply)

4

I actually did this. However, the only difference I’ve been able to locate is the schema name in the dataset RelationalTable physical-table-map. But when I update the schema name after import using the last code chunk that I posted above, this doesn’t seem to update. So I’m wondering if I should be using some override parameter on the start-asset-bundle-import-job call to remedy this as opposed to trying to perform an update on the dataset after the import? All of the start-asset-bundle-import-job examples I’ve found on the web seem to use only a csv file in an S3 bucket as opposed to an Athena table. If anyone has an example of updating the schema with an override parameter, perhaps, I think I could try this method. I’ve looked at the documentation, but I’m afraid I might not be attempting the dataset update correctly when I import (I’m very new to AWS, QuickSight, and the API, so an example would go a long way, because I’m still learning a bit how to navigate the documentation and get code to work without spending a lot of time with trial and error).

Thanks, in advance, for any further assistance you can provide.

1 Like
5

This solution did not work.

6

Hello @MJL, I do not have an example to link for this specific scenario, but did you run a command to describe the dataset after attempting to update it? I am wondering if there would be more details we could use to debug it in that instance. Also, a part of the problem might be that Athena will require different parameters than a CSV when creating a datasource/dataset through the start-asset-bundle-import-job API. I found this piece of information in the SDK documentation:

image
image809×359 11.7 KB

I know you are using the CLI, but there may be some helpful information in these docs as well:
https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/quicksight/client/start_asset_bundle_import_job.html

7

Hello @MJL, since we have not heard back from you, I wanted to check in and see if you were still working to resolve this issue. I will mark my above response as the solution for now, but please let me know if you have any remaining questions.

If we do not receive a response in the next 3 days, I will archive this topic. Thank you!