Use Amazon Athena and Amazon QuickSight in a cross-account environment
Many AWS customers use a multi-account strategy to host applications for different departments within the same company. However, you might deploy services like Amazon QuickSight using a single-account approach, which raises challenges when you need to use QuickSight in combination with Amazon Athena to build reports and dashboards. With the recently announced built-in support for cross-account Data Catalogs in Athena, you can now use AWS Glue Data Catalogs in different accounts to create datasets, and build reports and dashboards from a single AWS account using QuickSight and Athena – creating a serverless data visualization solution that lets you share insights from all your data to all your users.
In this post, I show you how to use this new feature to set up cross-account access to Athena for QuickSight.
To set up cross-account access, you complete the following steps:
- Grant QuickSight cross-account access to an AWS Glue Data Catalog.
- Register the Data Catalog in Athena.
- Grant QuickSight cross-account access to an Amazon Simple Storage Service (Amazon S3) bucket.
- Add the shared bucket to QuickSight.
- Connect QuickSight to Athena.
The following architecture shows the deployment steps.
Grant cross-account access for the Data Catalog
QuickSight uses a service role to interact with other AWS services. QuickSight creates this role for you under the name
aws-quicksight-s3-consumers-role-v0. You need this role to allow access to the Data Catalog cross-account share. To allow the QuickSight service role (Account A, the borrower account) to access the Data Catalog (Account B, the owner account), you need to grant cross-account access by updating the AWS Glue resource policy.
In the AWS account of the Data Catalog, complete the following steps:
- On the AWS Glue console, choose Settings in the navigation pane.
- Under Permissions, enter the following resource policy:
- Choose Save.
The resource policy gives QuickSight access to all the databases and tables in the Data Catalog. You can further scope it down by adding the name of the tables and databases to the resource element.
The following screenshot shows the Settings page on the AWS Glue console and the catalog UI for updating the resource permission.
Register the Data Catalog in Athena
Now you need to register the shared Data Catalog with Athena in the AWS account (borrower) that hosts QuickSight.
- On the Athena console, choose Data sources in the navigation pane.
- Choose Connect data source.
- For Choose where your data is located, select Query data in Amazon S3.
- For Choose a metadata catalog, select AWS Glue Data Catalog.
- Choose AWS Glue Data Catalog in another account.
- For Connection details, enter a Data Catalog name, optional description, and the Data Catalog owner’s AWS account ID.
- Choose Register.
When you complete these steps, you can see the borrowed catalog on the Data sources page on the Athena console.
Grant QuickSight cross-account access to an S3 bucket
Creating a resource policy on the Data Catalog to allow cross-account access for QuickSight is not sufficient. You also need to grant QuickSight access to the S3 bucket where the data is stored. You use the same QuickSight service role that we used for the Data Catalog to update the S3 bucket policy.
In the account of the Data Catalog, complete the following steps:
- On the Amazon S3 console, choose Buckets.
- Choose the bucket that you want to create a policy for, or whose policy you want to edit.
- Choose Permissions.
- Enter the following policy:
- Choose Save changes.
Add the shared S3 bucket to QuickSight
The last step before you can connect QuickSight to Athena is to add the S3 bucket (Account B) as a resource that the QuickSight service role (Account A) can access. To allow your QuickSight service role access to the S3 bucket in another account, perform the following steps:
- On the QuickSight console, on the account drop-down menu, choose Manage QuickSight.
- Choose Security & permissions.
- Choose Add or remove.
- Choose Details.
- Choose Select S3 buckets.
- Under Use a different bucket, add your bucket.
- Choose Finish.
Connect QuickSight to Athena
After you set up the necessary permissions, you can follow the instruction in this section to add a dataset in Athena by using the remote (borrowed) Data Catalog.
- On the Athena console, choose Datasets in the navigation pane.
- Choose New dataset.
- Create a new connection profile by providing a data source name and Athena workgroup.
- Choose Validate connection.
- Choose Create data source.
- In the Choose your table section, for Catalog, choose the catalog you created in Athena.
- Choose a database and table and click on Select.
- Choose Edit/Preview data.
- To create a dataset and analyse the data using the table, choose Visualize.
This post showed how to use the built-in support for a cross-account Data Catalog in Athena with Quicksight when the Data Catalog and the S3 bucket containing the data are in a different account. This feature greatly reduces operational overhead by having a single account managing the Data Catalog and its data.
After you set up your data sources, you can join your data across these various sources. You can also use these new data sources to gain further insights from your data by setting up ML Insights in QuickSight and set graphical representations of your data using QuickSight visuals.
Lotfi is a Senior Solutions Architect working for the Public Sector team with Amazon Web Services. He helps public sector customers across EMEA realize their ideas, build new services, and innovate for citizens. In his spare time, Lotfi enjoys cycling and running.