Visualising your Amazon DynamoDB data with Amazon QuickSight

Please noted that this content is translated from Thai language on this Blog because it has high demand for implementing this solution in real life.

Table of Content

1. Introduction
2. Architecture Diagram
3. Cost Estimation
4. Step 1: Prepare AWS Services
5. Step 2: Create Amazon Athena data source
6. Step 3: Test query on your data in DynamoDB via Amazon Athena
7. Step 4: Connect Amazon QuickSight and Amazon Athena with Federated Query
8. Step 5: Create QuickSight dataset of DynamoDB via Athena
9. Step 6: Create QuickSight analyse from DynamoDB dataset
10. Conclusion

Introduction

In modern application, NoSQL databases are used widely in the organisation, including Amazon DynamoDB, Amazon DocumentDB, Apache HBase, and so on, which each of them has their own query language. However, there is a demand that want to visualise those data in form of graphs for business purpose.

AWS has Amazon QuickSight, a cloud-native business intelligent tool that help you to create a dashboard, visualise data, find insights, and has build-in Machine Learning capability. Amazon QuickSight support many native build-in data source, such as Amazon RDS, Amazon Aurora, Self-Managed MySQL, or 3rd party software.

But NoSQL databases, which mostly do not natively support SQL language, need to have data pipeline or data processing to store that data into somewhere else, for example Amazon S3, before visualising that data in the business intelligent tool.

In this blog, I will show you how to visualise data on Amazon DynamoDB, one of AWS NoSQL database, without moving the data out, and natively update the data in near-real time on Amazon QuickSight.

Architecture Diagram

Cost Estimation

• Pricing for Storage and Read Capacity Unit (RCU) of Amazon DynamoDB
• Pricing for Data Scan of Amazon Athena
• Pricing for User and SPICE of Amazon QuickSight
• Pricing for Storage of Amazon S3

Info: It might has a bit additional price for AWS Lambda and Data Transfer.

Step 1: Prepare AWS Services

• Get started at Amazon Athena console, the engine version 2 is required, and prepare S3 Result Data bucket (e.g. nutchanon-athena-query-results).
• Create an Amazon S3 bucket for Spill Bucket Data of Athena.
• Go to Amazon QuickSight console - Setting Up for Amazon QuickSight.
• Prepare Amazon DynamoDB table with the sample data (e.g.quicksight-ddb).

Info: if you do not have the data in DynamoDB for testing, please check CSV Amazon S3 Amazon DynamoDB (English translation is required)

Step 2: Create Amazon Athena data source

• Create new data source from "Connect data source".
• Choose "Query a data source" and "Amazon DynamoDB".
• Choose "Configure new AWS Lambda function" for creating Lambda Connector.
• Choose "SpillBucket", "AthenaCatalogName" and confirm "Custom IAM Roles", and then click "Deploy". The system will automatically create AWS CloudFormation for Lambda Connector.
• Go back to choose Lambda Connector that you just created (might need a refresh), then provide the name of the catalog.

Step 3: Test query on your data in DynamoDB via Amazon Athena

• Choose "Data Source" to be your newly created catalog, then the table of DynamoDB will be able to selected. Let's try to query via DynamoDB data via Athena.

Step 4: Connect Amazon QuickSight and Amazon Athena with Federated Query

• Change QuickSight region to N.Virginia for edit configuration. Then go to "Manage QuickSight".
• Allocate SPICE capacity. (What QuickSight SPICE is? Please see [Managing SPICE Capacity].(https://docs.aws.amazon.com/quicksight/latest/user/managing-spice-capacity.html)
• For "Security & Permission" choose "Add or Remove".
• Choose Amazon Athena (If it already has chosen, please click twice times), then you will see the window of permission for Amazon S3. Next, choose Athena Spill and Result Data Bucket with write permission.
• Change QuickSight region to Singapore (or your preferred region) for creating the dataset.
• Go back to AWS Management Console and go to "IAM" Service. Then go to IAM Role.
• Add policy at IAM Role "aws-quicksight-s3-consumers-role-v0", which the system automated generated when you grant S3 permission in QuickSight.Add "inline policy" name "InvokeAthenaFedereted" by follow JSON document as below.

Please change "aws_account_id" and Lambda Function name that you created in step 2 ("AthenaCatalogName").

{    "Version": "2012-10-17",    "Statement": [        {            "Sid": "InvokeAthenaFedereted",            "Effect": "Allow",            "Action": "lambda:InvokeFunction",            "Resource": "arn:aws:lambda:ap-southeast-1:<aws_account_id>:function:dynamocatalog"        }    ]}

Step 5: Create QuickSight dataset of DynamoDB via Athena

• Create dataset from Athena Data Source with the connection name, e.g."athena-dynamodb".
• Choose catalog name that you created in step 2, and the table.
• Choose to create from SPICE.
• Wait until the data is imported into SPICE.
• Change data type of each column appropriately for the data calculation and filtering in the future.

Step 6: Create QuickSight analyse from DynamoDB dataset

• You can create analyse and dashboard directly from DynamoDB data.

Info: Changing the data type of dataset directly affect how to do calculation on graphs.

Conclusion

Amazon QuickSight can connect to non-directly support data source via Amazon Athena Federeted Query, which allow you to expand the functionality of building graph on many data source. By using Amazon Athena Federeted Query, you can even expand to NoSQL databases on AWS, such as Amazon DynamoDB, Amazon DocumentDB, or Amazon OpenSearch Service.

This solution will help to you easily build dashboard or visualise data on Amazon QuickSight without data movement or need to build data pipeline platform. Also, the data that show in the dashboard will be refreshed in near real-time based-on the source of data as well.

Reference

[1] https://aws.amazon.com/blogs/big-data/accessing-and-visualizing-data-from-multiple-data-sources-with-amazon-athena-and-amazon-quicksight/
[2] https://aws.amazon.com/blogs/big-data/query-any-data-source-with-amazon-athenas-new-federated-query/