Build Scalable APIs For Large Data Visualization With React Frontend

Question

We have Microsoft SQL DB and we are taking backup of data in CSV format from MSSQL db and uploading to S3 bucket. I tried to query data using Athena and AWS API Gateway to build API. Athena looks do not scale for our problem since using boto3 we are only able to fetch maximum 1000 rows at a time. We have 100, 000-1,000,000 rows in table. Since we faced this issue I checked Dynamo DB and Dynamo db has a limit of 1mb data.

Can we use redshift to query data from s3 directly (I believe if we copy data to redshift from s3 it will insert the data again and in turn duplicate the data. I cannot use merge/update existing data in the redshift due to cannot identify these data with a uique key)?

Is there any way to load data on client side quickly within 2-3 second?

Answer

Redshift can query data from S3 by using Redshift Spectrum, but it's likely to hit the same issue when transferring the result to the client because of the network overhead.

It might be worth reconsidering if you really need such a large dataset in the frontend. Transferring a large dataset to the frontend may lead to lower user experience because of longer page response and more memory consumption in the client. Also, in most cases users do not need to visualize that much rows at once.

So, one option is to aggregate or extract the specific data which gives the insight to the user.

Another option is to introduce incremental rendering like some BI applications and websites do. The frontend will fetch and display minimal data at the first render. As a user scrolls the screen, the frontend will incrementally fetch and render the paginated result. This will minimize the initial data transfer and page response. If the user needs the full dataset, it can be implemented as a data export button.

Build Scalable APIs For Large Data Visualization With React Frontend

Relevant content