Are there any plans to support DuckDB (https://duckdb.org) or workarounds folks might have to query a duckdb database using metabase?
Hi @littlegoblin
We would depend on the community to create a driver. Since there's a JDBC driver, then it should be possible to create a driver for Metabase with a little work:
https://www.metabase.com/docs/latest/developers-guide/drivers/start.html
@littlegoblin - did you do any further investigation or possibly create a DuckDB driver?
I am super interested in seeing is DuckDB could help me reduce some of my cloud costs (Postgres Server & Storage).
@ChrisH Someone has created a DuckDB driver: https://github.com//AlexR2D2/metabase_duckdb_driver
https://www.metabase.com/docs/latest/developers-guide/partner-and-community-drivers#community-drivers
1 Like
Super - I'll check it out!
I'm trying to use the DuckDB driver, but running into issues.
I'm running in a linux docker container.
I've tried with a single duckdb file, and I've tried with no file specified.
Both return this error in the UI:
And this error in the logs:
[b44c25d9-af89-4e2d-b840-64218b2c0d0c] 2022-09-28T16:32:33-07:00 DEBUG metabase.server.middleware.log POST /api/database 400 1.6 s (0 DB calls) {:message "/tmp/libduckdb_java16810484802722090474.so: Error loading shared library ld-linux-x86-64.so.2: No such file or directory (needed by /tmp/libduckdb_java16810484802722090474.so)"}
Is the shared library it's referring to something that should be present in Metabase already?
@ChrisH I would recommend you open an issue on the repo https://github.com/AlexR2D2/metabase_duckdb_driver/issues and provide as many details about your setup as possible.
I had this issue too. Think the problem is alpine base image that Metabase is using. I have tried a couple of hacks, but nothing really worked. So, I used following Dockerfile it worked:
FROM openjdk:19-buster
ENV MB_PLUGINS_DIR=/home/plugins/
ADD https://downloads.metabase.com/v0.44.3/metabase.jar /home
COPY plugins/duckdb.metabase-driver.jar /home/plugins/duckdb.metabase-driver.jar
RUN chmod 744 /home/plugins/duckdb.metabase-driver.jar
CMD ["java", "-jar", "/home/metabase.jar"]
@tomsej - Creating my own container worked, though that's certainly not convenient. I'll open an issue as @flamber suggested to help others avoid this step.
After getting unblocked on using the DuckDB driver by creating my own container, I hit the next issue.
The version of duckdb.exe I used is apparently incompatible with the version of the driver. When I try to open the database in Metabase I get this error:
IO Error: Trying to read a database file with version number 38, but we can only read version 33. The database file was created with a newer version of DuckDB. The storage of DuckDB is not yet stable; newer versions of DuckDB cannot read old database files and vice versa. The storage will be stabilized when version 1.0 releases. For now, we recommend that you load the database file in a supported version of DuckDB, and use the EXPORT DATABASE command followed by IMPORT DATABASE on the current version of DuckDB.
I realize this is a DuckDB compat issue as mentioned in the error. That means I can either use an old version of duckdb.exe to create the database, or a new version of the driver to open it. Since they are continually fixing bugs and making improvements to DuckDB, the best approach is to use a new version of the driver.
I followed the instructions in the repo for the driver ( https://github.com//AlexR2D2/metabase_duckdb_driver ) to build a new version, but I keep hitting errors trying to build:
> clojure -X :build.clj :project-dir "\"$(pwd)\""
Exception in thread "main" java.io.FileNotFoundException: -X (No such file or directory)
...
I have tried running the build from the driver folder as well as the metabase sub folder where I cloned the metabase repo to (as mentioned in the driver's readme) and I get the same error.
Here are the files present in both folders:
SL1:/mnt/d/duckdb/metabase_duckdb_driver$ ls
LICENSE.txt README.md deps.edn metabase resources src
SL1:/mnt/d/duckdb/metabase_duckdb_driver$ cd metabase/
SL1:/mnt/d/duckdb/metabase_duckdb_driver/metabase$ ls
Dockerfile bin jest.tz.unit.conf.json shadow-cljs.edn webpack.config.js
LICENSE-AGPL.txt build.clj jest.unit.conf.json shared webpack.shared.config.js
LICENSE-EMBEDDING.txt codecov.yml jsconfig.json snowplow webpack.static-viz.config.js
LICENSE-MCL.txt deps.edn locales src yarn.lock
LICENSE.txt dev modules test zen.md
Procfile docs package.json test_config
README.md enterprise postcss.config.js test_modules
SECURITY.md frontend renovate.json test_resources
app.json hooks resources tsconfig.json
Any thoughts on how to resolve my no such file or directory issue?
@flamber - if you or anyone else at Metabase has any advice, I would really appreciate it.
This is a major blocker for me moving forward to the next phase of building a solution I hope lots of people can use that is cheap and easy to manage (including a DuckDB database in a container is a lot easier than teaching folks how to install and manage a postgres db.
I have this working now, and it's quite fast with early testing of my raw data. Now I need to bring dbt in to create my reporting tables in DuckDB like I have in Postgres.
I'm going to provide all the steps I followed in case someone else stumbles across this thread and needs some help.
I have not been able to successfully build my own version of the driver, but AlexR2D2 created a new version of the DuckDB driver here: Release metabase-v1.44.3-duckdb-jdbc-v0.5.1
It will let me connect to my DuckDB database (my_data.duckdb) created with duckdb.exe v0.5.2-dev318 (source Actions · duckdb/duckdb (github.com). If the version available here advances far enough to become incompatible with the DuckDB driver from AlexR2D2, you may need to open an issue in his GitHub repo to ask for a new release or build your own.
With a slight tweak to the Dockerfile provided by @tomsej, I'm including my DuckDB database (my_data.duckdb) in the image so it is all self-contained. At this point I could publish this on DockerHub and others could pull the image and use it as-is to explore the data.
FROM openjdk:19-buster
ENV MB_PLUGINS_DIR=/home/plugins/
ADD https://downloads.metabase.com/v0.44.4/metabase.jar /home
COPY plugins/duckdb.metabase-driver.jar /home/plugins/duckdb.metabase-driver.jar
RUN chmod 744 /home/plugins/duckdb.metabase-driver.jar
COPY my_data.duckdb /home/data/my_data.duckdb
RUN chmod 744 /home/data/my_data.duckdb
CMD ["java", "-jar", "/home/metabase.jar"]
Then build the image:
docker build . --tag metaduck:latest
Then create the container:
docker run --name metaduck -d -p 80:3000 -m 2GB -e MB_PLUGINS_DIR=/home/plugins metaduck
(This container is for testing DuckDB - for production, don't use the built-in H2 database for Metabase, use Postgres, MySQL or MariaDB instead.
Open Metabase in the browser: http://localhost
After creating your account, when asked to connect to your database select DuckDB, provide a name for the database and give it the path /home/data.my_data.duckdb.
That's it! You can now explore the data in your DuckDB database!