jwills / target-duckdb

A Singer.io target for DuckDB
Other
17 stars 12 forks source link

Error when trying to load to different MotherDuck catalogs using the same schema name #25

Closed edgarrmondragon closed 1 year ago

edgarrmondragon commented 1 year ago

Describe the bug

Loading to different MotherDuck catalogs using the same schema results in an error. The same behavior can be reproduced using a local duckdb file with two or more databases attached.

To Reproduce

Setup configurations to load data to MotherDuck in two catalogs (e.g. dev and prod) using the same schema name:

{
  "path": "md:dev",
  "token": "...",
  "default_target_schema": "personal"
}
{
  "path": "md:prod",
  "token": "...",
  "default_target_schema": "personal"
}

Expected behavior

Data can be loaded in the dev.personal.<stream-id> and prod.personal.<stream-id> tables by switching configs.

Screenshots

Is a traceback ok?

$ cat lines.singer.jsonl | .meltano/loaders/target-duckdb/venv/bin/target-duckdb --config prod.config.json
Traceback (most recent call last):
  File "/Code/edgarrmondragon/meltano-dataops/.meltano/loaders/target-duckdb/venv/bin/target-duckdb", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/Code/edgarrmondragon/meltano-dataops/.meltano/loaders/target-duckdb/venv/lib/python3.11/site-packages/target_duckdb/__init__.py", line 427, in main
    persist_lines(connection, config, singer_messages)
  File "/Code/edgarrmondragon/meltano-dataops/.meltano/loaders/target-duckdb/venv/lib/python3.11/site-packages/target_duckdb/__init__.py", line 293, in persist_lines
    stream_to_sync[stream].sync_table()
  File "/Code/edgarrmondragon/meltano-dataops/.meltano/loaders/target-duckdb/venv/lib/python3.11/site-packages/target_duckdb/db_sync.py", line 613, in sync_table
    self.query(query)
  File "/Code/edgarrmondragon/meltano-dataops/.meltano/loaders/target-duckdb/venv/lib/python3.11/site-packages/target_duckdb/db_sync.py", line 311, in query
    cur.execute(query)
duckdb.CatalogException: Catalog Error: Schema with name personal does not exist!

Your environment

Additional context

I got a PR almost ready to fix this.