Hello, I have SQLite custom functions, in the form of SQLite extensions, that I would like to use in Metabase native SQL questions. How do I tell Metabase to load SQLite extensions when it connects to a SQLite database? Or as an alternative - How do I tell Metabase to make a set of SQLite extensions available to all SQLite database connections?
Thanks!
Hi @mbezzi
I don't fully understand how extensions work on SQLite. So they are not part of the database file, but something that has to be included in the driver? If that's the case, then you would need to build your own driver.
Hi Flamber, thank you.
SQLite extensions are shared library or DLLs that can be dynamically loaded at runtime. Alternatively extensions can be statically linked into the application using SQLite. In Metabase context I think the application is the driver, and I think you are right when you say that I would need to build my own driver.
Is there any documentation guiding on how to do it? I suspect that providing a modified version of the SQLite binaries (dll?) used by the driver would be the first step, but then, do I need to configure the driver to let it know of the newly available functions?
Thanks again,
mario
@mbezzi There's a lot of changes the past month on our build process, and I don't think the documentation is fully updated yet:
https://github.com/metabase/metabase/blob/master/docs/developers-guide-drivers.md
https://github.com/metabase/metabase/blob/master/docs/developers-guide.md
The SQLite driver is located here:
https://github.com/metabase/metabase/tree/master/modules/drivers/sqlite
You would probably only need to create a child-driver, which depends on the existing driver, and then modify it, so it loads the extensions you need.
Thank you @Flamber. It turns out that Metabase depends on (its built upon) the xerial sqlite jdbc driver. See https://github.com/xerial/sqlite-jdbc
In the metabase/modules/drivers/sqlite/deps.edn file I see:
:deps
{org.xerial/sqlite-jdbc {:mvn/version "3.25.2"}}}
The xerial driver already supports extensions. Some, like for example "ltrim", "rtrim" and "reverse" for strings are already included, and work like charm under metabase. Adding more would simply mean modifying the sqlite-jdbc/src/main/ext/extension-functions.c file and rebuilding the xerial driver.
What's not clear to me though, also after reading the documentation you pointed me at, is how to make metabase "use" my custom version of the xerial driver.
Thank you,
mario
@mbezzi If you create your own custom build of Metabase, then any changes you make will be included in the build you make, so that would be automatic.
If you want to build a separate driver, then you create a new driver that depends on something, so you don't have to create everything from scratch.
Example see the Redshift driver, which builds on top of the Postgres core driver.
But I don't know Clojure, so I cannot help you much more than that.
Thank you @flamber. I managed to build a new sqlite.metabase-driver.jar which includes my custom functions. I put it in the /plugin directory, but when I start metabase via a java -jar metabase.jar command, metabase initialization insists overwriting my driver with the shipped one.
2021-09-07 09:42:04,334 INFO metabase.plugins :: Loading plugins in /home/mario/metabase.test/plugins...
2021-09-07 09:42:04,461 INFO util.files :: Extract file /modules/sqlite.metabase-driver.jar -> /home/mario/metabase.test/plugins/sqlite.metabase-driver.jar
Is this expected? May I use a custom driver with the original metabase.jar?
Thank you
@mbezzi That is expected. You need to either bundle your driver, so it replaces the existing or give it a different namespace, so you'll have two SQLite drivers.
Hi @flamber, thank you. The multiple namespaces option sounds interesting. Is there any documentation which explains how to implement it?
Thanks!
mario
@mbezzi Every driver has it's own namespace. I don't know how you have created your own driver, so it's hard to tell what to change.
Line 6 and 17: https://github.com/metabase/metabase/blob/master/modules/drivers/sqlite/resources/metabase-plugin.yaml
Line 1: https://github.com/metabase/metabase/blob/master/modules/drivers/sqlite/src/metabase/driver/sqlite.clj
You can also modify the utime of the file to match the one shipped with Metabase, then it won't be replaced. Just remember that each version of Metabase might create a new update time.
https://github.com/metabase/metabase/blob/ce939219f8a3e28ecb00deb232154780a1dcfcef/src/metabase/util/files.clj#L80-L84