Ingest data from a REST API and store into Redshift

Hi,

I would reccomend the following:

• Use EventBridge Scheduler https://docs.aws.amazon.com/scheduler/latest/UserGuide/getting-started.html and associate it with a lambda function which has to be in a VPC.
• if your data from third party does not take minutes to be found, I would use a lambda function with your favorite programming language of choice. As the redshift cluster needs to be in a VPC, then you need to provision a NAT Gateway in public subnet so that you are able to call the api via internet
• if is the same lambda, then you can execute statements towards redshift, this is an example in python: https://github.com/aws-samples/aws-lambda-redshift-event-driven-app/blob/main/LambdaRedshiftDataApiETL.py.

If you want to have separated lambdas for retrieving data and store data to redshift, you could consider to use step function with 2 states, one for each lambda (retrieve data and store to redshift).

This link contains part of what you are trying to accomplish: https://aws.amazon.com/blogs/big-data/building-an-event-driven-application-with-aws-lambda-and-the-amazon-redshift-data-api/

Hope it helps ;)