How to send a huge data through AWS API gateway to a third party application

Hello,

when using API Gateway to expose large datasets from Redshift to third-party applications, keep in mind that API Gateway has a maximum payload size limit of 10 MB for both requests and responses.

This means that if your Redshift query returns more than 10 MB of data, you'll encounter issues when trying to send it through API Gateway. There are some approaches to handle large datasets like to implement pagination in your API to return data in smaller chunks. Instead of using "SELECT *", you can modify your query to:

• Include a LIMIT and OFFSET clause

• Return a subset of rows per request

• Provide a mechanism for the client to request subsequent pages

Example query:

sql
SELECT * FROM your_table LIMIT 1000 OFFSET 0

You can also encourage the third-party application to request only the specific data they need. This can be achieved by allowing column selection in the API and implementing filtering options

For very large datasets you can export the data from Redshift to an S3 bucket and generate a pre-signed URL for the S3 object, then return this URL through API Gateway instead of the actual data.

This approach allows the third-party to download the large dataset directly from S3.

If real-time access is crucial you can set up a proxy integration in API Gateway and stream the response directly from Redshift through API Gateway. Note that this method still has a 10 MB payload limit, but it can handle larger result sets more efficiently.