CODEWITHSUNDEEP
pythonpostgresqlamazon-web-servicesaws-lambdaamazon-redshift
flybonzai
flybonzai

Reputation: 3931

Lambda function unable to connect to Redshift : Temporary failure in name resolution

I have an AWS Lambda function which is connected to a Jira webhook. Anytime an issue is created/changed/deleted, it sends a request to my API Gateway, which calls my function. I'm receiving the following traceback when I try to connect to Redshift inside the function:

Traceback (most recent call last):
File "/var/task/jira_webhook_proc.py", line 171, in lambda_handler
update_item(request, issue, issue_key)
File "/var/task/jira_webhook_proc.py", line 193, in update_item
delete_item(delete_key)
File "/var/task/jira_webhook_proc.py", line 277, in delete_item
port=REDSHIFT_PORT) as conn:
File "/var/task/psycopg2/__init__.py", line 164, in connect
conn = _connect(dsn, connection_factory=connection_factory, async=async)
OperationalError: could not translate host name "***" to address: Temporary failure in name resolution

I'm using a pre-compiled psycopg2 library that can be found here https://github.com/jkehler/awslambda-psycopg2. From googling it seems this might be a PostgreSQL error where I don't have a config file set right to listen to all ports. I'm not sure how I would change that however.

Upvotes: 0

Views: 2125

Answers (2)

Mahan
Mahan

Reputation: 441

It might help someone as it worked perfectly in my case. 

import psycopg2
import od_red_load_cred as CONSTANTS

def main():

    # Amazon Redshift connect string 
    try:
        conn_string = "dbname={} port={} user={} password={} host={}"\
        .format(CONSTANTS.DBNAME, CONSTANTS.PORT, CONSTANTS.USER, 
            CONSTANTS.PASSWORD, CONSTANTS.HOST)

        print("Connect string Successful!")
    except:
        print("Unable to create the connect string")

    # query generate
    try:
        sql="""copy {}.{} from '{}'\
        credentials 'aws_access_key_id={};aws_secret_access_key={}' \
        DELIMITER ',' ACCEPTINVCHARS EMPTYASNULL ESCAPE COMPUPDATE OFF;commit;"""\
        .format(CONSTANTS.SCHEMA, CONSTANTS.TABLE_NAME_BV, CONSTANTS.S3_PATH_BV, 
            CONSTANTS.ACCESS_KEY_ID, CONSTANTS.SECRET_ACCESS_KEY)

        print("sql ==>", sql)
        print("Query creation Successful!")
        print(" ")
    except:
        print("Unable to create the query")

    # get connection
    try:
        con = psycopg2.connect(conn_string);
        print("con ==>", con)
        print("Connection Successful!")
    except Exception as e:
        print("Unable to connect to Redshift")
        print(e)
        exit(-1)

    # get cursor connection
    try:
        cur = con.cursor()
    except Exception as e:
        print("Unable to get the cursor from connection of Redshift")
        print(e)
        exit(-1)

    # execute cursor connection
    try:
        cur.execute(sql)
        print("Query executed successfully")
    except Exception as e:
        print("Failed to execute the query")
        print(e)
        exit(-1)

    # close connection
    try:
        con.close() 
        print("Connection closed Successfully!")
    except Exception as e:
        print("Unable to close connection to Redshift")
        print(e)

    print(" ")

# Main method
if __name__ == '__main__':
    print("start: __main__ ")
    main()
    print("end: __main__ ")

Upvotes: 0

halil
halil

Reputation: 1812

I use this code added below to connect redshift from lambda function. Using psycopg2

conn_string = "dbname='your_db_name' port='5439' user='redshift_user' password='^%+^+&7!+' host='xxxxxxx.yyyyyyy.eu-west-1.redshift.amazonaws.com'"

conn = psycopg.connect(conn_string)

cursor = conn.cursor()

cursor.execute("COPY data_table ........ ")

conn.commit()
cursor.close()
conn.close()

Upvotes: 1

Related Questions