Database Driver Management

Introduction

When using the Database Query Component Matillion ETL requires a Type 4 JDBC Driver (Type 3 drivers should also work but these remain untested)  to be loaded. Depending on the platform, Matillion ETL is delivered with some Type 4 Drivers but due to licensing restrictions others will need to be uploaded manually via the Database Driver Management screen. 


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 Any - latest recommended 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 would 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.

From the Admin Menu → Manage Database Drivers screen.



 - Click New to create a new driver group.


 - Select the new group 
 - Click Browse and select the jar file for the browser.


Note: Sometimes 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 and they will be added to the driver classpath.

Click Test. This will test that the driver has been successfully loaded in the class-path and retrieve its reported version. Note at this point we are not testing a database connection.
Finally click OK to save any change made.
 

FAQ

Where driver files stored? 

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.


I have a type 4 JDBC Driver for a database that is not listed here, can I connect?
 

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

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 wrong 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: An example URL used to prompt the user to enter the correct JDBC URL. Can be left blank or preferably with a URL such as jdbc:sybase:Tds:<serverName>:2638.
  • 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 database 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 these 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.amazonaws.athena.jdbc.AthenaDriver",
"url" : "jdbc:awsathena://athena.<REGION>.amazonaws.com:443",
"fetchsize" : "500",
"limit" : "limit-outer",
"prepmeta" : false,
"allowUpload" : true,
"defaultProperties" : {"logintimeout": "20"}
 }

Please contact support@matillion.com to get more help with this.