- A Driver Manager (because we are working on Linux)
- A Driver for Redshift
- A working Redshift
- A Security Group in AWS that allows the server to connect to Redshift
If you are missing ANY of these, you will not be successful!
Keep in mind that the Driver Manager (we are using Progress Data Direct) and the Driver are 2 very different things!
The driver for Redshift is what allows you to connect to it.
Whereas, the Driver Manager , well, it manages my drivers!
Keep in mind that the Driver Manager (we are using Progress Data Direct) and the Driver are 2 very different things!
The driver for Redshift is what allows you to connect to it.
Whereas, the Driver Manager , well, it manages my drivers!
In other words, if I was to pick different data sources (say, Redshift, and Impala), it would be the part that chooses the right driver for the right database.
Note that you still need the driver manager on Linux, even if you have a single data source.
Note that you still need the driver manager on Linux, even if you have a single data source.
Check unixODBC there
[ec2-user@ip-10-0-0-145 ~]$ sudo rpm -q unixODBC
package unixODBC is not installedIf not, install it
[ec2-user@ip-10-0-0-145 ~]$ sudo yum -y install unixODBC
Loaded plugins: amazon-id, rhui-lb
rhui-REGION-client-config-server-7 | 2.9 kB 00:00:00
rhui-REGION-rhel-server-releases | 3.7 kB 00:00:00
rhui-REGION-rhel-server-rh-common | 1.9 kB 00:00:00
(1/4): rhui-REGION-client-config-server-7/x86_64/primary_db | 4.3 kB 00:00:00
(2/4): rhui-REGION-rhel-server-rh-common/7Server/x86_64/updateinfo | 11 kB 00:00:00
(3/4): rhui-REGION-rhel-server-rh-common/7Server/x86_64/primary | 30 kB 00:00:00
(4/4): rhui-REGION-rhel-server-releases/7Server/x86_64/primary_db | 12 MB 00:00:00
(1/2): rhui-REGION-rhel-server-releases/7Server/x86_64/updateinfo | 549 kB 00:00:00
(2/2): rhui-REGION-rhel-server-releases/7Server/x86_64/group_gz | 133 kB 00:00:00
rhui-REGION-rhel-server-rh-common 131/131
Resolving Dependencies
--> Running transaction check
---> Package unixODBC.x86_64 0:2.3.1-10.el7 will be installed
--> Processing Dependency: libltdl.so.7()(64bit) for package: unixODBC-2.3.1-10.el7.x86_64
--> Running transaction check
---> Package libtool-ltdl.x86_64 0:2.4.2-20.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================================================================
Installing:
unixODBC x86_64 2.3.1-10.el7 rhui-REGION-rhel-server-releases 413 k
Installing for dependencies:
libtool-ltdl x86_64 2.4.2-20.el7 rhui-REGION-rhel-server-releases 49 k
Transaction Summary
================================================================================================================================================================================
Install 1 Package (+1 Dependent package)
Total download size: 461 k
Installed size: 1.3 M
Downloading packages:
(1/2): libtool-ltdl-2.4.2-20.el7.x86_64.rpm | 49 kB 00:00:00
(2/2): unixODBC-2.3.1-10.el7.x86_64.rpm | 413 kB 00:00:00
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 1.1 MB/s | 461 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : libtool-ltdl-2.4.2-20.el7.x86_64 1/2
Installing : unixODBC-2.3.1-10.el7.x86_64 2/2
Verifying : libtool-ltdl-2.4.2-20.el7.x86_64 1/2
Verifying : unixODBC-2.3.1-10.el7.x86_64 2/2
Installed:
unixODBC.x86_64 0:2.3.1-10.el7
Dependency Installed:
libtool-ltdl.x86_64 0:2.4.2-20.el7
Dealing with the Redshift Driver
Note that once again, there are multiple choices. Datadirect offers some drivers, for a fee. Here, we are using the drivers provided for free by AWS.
Check if it’s there
[ec2-user@ip-10-0-0-145 ~]$ sudo rpm -q AmazonRedshiftODBC-64bit.x86_64
package AmazonRedshiftODBC-64bit.x86_64 is not installed
If not, install it
Make sure to check this page for the hyperlink to the latest .rpm of the driver. (mine below dates back to May).
[ec2-user@ip-10-0-0-145 ~]$ wget https://s3.amazonaws.com/redshift-downloads/drivers/AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64.rpm--2015-05-15 12:45:31-- https://s3.amazonaws.com/redshift-downloads/drivers/AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64.rpm
Resolving s3.amazonaws.com (s3.amazonaws.com)... 54.231.9.88
Connecting to s3.amazonaws.com (s3.amazonaws.com)|54.231.9.88|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 10546318 (10M) [application/x-rpm]
Saving to: ‘AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64.rpm’
100%[======================================================================================================================================>] 10,546,318 --.-K/s in 0.1s
2015-05-15 12:45:31 (70.7 MB/s) - ‘AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64.rpm’ saved [10546318/10546318]
[ec2-user@ip-10-0-0-145 ~]$ sudo yum -y --nogpgcheck localinstall AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64.rpm
Loaded plugins: amazon-id, rhui-lb
Examining AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64.rpm: AmazonRedshiftODBC-64bit-1.1.0-1.x86_64
Marking AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package AmazonRedshiftODBC-64bit.x86_64 0:1.1.0-1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================================================================
Installing:
AmazonRedshiftODBC-64bit x86_64 1.1.0-1 /AmazonRedshiftODBC-64bit-1.1.0.0000-1.x86_64 30 M
Transaction Summary
================================================================================================================================================================================
Install 1 Package
Total size: 30 M
Installed size: 30 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : AmazonRedshiftODBC-64bit-1.1.0-1.x86_64 1/1
Verifying : AmazonRedshiftODBC-64bit-1.1.0-1.x86_64 1/1
Installed:
AmazonRedshiftODBC-64bit.x86_64 0:1.1.0-1
Complete!
Create some Environment Variables
The "USER DATA SOURCE" odbc.ini file located at /home/boxiadm/app/bodsx/dataservices/DataDirect/odbc/odbc.ini is the actual file used.
set the environment variables with the environment file. in my case it is .dbenv.csh
For UNIX system to pick up the right locations, we will need to define some environment variables.
But first, let’s see which location is currently defined.
Before changing anything else, you should run:
But first, let’s see which location is currently defined.
Before changing anything else, you should run:
ODBC configuration files
We will need to update the 3 files, to describe our driver, and our data sources. I advise you to do them in the order described here.
Check the drivers
To check which drivers are configured, I can run this:
[ec2-user@ip-10-0-0-145 ~]$ odbcinst -q -d
note - this file is updated by the driver installation. no manual addition required in most of the case.
Watch out: In your odbcinst.ini file, if you have one or more spaces at the beginning of the line with the driver name, the odbcinst -q -d will not pick them up. I does not seem to read the line at all. I wasted quite a bit of time, because I had "indented" the file. So remember that there should be no leading space on the line before the [MyRedshiftDriver].
amazon.redshiftodbc.ini
The default file needs to be modified because we are using unixODBC. The parts I changed are in Red below.
[ec2-user@ip-10-0-0-145 ~]$ cat /shared/odbc/amazon.redshiftodbc.ini
## If you did not install in the default directory, replace '/opt/amazon/redshiftodbc'
## with the correct location.
## - Note that this default DriverManagerEncoding of UTF-32 is for iODBC.
## - unixODBC uses UTF-16 by default.
## - If unixODBC was compiled with -DSQL_WCHART_CONVERT, then UTF-32 is the correct value.
## Execute 'odbc_config --cflags' to determine if you need UTF-32 or UTF-16 on unixODBC
## - AmazonDM can be used with UTF-8 or UTF-16.
## The DriverUnicodeEncoding setting will cause AmazonDM to run in UTF-8 when set to 2 or UTF-16 when set to 1.
[Driver]
#EG# DriverManagerEncoding=UTF-32
DriverManagerEncoding=UTF-16 --> make sure to update this
ErrorMessagesPath=/opt/amazon/redshiftodbc/ErrorMessages
LogLevel=0
LogPath=[LogPath]
## - Uncomment the ODBCInstLib corresponding to the Driver Manager being used.
## - Note that the path to your ODBC Driver Manager must be specified in LD_LIBRARY_PATH.
# Generic ODBCInstLib
# iODBC
#EG# ODBCInstLib=libiodbcinst.so
# AmazonDM / unixODBC
#ODBCInstLib=libodbcinst.so
# AIX specific ODBCInstLib
# iODBC
#ODBCInstLib=libiodbcinst.a(libiodbcinst.so.2)
# AmazonDM
#ODBCInstLib=libodbcinst.a(odbcinst.so)
# unixODBC
ODBCInstLib=/usr/lib64/libodbcinst.so --> make sure to update this ; otherwise ,
We will get this error while testing :
[ec2-user@ip-10-0-0-145 ~]$ isql -v MyRealRedshift
[S1000][unixODBC][Amazon][ODBC] (11560)
Unable to locate SQLGetPrivateProfileString function.
[ISQL]ERROR: Could not SQLConnect
odbc.ini
[ec2-user@ip-10-0-0-145 ~]$ cat /home/boxiadm/app/bodsx/dataservices/DataDirect/odbc/odbc.ini
ODBC Data Sources]
[AmazonODBC64]
# Driver: The location where the ODBC driver is installed to.
Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so
# Required: These values can also be specified in the connection string.
Server=abc
Port=1234
Database=mydb
locale=en-US
This is where we will define our data sources. To see what is currently defined:
[ec2-user@ip-10-0-0-145 ~]$ odbcinst -q -s
Make sure to have the right database, user name, password details in odbc.ini file.
[ec2-user@ip-10-0-0-145 ~]$ isql -v MyRealRedshift dummy dummy
