Custom Lambda function for AWS Database cleanup

Description:?Organization was importing millions of events into Redshift data warehouse to run some reports from Tableau. These events could be reproduced hence storing all in Redshift cluster was super expensive. Developers were running scripts manually from their laptop to truncate records periodically. This custom lambda function will take this developer job as a Serverless function and truncate records every 4 hours.

Here are the steps to create this Custom Lambda Function

1. Run below command to compile binary into function folder
2. pip install — platform manylinux2014_x86_64 — target=function — implementation cp — python 3.9 — only-binary=:all: — upgrade psycopg2-binary
3. Create?function.py?file in the same folder to delete the records as below

import os

import boto3

import psycopg2

import sys

def lambda_handler(event, context):

??REDSHIFT_DATABASE = os.environ['database']

??REDSHIFT_USER = os.environ['admin']

??REDSHIFT_PASSWD = os.environ['xxx']

??REDSHIFT_PORT = os.environ['5439']

??REDSHIFT_ENDPOINT = os.environ['xxx.us-west-2.redshift.amazonaws.com:5439']

??REDSHIFT_CLUSTER = os.environ['clustername']

??REDSHIFT_QUERY = "DELETE FROM <Table> where timestamp<(Current_date-730)"

??REDSHIFT_QUERY1 = "select min(timestamp),max(timestamp),max(timestamp)-min(timestamp),current_date-min(timestamp) from <Table>"

??try:

????client = boto3.client('redshift', region_name='us-west-2')

????creds = client.get_cluster_credentials(

?????DbUser=REDSHIFT_USER,

?????DbName=REDSHIFT_DATABASE,

?????ClusterIdentifier=REDSHIFT_CLUSTER,

?????DurationSeconds=3600)

??except Exception as ERROR:

????print("Credentials Issue: " + ERROR)

????sys.exit(1)

??try:

????conn = psycopg2.connect(

?????dbname=REDSHIFT_DATABASE,

?????user=creds['DbUser'],

?????password=creds['DbPassword'],

?????port=REDSHIFT_PORT,

?????host=REDSHIFT_ENDPOINT)

??except Exception as ERROR:

????print("Connection Issue: " + ERROR)

????sys.exit(1)

??try:

????cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

????print(cursor.execute(REDSHIFT_QUERY))

????print(cursor.execute(REDSHIFT_QUERY1))

????cursor.close()

????conn.commit()

????conn.close()

??except Exception as ERROR:

????print("Execution Issue: " + ERROR)

????sys.exit(1)

4. Create a Lambda deployment package .zip file archive that includes all of the installed libraries and source code by running the following command:

5.?zip -r ../function.zip .

6. aws lambda update-function-code — function-name MyLambdaFunction — zip-file fileb://function.zip

7. Create a cloud-watch rule to trigger this lambda function every 4 hours or based on your custom schedule.

Also posted on : https://medium.com/@nakul.barai/custom-lambda-function-for-aws-database-cleanup-7277bae4c91f

https://vivtechllc.com/f/custom-lambda-function-for-aws-database-cleanup