Question cross-account Amazon DynamoDB tables utilizing Amazon Athena Federated Question

Amazon DynamoDB is good for purposes that want a versatile NoSQL database with low learn and write latencies and the power to scale storage and throughput up or down as wanted with out code modifications or downtime. You need to use DynamoDB to be used instances together with cell apps, gaming, digital advert serving, reside voting, viewers interplay for reside occasions, sensor networks, log ingestion, entry management for web-based content material, metadata storage for Amazon S3 objects, e-commerce buying carts, and net session administration.

What when you have the necessity to enable different AWS accounts to question your DynamoDB desk? What if different accounts want to hitch knowledge in your DynamoDB desk with their knowledge saved in knowledge sources like Amazon CloudWatch, Amazon DocumentDB, Amazon Redshift, Amazon OpenSearch, MySQL, PostgreSQL linked with Athena knowledge supply connectors, and Amazon S3?

Amazon Athena cross-account federated question lets you run SQL queries throughout knowledge saved in relational, non-relational, object, and customized knowledge sources the place knowledge supply and its connector are in several AWS accounts from the consumer querying the info. There are not any new fees for querying connectors in one other account, however Athena’s commonplace charges for knowledge scanned, Lambda utilization, and different companies apply.

This publish will display Athena in an AWS account accessing a DynamoDB desk of one other AWS account through the use of the Athena cross-account federated question. It additionally explains deploying Amazon Athena DynamoDB connector utilizing AWS Serverless Utility Repository and establishing Athena cross-account federation between two accounts for the Demo.

Walkthrough

The answer has the next steps to display Athena cross-account federated question:

Arrange Athena federation – To deploy a Lambda perform for the info supply connector and join it to an information supply.
Arrange Athena cross-account federation – To arrange IAM permissions for Athena cross-account federation.
Check Athena cross-account federated question – To indicate a demo of how an AWS account can share its DynamoDB desk as an Athena knowledge supply with one other AWS account.

Stipulations

For this walkthrough, you must have the next stipulations:

Two AWS Accounts
AWS assets: Amazon Easy Storage Service (Amazon S3), Amazon Athena, Amazon DynamoDB, AWS Lambda

Information supply connectors

A knowledge supply connector is a bit of code that may translate between your goal knowledge supply and Athena. Athena makes use of knowledge supply connectors that run on AWS Lambda to run federated queries. You’ll be able to consider a connector as an extension of Athena’s question engine.

Connectors use Apache Arrow because the format for returning knowledge requested in a question, which allows connectors to be carried out in languages equivalent to C, C++, Java, Python, and Rust.

Athena makes use of knowledge supply connectors that run on AWS Lambda to run federated queries. Since connectors are processed in Lambda, they can be utilized to entry knowledge from any knowledge supply on the cloud or on premises that’s accessible from Lambda

To make use of a connector in your Athena queries, deploy it to your account utilizing one of many following methods:

This weblog makes use of the AWS Serverless Utility Repository to deploy the Amazon Athena DynamoDB connector.

After you deploy knowledge supply connectors, the connector is related to a catalog which you can specify in SQL queries. You’ll be able to mix SQL statements from a number of catalogs and span a number of knowledge sources with a single question. When a question is submitted in opposition to an information supply, Athena invokes the corresponding connector to determine components of the tables that should be learn, manages parallelism, and pushes down filter predicates. Primarily based on the consumer submitting the question, connectors can present or prohibit entry to particular knowledge parts.

Structure

AWS Account-A has a DynamoDB desk referred to as Music.
Account-A has an Athena knowledge supply connector to federate into DynamoDB.
AWS Account-B has Analysts who want to question the DynamoDB desk.
Account-A is sharing the Athena knowledge supply with Account-B through the use of Athena cross-account federated question.

The next determine reveals Amazon Athena cross-account federation for Account-B to entry DynamoDB in Account-A.

To display the Athena cross-account federation, create a pattern DynamoDB desk referred to as music in Account-A. Observe the directions at Getting began with DynamoDB to create the desk Music and cargo thesample knowledge.

Arrange Athena federation

Getting ready to create federated queries is a two-part course of: deploying a Lambda perform for the info supply connector and connecting the Lambda perform to an information supply. For extra particulars, see Enabling cross-account federated queries.

Deploy AthenaDynamoDBConnector utilizing AWS Serverless Utility Repository

Register as an administrator to AWS Account-A.
Open the Serverless Utility Repository.
Within the navigation pane, select Out there purposes.
Choose the choice Present apps that create customized IAM roles or useful resource insurance policies.
Within the search field, kind the title of the connector AthenaDynamoDBConnector.
Selecting a connector opens the Lambda perform’s Utility particulars web page within the AWS Lambda console.
On the suitable facet of the main points web page, for Utility settings, fill within the required info.
- Utility title – Identify of AWS CloudFormation Stack to deploy the connector: AthenaDynamoDBConnector.
- AthenaCatalogName – It’s the catalog title to create in Athena. It’s also the title of the Lambda perform. Give it in lowercase: acct1dynamodb.
- SpillBucket – Specify an present S3 bucket (spill-bucket) in your account to obtain knowledge from any giant response payloads that exceed Lambda perform response dimension limits.
Choose I acknowledge that this app creates customized IAM roles and useful resource insurance policies. For extra info, select the Data hyperlink.
On the backside proper of the Utility settings part, select Deploy.
Serverless Utility Repository will create an AWS CloudFormation stack to deploy the connector.
When the deployment is full, you will notice the Lambda perform within the Sources part of the AWS CloudFormation stack. Word down the Lambda perform title.

Join Athena to the info supply

Go to Athena console in Account-A.
Select Information sources. Click on Create Information supply.
In Select knowledge supply, seek for Amazon DynamoDB and choose it.
In Information supply particulars, give a Information supply title acct1dynamodb
For Lambda perform within the Connection particulars part, select the title of the perform acct1dynamodb from the dropdown.
On the Evaluation and create web page, evaluation the info supply particulars, after which select Create knowledge supply.
You will notice the info supply acctdynamodb within the Information sources.
Go to Question editor. Select the Information Supply acct1dynamodb from the dropdown.
You will notice all of the tables within the shared knowledge supply.

Run the next SQL in Athena Question editor

SELECT songtitle, albumtitle, solid(awards as int) as awards 
FROM "acct1dynamodb"."default"."music" 
WHERE artist="Acme Band" 
restrict 2;

Confirm Athena federation works.

Arrange Athena cross-account federation

In Account-A: Arrange IAM permissions for cross-account

Register as an administrator to Account-A.
On the S3 spill bucket (of the Lambda perform), grant GetObject and ListBucket permissions to the IAM consumer analyst of Account-B.

Word: Exchange Account-B-id along with your precise AWS cross-account id to which you wish to share the DynamoDB desk. Exchange spill-bucket along with your precise S3 bucket in Account-A.

{
    "Model": "2008-10-17",
    "Assertion": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": ["arn:aws:iam::Account-B-id:user/analyst"]
            },
            "Motion": [
                "s3:GetObject",
                "s3:ListBucket"
             ],
            "Useful resource": [
                "arn:aws:s3::: spill-bucket",
                "arn:aws:s3::: spill-bucket/*"
            ]
        }
     ]
 }

Grant InvokeFunction on Lambda perform acct1dynamodb to IAM consumer analyst of Account-B.

Word: Exchange Account-A-id along with your precise AWS account id the place you might have the DynamoDB desk. Exchange Account-B-id along with your precise AWS cross-account id to which you wish to share the DynamoDB desk.

{
  "Model": "2012-10-17",
  "Assertion": [
    {
      "Sid": "CrossAccountInvocationStatement",
      "Effect": "Allow",
      "Principal": {
        "AWS": ["arn:aws:iam::Account-B-id:user/analyst"]
      }, 
      "Motion": "lambda:InvokeFunction",
      "Useful resource": "arn:aws:lambda:aws-region:Account-A-id:perform:acct1dynamodb"
    }
  ]
}

Go to the Lambda perform acct1dynamodb. Select Configuration and Permissions.

Go to Useful resource-based coverage and Add permissions.

If you save the above permissions, you may see them below Coverage statements in Useful resource-based coverage of the Lambda perform.

In Account-B: Arrange IAM permissions for cross-account

Register as an administrator to AWS Account-B.
Create IAM function referred to as AthenaCrossAccountFederated-Account-A-id for Account-A to imagine. Add the next inline coverage to the function.

Word: Exchange Account-B-id along with your precise AWS cross-account id to which you wish to share the DynamoDB desk.

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Effect": "Allow",
            "Action": "athena:CreateDataCatalog",
            "Resource": "arn:aws:athena:aws-region:Account-B-id:datacatalog/*"
        }
    ]
}

Grant permission to the IAM consumer analyst to invoke the Lambda perform acct1dymanodb of Account-A

Word: Exchange Account-A-id along with your precise AWS account id the place you might have the DynamoDB desk.

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Effect": "Allow",
            "Action": "lambda:InvokeFunction",
            "Resource": "arn:aws:lambda:aws-region:Account-A-id:function:acct1dynamodb"
        }
    ]
}

Share the Athena Information supply with Account-B

After the permissions are in place, you share an information connector in your account (Account-A) with one other account (Account-B). Account-A retains full management and possession of the connector. When Account-A makes configuration modifications to the connector, the up to date configuration applies to the shared connector in Account-B.

Register as an administrator to Account-A.
On Athena, go to Information sources, select knowledge supply acct1dynamodb you wish to share. Go to the Share possibility within the high proper nook.

For Account ID, enter the Account-B-id to share your knowledge supply with Account-B and click on Share.

Check Athena cross-account federated question: Entry the shared knowledge supply from Account-B

Register as IAM consumer analyst to Account-B.
In Athena, go to Information sources. You will notice the info supply acct1dynamodb.

Go to Question editor. Select the Information Supply acct1dynamodb from the dropdown.

You will notice all of the tables within the shared knowledge supply.

Run the next SQL in Athena Question editor

SELECT songtitle, albumtitle, solid(awards as int) as awards 
FROM "acct1dynamodb"."default"."music" 
WHERE artist="Acme Band" 
restrict 2;

Athena cross-account federated has labored! This validates that consumer analyst in Account-B can see the info of the DynamoDB desk of Account-A.

Clear up

To keep away from incurring future fees, delete the next assets that have been provisioned for this demo:

S3 spill bucket utilized in AWS Lambda
Lambda perform used for the info supply connector
Pattern DynamoDB desk

Conclusion

On this publish, we noticed how one can entry a cross-account DynamoDB desk utilizing Athena Federated Question to question the info in place. You’ll be able to execute a single SQL question to hitch this knowledge throughout knowledge sources like Amazon CloudWatch, Amazon DocumentDB, Amazon Redshift, Amazon OpenSearch, MySQL, PostgreSQL, Oracle, SQL Server, HBase, Redis, BigQuery, Snowflake, Teradata with Athena knowledge supply connectors and Amazon S3.

In regards to the creator

Satya Adimula is a Senior Information Architect at AWS primarily based in Boston. With in depth expertise in knowledge and analytics, Satya helps organizations derive their enterprise insights from the info at scale.