KNIME Database Extension Guide


Introduction

The KNIME Database Extension provides a set of KNIME nodes that allow connecting to almost all JDBC-compliant databases. These nodes reside in the Database category in the Node Repository, where you can find a number of database access, manipulation and writing nodes.

The database nodes are part of every KNIME Analytics Platform installation. It is not necessary to install any additional KNIME Extensions.

Connecting to a database

The DatabaseConnector subcategory in the Node Repository contains

  • a set of database-specific connector nodes for commonly used databases such as MySQL,

  • as well as the generic Database Connector node.

The database-specific connector nodes already contain the necessary JDBC drivers and provide a configuration dialog that is tailored to the specific database. It is recommended to use these nodes over the generic Database Connector node, if possible.
020 database connector dialog

The generic Database Connector node can connect to arbitrary JDBC compliant databases. The most important node settings are described below

Database Driver: The dropdown box shows all available database drivers. You need to select an appropriate driver for your specific database. If there is no matching JDBC driver it first needs to be registered, see Vendor-specific JDBC drivers

Database URL: A driver-specific JDBC URL is required. Please consult the vendor documentation for the JDBC driver you are using.

Authentication: Login credentials can either be provided via credentials flow variables, or directly in the configuration dialog.

For the remaining settings please consult the Database Connector node description.

Vendor-specific JDBC drivers

For some databases KNIME Analytics Platform does not contain a ready-to-use JDBC driver. In these cases, it is necessary to first register a vendor-specific JDBC 4.1 driver in KNIME Analytics Platform. Please consult your database vendor to obtain the JDBC driver.

030 database preferences

To register your vendor-specific JDBC driver, go to FilePreferencesKNIMEDatabase Driver.

Add file: Click here, if the driver is provided as a single .jar file.

Add directory: Click here, if the driver is provided as a folder that contains several .jar files. Some vendors offer a .zip file for download, which needs to be unpacked to a folder first.

Working with databases

040 manipulation nodes

Once you have successfully connected to your database, there is a set of nodes that provide in-database data manipulation, such as row filtering, joining etc.

Additionally, there are nodes to read, write or delete data from a database, or to to run custom SQL statements, such as the Database SQL Executor node.

Please consult the example workflows, which are available in KNIME Analytics Platform in the KNIME Explorer View, when connecting to the EXAMPLES server. Under 01_Data_Access02_Databases you will find a variety of example workflows that demonstrate how to use the database nodes.