Configuring ODBC Drivers
Audience: System Administrators
Content Summary: Depending on the data source types you will be exposing through Immuta, you may need to enable additional data source types.
The base Immuta installation includes support for the following data sources:
- Amazon S3
- Apache HDFS
- FTP
- Greenplum
- PostgreSQL
- Redshift
Additional steps will be required to enable support for:
These steps should be completed on the Query Engine node, and each Web Service node.
Apache Hive
Locate the RPM for the Hive ODBC driver distributed by your Hadoop vendor. In some cases, the RPM will be available in a YUM repository. In other cases, you may need to download the ODBC driver from the vendor's website.
Cloudera's Hive ODBC driver can be downloaded from
https://www.cloudera.com/downloads/connectors/hive/odbc/2-5-25.html.
Choose Version Hive ODBC Driver 2.5.25
, Operating System Linux
, OS Version Rhel 7 - 64 bit
and click GET IT NOW!
.
The examples below are given for a Cloudera distribution, and will need to be modified for other vendors.
First, install the Hive ODBC driver on your Immuta server:
Note: The following command must be run as
root
.
yum -y install ClouderaHiveODBC
Register the Hive ODBC driver with unixODBC using odbcinst
:
Note: The following command must be run as
root
.
cat <<EOF | odbcinst -i -d -r
[Hive]
Description = Cloudera ODBC Driver for Apache Hive
Driver = /opt/cloudera/hiveodbc/lib/64/libclouderahiveodbc64.so
Driver64 = /opt/cloudera/hiveodbc/lib/64/libclouderahiveodbc64.so
DontDLClose = 1
EOF
Once the ODBC driver is installed, the Apache Hive data source type must be enabled in the Immuta configuration.
Uncomment the line corresponding to Apache Hive in the handlers array in /etc/immuta/config.yml
. This can be
accomplished with the following sed
command:
Note: The following command must be run as
root
.
sed -i '/- Apache Hive/s/^\s*#//' /etc/immuta/config.yml
Restart services for the change to take effect.
For Web Service nodes:
Note: The following command must be run as
root
.
systemctl restart immuta
For Query Engine nodes:
Note: The following command must be run as
root
.
systemctl restart postgresql-11
Apache Impala
Locate the RPM for the Impala ODBC driver. You may need to download the ODBC driver from the vendor's website.
Cloudera's Impala ODBC driver can be downloaded from
https://www.cloudera.com/downloads/connectors/impala/odbc/2-5-41.html.
Choose Version Impala ODBC Driver 2.5.41
, Operating System Linux
, OS Version Rhel 7 - 64 bit
and click GET IT NOW!
.
The examples below are given for a Cloudera distribution, and will need to be modified for other vendors.
First, install the Impala ODBC driver on your Immuta server:
Note: The following command must be run as
root
.
yum -y install ClouderaImpalaODBC
Register the Impala ODBC driver with unixODBC using odbcinst
:
Note: The following command must be run as
root
.
cat <<EOF | odbcinst -i -d -r
[Impala]
Description=Cloudera ODBC Driver for Impala
Driver=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so
Driver64=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so
DontDLClose=1
EOF
Once the ODBC driver is installed, the Apache Impala data source type must be enabled in the Immuta configuration.
Uncomment the line corresponding to Apache Impala in the handlers array in /etc/immuta/config.yml
. This can
be accomplished with the following sed
command:
Note: The following command must be run as
root
.
sed -i '/- Apache Impala/s/^\s*#//' /etc/immuta/config.yml
If the Impala databases that Immuta will be connecting to are using SSL, but their CA certificates are not in the
system CA bundle (/etc/pki/tls/certs/ca-bundle.pem
), it may make sense to update the default Impala connection
string options to include the TrustedCerts
property. Add or modify the following snippet in
/etc/immuta/config.yml
on the Web Service nodes:
handlers:
impalaHandler:
dbms:
driverArguments:
TrustedCerts: /path/to/the/ca
Note: This file path must exist on all Web Service and Query Engine nodes.
Options added to this driverArguments
object will be added to the default connection string option in the create data
source workflow, but can be modified as needed for each individual database. Additional default connection string
options can be added to the driverArguments
object, such as AllowHostNameCNMismatch
or AllowSelfSignedServerCert
.
Restart services for the change to take effect.
For Web Service nodes:
Note: The following command must be run as
root
.
systemctl restart immuta
For Query Engine nodes:
Note: The following command must be run as
root
.
systemctl restart postgresql-11
MariaDB and MySQL
Download the binary MariaDB ODBC driver for Linux directly from MariaDB: https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.0.2. Install the driver by extracting the archive into the appropriate directory:
Note: The following command must be run as
root
.
tar -xzf mariadb-connector-odbc-3.0.2-ga-rhel7-x86_64.tar.gz -C /usr/local
ln -s /usr/local/mariadb-connector-odbc-3.0.2-ga-rhel7-x86_64/ /usr/local/mariadb
Register the MariaDB ODBC driver for MariaDB and MySQL with unixODBC using odbcinst
:
Note: The following command must be run as
root
.
cat <<EOF | odbcinst -i -d -r
[MariaDB]
Description=MariaDB ODBC Driver
Driver=/usr/local/mariadb/lib64/libmaodbc.so
Driver64=/usr/local/mariadb/lib64/libmaodbc.so
DontDLClose=1
EOF
cat <<EOF | odbcinst -i -d -r
[MySQL]
Description=MySQL (Using MariaDB ODBC Driver)
Driver=/usr/local/mariadb/lib64/libmaodbc.so
Driver64=/usr/local/mariadb/lib64/libmaodbc.so
DontDLClose=1
EOF
Once the ODBC driver is installed, the MariaDB and MySQL data source types must be enabled in the Immuta configuration.
Uncomment the line corresponding to MariaDB and MySQL in the handlers array in /etc/immuta/config.yml
.
This can be accomplished with the following sed
commands:
Note: The following commands must be run as
root
.
sed -i '/- MariaDB/s/^\s*#//' /etc/immuta/config.yml
sed -i '/- MySQL/s/^\s*#//' /etc/immuta/config.yml
Restart services for the change to take effect.
For Web Service nodes:
Note: The following command must be run as
root
.
systemctl restart immuta
For Query Engine nodes:
Note: The following command must be run as
root
.
systemctl restart postgresql-11
Microsoft SQL Server
Immuta provides an RPM that can be used to install the FreeTDS ODBC driver that enables Microsoft SQL Server data source creation:
Note: The following command must be run as
root
.
yum install freetds
Register the FreeTDS ODBC driver with unixODBC using odbcinst
:
Note: The following command must be run as
root
.
cat <<EOF | odbcinst -i -d -r
[FreeTDS]
Description=v0.91 with protocol v8.0
Driver=/usr/lib64/libtdsodbc.so.0
Driver64=/usr/lib64/libtdsodbc.so.0
DontDLClose=1
EOF
Once the ODBC driver is installed, the Microsoft SQL Server data source types must be enabled in the Immuta
configuration. Uncomment the line corresponding to Microsoft SQL Server in the handlers array in
/etc/immuta/config.yml
. This can be accomplished with the following sed
command:
Note: The following command must be run as
root
.
sed -i '/- Microsoft SQL Server/s/^\s*#//' /etc/immuta/config.yml
Restart services for the change to take effect.
For Web Service nodes:
Note: The following command must be run as
root
.
systemctl restart immuta
For Query Engine nodes:
Note: The following command must be run as
root
.
systemctl restart postgresql-11
Netezza
You will need the npsclient.7.2.0.5-P1.tar.gz
file from IBM in order to complete the Netezza ODBC driver installation.
Install the driver by extracting the binaries:
Note: The following command must be run as
root
.
mkdir -p /opt/ibm/netezza
tar -xzf npsclient.7.2.0.5-P1.tar.gz -C /opt/ibm/neteza
Register the Netezza ODBC driver with unixODBC using odbcinst
:
Note: The following command must be run as
root
.
cat <<EOF | odbcinst -i -d -r
[Netezza]
Description = Netezza ODBC driver
Driver = /opt/ibm/netezza/lib/libnzsqlodbc3.so
Driver64 = /opt/ibm/netezza/lib64/libnzodbc.so
APILevel = 1
ConnectFunctions = YYN
DriverODBCVer = 03.51
DebugLogging = false
LogPath = /tmp
UnicodeTranslationOption = utf8
CharacterTranslationOption = all
PreFetch = 256
Socket = 16384
EOF
Once the ODBC driver is installed, the Netezza data source types must be enabled in the Immuta
configuration. Append a line for Netezza in the handlers array in
/etc/immuta/config.yml
:
handlers:
- Netezza
Minimization policies are disabled by default. To enable this policy type in the Immuta configuration,
add the following snippet in /etc/immuta/config.yml
:
handlers:
netezzaHandler:
config:
disabledPolicyTypes: []
Restart services for the changes to take effect.
For Web Service nodes:
Note: The following command must be run as
root
.
systemctl restart immuta
For Query Engine nodes:
Note: The following command must be run as
root
.
systemctl restart postgresql-11
Oracle
The Oracle ODBC drivers must be installed on the Immuta server in order to enable support for Oracle data sources. The
RPM that needs to be installed is oracle-instantclient12.1-odbc
and is available from Oracle. Visit
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
and download both oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
and
oracle-instantclient12.1-odbc-12.1.0.2.0-1.x86_64.rpm
. Install these RPMs with the following:
Note: The following command must be run as
root
.
yum install oracle-instantclient12.1-odbc oracle-instantclient12.1-basic
Register the Oracle ODBC driver with unixODBC using odbcinst
:
Note: The following command must be run as
root
.
cat <<EOF | odbcinst -i -d -r
[Oracle]
Description=Oracle Instant Client ODBC Driver
Driver=/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
Driver64=/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
DontDLClose=1
EOF
Once the ODBC driver is installed, the Oracle data source types must be enabled in the Immuta configuration. Uncomment
the line corresponding to Oracle in the handlers array in /etc/immuta/config.yml
. This can be accomplished
with the following sed
command:
Note: The following command must be run as
root
.
sed -i '/- Oracle/s/^\s*#//' /etc/immuta/config.yml
On the Query Engine node, make sure that the Oracle libraries are added to the LD_LIBRARY_PATH
:
Note: The following commands must be run as
root
.
echo /usr/lib/oracle/12.1/client64/lib > /etc/ld.so.conf.d/oracle-12.1.conf
ldconfig -v
On the Web Service nodes, make sure that the Oracle libraries are added to the LD_LIBRARY_PATH
:
Note: The following commands must be run as
root
.
echo /usr/lib/oracle/12.1/client64/lib > /etc/ld.so.conf.d/oracle-12.1.conf
ldconfig -v
To verify that the Oracle ODBC driver is properly configured create a temporary file named oracle.datasource.template
on each of the web service and query engine nodes. Use the example below to create an ODBC data source configuration for
one of your Oracle databases. Be sure to replace hostname, port, and SID:
[OracleTest]
Description = Test connection to using Oracle ODBC Driver
Driver = Oracle
ServerName = //<hostname>:<port>/<SID>
Register the data source with unixODBC using odbcinst
:
odbcinst -i -s -f oracle.datasource.template
Now you can verify your connection:
isql -v OracleTest <username> <password>
Note: If connection failed with the error
OID generation failed
, then the hostname on your the node that you are verifying on does not have a resolvable hostname set. To correct this issue, you must properly configure a hostname for all nodes, or add an entry in /etc/hosts to resolve the machines hostname to127.0.0.1
.The following command must be run as
root
:echo "127.0.0.1 $(hostname)" >> /etc/hosts
Once you have verified your connection, you may delete the user ODBC data source configuration file ~/.odbc.ini
.
Restart the services for the change to take effect.
For Web Service nodes:
Note: The following command must be run as
root
.
systemctl restart immuta
For Query Engine nodes:
Note: The following command must be run as
root
.
systemctl restart postgresql-11
Teradata
The Teradata ODBC drivers must be installed on the Immuta server in order to enable support for Teradata data sources.
The RPM that needs to be installed is tdodbc1510
and is available from Teradata:
Note: The following command must be run as
root
.
yum install tdodbc1510
Register the Teradata ODBC driver with unixODBC using odbcinst
:
Note: The following command must be run as
root
.
cat <<EOF | odbcinst -i -d -r
[Teradata]
Description=Teradata ODBC Driver
Driver=/opt/teradata/client/${TDODBC_VERSION}/lib64/tdata.so
Driver64=/opt/teradata/client/${TDODBC_VERSION}/lib64/tdata.so
DontDLClose=1
EOF
Once the ODBC driver is installed, the Teradata data source types must be enabled in the Immuta configuration.
Uncomment the line corresponding to Teradata in the handlers array in /etc/immuta/config.yml
. This can be
accomplished with the following sed
command:
Note: The following command must be run as
root
.
sed -i '/- Teradata/s/^\s*#//' /etc/immuta/config.yml
Restart services for the change to take effect.
For Web Service nodes:
Note: The following command must be run as
root
.
systemctl restart immuta
For Query Engine nodes:
Note: The following command must be run as
root
.
systemctl restart postgresql-11