Add a New JDBC Driver (v6.1+)

Prior to Version 6.1.0, a new driver could be added via the UI of an MI instance. Beginning in v6.1.0, new drivers can be added only via dataprocessor container. This article describes how to add a new driver via dataprocessor container for Simple Installation.  A similar process applies to Orchestrated environments.

1. Save the Driver to /opt/mi/data/dataprocessor/external-libs/

/opt/mi/data/dataprocessor/external-libs/  is a default shared mount that is accessible to both host and dataprocessor container.

2. Get into Data Processor Container

  1. List all containers: sudo docker ps
  2. Locate the dataprocessor container ID
  3. Get into the dataprocessor container: docker exec -it <dataprocessor container ID> bash

3. Locate the Driver in the /app/external-libs/ Directory

  1. List all the contents of the external-libs directory: ls -l external-libs/
  2. Locate the .jar driver file

4. Add or Update Driver Data in Database

Depending on whether you are adding a new JDBC driver or updating the existing driver, proceed with the corresponding substep.

4.1. Add New JDBC Driver
  1. Exit the dataprocessor container: exit
  2. Access the web container: mi-console
  3. Access MySQL dashboard database: mysql dashboard
  4. Insert the driver's data (can be found in driver documentation) into jdbc_driver table: INSERT INTO jdbc_driver (name, class_name, default_port, jdbc_string_template, current_time_sql_stmt, pre_bind_variable_values_ind) VALUES ('<driver name>', '<driver class name>', <port number>, '<JDBC string template>', '<SQL statement to return current time>', '<Y/N>');

Example: INSERT INTO jdbc_driver (name, class_name, default_port, jdbc_string_template, current_time_sql_stmt, pre_bind_variable_values_ind) VALUES ('Hadoop Hive 3', 'org.apache.hadoop.hive.jdbc.HiveDriver', 10000, 'jdbc:hive2://%h:%p/%d', 'select unix_timestamp()', 'Y');

If you encounter any difficulties providing JDBC driver parameters, contact [email protected] for help.

4.2. Update Existing JDBC Driver

If the driver you are adding already exists in MI, you need to check whether the new driver file's class name (can be found in driver documentation) matches the database entry.

  1. If you do not know the class name of the new JDBC driver:
    • Run unzip -L /app/external-libs/ | grep driver
    • Check the driver's class name (you will need to check the files with .class extension)
  2. Exit the dataprocessor container: exit
  3. Access the web container: mi-console
  4. Access MySQL dashboard database: mysql dashboard
  5. Locate the existing driver's class name in the database: SELECT * FROM jdbc_driver WHERE name LIKE '%<old driver name>%'\G
  6. If the class name of the new driver is different from the database entry, update the database entry:
    UPDATE jdbc_driver SET class_name='<new driver`s class name>' WHERE jdbc_driver_id='<driver ID>';

Example: UPDATE dashboard.jdbc_driver SET class_name='org.hsqldb.jdbcdriver' WHERE jdbc_driver_id=10;

Before applying changes, you have to delete the old driver's .jar file, or modify its extension to be different from .jar, for example, .jar.bak:

  1. Exit mysql: exit
  2. Exit web container: exit
  3. Access the dataprocessor container: docker exec -it <dataprocessor container ID> bash
  4. Navigate to /app/drivers: cd /app/drivers
  5. Remove the old driver file:  rm <old driver file>

5. Confirm that the Driver Has root:root Owner and 644 Rights

  1. List the driver file in a long format: ls -l <driver file name>
  2. Confirm that the driver has root:root owner and 644 rights (-rw-r--r-- 1 root root). If it does not:
    • chmod 644 /app/external-libs/<jdbc-driver>.jar
    • chown root:root /app/external-libs/<jdbc-driver>.jar

6. Apply Changes

Beginning in MI v6.2.4, there is no need to restart the dataprocessor container: to apply driver changes, simply restart the dataprocessor service by running service dataprocessor restart.

For MI versions prior to 6.2.4 do the following:

  1. Exit container: exit
  2. Restart the container: sudo docker restart <dataprocessor container ID>
  3. Wait a few minutes for changes to apply
  4. Make sure that the dataprocessor container is up and healthy: sudo watch docker ps

7. Check the Driver in the UI

Access your MI instance from a browser. The added driver should appear in the drivers list.