How to better use QuickSights

Question & Answer
1

Hey all,
I am trying to use Quicksights to visualize our data. The workflow is still pretty flexible, just looking for the best approach.

The plan right now is to write data into S3 as JSON and use Athena and Glue to make tables out of them query for Quicksights. But Glue is very costly especially when I want near-real time updates - which using crawlers can achieve. And direct reads from S3 will load into spice which fastest refresh rate is hourly (I want at least 10mins refresh rate). So it looks like direct queries from Quicksight is the only way.

I am open to not use S3/JSON, like using Dynamodb instead but that will require an Athena federated query which I believe performs large scans of my tables and that may be slow and not efficient.

I wanted to ask is there a better way to achieve this?

2

Hi @timmlui - Welcome to AWS QuickSight community and thanks for posting the question.
Looking at your use case, Option -1 is a good choice, can you look at the below approach as well

  1. Create the athena table on top of the JSON file directly, I believe it is possible, please have a look on the below link - Create Tables in Amazon Athena from Nested JSON and Mappings Using JSONSerDe | AWS Big Data Blog
  2. Point QuickSight to the Athena table.
    However the above solution may be costly as every time the data will be scan the whole folder.

One other solution I am thinking of, but needs to do the POC.

  1. crawl the json data incrementally using glue bookmark.
  2. Convert the data to parquet format via a glue job and atena table will be on top of this data, you can create partition on date and hour basis.
  3. crawl the final table again.

Use this final table for your quicksight and you can put a filter to restrict the data in athena as well.
if your data volume is less, then within 10 mins it can complete but its a near real time solution.

Also I know that MySQL 8.0 version support NO SQL data which means you can load JSON data to mysql as well. I believe RDS MYSQL is also supported the same, you can simply dumped s3 json data to mysql and do the quicksight custom sql to parse the json and do the reporting as well.

Hope the above details will help you.

Regards - Sanjeeb

2 Likes
3

Hi,

Start of my solution, we use the ETL glue to migration data, later on try the AWS DATA Pipelines during our R&D.
Due to cost we shifted on Lambda services as data migration from multiple databases to a single DB like Redshift.

Prep your data on Redshift and get maximum performance on QS to handle the Big data.

Major benefit to shift on this process flow we go its COST.

It has no VPC cost too from one account to other account data transfer.

regards,
Naveed Ali

2 Likes