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
  1. 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
  1. Execute the bin file and install the driver.
./ PROGRESS_DATADIRECT_ODBC_8.0_LINUX_64_INSTALL.bin
  1. After the installation is complete, move to the installation folder, execute the shell script odbc.sh or odbc.csh, and set the environment variables.

  2. 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
  1. 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

Recommended Posts

Data integration from Python app on Linux to Amazon Redshift with ODBC
Data integration from Python app on Windows to Amazon Redshift with ODBC
ODBC access to SQL Server from Linux with Python
[Amazon Linux] Switching from Python 2 series to Python 3 series
Copy data from Amazon S3 to Google Cloud Storage with Python (boto)
How to scrape image data from flickr with python
Yum command to access MySQL with Python 3 on Linux
How to build a Python environment on amazon linux 2
Get data from database via ODBC with Python (Access)
[Linux] Copy data from Linux to Windows with a shell script
Notes on importing data from MySQL or CSV with Python
Install Python Pillow on Amazon Linux
Introduce Python 3.5.2 environment on Amazon Linux
Update Python on Mac from 2 to 3
Introduction to Data Analysis with Python P17-P26 [ch02 1.usa.gov data from bit.ly]
Get data from MySQL on a VPS with Python 3 and SQLAlchemy
Manipulating kintone data with Python & C Data ODBC Driver from AWS Lambda
Receive textual data from mysql with python
Building a Python3 environment with Amazon Linux2
[Note] Get data from PostgreSQL with Python
Retrieving food data with Amazon API (Python)
Convert Excel data to JSON with python
[C] [python] Read with AquesTalk on Linux
How to update php on Amazon linux 2
Convert FX 1-minute data to 5-minute data with Python
Connecting from python to MySQL on CentOS 6.4
Upgraded mysql on Cloud9 (Amazon Linux) (5.5 to 5,7)
How to install Anisble on Amazon Linux 2
Install Python 3.8, Pip 3.8 on EC2 (Amazon Linux 2)
[Data science basics] I tried saving from csv to mysql with python
Run a batch of Python 2.7 with nohup on Amazon Linux AMI on EC2
ODBC connection to FileMaker 11 Server Advanced with Python 3
Vienna with Python + Flask web app on Jenkins
[Python] How to read data from CIFAR-10 and CIFAR-100
I tried to get CloudWatch data with Python
How to handle Linux commands well from Python
Folium: Visualize data on a map with Python
[Python] Flow from web scraping to data analysis
PHP and Python integration from scratch on Laravel
Introduction to Python with Atom (on the way)
Write CSV data to AWS-S3 with AWS-Lambda + Python
Back up from QNAP to Linux with rsync
From Python environment construction to virtual environment construction with anaconda
Install PHP 7 series on Amazon Linux 2 with Amazon Linux Extras
Run Python on Apache to view InfluxDB data
Connect to MySQL with Python on Raspberry Pi
How to prepare an environment with different python version and package for each project with pyenv-virtualenv on Amazon Linux
Extract data from a web page with Python
How to create a Python 3.6.0 environment by putting pyenv on Amazon Linux and Ubuntu
I tried to implement Minesweeper on terminal with python
App development to tweet in Python from Visual Studio 2017
Preferences to generate animated GIFs from Python on Mac
Publish your Django app on Amazon Linux + Apache + mod_wsgi
Reading Note: An Introduction to Data Analysis with Python
From python to running instance on google cloud platform
Send data from Python to Processing via socket communication
Steps from installing Python 3 to creating a Django app
Interact with Python on Android from PC via adb
Copy files directly from Amazon EC2 (Amazon linux) to S3
From buying a computer to running a program with python
PIL with Python on Windows 8 (for Google App Engine)