Duckdb Queries
CertifiedExecute multiple SQL statements against DuckDB
Duckdb Queries
Execute multiple SQL statements against DuckDB
Runs multiple SQL statements in DuckDB embedded analytical database. Supports reading from CSV, Parquet, and JSON files directly. Can work with in-memory databases or persistent database files. Supports parameterized queries, transactions (default enabled), and all fetch modes. Can output the database file to internal storage.
type: io.kestra.plugin.jdbc.duckdb.QueriesExamples
Execute queries that export results to a file, which is automatically captured as an output file.
id: queries_export
namespace: company.team
tasks:
- id: queries
type: io.kestra.plugin.jdbc.duckdb.Queries
sql: |-
CREATE TABLE users (id INT, name VARCHAR);
INSERT INTO users VALUES (1, 'John'), (2, 'Jane');
COPY users TO '{{ workingDir }}/users_export.csv' (HEADER, DELIMITER ',');
Execute multiple queries that reads a csv, and outputs a select and a count.
id: queries_duckdb
namespace: company.team
tasks:
- id: http_download
type: io.kestra.plugin.core.http.Download
uri: "https://huggingface.co/datasets/kestra/datasets/raw/main/csv/orders.csv"
- id: queries
type: io.kestra.plugin.jdbc.duckdb.Queries
url: 'jdbc:duckdb:'
timeZoneId: Europe/Paris
sql: |-
CREATE TABLE new_tbl AS SELECT * FROM read_csv_auto('in.csv', header=True);
SELECT count(customer_name) FROM new_tbl;
SELECT customer_name FROM new_tbl;
inputFiles:
in.csv: "{{ outputs.http_download.uri }}"
Execute a query that reads a CSV file, and outputs another CSV file.
id: query_duckdb
namespace: company.team
tasks:
- id: http_download
type: io.kestra.plugin.core.http.Download
uri: "https://huggingface.co/datasets/kestra/datasets/raw/main/csv/orders.csv"
- id: query
type: io.kestra.plugin.jdbc.duckdb.Queries
url: 'jdbc:duckdb:'
timeZoneId: Europe/Paris
sql: |-
CREATE TABLE new_tbl AS SELECT * FROM read_csv_auto('data.csv', header=True);
COPY (SELECT order_id, customer_name FROM new_tbl) TO '{{ outputFiles.out }}' (HEADER, DELIMITER ',');
inputFiles:
data.csv: "{{ outputs.http_download.uri }}"
outputFiles:
- out
Properties
sql *string
SQL statement(s) to execute
Runs one or more SQL statements rendered with flow variables. Query tasks accept a single statement; Queries tasks can execute multiple statements separated by semicolons
afterSQL string
SQL to execute after main query in same transaction
Optional SQL executed in the same transaction after the main statement. Useful for marking rows as processed to avoid duplicates; only a single statement is allowed. Commit covers both sql and afterSQL
assets
Assets this task consumes as inputs or produces as outputs, for lineage tracking and the asset graph (Enterprise Edition). A flow declaring this property on a task is rejected in the open-source edition.
io.kestra.core.models.assets.AssetsDeclaration
IGNOREFAILWARNAsset failure behavior
Behavior applied to the task state when a declared asset fails to render, emit, or be persisted (e.g. a lock conflict): FAIL escalates it to FAILED, WARN (default) warns it if it would otherwise succeed, IGNORE leaves the state untouched.
Whether to auto-register assets referenced dynamically at runtime that are not statically declared in inputs or outputs.
The assets consumed as inputs.
io.kestra.core.models.assets.AssetIdentifier
1The assets produced as outputs.
io.kestra.plugin.ee.assets.Dataset
1150{}1150io.kestra.plugin.ee.assets.File
1150{}1150io.kestra.plugin.ee.assets.Table
1150{}1150io.kestra.plugin.ee.assets.VM
1150{}1150io.kestra.core.models.assets.External
1150{}1150io.kestra.core.models.assets.Custom
11501Custom asset type
{}1150captureOutputFiles booleanstring
trueWhether to automatically capture output files
Whether to automatically capture and upload files created in the working directory during execution. Defaults to true.
Note that files ending in .db, .wal, or .tmp, the task's database file, the .duckdb_extensions/ directory,
and pre-existing unchanged files are excluded from automatic capture.
communityExtensions array
["ion"]DuckDB community extensions to install and load before running the SQL
Defaults to ["ion"]. Each extension is attempted on a best-effort basis using INSTALL <ext> FROM community followed by LOAD <ext>. If installation or loading fails, Kestra logs a warning and continues.
connectionPoolSize integerstring
10Maximum number of pooled connections
Maximum connections held in the pool for a given URL and credentials. Default 10. Increase for flows that run many concurrent queries against the same database to avoid waiting for an available connection. Ignored when connectionPooling is false or for embedded drivers.
connectionPooling booleanstring
trueReuse database connections via a connection pool
When true (default), connections are pooled and reused across executions, keyed by URL and credentials, removing the connect and TLS-handshake cost on each run. Set to false if your SQL relies on session state persisting on the connection (for example SET search_path, session-scoped temp tables or variables), since pooled connections are reused. Embedded drivers (DuckDB, SQLite, MS Access) never pool regardless of this setting.
databaseUri string
Database URI
Kestra's URI to an existing Duck DB database file
fetchSize integerstring
10000Number of rows to fetch per database round trip
Controls JDBC fetch size for STORE mode. Default: 10,000 rows; use Integer.MIN_VALUE for MySQL streaming. Ignored for FETCH and FETCH_ONE
fetchType string
NONESTOREFETCHFETCH_ONENONEResult fetching mode
FETCH returns all rows, FETCH_ONE returns the first row only, STORE streams rows to internal storage (ION), NONE returns no data. Default: NONE
inputFiles object
Input files to be loaded from DuckDb
Describe a files map that will be written and usable by DuckDb. You can reach files by their filename, example: SELECT * FROM read_csv_auto('myfile.csv'); Relative paths are resolved by DuckDB against the Kestra process directory, not the task working directory; the working directory is only used as a fallback for reads when no file with that name exists in the process directory. If such a file exists, the task fails to avoid silently reading the wrong data. To be unambiguous, reference the file as '{{ workingDir }}/myfile.csv'.
maxCapturedBytes integerstring
104857600Maximum total size (in bytes) of output files to automatically capture
Maximum combined size in bytes of new or modified files to automatically capture and upload. If exceeded, the task will fail. Defaults to 104857600 (100 MB).
maxCapturedFiles integerstring
100Maximum number of output files to automatically capture
Maximum number of new or modified files to automatically capture and upload. If exceeded, the task will fail. Defaults to 100.
outputDbFile booleanstring
falseOutput the database file
This property lets you define if you want to output the in-memory database as a file for further processing.
parameters object
Named parameter bindings for SQL query
Map of parameter names to values. Use : name placeholders rendered then bound as prepared-statement parameters; supports nulls and typed values
password string
The database user's password
timeZoneId string
The time zone id to use for date/time manipulation. Default value is the worker's default time zone id
transaction booleanstring
trueTransaction
If one query failed, rollback transactions.
url string
jdbc:duckdb:The JDBC URL to connect to the database
The default value, jdbc: duckdb: , will use a local in-memory database.
Set this property when connecting to a persisted database instance, for example jdbc: duckdb: md: my_database?motherduck_token=<my_token> to connect to MotherDuck.
username string
The database user
Outputs
affectedRows array
The number of affected rows for each query that doesn't return a result set
One entry per INSERT, UPDATE, DELETE or DDL statement, in execution order, as reported by the JDBC driver (DDL statements usually report 0). Queries returning a result set are not included here; their results are in outputs.
outputs array
The list of per-query outputs
io.kestra.plugin.jdbc.AbstractJdbcBaseQuery-Output
First row of fetched data
Only populated when fetchType is FETCH_ONE
List of all fetched rows
Only populated when fetchType is FETCH
Number of rows fetched
Only populated when fetchType is FETCH or STORE
uriURI of stored results in internal storage
Only populated when fetchType is STORE; file is stored in internal storage using ION format
Metrics
fetch.size counter
rowsThe number of fetched rows.
