My current Database size is of around ~2 GB and it is increasing day by day. I have a requirement to integrate it with BI service to give clear insights of the data to the stakeholders.
Is it possible to integrate MongoDB directly with AWS QuickSight? There are options of importing CSVs, JSON etc, But as data growth is high it doesn’t look feasible and I am looking for real time solutions.
Note: I am running MongoDB atlas on AWS ec2 instances.
What I would like to know is:
What is the best way to connect to MongoDB cluster with AWS QuickSight with Realtime outputs? if not, What are the other best possible solutions?
You can use this: Visualize MongoDB data from Amazon QuickSight using Amazon Athena https://aws.amazon.com/blogs/big-data/visualize-mongodb-data-from-amazon-quicksight-using-amazon-athena-federated-query/
you can still connect MongoDB to QuickSight using a few alternative methods.. Here are some options:
Use MongoDB Connector for BI: MongoDB provides a Connector for BI that allows you to connect your MongoDB data to popular BI tools, including QuickSight. This connector translates SQL queries into MongoDB queries, allowing BI tools that speak SQL to interact with MongoDB. You can find more information about the MongoDB Connector for BI on the official MongoDB documentation.
Export Data to a Supported Format: Periodically export MongoDB data to a supported format (CSV, JSON) and upload it to Amazon S3. Configure QuickSight to use the S3 bucket as a data source. This method may not provide real-time data but can offer near-real-time insights depending on your export frequency.
Use Amazon Lambda with MongoDB Atlas Triggers: Set up MongoDB Atlas triggers to capture changes in your MongoDB data. Create an AWS Lambda function triggered by these changes, which pushes the data to an Amazon S3 bucket. Configure QuickSight to use the S3 bucket as a data source. This approach allows you to have a more real-time solution compared to periodic exports.
Custom API or Middleware: Develop a custom API or middleware that acts as an interface between MongoDB and QuickSight. The API can handle the translation of queries and data retrieval from MongoDB to provide a format that QuickSight understands.