KNIME Documentation

Read and filter data from a database ​

This tutorial shows how to connect to a database, select a table, filter its rows in-database, and read the result into KNIME Analytics Platform.

What you need ​

  • KNIME Analytics Platform. All database nodes are included in every installation, so no additional extension is required.
  • A JDBC-compliant database (this tutorial uses H2)
  • Database credentials (username and password), if required by your database

Steps ​

1. Connect to the database ​

Add an H2 Connector node to your workflow. Click the node once to open its configuration, select the database dialect and driver, and specify the location of the database file. To create a temporary database stored in memory, choose In-memory if the database supports it. Execute the node to open the connection.

2. Select a table ​

Connect a DB Table Selector node to the Connector node. Click the node once to open its configuration, then click Browse to choose a table or view from the database.

To write your own SQL query instead, enable the Custom Query checkbox. Any SELECT statement is accepted. The placeholder #table# refers to the table selected via the browser.

3. Filter rows in the database ​

Connect a DB Row Filter node to the DB Table Selector. Click the node once to open its configuration, then add your filter conditions. No data leaves the database at this stage — the node builds a SQL WHERE clause from the conditions you define.

4. Read the result ​

Connect a DB Reader node to the DB Row Filter. Execute the node. It runs the input SQL query in the database and loads the data into KNIME as a KNIME data table.

An example workflow is available on KNIME Hub.