Using Psycopg2 with Lambda to Update Redshift (Python)

Using psycopg2 with Lambda to Update Redshift (Python)

In order for this to work you need to build psycopg2 with statically linked libpq.so library. Check out this repo https://github.com/jkehler/awslambda-psycopg2. It has already build psycopg2 package and instructions how to build it yourself.

Back to your questions:

What is causing this problem?

psycopg2 needs to be build an compiled with statically linked libraries for Linux.

Does it matter that Lambda uses Python 2.7 when I use 3.4?

Yes it does, lambda only supports 2.7 version. Just create virtual environment and install all necessary packages in there.

Does it matter that I zipped the contents of my file on a Windows machine?

As long as all the libraries you zipped could ran on Linux it doesn't

Has anyone been able to successfully connect to Redshift from lambda?

yes.

How to insert data in redshift using either of boto3 or psycopg2 python libraries

Sample basic python code for all three operations using boto3.

import json
import boto3

clientdata = boto3.client('redshift-data')

# looks up table and returns true if found
def lookup_table(table_name):
  response = clientdata.list_tables(
    ClusterIdentifier='redshift-cluster-1',
    Database='dev',
    DbUser='awsuser',
    TablePattern=table_name
  )
  print(response)
  if ( len(response['Tables']) == 0 ):
    return False
  else:
    return True

# creates table with one integer column
def create_table(table_name):
  sqlstmt = 'CREATE TABLE '+table_name+' (col1 integer);'
  print(sqlstmt)
  response = clientdata.execute_statement(
    ClusterIdentifier='redshift-cluster-1',
    Database='dev',
    DbUser='awsuser',
    Sql=sqlstmt,
    StatementName='CreateTable'
  )
  print(response)

# inserts one row with integer value for col1
def insert_data(table_name, dval):
  print(dval)
  sqlstmt = 'INSERT INTO '+table_name+'(col1) VALUES ('+str(dval)+');'
  response = clientdata.execute_statement(
    ClusterIdentifier='redshift-cluster-1',
    Database='dev',
    DbUser='awsuser',
    Sql=sqlstmt,
    StatementName='InsertData'
  )
  print(response)

result = lookup_table('date')
if ( result ):
  print("Table exists.")
else:
  print("Table does not exist!")

create_table("testtab")
insert_data("testtab", 11)

I am not using Lambda, instead executing it just from my shell.
Hope this helps. Assuming credentials and default region are already set up for the client.

Error: No Module Named 'psycopg2.__psycopg' in AWS Lambda with python 3.8.7 version

Your question implicates that you have downloaded psycopg2 library on Windows, and are trying to use the same library within Lambda runtime environment, which ultimately uses Linux operating system.

AWS Documentation on Lambda Runtime Environment provides more documentation, but to get this working you have few options

1. Run amazon provided Linux container, install and zip dependencies in there. You could achieve this by running following in your project folder

$ docker run --rm -v $PWD:/src --entrypoint '' amazon/aws-lambda-python:3.8 bash
# execute below within container
$ pip3 install psycopg2-binary -t /src/lambdalib

Alternatively, you can build extension on Linux platform, within that container using following

$ docker run --rm -v $PWD:/src --entrypoint '' amazon/aws-lambda-python:3.8 bash
# execute below within container
$ yum -y install cmake c++ gcc postgresql-devel && pip3 install psycopg2 -t /src/lambdalib

This will install linux binary version of psycopg2 within your lambdalib folder. I suggest you execute this from cloud console, or Cloud9 IDE if Windows workstation is not having

2. Search for publicly available Lambda Layer for psycopg2, add it to your lambda function, and avoid packaging this library altogether.

---

Related Topics