A Comprehensive Guide to Establishing a Successful Connection to Amazon Redshift Using the ODBC Driver

1. CentOS or RHEL needs to be used as AmazonReshift ODBC driver works only on those? Steps

• Get an ec2 linux ○ Ubuntu 24.04 ec2 with t2.micro - odbc-linux-test
• Configure the ODBC connection - https://s3.amazonaws.com/redshift-downloads/drivers/odbc/1.5.16.1019/Amazon+Redshift+ODBC+Connector+Install+Guide.pdf
• Requirements ○ RHEL, CentoOS 8, SUSE 12/15, Debian 11, Ubuntu 20.04/22.04/24.04, Oracle Linux 7.5 ○ 150 MB ○ Driver Managers - iODBC 3.52.9<=, unixODBC 2.2.14<= ○ Glibc 2.17<= ○ Download the driver to home/ubuntu using 'curl -O -J https://s3.amazonaws.com/redshift-downloads/drivers/odbc/1.5.16.1019/AmazonRedshiftODBC-64-bit-1.5.16.1019-1.x86_64.deb'
• Install the driver - sudo apt install ./AmazonRedshiftODBC-64-bit-1.5.16.1019-1.x86_64.deb
• Install the driver manager unixODBC - sudo apt-get update && sudo apt-get install unixodbc unixodbc-dev
• Add driver manager libraries to env variable ○ echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/amazon/redshiftodbc/lib/64' >> ~/.bashrc ○ source ~/.bashrc
• Connector Configuration Files ○ Add /home/ubuntu/odbc-config/.odbc.ini & .odbcinst.ini (custom location to verify env variables) ○ echo 'export ODBCINI=/home/ubuntu/odbc-config/odbc.ini' >> ~/.bashrc ○ echo 'export ODBCINSTINI=/home/ubuntu/odbc-config/odbcinst.ini' >> ~/.bashrc echo 'export AMAZONREDSHIFTODBCINI=/home/ubuntu/odbc-config/amazon.redshiftodbc.ini ' >> ~/.bashrc ○ source ~/.bashrc
• Update all 3 ini files with proper configuration
• Added inbound rule for ec2 security group in redshift poc cluster - sg-0ac32042ceb240747 (launch-wizard-41)
• Run isql -v AmazonRedshiftDSN64

Configurations after POC:

.bashrc

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/amazon/redshiftodbc/lib/64

export ODBCINI=/home/ubuntu/odbc-config/odbc.ini

export ODBCINSTINI=/home/ubuntu/odbc-config/odbcinst.ini

export AMAZONREDSHIFTODBCINI=/home/ubuntu/odbc-config/amazon.redshiftodbc.ini

/home/ubuntu/odbc-config/odbc.ini

[ODBC]

Trace=yes

TraceFile=/tmp/odbc_trace.log

[ODBC Data Sources]

AmazonRedshiftDSN64=Amazon Redshift (x64)

[AmazonRedshiftDSN64]

Description=Amazon Redshift ODBC Driver (64-bit) DSN

Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so

Host=redshift-host.redshift.amazonaws.com

Port=5439

UID=awsuser

PWD=password

Database=dev

locale=en-US

/home/ubuntu/odbc-config/odbcinst.ini

[ODBC Drivers]

Amazon Redshift (x64)=Installed

[Amazon Redshift (x64)]

Description=Amazon Redshift ODBC Driver(64-bit)

Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so

/home/ubuntu/odbc-config/amazon.redshiftodbc.ini

[Driver]

- DriverManagerEncoding is detected automatically.

Add DriverManagerEncoding entry if there is a need to specify.

ErrorMessagesPath=/opt/amazon/redshiftodbc/ErrorMessages

LogLevel=0

LogPath=[LogPath]

SwapFilePath=/tmp

So no, ODBC can be used on Ubuntu 24.04 and potentially some other distro/flavour that'll be suitable for us.

Roadmap

1. Clean up code and make it more modular
2. GetTableNames is using currentMonth but not currentYear, that could be an issue
3. Update the tablecreation job to use ODBC
4. Setting up Redshift ODBC locally to invoke debugger to test code. Dockerfile can be worked on later.
5. Find a way to decouple the ODBC code to make it available to other services
6. Test the commonality with datadrop
7. Update the Dockerfile to use the appropriate image and build the environment
8. Test on dev

Questions

1. Redshift ODBC 1.x vs 2.x

Issues

1. Multi statement queries not supported, trying to make a code based transaction system like shared.