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

Amazon DynamoDB is good for functions that want a versatile NoSQL database with low learn and write latencies and the flexibility to scale storage and throughput up or down as wanted with out code modifications or downtime. You should utilize DynamoDB to be used circumstances together with cellular 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 in case you have the necessity to enable different AWS accounts to question your DynamoDB desk? What if different accounts want to affix 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 numerous AWS accounts from the person querying the info. There aren’t any new expenses for querying connectors in one other account, however Athena’s customary charges for knowledge scanned, Lambda utilization, and different companies apply.

This submit will exhibit 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 Software Repository and organising Athena cross-account federation between two accounts for the Demo.

Walkthrough

The answer has the next steps to exhibit 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.
• Take a look at Athena cross-account federated question – To point out a demo of how an AWS account can share its DynamoDB desk as an Athena knowledge supply with one other AWS account.

Conditions

For this walkthrough, you must have the next conditions:

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

Information supply connectors

An information 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 possibly can 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 permits connectors to be carried out in languages comparable 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 Software Repository to deploy the Amazon Athena DynamoDB connector.

After you deploy knowledge supply connectors, the connector is related to a catalog you can specify in SQL queries. You possibly can 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 establish elements of the tables that have to be learn, manages parallelism, and pushes down filter predicates. Primarily based on the person submitting the question, connectors can present or limit entry to particular knowledge parts.

Structure

• AWS Account-A has a DynamoDB desk known 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 exhibits Amazon Athena cross-account federation for Account-B to entry DynamoDB in Account-A.

To exhibit the Athena cross-account federation, create a pattern DynamoDB desk known 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

Making 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 Software Repository

1. Register as an administrator to AWS Account-A.
2. Open the Serverless Software Repository.
3. Within the navigation pane, select Out there functions.
4. Choose the choice Present apps that create customized IAM roles or useful resource insurance policies.
5. Within the search field, sort the identify of the connector AthenaDynamoDBConnector.
   

   

6. Selecting a connector opens the Lambda perform’s Software particulars web page within the AWS Lambda console.
7. On the best facet of the small print web page, for Software settings, fill within the required data.
   • Software identify – Title of AWS CloudFormation Stack to deploy the connector: AthenaDynamoDBConnector.
   • AthenaCatalogName – It’s the catalog identify to create in Athena. Additionally it is the identify of the Lambda perform. Give it in lowercase: acct1dynamodb.
   • SpillBucket – Specify an current S3 bucket (spill-bucket) in your account to obtain knowledge from any massive response payloads that exceed Lambda perform response measurement limits.
     
8. Choose I acknowledge that this app creates customized IAM roles and useful resource insurance policies. For extra data, select the Information hyperlink.
9. On the backside proper of the Software settings part, select Deploy.
10. Serverless Software Repository will create an AWS CloudFormation stack to deploy the connector.
11. When the deployment is full, you will note the Lambda perform within the Assets part of the AWS CloudFormation stack. Word down the Lambda perform identify.
    

    

Join Athena to the info supply

1. Go to Athena console in Account-A.
2. Select Information sources. Click on Create Information supply.
   
3. In Select knowledge supply, seek for Amazon DynamoDB and choose it.
   
4. In Information supply particulars, give a Information supply identify acct1dynamodb
   
5. For Lambda perform within the Connection particulars part, select the identify of the perform acct1dynamodb from the dropdown.
   
6. On the Overview and create web page, overview the info supply particulars, after which select Create knowledge supply.
   
   
7. You will notice the info supply acctdynamodb within the Information sources.
   
8. Go to Question editor. Select the Information Supply acct1dynamodb from the dropdown.
   
9. You will notice all of the tables within the shared knowledge supply.
   
10. Run the next SQL in Athena Question editor 

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

11. Confirm Athena federation works.
    

Arrange Athena cross-account federation

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

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

Word: Substitute Account-B-id together with your precise AWS cross-account id to which you wish to share the DynamoDB desk. Substitute spill-bucket together 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/*"
            ]
        }
     ]
 }

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

Word: Substitute Account-A-id together with your precise AWS account id the place you’ve got the DynamoDB desk. Substitute Account-B-id together 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"
    }
  ]
}

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

5. Go to Useful resource-based coverage and Add permissions.

Once you save the above permissions, you’ll be able to see them beneath Coverage statements in Useful resource-based coverage of the Lambda perform.

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

1. Register as an administrator to AWS Account-B.
2. Create IAM position known as AthenaCrossAccountFederated-Account-A-id for Account-A to imagine. Add the next inline coverage to the position.

Word: Substitute Account-B-id together 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/*"
        }
    ]
}

3. Grant permission to the IAM person analyst to invoke the Lambda perform acct1dymanodb of Account-A

Word: Substitute Account-A-id together with your precise AWS account id the place you’ve got 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.

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

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

Take a look at Athena cross-account federated question: Entry the shared knowledge supply from Account-B

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

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

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

4. Run the next SQL in Athena Question editor

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

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

Clear up

To keep away from incurring future expenses, 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 submit, we noticed how one can entry a cross-account DynamoDB desk utilizing Athena Federated Question to question the info in place. You possibly can execute a single SQL question to affix 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.

Concerning the writer

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