How to install an ODBC driver to Exploratory Server

To install an Denodo ODBC driver to Exploratory Server, you can create a customized Docker image based on the default R environment Docker image of Exploratory Server.

1. Run a Docker container based on the default r-exploratory Docker image

Run the following command to start a Docker container based on the default r-exploratory Docker image. In this example, we use the Docker image from Collaboration Server version 8.3.2. Please replace the version number in the command with the one you are using.

docker run -it r-exploratory:8.3.2 /bin/bash

2. Install an Denodo ODBC driver to the Docker container

With the shell you opened, install the Denodo ODBC driver that you want to use on the Exploratory Server.

Download and install ODBC Driver

You can download the ODBC driver from the Denodo's website.

Install ODBC Driver

NOTE: if you need a proxy server to connect to the outside network, run the below command before hand on the terminal. (Please replace username, password, server, and port with actual values)

export http_proxy=
http://username:password@server:port/

Place the ODBC driver to your file server so that you can get it within Docker. Here is some example.

curl -O https://<your_file_server>/downloads/denodo-vdp-odbcdriver-8.0-update-20230914-linux.zip

Then unzip the zip file.

unzip denodo-vdp-odbcdriver-8.0-update-20230914-linux.zip

Install text editor

Since we need to update the configuration file as the next step, let's install text editor (vim) as follows.

apt-get -y --no-install-recommends install vim

Add the ODBC driver to ODBC configuration file.

Once installation is done, type the below command.

odbcinst -j

And this will show you the ODBC configuration file location as follows:

unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

So now you know that /etc/odbcinst.ini is the configuration file for ODBC drivers.

For this example, the ODBC driver is installed in /denodo-vdp-odbcdriver-linux-8.0.8/ so add the entries to /etc/odbcinst.ini file.

Open the odbcinst.ini file with the Text Editor (Vim) you just installed.

vim /etc/odbcinst.ini

and add the below at the bottom of the file and save it.

[DenodoODBCDriver]
Description=ODBC driver for Denodo 8.0
Driver=/denodo-vdp-odbcdriver-linux-8.0.8/denodo-vdp-odbcdriver-linux/lib/unixodbc_x64/denodoodbc.so
UsageCount=1

Make sure the Driver path is correct. You can check it by running following command from terminal and ensure this returns result.

ls -l /denodo-vdp-odbcdriver-linux-8.0.8/denodo-vdp-odbcdriver-linux/lib/unixodbc_x64/denodoodbc.so

3. Overwrite libcurl included in the ODBC driver

Since the ODBC driver includes older version of libcurl, we want to Overwrite it with the one comes with Docker.

Go to the directory where the ODBC driver is installed.

cd /denodo-vdp-odbcdriver-linux-8.0.8/denodo-vdp-odbcdriver-linux/lib/unixodbc_x64

Backup libcurl.so.4 file included in the ODBC driver

Let's backup the file as librucl.so.4.back

mv libcurl.so.4 libcurl.so.4.back

Create a symbolic link to the libcurl included in the Docker.

ln -s /lib/x86_64-linux-gnu/libcurl.so.4 libcurl.so.4

Check if the symbolic link is valid.

Type in ldd as follows and make sure it returns result.

ldd libcurl.so.4

4. Test the ODBC Driver connection

On the terminal type in R and start the R session.

In the R's command-line interface, type in odbc::odbcListDrivers() and ensure it shows DenodoODBCDriver entry in the result.

> odbc::odbcListDrivers()

In the R's command-line interface type in below command. (Replace host, port, username, password, and database with actual values) and make sure this returns TRUE

conn <- exploratory::getDBConnection(type='dbiodbc', subType='conn_str_kv', driver = 'DenodoODBCDriver', host = 'host', port=1234, username = 'username', password = ‘password', database = 'database'); DBI::dbIsValid(conn);

5. Create a customized Docker image based on the container

Open another shell on the host Linux machine, and run the following command to find the ID of the Docker container we just ran and made the modifications on.

docker ps

The above command should give the table that looks like the following as the output.

CONTAINER ID        IMAGE                    COMMAND         PORTS       
aad6f8d956a9        r-exploratory:8.3.2   "/bin/bash"    6311/tcp

Find the Container ID for the Docker container from the table. In this case, it's aad6f8d956a9.

With the ID, you can now create a Docker image with the modifications. Here, let's give the new image the customized version number 8.3.2.1.

docker commit  -c 'CMD ["R", "-e", "Rserve::run.Rserve(remote=TRUE)"]' -c 'EXPOSE 6311' aad6f8d956a9 r-exploratory:8.3.2.1

6. Verify that the new Docker image has your changes

Start a Docker container with the new Docker image you just created.

docker run -it r-exploratory:8.3.2.1 /bin/bash

7. Use the customized r-exploratory Docker image from the Collaboration Server

Locate the line in the docker-compose.yml file that specified the Docker image version for the r-exploratory Docker container that looks like the following.

...
  rserve:
    image: r-exploratory:8.3.2
...

Modify the version to the one you just created.

...
  rserve:
    image: r-exploratory:8.3.2.1
...

Now, restarting the Exploratory Server would make it start using the new R environment with the custom modifications.