Visualize DynamoDB data in AWS Quicksight

Question

I am looking for an AWS-centric solution (avoiding 3rd party stuff if possible) for visualizing data that is in a very simple DynamoDB table.

We use AWS Quicksight for many other reports and dashboards for our clients so that is goal to have visualizations made available there.

I was very surprised to see that DynamoDB was not a supported source for Quicksight although many other things are like S3, Athena, Redshift, RDS, etc.

Does anyone have any experience for creating a solution for this?

I am thinking that I will just create a job that will dump the DynamoDB table to S3 every so often and then use the S3 or Athena integrations with Quicksight to read/display it. It would be nice to have a simple solution for more live data.

Courtesy

Related: aws.amazon.com/blogs/big-data/… and aws.amazon.com/blogs/big-data/… – Eucalyptus

Answer 1

!!UPDATE!! As of 2021, we can finally get Athena Data connectors to expose DynamoDB data in Quicksight without any custom scripts or duplicate data.

That being said, I would like the caveat this by saying just because it can be done, you may need to ask yourself if this is really a good solution for your workload. DynamoDB isn't the best for data warehousing use cases and performing large scans on tables can end up being slow/costly. If your dataset is very large and this is a real production use case, it would probably be best to still go with an ETL workflow and move the DynamoDB data to a more appropriate data store.

But.. if you are still interested in seeing DynamoDB data live QuickSight without any additional ETL processes to move/transform the data: I wrote a detailed blog post with step by step instructions but in general, here is the process:

1. Ensure you have an Athena Workgroup that uses the new Athena Engine version 2 and if not, create one
2. In Athena under data sources, create a new data source and select "Query a data source" and then "Amazon DynamoDB"
3. On the next part of the wizard, click the "Configure new AWS Lambda function" to deploy the prebuilt AthenaDynamoDBConnector.
4. Once the AthenaDynamoDBConnector is deployed, select the name of the function you deployed in the Data Source creation wizard in Athena, give your DynamoDB data a catalog name like "dynamodb" and click "Connect"
   1. You now should be able to query DynamoDB data in Athena but there are a few more steps to get things working in QuickSight.
5. Go to the IAM console and find the QuickSight service role (i.e. aws-quicksight-service-role-v0).
6. Attach the AWS Managed "AWSLambdaRole" policy to the QuickSight role since QuickSight now needs the permissions to invoke your data connector.
7. Go to the QuickSight console and add a new Athena data source that uses the version 2 engine that you created in Step 1
8. You should now be able to create a data set with that Athena Engine version 2 workgroup data source and choose the Athena catalog name you gave the DynamoDB connector in Step 4.

Bingo bango, you should now be able to directly query or cache DynamoDB data in Quicksight without needing to create custom code or jobs that duplicate your data to another data source.

---

As of March 2020, Amazon is making available a beta feature called Athena DynamoDB Connector.

Unfortunately, it's only beta/preview and you can get it setup in Athena but I don't see a way to use these new Athena catalogs in Quicksight.

Hopefully once this feature is GA, it can be easily imported into Quicksight and I can update the answer with the good news.

Instructions on getting up a DynamoDB connector

There are many new data sources that AWS is making available in beta for autmoting the connections to Athena.

You can set these up via the console by:

1. Navigate to the "Data Sources" menu in the AWS Athena console.
2. Click the "Configure Data Source" button
3. Choose "Query a data source" radio button
4. Select "Amazon DynamoDB" option that appears
5. Click the "Configure new function" option

• You'll need to specify a bucket to help put "spilled" data into and provide a name for the new DyanmoDB catalog.

6. Once the app is deployed from Step 5, select the Lambda name (the name of the catalog you entered in Step 5) in the Athena data source form from Step 4 and also provide that same catalog name.
7. Create the data connector

Now you can go to the Athena query editor, select the catalog you just created and see a list of all DyanmoDB tables for your region, under the default Athena database in the new catalog, that you can now query as part of Athena.

Courtesy

do you know if this is now working as intended? I have given it a try, and although it is possible to setup the connector via Athena and Glue, it throws and exception about the path to the table not being correct, but it is. – Giffin

this feature still seems to be in preview and I don't think it's compatible with Quicksight yet – Courtesy

confirm that, as of today, still not compatible with Quicksight. You can query DynamoDB from Athena, but in Quicksight you can connect data from AWSDataCatalog, that is not where data connected through Lambda DynamoDB connector goes – Wait

I've updated the answer but it finally appears possible to use Athena Data connectors to get DynamoDB data exposed in QuickSight! – Courtesy

it is possible, i have it working but it times out for large datasets... not production ready I'm afraid – Dodeca

I tried the steps in here and also in the blog link and got the lambda function deployed. Additional to the options mentioned here currently it asks for Security groups and subnet IDs. but in athena when i select the catalog from drop down I get "Failed to invoke lambda function due to com.amazonaws.services.lambda.invoke.LambdaFunctionException: The connection string is invalid. Connection strings must start with either 'mongodb://' or 'mongodb+srv://" error. any ideas what am i missing.? – Edenedens

Answer 2

We want DynamoDB support in Quicksight!

The simplest way I could find is below:

1 - Create a Glue Crawler which takes DynamoDB table as a Data Source and writes documents to a Glue Table. (Let's say Table X)

2 - Create a Glue Job which takes 'Table X' as a data source and writes them into a S3 Bucket in parquet format. (Let's say s3://table-x-parquets)

3 - Create a Glue Crawler which takes 's3://table-x-parquets' as data source and creates a new Glue Table from it. (Let's say Table Y)

Now you can execute Athena queries in Table Y and also you can use it as Data Set in Quicksight.

Underwrite

Answer 3

I'd also like to see a native integration between DynamoDB and QuickSight, so I will be watching this thread as well.

But there is at least 1 option that's closer to what you want. You could enable Streams on your DynamoDB table and then set up a trigger to trigger a Lambda function when changes are made to DynamoDB.

Then you could only take action on specific DynamoDB events if you like ('Modify', 'Insert', 'Delete') and then dump the new/modified record to S3. That would be pretty close to real-time data, as it would trigger immediately upon update.

I did something similar in the past but instead of dumping data to S3 I was updating another DynamoDB table. It would be pretty simple to switch the example to S3 instead. See below.

const AWS = require('aws-sdk');

exports.handler = async (event, context, callback) => {

    console.log("Event:", event);
    const dynamo = new AWS.DynamoDB();

    const customerResponse = await dynamo.scan({
        TableName: 'Customers',
        ProjectionExpression: 'CustomerId'
    }).promise().catch(err => console.log(err));

    console.log(customerResponse);

    let customers = customerResponse.Items.map(item => item.CustomerId.S);

    console.log(customers);

    for(let i = 0; i < event.Records.length; i++)
    {
        if(event.Records[i].eventName === 'INSERT')
        {
            if(event.Records[i].dynamodb.NewImage)
            {
                console.log(event.Records[i].dynamodb.NewImage);
                for(let j = 0; j < customers.length; j++)
                {
                    await dynamo.putItem({
                        Item: {
                            ...event.Records[i].dynamodb.NewImage,
                            CustomerId: { S: customers[j] }
                        },
                        TableName: 'Rules'
                    }).promise().catch(err => console.log(err));
                }
            }
        }
    }
}

Brobdingnagian

Answer 4

Would love to see DynamoDB integration with Quicksight. Using DynamoDB streams to dump to S3 doesn't work because DynamoDB streams send out events instead of updating records. Hence if you read from this S3 bucket you'll have two instances of the same item: one before update and one after update.

One solution that I see now is to dump data from DynamoDB to a S3 bucket periodically using data pipeline and use Athena and Quicksight on this s3 bucket.

Second solution is to use dynamo db stream to send data to elastic search using lambda function. Elastic search has a plug in called Kibana which has pretty cool visualizations. Obviously this is going to increase your cost because now you are storing your data in two places.

Also make sure that you transform your data such that each Elastic Search document has the most granular data according to your needs. As kibana visualizations will aggregate everything in one document.

Henryhenryetta

Who knows is it really now (in 2022) "Using DynamoDB streams to dump to S3 doesn't work " ? – Pause

Answer 5

Possible solutions are explained in other answers. Just wanted to discuss another point.

BI tools such as QuickSight are designed to be usually used on top of analytical data stores such as Redshift, S3 etc. DynamoDB is not a very suitable data storage for analytics purposes. Row by row operations such as "put" or "get" are very efficient. But bulk operations such as "scan" are expensive. If you are constantly doing scans during the day, your DynamoDB costs might grow fast.

A possible way is to cache the data in SPICE (QuickSight's in memory cache). But a better way is to unload the data into a better suited storage such as S3 or RedShift. Couple of solutions are given on other answers.

Allophane

As the author of the answer that discusses the method using Athena Data Connectors, I have to agree with you. While it can be done and maybe okay for small datasets it does not scale well to have to scan large datasets in DynamoDB so it's probably still best to continue with an ETL approach for large production workloads. – Courtesy