Skip to content

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:

The steps outlined on this page 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 6 - 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:

The following command must be run as root:

yum -y install ClouderaHiveODBC

Register the Hive ODBC driver with unixODBC using odbcinst:

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:

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.

Web Service Nodes

The following command must be run as root:

service immuta restart

Query Engine Nodes

The following command must be run as root:

service postgresql-11 restart

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

The following command must be run as root:

yum -y install ClouderaImpalaODBC

Register the Impala ODBC driver with unixODBC using odbcinst.

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, which 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.

Web Service Nodes

The following command must be run as root:

service immuta restart

Query Engine Nodes

The following command must be run as root:

service postgresql-11 restart

IBM DB2

The IBM DB2 ODBC drivers will need to be installed in order for IBM DB2 data sources to be enabled. These drivers may be available in a YUM repo, but if they are not, you can download them from http://www.ibm.com/services/forms/preLogin.do?source=swg-idsoc97 . The required file is IBM Data Server Driver for ODBC and CLI (Linux AMD64 and Intel EM64T) Version 11.1 you'll need to agree to the terms and fill out a small form and then you can select the Download using http tab and download the file ibm_data_server_driver_for_odbc_cli_linuxx64_v11.1.tar.gz.

To install the drivers from the tarball, simply extract them to a location on the Immuta server.

The following command must be run as root:

mkdir -p /opt/ibm/db2
tar --directory /opt/ibm/db2 -xzf ibm_data_server_driver_for_odbc_cli_linuxx64_v11.1.tar.gz

Register the IBM DB2 ODBC driver with unixODBC using odbcinst.

The following command must be run as root:

cat <<EOF | odbcinst -i -d -r
[DB2]
Description=DB2 Driver
Driver=/opt/ibm/db2/clidriver/lib/libdb2o.so
Driver64=/opt/ibm/db2/clidriver/lib/libdb2o.so
FileUsage=1
DontDLClose=1
EOF

Once the ODBC driver is installed, the IBM DB2 data source type must be enabled in the Immuta configuration. Uncomment the line corresponding to DB2 in the handlers array in /etc/immuta/config.yml. This can be accomplished with the following sed command, which must be run as root:

sed -i '/- DB2/s/^\s*#//' /etc/immuta/config.yml

Restart services for the change to take effect.

Web Service Nodes

The following command must be run as root:

service immuta restart

Query Engine Nodes

The following command must be run as root:

service postgresql-11 restart

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.

The following command must be run as root:

tar -xzf mariadb-connector-odbc-3.0.2-ga-rhel6-x86_64.tar.gz -C /usr/local
ln -s /usr/local/mariadb-connector-odbc-3.0.2-ga-rhel6-x86_64/ /usr/local/mariadb

Register the MariaDB ODBC driver for MariaDB and MySQL with unixODBC using odbcinst.

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, which 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.

Web Service Nodes

The following command must be run as root:

service immuta restart

Query Engine Nodes

The following command must be run as root:

service postgresql-11 restart

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.

The following command must be run as root:

yum install freetds

Register the FreeTDS ODBC driver with unixODBC using odbcinst.

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 commands, which must be run as root:

sed -i '/- Microsoft SQL Server/s/^\s*#//' /etc/immuta/config.yml

Restart services for the change to take effect.

Web Service Nodes

The following command must be run as root:

service immuta restart

Query Engine Nodes

The following command must be run as root:

service postgresql-11 restart

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.

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.

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.

Web Service Nodes

The following command must be run as root:

service immuta restart

Query Engine Nodes

The following command must be run as root:

service postgresql-11 restart

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 command, which must be run as root:

yum install oracle-instantclient12.1-odbc oracle-instantclient12.1-basic

Register the Oracle ODBC driver with unixODBC using odbcinst.

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 commands, which 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.

The following command 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.

The following command 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>

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 to 127.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.

Web Service Nodes

The following command must be run as root:

service immuta restart

Query Engine Nodes

The following command must be run as root:

service postgresql-11 restart

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.

The following command must be run as root:

yum install tdodbc1510

Register the Teradata ODBC driver with unixODBC using odbcinst.

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 commands, which must be run as root:

sed -i '/- Teradata/s/^\s*#//' /etc/immuta/config.yml

Restart services for the change to take effect.

Web Service Nodes

The following command must be run as root:

service immuta restart

Query Engine Nodes

The following command must be run as root:

service postgresql-11 restart