Mon, 01 Aug 2016
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.
- 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:
- Download the Easysoft JDBC-ODBC Bridge. (Registration required.)
- 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.
- In KNIME Analytics Platform, choose File > Preferences.
The Preferences dialog box is displayed.
- In the left pane, choose KNIME > Databases.
- In the right pane, choose Add file.
The Open dialog box is displayed.
- 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:
On 64-bit Windows, the default location for <easysoft_install> is <drive>:\Program Files (x86)\Easysoft Limited\Easysoft JDBC-ODBC Bridge.
- 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.
- Create a new KNIME workflow.
- Drag a Database > Connector > Database Connector from the Node Repository pane to the project workspace.
- Double-click the new connector to configure it.
The Database Connector dialog box is displayed.
- 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.
- Choose OK.
- Drag a Database > Connector > Database Reader from the Node Repository pane to the project workspace.
- Connect the output of the database connector to the input of the database reader.
- Double-click the database reader to configure it.
The Database Reader dialog box is displayed.
- Type an SQL query in the SQL Statement pane, and then choose OK.
- Right-click the database reader. From the shortcut menu, choose Execute.
- 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.