KNIME

The Konstanz Information Miner (KNIME) is a data analytics, reporting, and integration platform for Windows, OS X and Linux. KNIME is an open-source application and was initially developed in 2006 to perform data analysis for the pharmaceutical industry.

KNIME uses workflows for data analysis and integration. Workflows are displayed graphically as a set of nodes linked together by arrows that indicate the direction that data flows.

KNIME can use data from any database for which a JDBC driver is available. If you want to use data in KNIME by using an ODBC driver, you need a bridge driver that can translate between JDBC and ODBC. The Easysoft JDBC-ODBC Bridge is such a driver, and this blog will show you how to use the bridge to access data from an ODBC database such as Microsoft Access in KNIME.

  1. Configure a System ODBC data source for the database that you want to connect to in KNIME.

    To do this, use the 32-bit version of ODBC Data Source Administrator on your KNIME machine. On some versions of Windows, this is located in Control Panel > Administrative Tools. On some version of Windows, you need to search for ODBC in the taskbar search box. The 32-bit version of ODBC Data Source Administrator should be clearly labelled. If in doubt, in the Windows Run dialog box, type:

    %windir%\syswow64\odbcad32.exe
  2. Download the Easysoft JDBC-ODBC Bridge. (Registration required.)
  3. Install and license the Easysoft JDBC-ODBC Bridge on the machine where the KNIME is installed.

    For installation instructions, see the Easysoft JDBC-ODBC Bridge documentation.

KNIME provides a mechanism for adding database drivers. To use the Easysoft JDBC-ODBC Bridge under KNIME, we first need to add it as a driver.

  1. In KNIME Analytics Platform, choose File > Preferences.

    The Preferences dialog box is displayed.

  2. In the left pane, choose KNIME > Databases.
  3. In the right pane, choose Add file.

    The Open dialog box is displayed.

  4. Browse for the Easysoft JDBC-ODBC Bridge JAR file, EJOB.jar.

    EJOB.jar is installed in the following location on the machine where you installed the Easysoft JDBC-ODBC Bridge:

    <easysoft_install>\Jars

    On 64-bit Windows, the default location for <easysoft_install> is <drive>:\Program Files (x86)\Easysoft Limited\Easysoft JDBC-ODBC Bridge.

  5. Choose OK to return to KNIME.

To try out the Easysoft JDBC-ODBC Bridge with KNIME, we will retrieve some ODBC data by using a database reader.

  1. Create a new KNIME workflow.
  2. Drag a Database > Connector > Database Connector from the Node Repository pane to the project workspace.
  3. Double-click the new connector to configure it.

    The Database Connector dialog box is displayed.

  4. Complete the dialog box fields:
    Field Value
    Name Easysoft JDBC-ODBC Bridge
    Driver easysoft.sql.jobDriver
    Database URL jdbc:easysoft://localhost/data_source:logonuser=windows_user:logonpassword=password
    Username my_database_user

    This field is only required of the target database requires it.
    Password my_database_password
  5. Choose OK.
  6. Drag a Database > Connector > Database Reader from the Node Repository pane to the project workspace.
  7. Connect the output of the database connector to the input of the database reader.
  8. Double-click the database reader to configure it.

    The Database Reader dialog box is displayed.

  9. Type an SQL query in the SQL Statement pane, and then choose OK.
  10. Right-click the database reader. From the shortcut menu, choose Execute.
  11. Right-click the database reader. From the shortcut menu, choose Data from Database.

    The data retrieved through the Easysoft JDBC-ODBC Bridge is displayed in a database reader window.