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.
For CentOS
sudo yum install unixODBC-devel unixODBC
For Ubuntu / Debian
sudo apt-get install unixODBC-dev unixODBC
The contents of
/etc/odbcinst.ini
Paste in.
ar -xvf PROGRESS_DATADIRECT_ODBC_REDSHIFT_LINUX_64.tgz
./ PROGRESS_DATADIRECT_ODBC_8.0_LINUX_64_INSTALL.bin
After the installation is complete, move to the installation folder, execute the shell script odbc.sh or odbc.csh, and set the environment variables.
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
pip install pyodbc
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
Recommended Posts