MotherDuck connection does not work

alvinwanyeki commented 1 month ago

issue summary

lea's prepare and run commands fail to work properly with MotherDuck connections, but they work with standard DuckDB connections (for the same views).

environment

lea version: 0.5.0
Database: MotherDuck (v1.1.2)
Python version: 3.12
Views: Using example view from jaffle_shop

observed behavior

schema creation (lea prepare)
- Commands appear successful but schema isn't created on md:
```
$ lea prepare
[13:30:35] Created schema staging                                     duckdb.py:55
```
$ lea prepare --production [13:30:41] Created schema staging duckdb.py:55

view creation (lea run)

Development mode fails with syntax error (same model works on DuckDB):

$ lea run
[13:30:46] 1 out of 1 views selected                                                  runner.py:74   
#  view                  status   duration  cost
1  staging.raw_payments  ERRORED        0s
      Took 0s                                                                    runner.py:74
┏━━━━━━━━━┳━━━━━━━┓
┃ status  ┃ count ┃
┡━━━━━━━━━╇━━━━━━━┩
│ ERRORED │ 1     │
└─────────┴───────┘
staging.raw_payments
Parser Error: syntax error at or near ":"

Production mode reports success but no view is created on md:

$ lea run --production
[13:30:55] 1 out of 1 views selected                                                  runner.py:74
#  view                  status   duration  cost
1  staging.raw_payments  SUCCESS        0s
      Took 0s                                                                    runner.py:74
┏━━━━━━━━━┳━━━━━━━┓
┃ status  ┃ count ┃
┡━━━━━━━━━╇━━━━━━━┩
│ SUCCESS │ 1     │
└─────────┴───────┘

Expected Behavior

Schema and views should be created in MotherDuck as indicated by the command output

additional information

I've set up my env as such:
```
LEA_USERNAME=<my_name>
```

LEA_WAREHOUSE=duckdb LEA_DUCKDB_PATH=md: MOTHERDUCK_TOKEN=

MaxHalford commented 1 month ago

Thanks for the report, I will take a look. To be honest I was waiting for someone to give it a spin, as I haven't had the time to try it out properly yet.

alvinwanyeki commented 1 month ago

Thanks @MaxHalford -- Happy to help with testing, looking forward to adopting lea!

alvinwanyeki commented 1 month ago

@MaxHalford -- i've been able to fix this issue; is there anything i need to do/know before i open a pr? thank you!

MaxHalford commented 1 month ago

Good job!

Well actually, it's a bit of a surprise, but I am completely rewriting the library to make it better. The interface will be the same, but the internals will be the same. I suggest that you make the pull request, and I'll take of integrating it. I hope that's ok 👍

alvinwanyeki commented 4 weeks ago

thank you; this is okay! i'll open the pr later today 👍

interface will be the same, but the internals will be the same.

on the rewrite, to confirm, did you mean the interface will be the same BUT the internals will be different? also, i'm happy to help with testing the duckdb/motherduck connectors :)

MaxHalford commented 4 weeks ago

on the rewrite, to confirm, did you mean the interface will be the same BUT the internals will be different? also, i'm happy to help with testing the duckdb/motherduck connectors :)

Yes, my bad :)

alvinwanyeki commented 3 weeks ago

@MaxHalford - just curious, on rewriting lea, will you change how 'non-root' .py views are currently identified, i noticed that the current implementation seems limited to pandas, so using something like duckdb.sql() or pl.read_database() in 'non-root' views won't work.

carbonfact / lea