node-red-contrib-duckdb

Basic node red node for DuckDB.

Nodes

Database:

config database path, such as /tmp/duckdb.db. Or use :memory: Please read offical docs DuckDB Docs

Duckdb SQL Node (duckdb-sql)

Sql Option:

There are some sql execution options.

• msg.sql exec: executes the sql query(ies) from input msg.sql, and does not return any result. DuckDB exec
• sql exec: execute the sql query(ies) from code editor, and does not return any results. DuckDB exec
• sql all: execute one sql query from code editor, and returns execution results. DuckDB all
• sql each: execute one sql query from code editor, and returns row by row. DuckDB each
• PS: execute the sql procedure statement from code editor, taken msg.params as parameters. msg.params must be an array. And does not return any results. example

Code Editor:

Input SQL queries.

DuckDB Function Node (duckdb func)

This node prototyped a node-red data transform node which read from database, transform data and then insert into database. The drive behind of this idea is that I think for the personal use all the data platform exist I knew are too heavy and difficult to setup and use. Data linage would also be difficult to achive.

This node provided an javascript code editor for transforming each row by function msg.proc and insert the processed data into database, also be able to output the result. This node also provided a template for user. The template is

msg.beforeProc = "CREATE TABLE <table name>(...);"
msg.procQuery = "SELECT * FROM <prev table name>";
msg.proc = function(row) {
    // transform row from proc query
    // return insert to new table
    return "INSERT INTO <table name> VALUES(" + JSON.stringify(Object.values(row)).slice(1, -1).replaceAll('"', '\'') + ");";
}

msg.afterProc = "SELECT * FROM <table name> LIMIT 10;";

Batch input defined the batch size of the process query. The value default to 100.

msg.beforeProc defined a sql that will be executed before the process function. Usually it should create a table to which new data insert. This field is optional.

msg.procQuery defined a sql that return data which will be processed from database. It should be a SELECT and MUST NOT end with ;. The code will add the limit and offset for batch process. This field is required for get data from db.

msg.proc defined a function which input is the row returned from sql defined in msg.procQuery. Function body should transform the data into some format and then return an INSERT query. The code running on background will handle the insert.

msg.afterProc defined a query which will be executed after all the rows being processed. The result of this query will be added to msg.payload and pass to the next node(s). This field is optional.

DuckDB Import Node (duckdb import)

A node for importing csv or parquet file to duckdb. User can pass advanced sql import to msg.import as input. DuckDB Import

Database:

config database path, such as /tmp/duckdb.db. Or use :memory:. Please read offical docs duck db docs

Import Type:

• csv: load csv file from local and create table given file path and table name. DuckDB Import CSV
• parquet: load parquet file from local and create table given file path and table name. DuckDB Import Parquet
• msg.import: execute the import sql get from input msg.import . DuckDB Import SQLs

Table Name:

Input the create table name if choose csv or parquet.

File Path:

Input the csv or parquet file path.

DuckDB Export Node (dukdb export)

A node for exporting csv or parquet file to duckdb. User can pass advanced sql from msg.export as input. DuckDB Export

Database:

config database path, such as /tmp/duckdb.db. Or use :memory:. Please read offical docs duck db docs

Export Type:

• parquet: from table export parquet file to local given file path and table name. DuckDB Export Parquet
• msg.export: execute the export sql get from input msg.export . DuckDB export SQLs

Table Name:

Input the table name if choose parquet.

File Path:

Input the parquet file path.

Examples

1. All the basic sql nodes

2. DuckDB Function Node