Icon

01.2 Database Extension and In-Spark Processing

The company tracks the usage of the website and stores the information about each session.

- Various data are collected, e.g., session start, duration, # clicks, etc., as well as the session satisfaction score (optional)
- The company calculates averaged statistics for each customer, e.g., total # visits, average satisfaction, etc., and updates the "statistics" table in different locations
- Session satisfaction score column has missing values which need to be imputed, e.g., with machine learning predictions.

We access the usage data from Hive and personal data (anonymized & updated in sessions 1 & 2) and contracts data from a database. We perform in-database processing, read the data into Spark, enrich the usage data with the personal and contract data to predict missing values better, aggregate usage data on Spark, and save the aggregated data.

Database Extension (Link to the Documentation)


Nodes to perform Simple DB Operations


Insert, Load, Merge, Update



1️⃣ DB Insert

  • What it does: Adds new rows into a selected database table.

  • Use when: You want to append fresh data from your KNIME input table to an existing database table.

2️⃣ DB Loader

  • What it does: Bulk-loads large datasets from external files (CSV, JSON, Parquet) directly into a database.

  • Use when: You need fast loading of large volumes of data (database-specific bulk import).

3️⃣ DB Merge

  • What it does: Combines INSERT and UPDATE operations — if a record exists, it’s updated; if not, it’s inserted.

  • Use when: You want to synchronize or upsert data between KNIME and a database.

4️⃣ DB Update

  • What it does: Updates existing rows in a database table that match specific conditions from your KNIME input table.

  • Use when: You need to modify existing data values in place without inserting new rows.

customers
DB Table Selector
contracts
DB Table Selector
Connect tothe database
SQLite Connector
Merges (insert/updates) all data rows from the selected database table that match the values of a row from the selected KNIME input table columns.
DB Merge
Import into KNIME
DB Reader
Updates all data rows from the selected database table that match the values of a row from the selected KNIME input table columns.
DB Update
Aggregate # contracts & total value for each customer
DB GroupBy
Add aggregated contracts data to customers
DB Joiner
Inserts Rows
DB Writer
Create a Spark Context
Create Local Big Data Environment
This node uses database specific bulk loading functionality that only some databases
DB Loader
This node concatenates two or more database tables.
DB Concatenate
Inserts data rows into the selected database table based on the values from the selected KNIME input table columns.
DB Insert
Import into Spark DataFrame
DB to Spark
This node sorts the DB Data rows according to user-defined criteria.
DB Sorter
Read New Table
DB Table Selector
This node allows columns to be filtered from the input DB Data object
DB Column Filter
DB Reader
DB Table Creator
Convert Age column to double and remove irrelevant columns
DB Query
Write and perform an SQL Statement
DB Query
Adjust data typesand rename aggregatedcolumns
Spark SQL Query
Rename database column names.
DB Column Renamer

Nodes

Extensions

Links