Manage Database Drivers

Manage Database Drivers


Overview

When using the Database Query component, Matillion ETL requires a Type 4 JDBC Driver to be loaded. Depending on the platform, Matillion ETL is delivered with some Type 4 drivers, but due to licencing restrictions, some drivers will need to be uploaded manually via the Database Driver Management screen.

Type 3 drivers should also work, but these remain untested.


Currently Included Drivers

Database Driver Included Version Download/Information
Microsoft SQL Server  JTDS 1.3.1 http://jtds.sourceforge.net/
Sybase ASE JTDS 1.3.1 http://jtds.sourceforge.net/
IBM DB2 for i JTOpen 6.0 http://jt400.sourceforge.net/
IBM DB2 No - manual upload Match your DB2 version http://www-01.ibm.com/support/docview.wss?uid=swg21363866
Use the db2jcc4.jar
PostgresSQL Yes 9.4-1206-jdbc41 https://jdbc.postgresql.org/
MySQL No - manual upload v8 Recommended (5.1.47 supported) https://dev.mysql.com/downloads/connector/j/
The file name required is normally mysql-connector-java-X.X.XX.jar where X.X.XX is the version number
Oracle No - manual upload We strongly recommend using the V12 or newer driver. http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html
The file name required is normally 'ojdbc7.jar'
Teradata No - manual upload Any - latest recommended https://downloads.teradata.com/download/connectivity/jdbc-driver


Uploading a Driver via the UI

This will associate the driver you are uploading with the listed drivers, and ensure it is loaded by Matillion ETL when you attempt a database connection.

1. Click AdminManage Database Drivers.

2. Click New.

3. Select a driver from the drop-down menu. Click OK

4. Click Browse to locate the .jar file.

5. Use the Test button to confirm that the file has been uploaded successfully.

6. Click OK.

Please Note

  • In some instances, a driver is not contained as a single JAR file, but has some dependencies. If you can add these dependant jar files as additional uploads, they will be added to the driver classpath.
  • Currently, users can only add drivers specified in the jdbc-providers.properties file. If your drop-down menu is empty, or missing the driver you want to add, see the below question: "I have a Type 4 JDBC driver for a database that is not listed here, can I connect?"


FAQ

Q. Where are driver files stored?

A. When a driver is uploaded via the web interface, it is placed and loaded from the following location in the Instance file system:

/usr/share/tomcat8/Drivers/<Driver Group Name>/
/etc/tomcat8/Drivers/<Driver Group Name>/

Where <Driver Group Name> is the name of the Driver group in the Matillion Web Interface. E.g. Teradata.

Q. I have a Type 4 JDBC driver for a database that is not listed here, can I connect?

A. Yes—but some configuration file changes are needed to support this. The drivers supported in Matillion are configurable in the following file:

/usr/share/emerald/WEB-INF/classes/jdbc-providers.properties.

In here you will see a JSON file with the repeating elements—a set for each driver will be present.

To add an entry for IQ, copy an existing block such as the ASE example above, and then edit it. Be careful with the commas between the blocks. If the JSON syntax is incorrect, Matillion ETL wont start.

Each entry is defined by the following entries:

  • name: This can be anything and will just show up in the Database Query Component with this name.
  • driver: The base class name for the JDBC Driver.
  • url: A template URL used to prompt the user to enter the correct JDBC URL.
  • fetchsize: The number of rows fetched at once, if supported by the database driver.
  • limit: When Matillion gets metadata about the query, but “prepmeta” is false, it needs to fetch one row of data to determine this metadata. This is done using a limit, but different databases support different ways of supporting that:
    • top-n: SELECT TOP 1 * FROM (query)
    • fetch-first-n: query FETCH FIRST 1 ROWS ONLY
    • limit-outer: SELECT * FROM (query) LIMIT 1
    • limit-inline: query LIMIT 1
    • rownum: SELECT * FROM (query) WHERE ROWNUM <=1
    • none: query
  • prepmeta: (True or False) Whether the metadata for the query can be retrieved using a prepared statement. The default is true. If a driver is giving strange errors, set to false and choose an appropriate limit above
  • allowUpload: (True or False) Enables the ability to upload your own driver JAR via the UI. Recommended value is true.
  • defaultProperties: Any documented driver properties to set by default. These can be overridden in the UI.


For example:

{
"name" : "Sybase IQ",
"driver" : "com.sybase.jdbc4.jdbc.SybDriver",
"url" : "jdbc:sybase:Tds:12.140.13.8:2638",
"fetchSize" : "500",
"limit" : "top-n",
"prepmeta" : true,
"allowUpload" : true,
"defaultProperties" : {"loginTimeout": "20"}
}


Another example:

{
"name" : "Amazon Athena",
"driver" : "com.simba.athena.jdbc.Driver",
"url" : "jdbc:awsathena://athena.<REGION>.amazonaws.com:443",
"fetchSize" : "500",
"limit" : "limit-outer",
"prepmeta" : false,
"allowUpload" : true,
"defaultProperties" : {"loginTimeout": "20"}
}

Q. How do I avoid the "No suitable driver found" error when attempting to add a Snowflake driver?

A.In addition to the previous question, the entry in the jdbc-providers.properties file for Snowflake may be incorrect and require minor edits. To avoid a "No suitable driver found" error, the entry should take the form:

{"name" : "SnowCDW",
"driver" : "com.snowflake.client.jdbc.SnowflakeDriver",
"url" : "jdbc:snowflake://host/?warehouse=whs",
"fetchSize" : "500",
"limit" : "limit-inline",
"prepmeta" : false,
"allowUpload" : true
}

Please do not hesistate to Contact Support if you are experiencing difficulties with database drivers.