Duckdb dynamic database

[Vinceveve]

Describe the issue

I have an issue with duckdb if i use the sample from the doc I get and error >io.kestra.plugin.jdbc.duckdb.Query["databaseFile"]: Unrecognized field "databaseFile"

If I use a variable (or input) on the url, I get The database file path is not valid (Path to database file must be absolute)

Example task :

variables: 
  db: /temp/occ/rodez/set_parent_station.db
tasks: 
  - id: create_stop_table
    type: io.kestra.plugin.jdbc.duckdb.Query
    url: "jdbc:duckdb:{{ vars.db }}"

Environment

• Kestra Version: 0.19.10

[Ben8t]

Indeed there's something wrong in the documentation about the databaseFile, as it's not a proper property.

FYI in the meantime, we have recently fixed an old DuckDB issue: now Kestra use the latest version 1.1.3. The following flow works:

id: duckdb_fil
namespace: kestra.test

tasks:  

  - id: db
    type: io.kestra.plugin.jdbc.duckdb.Queries
    url: jdbc:duckdb:/test_db/data.db  # this file is mounted on my local computer
    sql: |
      CREATE TABLE IF NOT EXISTS t1 (i INTEGER, j INTEGER);
      INSERT INTO t1 VALUES (1, 2);
      SELECT * FROM t1;
    store: true


  - id: db2
    type: io.kestra.plugin.jdbc.duckdb.Query
    url: jdbc:duckdb:/test_db/data.db
    sql: |
      SELECT * FROM t1;
    store: true

@mgabelle can you check why the databaseFile prop isn't accessible from a user pov ? Can you ping me when you have an idea please? We might want to reevulate the overlap between using url: jdbc:duckdb:/test_db/data.db and the databaseFile (which seems the same to me 🤔 )

[Vinceveve]

url does not support dynamic properties (ie : jdbc:duckdb:{{ inputs.mydb}}).
databaseFile might be here to support this dynamic database property (really useful)

[mgabelle]

Just checked and the url property should be dynamic.
I don't know the goal of databaseFile and don't really know why it's not accessible from a user pov

[Vinceveve]

@mgabelle you're right it should be dynamic, but it's not

  - id: agency_table_exists
    type: io.kestra.plugin.jdbc.duckdb.Query
    url: "jdbc:duckdb:/var/otp/db/agency-{{ inputs.dataset_id }}.db"
    fetchType: FETCH_ONE
    sql: | #sql
      SELECT count(*) as exist FROM duckdb_tables where table_name = '{{ outputs.table.value }}';

[Vinceveve]

@mgabelle currently 0.20.10, tried it with 0.18.x and 0.19.x