Tuesday, July 5, 2022
HomeBig DataVisualize MongoDB information from Amazon QuickSight utilizing Amazon Athena Federated Question

Visualize MongoDB information from Amazon QuickSight utilizing Amazon Athena Federated Question

On this put up, you’ll discover ways to use Amazon Athena Federated Question to attach a MongoDB database to Amazon QuickSight as a way to construct dashboards and visualizations.

Amazon Athena is a serverless interactive question service, primarily based on Presto, that gives full ANSI SQL assist to question quite a lot of customary information codecs, together with CSV, JSON, ORC, Avro, and Parquet, which are saved on Amazon Easy Storage Service (Amazon S3). For information that isn’t saved on Amazon S3, you should use Athena Federated Question to question the info in place or construct pipelines that extract information from a number of information sources and retailer it in Amazon S3. With Athena Federated Question, you possibly can run SQL queries throughout information that’s saved in relational, non-relational, object, and customized information sources.

MongoDB is a well-liked NoSQL database choice for web sites and API endpoints. You may select to deploy MongoDB as a self-hosted or fully-managed database. Databases are a preferred selection for UI purposes for managing person profiles, product catalogs, profile views, clickstream occasions, occasions from a linked machine, and so forth. QuickSight is a serverless enterprise analytics service with built-in machine studying (ML) capabilities that may robotically search for patterns and outliers, and has the pliability to embed dashboards in purposes for a data-driven expertise. You may as well use QuickSight Q to permit customers to ask questions utilizing pure language and discover solutions to enterprise questions instantly.

Overview of Athena Federated Question

Athena Federated Question makes use of information supply connectors that run on AWS Lambda to run federated queries to different information sources. Prebuilt information supply connectors can be found for native shops, like Amazon Timestream, Amazon CloudWatch Logs, Amazon DynamoDB, and exterior sources like Vertica and SAP Hana. You may as well write a connector by utilizing the Athena Question Federation SDK. You may customise Athena’s prebuilt connectors in your personal use, or modify a duplicate of the supply code to create your personal AWS Serverless Utility Repository bundle.

Answer overview

The next structure diagram exhibits the elements of the Athena Federated Question MongoDB connector. It accommodates the next elements:

  • A digital non-public cloud (VPC) configured with private and non-private subnets throughout three Availability Zones.
  • A MongoDB cluster with customizable Amazon Elastic Block Retailer (Amazon EBS) storage deployed in non-public subnets and NAT gateways in a public subnet for outbound web connectivity for MongoDB situations.
  • Bastion hosts in an auto scaling group with Elastic IP addresses to permit inbound SSH entry.
  • An AWS Identification and Entry Administration (IAM) MongoDBnode function with Amazon Elastic Compute Cloud (Amazon EC2) and Amazon S3 permissions.
  • Safety teams to allow communication inside the VPC.
  • Lambda features deployed in a non-public subnet accessing S3 buckets. Athena invokes the Lambda operate, which in flip fetches the info from MongoDB and maps the response again to Athena.
  • AWS Secrets and techniques Supervisor via a VPC endpoint.


To implement the answer, you want the next:

  • An AWS account to entry AWS providers.
  • An IAM person with permission to CreateRole, ListRoles, GetPolicy, and AttachRolePolicy.
  • An IAM person with an entry key and secret key to configure an built-in improvement setting (IDE).
  • A MongoDB database. You may deploy a hosted MongoDB on Amazon EC2 or MongoDB Atlas in a VPC.
  • For those who don’t have a QuickSight subscription configured, join one. You may entry the QuickSight free trial as a part of the AWS Free Tier choice.
  • A brand new secret in Secrets and techniques Supervisor to retailer your MongoDB person identify and password.
  • Information loaded into your MongoDB database. For this instance, we used an airline dataset. Load the pattern information both from the MongoDB command line or the MongoDB Atlas person interface, if utilizing MongoDB Atlas.

Configure a Lambda connector

Step one within the deployment is to arrange the connector setting. Athena makes use of information supply connectors that run on Lambda to run federated queries. To attach with MongoDB, use the Amazon Athena DocumentDB Connector, which additionally works with any endpoint that’s suitable with MongoDB.

To configure a Lambda connector, full the next steps:

  1. On the Athena console, select Information sources within the navigation pane.
  2. To view a broadcast checklist of information sources for Athena, choose Amazon DocumentDB.
  3. Select Subsequent.
  4. Within the Information supply particulars part, give your information supply a singular identify; for instance, ds_mongo.
    This would be the connection identify that seems underneath Information sources for Athena.
  5. Select Create Lambda operate.
    This launches the Create operate web page in Lambda. The connector is deployed by utilizing AWS Serverless Utility Repository.
  6. For SecretNameOrPrefix, enter mongo.
  7. For SpillBucket, enter spl-mongo-athena-test.
  8. For AthenaCatalogName, enter us-west-mongo-cat.
  9. For DocDBConnectionString (the MongoDB connection), enter the next:

  10. For SecurityGroupIds, select the safety group that you simply wish to affiliate with the operate. Guarantee that the safety group of the MongoDB occasion permits site visitors from the Lambda operate.
  11. For SpillPrefix, enter athena-spill.
  12. For Subnetids, enter the subnet IDs of subnets with MongoDB situations.
    On this case, LambdaMemory and LambdaTimeout have been set to the utmost values, however these can differ relying on the question run and reminiscence necessities. SpillBucket is an S3 bucket in your account to retailer information that exceeds the Lambda operate response measurement limits.
  13. Maintain the remainder as defaults.
  14. Choose the acknowledgement test field select Deploy.
    The connection operate is launched primarily based on the given parameters.
  15. Create a VPC endpoint to permit the Lambda operate to entry Amazon S3 via an endpoint.
    That is for the spill bucket. The spill bucket is a staging space for copying the outcomes of the queries which are carried out on MongoDB by way of Athena federation. That is in order that the Lambda operate within the VPC can entry Amazon S3.
  16. Return to the Athena console.
  17. Below Connection particulars, for Lambda operate, select the newly created Lambda operate.
  18. Select Subsequent.

  19. To confirm the connection, on the Athena console, select Information sources, then select ds_mongo.
    Related databases from the connection needs to be listed.

    You must now be capable to question the datasets from the Athena question editor by utilizing SQL.
  20. Within the question editor, for Information Supply, select ds_mongo.

Athena federates the question utilizing the connector, which invokes the Lambda operate. Then the question is carried out by the operate on MongoDB, and the question outcomes are translated again to Athena. The next is a pattern question that was carried out on the airways dataset.

Create a dataset on QuickSight to learn the info from MongoDB

Earlier than you launch QuickSight for the primary time in an AWS account, you should arrange an account. For directions, see Signing in to Amazon QuickSight.

After the preliminary setup, you possibly can create a dataset with Athena because the supply. The QuickSight service function wants permission to invoke the Lambda operate that connects MongoDB. The aws-quicksight-service-role-v0 service function is robotically created with the QuickSight account.

To create a dataset in QuickSight, full the next steps:

  1. On the IAM console, within the navigation pane, select Roles.
  2. Seek for the function aws-quicksight-service-role-v0 and add the permission Lambda _fullaccess.
    In a company, there might be totally different information shops primarily based on information load and consumption patterns. Examples embrace catalog or guide information that’s related to merchandise in a MongoDB or key-value index retailer, transactions or gross sales information in a SQL database, and pictures or video clips which are related to the product in an object retailer.
    On this case, an airways desk from MongoDB is joined with a flat file that accommodates data on the airports.
  3. Use the QuickSight cross-data retailer function to affix information from totally different sources on frequent fields.
  4. We then replace the info sorts for our geographic fields like fields like metropolis, nation, latitude, and longitude so we will construct maps later.
  5. You may as well create calculated fields whereas making ready your dataset, which lets you reuse them in different QuickSight analyses.

With a number of clicks, it’s best to be capable to create a dashboard with the revealed dataset. As an example, you possibly can plot your information on a map, present developments in a line chart, and add autonarratives from the checklist of Instructed Insights to create the evaluation proven within the following screenshot.

Clear up

Be certain that to wash up your sources to keep away from useful resource spend and related prices. You could delete the EC2 situations with MongoDB. Within the case of MongoDB Atlas, you possibly can delete the databases and tables. Delete the Athena information supply ds_mongo and unsubscribe your QuickSight account from the Handle QuickSight admin web page.


With QuickSight and Athena Federated Question, organizations can entry extra information sources past these already supported by QuickSight. In case you have information in sources apart from Amazon S3, you should use Athena Federated Question to investigate the info in place or construct pipelines that extract and retailer information in Amazon S3. Athena now additionally helps cross-account federated queries to allow groups of analysts, information scientists, and information engineers to question information saved in different AWS accounts. Attempt connecting to proprietary information codecs and sources, or construct new user-defined features, with the Athena Question Federation SDK.

Concerning the Creator

Soujanya Konka is a Options Architect and Analytics specialist at AWS, targeted on serving to clients construct their concepts on cloud. Experience in design and implementation of enterprise data methods and Information warehousing options. Earlier than becoming a member of AWS, Soujanya has had stints with firms akin to HSBC, Cognizant.

Nilesh Parekh is a Accomplice Answer Architect with ISV India section. Nilesh assist help accomplice to evaluation and remediate their workload working on AWS primarily based on the AWS Effectively-Architected and Foundational Technical Assessment greatest practices. He additionally helps help companions on Utility Modernizations and delivering POCs.



Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments