How to use/fetch Redshift data in a Python Lambda Function?

To use and fetch data from Amazon Redshift in a Python Lambda function, you can utilize the Redshift Data API. Here's a general approach to accomplish this:

1. First, ensure your Lambda function has the necessary IAM permissions to access Redshift and Secrets Manager (if you're storing Redshift credentials there).

2. In your Lambda function, use the boto3 library to create a Redshift Data API client.

3. Use the execute_statement() method to run your SQL query against Redshift.

4. Check the status of the query execution using describe_statement().

5. Once the query is complete, retrieve the results using get_statement_result().

Here's a sample Python code structure for your Lambda function:

import boto3
import json
import time

def lambda_handler(event, context):
    client = boto3.client('redshift-data')
    
    # Execute the SQL statement
    response = client.execute_statement(
        ClusterIdentifier='your-cluster-id',
        Database='your-database',
        SecretArn='your-secret-arn',
        Sql='SELECT * FROM your_table',
        StatementName='fetch_data'
    )
    
    statement_id = response['Id']
    
    # Check query status
    while True:
        status = client.describe_statement(Id=statement_id)['Status']
        if status == 'FINISHED':
            break
        elif status in ['FAILED', 'ABORTED']:
            raise Exception(f"Query failed with status: {status}")
        time.sleep(2)
    
    # Retrieve results
    result = client.get_statement_result(Id=statement_id)
    
    # Process the results as needed
    # ...

    return {
        'statusCode': 200,
        'body': json.dumps(result)
    }

Remember to replace 'your-cluster-id', 'your-database', 'your-secret-arn', and 'SELECT * FROM your_table' with your actual Redshift cluster details and SQL query.

This approach allows you to execute SQL queries against your Redshift cluster and retrieve the results within your Lambda function. You can then process this data as needed for your specific use case.

Note that for larger datasets, you might need to handle pagination of results or consider using the UNLOAD command to move data to S3 first, especially for ML training datasets or other large-scale data processing tasks.
Sources
Modernize your legacy databases with AWS data lakes, Part 3: Build a data lake processing layer | AWS Big Data Blog
Create cross-account Lambda function to access Amazon Redshift | AWS re:Post