search

malloydata / malloy

Malloy is an experimental language for describing data relationships and transformations.
http://www.malloydata.dev
MIT License
1.99k stars 76 forks source link

Can't connect to DuckDB database file #1059

Closed kylelundstedt closed 8 months ago

kylelundstedt commented 1 year ago

I'm not sure if this issue is covered by #557, so I'll raise it separately.

Malloy leverages DuckDB wonderfully for reading CSV and Parquet files. However, we'd really like to be able to point Malloy at tables stored in a persistent DuckDB database stored locally as a file on disk.

Is that currently possible, and I just don't understand the DuckDB connection string or file path? If so, could I request an example to follow? If not, I'd like to make a feature request.

As a recovering LookML'er, it's been delightful to discover Malloy. As an analyst, the language really lets me focus on the business problem, rather than my own SQL inadequacies. Cheers!

bporterfield commented 1 year ago

Sorry, it's currently not possible - some complications around VSCode and opening the database file across tabs/files, if I recall. We have intended to resolve this for a while and will raise it in priority.

kylelundstedt commented 1 year ago

@bporterfield, I also use this DuckDB VS Code extension that allows me to open and use DuckDB database files. Maybe it illustrates some ways to resolve this issue more easily?

bporterfield commented 1 year ago

This jogs my memory - I think prior this flag was not functional in the DuckDB npm package. @whscullin I think I recall you making this work, can we access DuckDB databases now?

whscullin commented 1 year ago

The native duckdb format is problematic to support because it's not stable - right now every minor release breaks the previous versions, frequently in the form of segfaults. They haven't committed to backwards compatibility until 1.0. So while we could make it possible to reference a db file in the connection config, say, it would introduce a lot of potential friction to our ability to update duckdb.

jwhitaker-gridcog commented 8 months ago

tangential, so I'm not sure if this should be a new issue or not, but the use case is the same... I'm trying to work with data in an existing duckdb database.

I understand all the issues around minor version incompatibility - DuckDB recommend to work around this using import database.

Am I able to do this with malloy? I tried a very naive

run: duckdb.sql("import database 'export'")

where export/ is the result of me doing export database with duckdb directly, but got an invalid sql error (it tried to do describe select * from (import database 'export')).

lloydtabb commented 8 months ago

You should be able to set the database in the DuckDb connection now. Please give that a try.

On Tue, Mar 5, 2024, 2:53 PM Jarrad @.***> wrote:

tangential, so I'm not sure if this should be a new issue or not, but the use case is the same... I'm trying to work with data in an existing duckdb database.

I understand all the issues around minor version incompatibility - DuckDB recommend to work around this using import database.

Am I able to do this with malloy? I tried a very naive

run: duckdb.sql("import database 'export')

but got an invalid sql error (it tried to do describe select * from (import database 'export')).

— Reply to this email directly, view it on GitHub https://github.com/malloydata/malloy/issues/1059#issuecomment-1979773015, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAIK6UWYXH5PYPHTXCMJRRTYWZEGDAVCNFSM6AAAAAAVZS22MKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNZZG43TGMBRGU . You are receiving this because you are subscribed to this thread.Message ID: @.***>