Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Visualize DynamoDB data in AWS Quicksight

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.

like image 953
JD D Avatar asked Sep 03 '19 16:09

JD D


People also ask

Can QuickSight read from DynamoDB?

You can also set up Amazon QuickSight to visualize the data and perform ad hoc queries of data in Athena or Amazon S3 directly. Your application can query hot data directly from DynamoDB and also access analytical data through Athena APIs or Amazon QuickSight visualizations.

Can QuickSight read JSON file?

JSON data. Amazon QuickSight natively supports JSON flat files and JSON semistructured data files.

Can Athena connect to DynamoDB?

The Amazon Athena DynamoDB connector enables Amazon Athena to communicate with DynamoDB so that you can query your tables with SQL. Write operations like INSERT INTO are not supported.


2 Answers

!!UPDATE!! As of 2021, we can finally get Athena Data connectors to expose DynamoDB data in Quicksight without any custom scripts or duplicate 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.
  1. 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.
  2. 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.

like image 156
JD D Avatar answered Oct 02 '22 17:10

JD D


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.

like image 41
Emre Alparslan Avatar answered Oct 02 '22 16:10

Emre Alparslan