Authorizing Connections to Amazon Athena - Amazon QuickSight

Authorizing Connections to Amazon Athena

If you need use Amazon QuickSight with Amazon Athena or Amazon Athena Federated Query, you first need to authorize connections to Athena and the associated buckets in Amazon Simple Storage Service (Amazon S3). Amazon Athena is an interactive query service that makes it easy to analyze data directly in Amazon S3 using standard SQL. Athena Federated Query provides access to more types of data by using AWS Lambda. Using a connection from QuickSight to Athena, you can write SQL queries to interrogate data that's stored in relational, non-relational, object, and custom data sources. For more information, see Using Amazon Athena Federated Query in the Amazon Athena User Guide.

Review the following considerations when setting up access to Athena from QuickSight:

  • Athena stores query results from QuickSight in a bucket. By default, this bucket has a name similar to aws-athena-query-results-AWSREGION-AWSACCOUNTID, for example aws-athena-query-results-us-east-2-111111111111. Therefore, it's important to make sure QuickSight has permissions to access the bucket Athena is currently using.

  • If your data file is encrypted with an AWS KMS key, grant permissions to the Amazon QuickSight IAM role to decrypt the key. The easiest way to do this is to use the AWS CLI.

    You can run the KMS create-grant API operation in AWS CLI to do this.

    aws kms create-grant --key-id <KMS_KEY_ARN> / --grantee-principal <QS_ROLE_ARN> --operations Decrypt

    The Amazon Resource Name (ARN) for the Amazon QuickSight role has the format arn:aws:iam::<account id>:role/service-role/aws-quicksight-s3-consumers-role-v<version number> and can be accessed from the IAM console. To find your KMS key ARN, use the S3 console. Go to the bucket that contains your data file and choose the Overview tab. The key is located near KMS key ID.

  • For Amazon Athena, Amazon S3, and Athena Query Federation connections, QuickSight uses the following IAM role by default:

    arn:aws:iam::AWS-ACCOUNT-ID:role/service-role/aws-quicksight-s3-consumers-role-v0

    If the aws-quicksight-s3-consumers-role-v0 is not present, then QuickSight uses:

    arn:aws:iam::AWS-ACCOUNT-ID:role/service-role/aws-quicksight-service-role-v0
  • If you assigned scope-down policies to your users, verify that the policies contain the lambda:InvokeFunction permission. Without this permission, your users can't access Athena Federated Queries. For more information about assigning IAM policies to your users in QuickSight, see Setting Granular Access to AWS Services Through IAM. For more information about the lambda:InvokeFunction permission, see Actions, resources, and condition keys for AWS Lambda in the IAM User Guide.

To authorize QuickSight to connect to Athena or Athena Federated Data Sources

  1. (Optional) If you are using AWS Lake Formation with Athena, you also need to enable Lake Formation. For more information, see Authorizing Connections Through AWS Lake Formation.

  2. Open your profile menu at top right and choose Manage QuickSight. You must be a QuickSight administrator to do this. If you don't see Manage QuickSight on the profile menu, you don't have sufficient permissions.

  3. Choose Security & permissions, Add or remove.

  4. Choose the box near Amazon Athena, Next.

    If it was already enabled, you might have to double-click it. Do this even if Amazon Athena is already enabled, so you can view the settings. No changes are saved until you choose Update at the end of this procedure.

  5. Enable the S3 buckets you want to access.

  6. (Optional) To enable Athena federated queries, select the Lambda functions you want to use.

    Note

    You can only see Lambda functions for the Athena catalogs in the same region of QuickSight.

  7. To confirm your changes, choose Finish.

    To cancel, choose Cancel.

  8. To save changes to security and permissions, choose Update.

To test the connection authorization settings

  1. From the QuickSight start page, choose Datasets, New dataset.

  2. Choose the Athena card.

  3. Follow the screen prompts to create a new Athena data source using the resources you need to connect to. Choose Validate connection to test the connection.

  4. If the connection validates, you have successfully configured an Athena or Athena Federated Query connection.

    If you don't have sufficient permissions to connect to an Athena dataset or run an Athena query, an error displays directing you to contact a QuickSight administrator. This error means need to recheck your connection authorization settings to find the discrepancy. .

  5. After you can connect successfully, you or your QuickSight authors can create data sources connections and share them with other QuickSight authors. The authors can then create multiple datasets from the connections, to use in QuickSight dashboards.

    For troubleshooting information on Athena, see Troubleshooting Issues When Using Athena with Amazon QuickSight.