Skip to content

Manage Database Drivers

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 Manage Database Drivers dialog.

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

Note

This page describes database drivers used with the Database Query component. Drivers listed below—such as Snowflake or Amazon Redshift—do not require you to also be running Matillion ETL for Snowflake and/or Matillion ETL for Redshift respectively. For example, users on Matillion ETL for BigQuery can run the Database Query component connecting to a Snowflake or Redshift database.

Drivers

Database Driver included? Version Download/info
Amazon Redshift Yes 2.0 https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-download-driver.html
IBM DB2 No. Manual upload required. Match your DB2 version. http://www-01.ibm.com/support/docview.wss?uid=swg21363866. Use the db2jcc4.jar
IBM DB2 for i JTOpen 6.0 http://jt400.sourceforge.net/
Microsoft SQL Server JTDS 1.31. http://jtds.sourceforge.net/
MySQL No. Manual upload required. v8 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. For example, 5.1.47.
Netezza No. Manual upload required. Any. Latest recommended. https://www.ibm.com/docs/en/psfa/7.2.1?topic=configuration-installing-configuring-jdbc
Oracle No. Manual upload required. Supports v12 and higher. http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html. The file name required is normally ojdbc8.jar.
PostgreSQL Yes 42.3.4 https://jdbc.postgresql.org/
SAP Hana No. Manual upload required. Any. Latest recommended. https://developers.sap.com/tutorials/hana-clients-jdbc.html
Snowflake Yes. 3.13.26. Release notes. https://docs.snowflake.com/en/user-guide/jdbc-download.html
SQL Server (Microsoft driver) Yes Any. Latest recommended. https://learn.microsoft.com/en-us/sql/connect/jdbc/using-the-jdbc-driver?view=sql-server-ver15
Sybase ASE JTDS 1.31. http://jtds.sourceforge.net/
Teradata No. Manual upload required. Any. Latest recommended. https://downloads.teradata.com/download/connectivity/jdbc-driver.

Uploading a driver via the Matillion ETL 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 Admin → Manage Database Drivers.
  2. Click New.
  3. Choose a driver from the dropdown menu and click OK.
  4. Click Browse and locate your .jar file.
  5. Click Test to confirm that file upload is successful.
  6. Click OK to finish.

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 class path.
  • Currently, users can only add drivers specified in the jdbc-providers.properties file. If your dropdown 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

Where are driver files stored?

When a driver is uploaded via the Matillion ETL UI, it is placed and loaded from the following location in the instance's file system:

Snowflake and Amazon Redshift:

/usr/share/tomcat/Drivers/<Driver Group Name>/

Google BigQuery:

/etc/tomcat/Drivers/<Driver Group Name>/

Where <Driver Group Name> is the name of the driver group in the Matillion ETL UI. For example, Teradata.

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

Yes—but some configuration file changes are needed to support this. The drivers supported in Matillion ETL 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 Matillione ETL 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 throwing 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"}
}

{
"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"}
}

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

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 contact support if you're experiencing difficulties with database drivers.

© Copyright 2024 Matillion Ltd | Legal | Privacy Policy | Disclaimer | Modern Slavery Statement