Cross AWS Account Enterprise Athena With Dynamo DB and Lambda

Question & Answer
, ,
1

We have data in DynamoDB that we want to show in an adsbi Quicksight dashboard. We are looking to onboard an Athena table using DDB as the data source via Athena/DDB connector with result outputs written in S3.

This connector needs to be onboarded as a POC to verify connectivity using an Athena DynamoDB connector + Athena Federated Query.

We are getting errors with connection and reading tables. What can we do?

2

Thanks to Eric from our partner team for requesting this and bearing with me while I was trying to find the ideal solution for this issue in our account.

Step-by-Step Solution for Cross-Account Athena Data Source Access Using Lambda Connector

In Account A (Source Account with DynamoDB and Athena)

Step 1: Create and Configure IAM Role

  1. Create IAM Role in Account A:

    • Go to the IAM console in Account A.
    • Create a new role, choose “Another AWS account” as the trusted entity, and enter the Account ID of Account B (where QuickSight is running).

  2. Attach Policies to the Role:

    • Attach policies to the role to allow access to Athena and DynamoDB. Here’s an example policy:
      {
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "dynamodb:Query",
        "dynamodb:Scan",
        "dynamodb:GetItem",
        "dynamodb:BatchGetItem",
        "lambda:InvokeFunction",
        "s3-object-lambda:GetObject"
      ],
      "Resource": [
        "arn:aws:dynamodb:us-east-1:<AccountA-ID>:table/CampaignMeasurementAudiences",
        "arn:aws:lambda:us-east-1:<AccountA-ID>:function:athena-ddb-connector-data-catalog"
      ]
    }
  ]
}

  3. Allow Assume Role from Account B:

    • In the trust relationship of the IAM role, add permissions for the QuickSight service role from Account B to assume this role:
      {
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::<AccountB-ID>:role/service-role/aws-quicksight-service-role-v0"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

In Account B (QuickSight Account)

Step 2: Create Lambda Connector for Athena to DynamoDB

  1. Create a Lambda Connector in Athena:

    • Go to the Athena console in Account B.
    • Create a data source using a custom or shared Lambda connector. Specify the Lambda function from Account A.

  2. Update IAM Policy in Account B:

    • Update the IAM policy in Account B to allow necessary actions and resources. Here’s an example policy:
      {
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "sts:AssumeRole",
        "dynamodb:*",
        "lambda:*",
        "s3-object-lambda:*"
      ],
      "Resource": [
        "arn:aws:iam::<AccountA-ID>:role/athena-ddb-connector-data-catalog-connector-role",
        "arn:aws:lambda:us-east-1:<AccountA-ID>:function:athena-ddb-connector-data-catalog"
      ]
    }
  ]
}

Step 3: Update Athena Data Source to Use Lambda Connector

  1. Go to the Athena Console in Account B:

    • Log in to the Athena console in Account B.

  2. Create or Update the Data Source:

    • Create a new data source or update an existing data source to use the Lambda connector.
    • Specify the Lambda function ARN from Account A as the data source connector.

  3. Specify the New Database and Catalog:

    • Provide the details of the new database and catalog onboarded in Athena.

Step 4: Configure QuickSight to Use the Existing Athena Data Source

  1. Go to the QuickSight Console:

    • Log in to the QuickSight console in Account B.

  2. Create a New Dataset:

    • Navigate to Datasets in QuickSight.
    • Click on New Dataset and choose the existing Athena data source you updated in Step 3.

  3. Select the Database and Table:

    • Choose the new database (mydatabase) and select the table you want to use.

  4. Finish Creating the Dataset:

    • Follow the prompts to finish creating the dataset.
    • Optionally, you can prepare and visualize the data as needed.

Testing and Validation

  1. After setting up the dataset, run a simple analysis in QuickSight to ensure it can access the data from Athena in Account A without any issues.
    SELECT * FROM mydatabase.mytable LIMIT 10;

Summary

  1. Account A:

    • Create and configure an IAM role with permissions to access DynamoDB and invoke the Lambda connector.
    • Ensure the IAM role allows Account B to assume it.

  2. Account B:

    • Create a Lambda connector for Athena to DynamoDB using the ARN for Account A’s Lambda function.
    • Update the IAM policy in Account B to allow necessary actions and resource access.
    • Update the Athena data source to use the new Lambda connector, specifying the new database and catalog.
    • Create a new dataset in QuickSight using the existing Athena data source.

By following these steps, you should be able to set up cross-account access using the Lambda connector, allowing QuickSight in Account B to query DynamoDB tables in Account A via Athena. This solution ensures the proper setup of permissions and connectivity without modifying the default QuickSight service role.

1 Like