How to use redshift_connector from Glue?

[dburtsev]

Driver version

redshift_connector-2.0.882-py3-none-any.whl

Redshift version

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.27747

Client Operating System

Glue 1.0

Python version

Python3 Glue version 1.0

Problem description

1. Expected behaviour:
   Connect to the database

2. Actual behaviour:
   CalledProcessError: Command '['/usr/local/bin/python', '-m', 'pip', 'install', '--target=/glue/lib/installation', '/tmp/glue-python-libs-fj9rfah5/redshift_connector-2.0.882-py3-none-any.whl']' returned non-zero exit status 1.

3. Any other details that can be helpful:
   The script is running on my PC but doesn't tun on Glue

Python Driver trace logs

Reproduction code

import redshift_connector

Connects to Redshift cluster using AWS credentials

conn = redshift_connector.connect(
host='xyz.redshift.amazonaws.com',
database='xyz',
user='xyz',
password='xyz',
port = 5439
)

cursor: redshift_connector.Cursor = conn.cursor()
cursor.execute("SELECT version()")
return_value = cursor.fetchone()[0]
print(return_value)
conn.close

[Brooke-white]

Hi @dburtsev ,

Thanks for reaching out. The team will try to reproduce.

[dburtsev]

Change job type from 'Python shell' to Spark 2.4 / Python 3
New error: ModuleNotFoundError: No module named 'scramp'

[dburtsev]

ModuleNotFoundError: No module named 'asn1crypto'
I load scramp-1.4.0-py3-none-any.whl and asn1crypto-1.4.0-py2.py3-none-any.whl into s3 and add references to the job python library path.
New error: InterfaceError: ('communication error', NotADirectoryError(20, 'Not a directory'))

[Brooke-white]

Here is one option for importing redshift_connector to use with Glue.

If this option is not available to you , the following code block can be run on your development machine to download redshift_connector, and it's dependencies. These can be placed in an S3 bucket referenced by the Glue Python library path.

pip3 download redshift_connector

For example, the following dependencies were downloaded to my machine.

asn1crypto-1.4.0-py2.py3-none-any.whl       idna-2.10-py2.py3-none-any.whl              requests-2.25.1-py2.py3-none-any.whl
beautifulsoup4-4.9.3-py3-none-any.whl       jmespath-0.10.0-py2.py3-none-any.whl        s3transfer-0.5.0-py3-none-any.whl
boto3-1.18.1-py3-none-any.whl               lxml-4.6.3-cp39-cp39-macosx_10_9_x86_64.whl scramp-1.4.0-py3-none-any.whl
botocore-1.21.1-py3-none-any.whl            python_dateutil-2.8.2-py2.py3-none-any.whl  six-1.16.0-py2.py3-none-any.whl
certifi-2021.5.30-py2.py3-none-any.whl      pytz-2021.1-py2.py3-none-any.whl            soupsieve-2.2.1-py3-none-any.whl
chardet-4.0.0-py2.py3-none-any.whl          redshift_connector-2.0.882-py3-none-any.whl urllib3-1.26.6-py2.py3-none-any.whl

[Brooke-white]

If the second option is appropriate for your use case, zipping the dependencies and placing them in an S3 bucket should work.

[Brooke-white]

I have confirmed importing redshift_connector works using a Spark 2.4 / Python 3 job. The approach detailed below was easiest for me to get working.

If you are working from the AWS Console.

• Click to edit the Glue job
• Under the Security configuration, script libraries, and job parameters tab scroll to the Job parameters section
• Set the key to --additional-python-modules and the value to redshift_connector
• Run the job

Example job

print("pre-import print stmt")

import redshift_connector

print("import worked!")

[dburtsev]

I add --additional-python-modules key
Got new error: Command failed with exit code 1
How many libraries do you have in Python library path? I have only redshift_connector, scramp and asn1crypto

[dburtsev]

It works now.
I misspell library and got "Command failed with exit code 1" - fixes
I got "NotADirectoryError: [Errno 20] Not a directory" - fixed by adding SSL = False in redshift_connector.connect