Icon

Reading and Processing CSV via DuckDB

<p>This workflow demonstrates using DuckDB in KNIME to process large CSV files. DuckDB provides measurable performance gains for CSV parsing and in-database aggregation. Parameter limits must be set carefully, as DuckDB consumes additional main memory and CPU. Always close the DuckDB connection to release memory.</p>

Runtime comparison
KNIME vs. DuckDB (via KNIME)

This workflow demonstrates using DuckDB in KNIME to process large CSV files. DuckDB provides measurable performance gains for CSV parsing and in-database aggregation. Parameter limits must be set carefully, as DuckDB consumes additional main memory and CPU. Always close the DuckDB connection to release memory.


KNIME Configuration

Register the DuckDB jdbc driver in the KNIME Preferences, then update the configuration the DB Connector and the set up the file paths in the File Reader and DB Table Selector node.

Test Setup

Use a large, well-formed CSV file and configure both KNIME and DuckDB nodes to read it. The workflow is preconfigured for the dataset below.

Data Set: Motor Vehicle Collisions - Crashes

Data Description, license etc: data.gov

Download Link: https://data.cityofnewyork.us/api/views/h9gi-nx95/rows.csv?accessType=DOWNLOAD

Data Size: 2.2M rows x 29 cols; 440 MB file uncompressed CSV

  • KNIME File Reader: 3.7s

  • KNIME GroupBy: 1.7s

  • DuckDB (all together): 1.3s




Data Set: NY City Taxi Trip Record Data, 2015

(not pre-configured here)

Data Description, download (as parquet), license: nyc.gov

Data Size 146M rows x 19 cols; 13GB aggregated flat CSV

  • File Reader KNIME: 90s

  • GroupBy (with sorting): 115s (60s in-memory)

  • DuckDB (read + group): <40s

DB Connector
DB Table Selector
DuckDB params
DB SQL Executor
Timer Info
CSV file locaton + params
DB SQL Executor
DB GroupBy
File Reader
GroupBy
!! DO NOT FORGET TO CLOSE THE SESSION !!
DB Connection Closer
DB Reader

Nodes

Extensions

Links