Basic node red node for DuckDB.
config database path, such as /tmp/duckdb.db
. Or use :memory:
Please read offical docs DuckDB Docs
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
Input SQL queries.
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.
A node for importing csv or parquet file to duckdb. User can pass advanced sql import to msg.import as input. DuckDB Import
config database path, such as /tmp/duckdb.db
. Or use :memory:
. Please read offical docs duck db docs
- 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
Input the create table name if choose csv or parquet.
Input the csv or parquet file path.
A node for exporting csv or parquet file to duckdb. User can pass advanced sql from msg.export as input. DuckDB Export
config database path, such as /tmp/duckdb.db
. Or use :memory:
. Please read offical docs duck db docs
- 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
Input the table name if choose parquet.
Input the parquet file path.