KNIME

KNIME Analytics Platform is "the free, open-source software for creating data science." KNIME provides a drag-and-drop desktop GUI for connecting data sources to workflows that can manipulate, analyze, and visualize data. KNIME can be combined with bit.io as a data warehouse to provide convenient, secure, and sharable storage.

Connecting to bit.io with KNIME

In order to connect KNIME with bit.io, you'll need to find your PosgreSQL connection credentials from the bit.io Connect Tab of the database to which you would like to connect. You can find everything you need to know about your PostgreSQL credentials here.

Then, you will need to add the PostgreSQL Connector Node to your workflow and specify the connection credentials and JDBC Parameters as shown below with an example database "knime_demo/dataset" and username "knime_demo". bit.io requires an encrypted database connection, so ensure that you add the JDBC Parameter "sslmode" with the value "require".

🚧

SSL Required

bit.io requires an encrypted database connection, so ensure that you add the JDBC Parameter "sslmode" with the value "require".

🚧

Automatic reconnection

bit.io automatically closes idle connections after a timeout period is exceeded (currently 60 seconds). You can configure KNIME to handle reconnecting automatically by selecting "Automatically reconnect to database" in the Advanced configuration tab.

Using KNIME with bit.io

Once we've set up our connection, we can build workflows using bit.io as a data source. For our example, we will adapt the KNIME "Getting Started" example workflow to use a bit.io database in place of a local csv file. Here is schematic of the example with a local csv file:

After completing the getting started guide on the KNIME website, you can upload the csv file provided by KNIME to a bit.io database. Click "Upload Data" at the top of your bit.io database page and then drag-and-drop the csv file to create a table in your database.

Then, delete the "File Reader" node and add "DB Table Selector" and "DB Reader" nodes as shown below.

The "DB Table Selector" configuration is shown below. You can also use a "DB Query" node to specify custom SQL queries, including table joins and filters. See the KNIME docs for more details of working with database connections in KNIME.

From there, you are ready to run the workflow and view the example visualizations. Here is a screenshot of the resulting pie chart.

👍

Next steps

You now know the basics of connecting bit.io to a KNIME workflow. You can also use KNIME to write processed data into bit.io. See the KNIME docs for information about Nodes that write data back to databases and other more advanced features.