Allow relative path to duckdb

[ReneTC]

Currently the duckdb file needs to be in the same path as evidence.
Can we make it possible to point at a relative path?

[archiewood]

@ReneTC where would you like to keep yours specifically?

(Good workarounds in the meantime include for example, a CI step that copies it from the current location to the root)

[ReneTC]

Thanks @archiewood - I use evidence with meltano, so the project is running inside a meltano project. My data is outside of the meltano project.

It's a good suggestion with a CI copy for now. However the database might grow really big

[ReneTC]

Any thoughts? Is it something that you find useful?

[archiewood]

This is a good idea, yes. We have some upcoming changes in the next branch that may address this. (I am not 100% sure).

Otherwise I'll leave this open to track the issue

[matsonj]

Just adding to this - in the mdsinabox.com project, I am using duckdb with "external materializations" which are parquet files. When I run npm run dev etc, the path does not resolve correctly and the files cannot be found. The current workaround is building the entire env in meltano, which resolves the pathing issues.

[ReneTC]

Can you elbaorate matsonj? how Meltano solves the pathing issues :D? I am asking becease the Meltano utility https://hub.meltano.com/utilities/evidence/ says:
EVIDENCE_SETTINGS_DUCKDB_FILENAME: Name of the DuckDB database file stored in the same directory as your Evidence project.

So I am not sure how meltano would solve the issue.

[matsonj]

Sure! I’m sharing my config from https://github.com/matsonj/nba-monte-carlo

  - name: evidence
    variant: meltanolabs
    pip_url: evidence-ext>=0.5
    config:
      home_dir: $MELTANO_PROJECT_ROOT/evidence
      settings:
        database: duckdb
        duckdb:
          filename: ../data/data_catalog/mdsbox.db

Since it remaps everything to the root and build its own environments, it handles the relative pathing by keeping everything its own instance for evidence (installed via meltano)

[matsonj]

@ReneTC oh I’m realizing that the documentation is wrong in meltano util! Good news, I’m one of the maintainers 😆

[ReneTC]

Thank you very much, I appreciate your help. It's up to the evidence developers if this ticket should be closed or not. The meltano tip solved my issue.

…

On Mon, 23 Oct 2023, 20.30 Jacob Matson, ***@***.***> wrote: @ReneTC <https://github.com/ReneTC> oh I’m realizing that the documentation is wrong in meltano util! Good news, I’m one of the maintainers 😆 — Reply to this email directly, view it on GitHub <#1220 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AJSZI4NJDHOJXQRT44HUOODYA2ZSZAVCNFSM6AAAAAA5MDF6NOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTONZVG43TQMRSHE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[archiewood]

We'll keep this open, I think it's not full solved.

However, I want to add another workaround that can work here that I tested today.

If you edit the credentials file inside .evidence/template/evidence.settings.json, rather than the UI, you can use a relative path.

However, you need to map the path relative to this .evidence/template subfolder (rather than the root of the evidence project), as this subfolder is where the process actually runs.

For example, if you stored your duckdb inside ./a-folder/database.duckdb then you would need to use the path ../../a-folder/database.duckdb

Similarly, this is the path that will be copied when you set the environment variables also.

[archiewood]

This can now be configured in the connection.yaml for your DuckDB file.

The filepath is relative to the directory where the connection.yaml file is stored.

For example you can use something like this:

connection.yaml

# This file was automatically generated
name: needful_things
type: duckdb
options:
  filename: ../../../../../../Users/archie/Downloads/needful_things.duckdb