​Query

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

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

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

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