Big Data

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

3 years ago
Add Comment
by admin
Question cross-account Amazon DynamoDB tables utilizing Amazon Athena Federated Question
Written by admin


Amazon DynamoDB is right 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 instances together with cellular apps, gaming, digital advert serving, dwell voting, viewers interplay for dwell occasions, sensor networks, log ingestion, entry management for web-based content material, metadata storage for Amazon S3 objects, e-commerce procuring carts, and internet session administration.

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

Amazon Athena cross-account federated question lets you run SQL queries throughout information saved in relational, non-relational, object, and customized information sources the place information supply and its connector are in numerous AWS accounts from the consumer querying the info. There aren’t any new expenses for querying connectors in one other account, however Athena’s normal charges for information scanned, Lambda utilization, and different providers apply.

This publish will show Athena in an AWS account accessing a DynamoDB desk of one other AWS account by utilizing 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 show Athena cross-account federated question:

  • Arrange Athena federation – To deploy a Lambda operate for the info supply connector and join it to a knowledge 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 information supply with one other AWS account.

Stipulations

For this walkthrough, it’s best to have the next conditions:

  • Two AWS Accounts
  • AWS sources: 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 information supply and Athena. Athena makes use of information supply connectors that run on AWS Lambda to run federated queries. You may consider a connector as an extension of Athena’s question engine.

Connectors use Apache Arrow because the format for returning information requested in a question, which allows connectors to be applied in languages resembling C, C++, Java, Python, and Rust.

Athena makes use of information supply connectors that run on AWS Lambda to run federated queries. Since connectors are processed in Lambda, they can be utilized to entry information from any information 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 information supply connectors, the connector is related to a catalog which you could specify in SQL queries. You may mix SQL statements from a number of catalogs and span a number of information sources with a single question. When a question is submitted towards a knowledge supply, Athena invokes the corresponding connector to determine components of the tables that should be learn, manages parallelism, and pushes down filter predicates. Based mostly on the consumer submitting the question, connectors can present or limit entry to particular information parts.

Structure

  • AWS Account-A has a DynamoDB desk known as Music.
  • Account-A has an Athena information supply connector to federate into DynamoDB.
  • AWS Account-B has Analysts who want to question the DynamoDB desk.
  • Account-A is sharing the Athena information supply with Account-B by utilizing Athena cross-account federated question.

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

To show 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 information.

Arrange Athena federation

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

Deploy AthenaDynamoDBConnector utilizing AWS Serverless Software Repository

  1. Sign up as an administrator to AWS Account-A.
  2. Open the Serverless Software Repository.
  3. Within the navigation pane, select Obtainable 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 operate’s Software particulars web page within the AWS Lambda console.
  7. On the appropriate aspect of the main points web page, for Software settings, fill within the required info.
    • Software identify – Identify 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 operate. Give it in lowercase: acct1dynamodb.
    • SpillBucket – Specify an current S3 bucket (spill-bucket) in your account to obtain information from any massive response payloads that exceed Lambda operate response measurement limits.
  8. Choose I acknowledge that this app creates customized IAM roles and useful resource insurance policies. For extra info, select the Data 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 operate within the Assets part of the AWS CloudFormation stack. Notice down the Lambda operate 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 information supply, seek for Amazon DynamoDB and choose it.
  4. In Information supply particulars, give a Information supply identify acct1dynamodb
  5. For Lambda operate within the Connection particulars part, select the identify of the operate acct1dynamodb from the dropdown.
  6. On the Evaluate and create web page, evaluation the info supply particulars, after which select Create information 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 information 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. Sign up as an administrator to Account-A.
  2. On the S3 spill bucket (of the Lambda operate), grant GetObject and ListBucket permissions to the IAM consumer analyst of Account-B.

Notice: Change Account-B-id along with your precise AWS cross-account id to which you need to share the DynamoDB desk. Change 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/*"
            ]
        }
     ]
 }

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

Notice: Change Account-A-id along with your precise AWS account id the place you have got the DynamoDB desk. Change Account-B-id along with your precise AWS cross-account id to which you need 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:operate:acct1dynamodb"
    }
  ]
}

  1. Go to the Lambda operate acct1dynamodb. Select Configuration and Permissions.

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

Once you save the above permissions, you may see them underneath Coverage statements in Useful resource-based coverage of the Lambda operate.

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

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

Notice: Change Account-B-id along with your precise AWS cross-account id to which you need 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/*"
        }
    ]
}

  1. Grant permission to the IAM consumer analyst to invoke the Lambda operate acct1dymanodb of Account-A

Notice: Change Account-A-id along with your precise AWS account id the place you have 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 a knowledge 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. Sign up as an administrator to Account-A.
  2. On Athena, go to Information sources, select information supply acct1dynamodb you need to share. Go to the Share possibility within the prime proper nook.

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

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

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

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

  1. You will notice all of the tables within the shared information supply.

  1. 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;

  1. 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 expenses, delete the next sources that had been provisioned for this demo:

  • S3 spill bucket utilized in AWS Lambda
  • Lambda operate 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 may execute a single SQL question to affix this information throughout information sources like Amazon CloudWatch, Amazon DocumentDB, Amazon Redshift, Amazon OpenSearch, MySQL, PostgreSQL, Oracle, SQL Server, HBase, Redis, BigQuery, Snowflake, Teradata with Athena information 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 information and analytics, Satya helps organizations derive their enterprise insights from the info at scale.

You may also like

About the author

admin

View all posts

Leave a Comment