Skip to content

You are viewing documentation for Immuta version 2.8.

For the latest version, view our documentation for Immuta SaaS or the latest self-hosted version.

cancel

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

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