KNIME Documentation

Write a transformed dataset to a database ​

This tutorial shows how to connect to a database, aggregate and join data entirely in-database, write the result to a new table, and verify the output.

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 MySQL)
  • Database credentials (username and password)

Steps ​

1. Connect to the database ​

Add a MySQL Connector node to your workflow (or use the PostgreSQL Connector or generic DB Connector for other databases). Click the node once to open its configuration, then enter the Hostname, Port, and Database name. Under Authentication, provide your Username and Password. Execute the node to open the connection.

2. Select the source table ​

Connect a DB Table Selector node to the Connector node. Click the node once to open its configuration, then click Browse to view the available schemas and tables and select your source data table.

3. Group rows in the database ​

Connect a DB GroupBy node to the DB Table Selector. In the Groups tab, add the columns you want to group by (for example, Category or Region). In the Manual Aggregation tab, select the numerical columns to aggregate and choose an aggregation method (for example, SUM, AVG, or MAX).

4. Join the grouped result ​

Connect a DB Joiner node. Connect the DB GroupBy output to the bottom input port and the original DB Table Selector output to the top input port. Click the node once to open its configuration. Under Matching Criteria, click + Add matching criterion and select the matching column under Top input ('left' table) and Bottom input ('right' table) (for example, Region on both sides). Under Output Columns, use Manual, Pattern, or Type selection to include or exclude specific columns from the final combined dataset.

5. Write the result to a new table ​

Connect a DB Writer node to the DB Joiner output. Click the node once to open its configuration, then type a name for your output table in the Table name field. Under If table exists in database, choose Fail to stop execution if the name is already taken, or Remove existing table and contained data to overwrite it.

INFO

Executing the DB Writer node sends the compiled SQL script to your database server. The database performs the grouping and joining on its own engine and writes the final table.

6. Verify the output ​

Connect a DB Reader node to the DB Writer output and execute it. The node reads the result back into KNIME Analytics Platform so you can inspect the written table.

An example workflow is available on KNIME Hub.