AWS Redshift Serverless execute Python script to API Call within a function/stored procedure to be query scheduled

Question

Hi,

Setting up a zero code ETL environment using Redshift mostly
I will be setting up Scheduler to ingest data from FDW RDS but for API I would like a SP/Function to execute Python scripts that do an API call and write a destination to the redshift table that can be query editor v2 scheduled?

Please Help

Regards

Answer

Thank You for assisting.

Can you kindly provide links/example to follow the setup?

Answer

To set up a zero code ETL environment to ingest data from an API into Redshift using Lambda, here is what you can do:
-Create a Lambda function using Python runtime. The function code should make an API call, parse the response and insert the data into a Redshift table.
-Grant the Lambda execution role permissions to call the API, insert into Redshift and any other dependent services.
-Create an API Gateway REST API and integrate it with the Lambda function using Lambda proxy integration.
-Configure the API Gateway stage for public access.
-Schedule the API Gateway endpoint URL using EventBridge or CloudWatch Events to trigger the Lambda function on a periodic basis.

This allows ingesting API data into Redshift in a serverless manner without having to write any ETL code. The API Gateway endpoint acts as the trigger that executes the Lambda function which fetches data and loads it into Redshift.

AWS Redshift Serverless execute Python script to API Call within a function/stored procedure to be query scheduled

Relevant content