search

duckdb / dbt-duckdb

dbt (http://getdbt.com) adapter for DuckDB (http://duckdb.org)
Apache License 2.0
880 stars 78 forks source link

Cannot connect to duckdb file using relative path #358

Closed vbalalian closed 6 months ago

vbalalian commented 6 months ago

I'm setting up a target profile in profiles.yml, trying to connect to an existing duckdb database in another folder using a relative path, but dbt is unable to connect using a relative path.

It's able to connect when I use the absolute path, as in: my_project: target: dev outputs: dev: type: duckdb path: '/home/user/my_project_repo/duckdb/data/data.duckdb"

But when I try to set a relative path (i.e. './duckdb/data/data.duckdb"), which is necessary for my use case, dbt only looks for paths in the pwd. Supposedly relative paths are supported, at least in the context of attaching additional databases, but I need to be able to connect to a database outside the project folder (isn't that the point of using dbt?). Hopefully this is a simple mistake on my part, and the functionality is there. Happy to be corrected.

I'm getting this error: "dbt was unable to connect to the specified database. The database returned the following error:

Runtime Error IO Error: Cannot open file "/home/user/my_project_repo/dbt/my_project/.duckdb/data/data.duckdb": No such file or directory"

and this error when not using a relative path or an absolute path:

"dbt was unable to connect to the specified database. The database returned the following error:

Runtime Error IO Error: Cannot open file "/my_project_repo/duckdb/data/data.duckdb": No such file or directory"

jwills commented 6 months ago

Mmm it's a little tough to grok what's going on here, but if your dbt project is here:

/home/user/my_project_repo/dbt/

...and your duckdb database is here: /home/user/my_project_repo/duckdb/data/data.duckdb

...then the relative path to the DuckDB database is: ../duckdb/data/data.duckdb, not ./duckdb/data/data.duckdb (two periods in the front, not one.)

vbalalian commented 6 months ago

Yes I tried that as well, but got the same error, only with two periods instead of one.

jwills commented 6 months ago

huh; how/where are you running dbt at the cli? From /home/user/my_project_repo/? Or from /home/user/my_project_repo/dbt/?

vbalalian commented 6 months ago

From /home/user/my_project_repo/dbt/ since that's where dbt_project.yml is located

jwills commented 6 months ago

Are you using a profiles.yml file in the dbt project directory, or the default one at ~/.dbt/profiles.yml?

vbalalian commented 6 months ago

Yeah profiles.yml is in the same dbt project directory

jwills commented 6 months ago

huh okay then, I'm stumped here-- I have the same setup here and my relative paths work fine. Not sure what else could be going on.

vbalalian commented 6 months ago

@jwills I figured out what my issue was, and it was a silly mistake on my part. The folder containing my duckdb file was two folders up, and I mistakenly thought I was using the relative path correctly. What I needed to do was add a second "../" to my relative path ("../../duckdb/data/data.duckdb"). Rookie mistake, I know! Thanks again for your help!