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 - '
  


• 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.