Duckdb Query

Duckdb Query

Certified

Execute a single SQL query against DuckDB

Runs one SQL statement 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 with afterSQL, and all fetch modes. Can output the database file to internal storage.

yaml
type: io.kestra.plugin.jdbc.duckdb.Query

Query multiple CSV files from a ZIP file, include the filename in the result, and output both the final dataset in ION format and the DuckDB database file.

yaml
id: query_multiple_csv_files
namespace: company.team

tasks:
  - id: get_zip_file
    type: io.kestra.plugin.core.http.Download
    uri: https://huggingface.co/datasets/kestra/datasets/resolve/main/zip/2023-01.zip

  - id: unzip
    type: io.kestra.plugin.compress.ArchiveDecompress
    algorithm: ZIP
    from: "{{outputs.get_zip_file.uri}}"

  - id: duckdb
    type: io.kestra.plugin.jdbc.duckdb.Query
    inputFiles: "{{outputs.unzip.files}}"
    sql: SELECT * FROM read_csv_auto('**/*-outcomes.csv', union_by_name=true, filename=true);
    store: true  # output data in ION format
    outputDbFile: true  # output the DuckDB database file

Execute a query that reads from an existing database file using a URL.

yaml
id: query_duckdb
namespace: company.team

inputs:
  - id: my_db
    type: FILE

tasks:
  - id: query1
    type: io.kestra.plugin.jdbc.duckdb.Query
    databaseUri: "{{ inputs.my_db }}"
    sql: SELECT * FROM table_name;
    fetchType: STORE

Read an Ion file directly with DuckDB. Kestra uses Ion as an intermediate storage format, and the bundled Ion extension lets DuckDB read it directly.

yaml
id: read_ion_file
namespace: company.team

tasks:
  - id: read
    type: io.kestra.plugin.jdbc.duckdb.Query
    inputFiles:
      sample.ion: |
        {a: 1, b: "x"}
    sql: |
      SELECT a, b
      FROM read_ion('sample.ion');
    fetchType: FETCH_ONE

Run a SQL query with DuckDB on MotherDuck and get the result as a CSV file

yaml
id: motherduck
namespace: company.team

tasks:
  - id: query
    type: io.kestra.plugin.jdbc.duckdb.Query
    sql: |
      SELECT by, COUNT(*) as nr_comments
      FROM sample_data.hn.hacker_news
      GROUP BY by
      ORDER BY nr_comments DESC;
    fetchType: STORE

  - id: csv
    type: io.kestra.plugin.serdes.csv.IonToCsv
    from: "{{ outputs.query.uri }}"

pluginDefaults:
  - type: io.kestra.plugin.jdbc.duckdb.Query
    values:
      url: jdbc:duckdb:md:my_db?motherduck_token={{ secret('MOTHERDUCK_TOKEN') }}
      timeZoneId: Europe/Berlin
Properties

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

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

SubTypestring
Default["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.

Default10

Maximum 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.

Defaulttrue

Reuse 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.

Database URI

Kestra's URI to an existing Duck DB database file

Default10000

Number 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

DefaultNONE
Possible Values
STOREFETCHFETCH_ONENONE

Result 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

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');

Defaultfalse

Whether to store or not the database file in the internal storage

SubTypestring

Output file names to capture after SQL execution

Creates named temporary files in the task working directory before the SQL runs, making their absolute paths available as {{ outputFiles.name }} Pebble variables in the SQL template. Only supported by embedded, in-process drivers (DuckDB, SQLite) where the database engine writes to the same filesystem as the Kestra worker. Remote database drivers (Postgres, MySQL, etc.) do not support this — they execute SQL on a separate server that cannot write to the Kestra worker filesystem.

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

The database user's password

Reference (ref) of the pluginDefaults to apply to this task.

The time zone id to use for date/time manipulation. Default value is the worker's default time zone id

Defaultjdbc: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.

The database user

First row of fetched data

Only populated when fetchType is FETCH_ONE

SubTypeobject

List of all fetched rows

Only populated when fetchType is FETCH

Number of rows fetched

Only populated when fetchType is FETCH or STORE

Formaturi

URI of stored results in internal storage

Only populated when fetchType is STORE; file is stored in internal storage using ION format

Unitrows

The number of fetched rows.

More Plugins in this Category

Liquibase
Liquibase

Tasks that run Liquibase change management commands.

Data

2 tasks

PostgreSQL
PostgreSQL

Tasks that connect to PostgreSQL via JDBC to run queries and loads.

Data

6 tasks

25 blueprints