vdsql: Don't know how to connect to .ddb (DuckDB)

[fleimgruber]

Small description
vdsql is not able to open a .ddb file

Expected result
vdsql is able to open a .ddb file.

Actual result with screenshot

File "C:\Users\LeimgruberF\lg\.venv\lib\site-packages\visidata\threads.py", line 220, in _toplevelTryFunc
t.status = func(*args, **kwargs)
File "C:\Users\LeimgruberF\lg\.venv\lib\site-packages\visidata\sheets.py", line 260, in reload
self.loader()
File "C:\Users\LeimgruberF\lg\.venv\lib\site-packages\visidata\sheets.py", line 285, in loader
for r in self.iterload():
File "C:\Users\LeimgruberF\lg\.venv\lib\site-packages\visidata\apps\vdsql\_ibis.py", line 123, in iterload
with self.con as con:
File "C:\Users\LeimgruberF\.pyenv\pyenv-win\versions\3.10.11\lib\contextlib.py", line 135, in __enter__
return next(self.gen)
File "C:\Users\LeimgruberF\lg\.venv\lib\site-packages\visidata\apps\vdsql\_ibis.py", line 93, in get_conn
r = ibis.connect(str(self.source))
File "C:\Users\LeimgruberF\lg\.venv\lib\site-packages\ibis\backends\base\__init__.py", line 1353, in connect
raise ValueError(f"Don't know how to connect to {resource!r}")
ValueError: Don't know how to connect to 'test.ddb'

Steps to reproduce with sample data and a .vd
Sample data:

import duckdb

con = duckdb.connect("test.ddb")
con.sql("CREATE TABLE test (i INTEGER)")
con.sql("INSERT INTO test VALUES (42)")
con.table("test").show()
con.close()

command:

visidata -f vdsql test.ddb

commandlog:

#!vd -p
{"sheet": "global", "col": null, "row": "filetype", "longname": "set-option", "input": "vdsql", "keystrokes": "", "comment": null}
{"longname": "open-file", "input": "test.ddb", "keystrokes": "o"}

Additional context
Python 3.10.11
VisiData 96d2702

[midichef]

It appears that the ibis library expects a DuckDB file to have the extension .duckdb. I do not think there's any way to get it to read a file ending in .ddb, without submitting a change to ibis-project.

After renaming the .ddb file, vd -f vdsql test.duckdb worked for me.

[saulpw]

I thought this commit enabled this: a94cdff. I wonder what happened.

[midichef]

I'm not sure what changed. But ibis will take a duckdb:// URL that takes a relative or absolute path. So with a loader something like this:

@VisiData.api
def openurl_duckdb(vd, p, filetype=None):
    return vd.open_vdsql(p, filetype)

the .ddb file can be loaded with vd -f vdsql duckdb://test.ddb. Would that be enough?
(EDIT: vd duckdb://test.ddb works and is shorter.)

[fleimgruber]

vd -f vdsql test.duckdb

Works for me as well, thanks.

vd -f vdsql duckdb://test.ddb

I am not using explicit loaders myself, but that could be nice to have. I think with a94cdff working it would be a better user experience in any case.

[reagle]

Neither work for me.

❯ vd -f vdsql duckdb://test.ddb
saul.pw/VisiData v3.0.2
no loader for url scheme: duckdb

[midichef]

@reagle
Opening a duckdb:// URL can only work if you add the openurl_duckdb() code above to your .visidatarc. It's not clear from context whether you had that code in your .visidatarc when you ran it. Does the failure happen with that code added?

The other method is to open a file ending in .duckdb, by adding -f vdsql:
vd -f vdsql test.duckdb
However, this method won't work for files ending in .ddb, only .duckdb. If your file had the right extension, and still couldn't be loaded, please let me know what output visidata gave when you ran it with -f vdsql.

[reagle]

Hi @midichef, see https://asciinema.org/a/b8MNhZNnGY854lBDhGeJ1zl08 .

[midichef]

I see you got the message unknown "vdsql" filetype, and it doesn't work. I get that too when I install vdsql with pip install vdsql.

But the .duckdb file will open if I follow the alternate vdsql install instructions from the vdsql README.md:

git clone git@github.com:saulpw/visidata.git
cd visidata/visidata/apps/vdsql
pip3 install .

Does it work for you if you do that, and then run vd -f vdsql test.duckdb?