Data integration from Python app on Linux to Amazon Redshift with ODBC

Introduction

In this article, I will explain how to use the DataDirect driver to easily link data from a Python application on Linux or UNIX to Amazon Redshift with ODBC.

Install unixODBC

1. Install the unixODBC package with the following command.

For CentOS

sudo yum install unixODBC-devel unixODBC

For Ubuntu / Debian

sudo apt-get install unixODBC-dev unixODBC

2. After installing unixODBC /home//Progress/DataDirect/ODBC_80_64bit/odbcinst.ini

The contents of

/etc/odbcinst.ini

Paste in.

Install DataDirect driver for Amazon Redshift

1. Download DataDirect ODBC Driver for Amazon Redshift .
2. Execute the following command to extract the package.

ar -xvf PROGRESS_DATADIRECT_ODBC_REDSHIFT_LINUX_64.tgz

3. Execute the bin file and install the driver.

./ PROGRESS_DATADIRECT_ODBC_8.0_LINUX_64_INSTALL.bin

4. After the installation is complete, move to the installation folder, execute the shell script odbc.sh or odbc.csh, and set the environment variables.

5. This will set 3 environment variables. Make sure it's set up correctly and move on!

[progress@centos7264 ODBC_80_64bit]$ echo $LD_LIBRARY_PATH && echo $ODBCINI && echo $ODBCINST
/home/progress/Progress/DataDirect/ODBC_80_64bit/lib:/home/progress/Progress/DataDirect/ODBC_80_64bit/jre/lib/amd64/server
/home/progress/Progress/DataDirect/ODBC_80_64bit/odbc.ini
/home/progress/Progress/DataDirect/ODBC_80_64bit/odbcinst.ini

Connect to Redshift from Python

1. To access Redshift from a yPthon application on Linux or Unix, you must install the pyodbc package. Please install with the following command.

pip install pyodbc

2. Let's access the data from Redshift with the following sample Python program.

import pyodbc
 
conn = pyodbc.connect('Driver={DataDirect 8.0 Amazon Redshift Wire Protocol}; HostName=redshift-cluster-1.cy1mp8nn6ntk.us-west-2.redshift.amazonaws.com; Database=dev; UID=awsuser; PWD=Galaxy472; Port=5439')
 
cursor = conn.cursor()
 
## Create Tables
cursor.execute("CREATE TABLE Track ( TrackId INT NOT NULL, Name VARCHAR(200) NOT NULL, AlbumId INT, MediaTypeId INT NOT NULL, GenreId INT, Composer VARCHAR(220), Milliseconds INT NOT NULL, Bytes INT, UnitPrice NUMERIC(10,2) NOT NULL);")
 
cursor.execute("INSERT INTO Track (TrackId, Name, AlbumId, MediaTypeId, GenreId, Composer, Milliseconds, Bytes, UnitPrice) VALUES (1, 'For Those About To Rock (We Salute You)', 1, 1, 1, 'Angus Young, Malcolm Young, Brian Johnson', 343719, 11170334, 0.99);")
 
conn.commit()
 
##Access Data using SQL
cursor.execute("select * from Track")
while True:
    row = cursor.fetchone()
    if not row:
        break
    print(row)
 
##Access Data using SQL
cursor.execute("select * from Artist")
while True:
    row = cursor.fetchone()
    if not row:
        break
    print(row)

It's very easy.

Reference article

Real-time access to Amazon Redshift

DataDirect for Redshift Tutorial