Hands-on database tasks in KNIME
1. Hands-on database tasks in KNIME
In this video, we will explore how to manage data directly in databases using KNIME. We will cover connecting to a database, selecting tables, filtering rows, merging tables, and writing data back to the database We will start by connecting to a database. In this case, we’re working with a customer and transactions table in the database. In this example, we want to connect to an existing database. Instead of configuring it with the database credentials, we simply drag and drop the SQLite database from the data folder onto the workflow canvas. Once connected, use the DB Table Selector node to choose the "customers" table. This lets us pull the needed information from the database, avoiding loading unnecessary data into KNIME. Now that we have imported our data, let’s filter this table to focus only on active customers. Add the DB Row Filter node and connect it to the workflow. In the configuration, set a filter on the "status" column to include only rows where the value is "active." This keeps our data relevant and reduces the rows sent to KNIME. We have now table with only active customers. Suppose we want to enrich this customer data with purchase information. We need to read another table. So, we add another DB Table Selector node as a second workstream. Select the “transactions” table this time. Add the DB Joiner node to merge the "customers" table with the "transactions" table. In the configuration, set "id" and “customer” as the matching criterion for joining. This will give a unified view of each customer's details and purchase history. We only want to include the "purchase_quantity" and "purchase_date" columns, so we select only those in the DB Joiner node configuration. All tasks on database level are now done. It's time to import it into KNIME for advanced analysis. Add the DB Reader node to the workflow and connect it to the output of the Joiner. Execute the node to bring the enriched dataset into KNIME as a local table. Now, you can manipulate or visualize the data within the platform. As a last step, let’s write our processed dataset into a new database table for future use. Add the DB Writer node. Connect the dataset and the database connection. Configure it to create a new table, "active_customers_with_purchases." This ensures that the updated data is available for other teams or workflows. This video shows how to use KNIME to connect to a database, filter data, perform joins, and write data back to the database. No need to learn SQL or any other programming language. These steps let you handle large datasets efficiently. They will make your workflows faster and more streamlined.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.