Metabase IRIS Driver

Hi InterSystems Community! I'm Sidd one of the interns at the Singapore office and I recently had the chance to develop to develop a driver to connect IRIS to Metabase to help some of the Sales Engineers here. I was encouraged to share it here so that if any others have a similar issue they can use the driver and as well as get some feedback on potential improvements. The full GitHub repo with the quick start step, brief overview and the driver building process can be found here. The main purpose of this post will be to go more into detail into the main driver code and talk about ideas on how the driver can be improved.

Brief Overview

The motivation behind this driver is pretty straightforward: Metabase doesn't offer native JDBC support for InterSystems IRIS, so we needed a custom driver to bridge the gap between Metabase's backend and IRIS databases. Without this bridge, there's simply no way to connect Metabase to IRIS instances, which is obviously a problem if you're trying to build dashboards and analytics on top of your IRIS data.

The good news is that since IRIS already comes with its own JDBC driver, and Metabase has a solid generic SQL JDBC driver implementation, I didn't have to reinvent the wheel. Most of the heavy lifting - like establishing database connections, handling basic queries, managing connection pools, and dealing with standard SQL operations - could be delegated to Metabase's existing generic driver framework. This saved a tremendous amount of development time and ensured compatibility with Metabase's core functionality.

Overriding multi-methods

The approach was essentially to start a minimal skeleton driver, test it against IRIS, identify the pain points where things broke or behaved unexpectedly, and then selectively override those methods with IRIS-specific implementations.

Driver skeleton

The driver skeleton forms the foundation by defining IRIS's SQL capabilities within Metabase's context and a robust type mapping system that translates between IRIS JDBC data types and Metabase's internal type system.

This is followed by some DateTime functions that lets Metabase know how to generate the necessary SQL required for time-series visualisations. This hasn't been rigorously tested yet but rather implemented by taking a look at similar implementations in other JDBC drivers.

Connecting to IRIS

One of the most important method to override would be the sql-jdbc.conn/connection-details->spec method which allows the driver to establish a connection with the IRIS JDBC.

(defn- jdbc-spec
    [{:keys [host port namespace user password additional-options]
        :or {host "localhost", port 1972, namespace "USER"}
        :as details}]

    (-> {:classname "com.intersystems.jdbc.IRISDriver"
    :subprotocol "IRIS"
    :subname (str "//" host ":" port "/" namespace)
    :user user
    :password password}
    (merge (dissoc details :host :port :namespace :user :password :additional-options))
    (sql-jdbc.common/handle-additional-options additional-options)))

(defmethod sql-jdbc.conn/connection-details->spec :iris-jdbc
    [_ details-map]
    (jdbc-spec details-map))

The method calls the helper method jdbc-spec which first reads the input the user has given before building a connection string and passing it along to the appropriate sql-jdbc function that will handle the work of actually connecting to the IRIS instance.

Describing databases

When Metabase first connects to a database, it runs a series of SQL queries to figure out, among other things, the tables the user has access to as well as the metadata of these tables. This is done though a describe-database method call which then calls sql-jdbc.sync/have-select-privilege? and sql-jdbc.sync/fallback-metadata-query among other methods. Initial attempts to override the overarching describe-database method didn't really work as I would have to implement all of the logic of the method. Instead I focused on the method calls that were failing and just decided to override those.

(defmethod sql-jdbc.sync/fallback-metadata-query :iris-jdbc
    [_ _db-name schema table]

    [(format "SELECT * FROM %s.%s WHERE 1=0 LIMIT 0"
        schema
        table)])

(defmethod sql-jdbc.sync/have-select-privilege? :iris-jdbc
    [_ _db schema table]

    [(format "SELECT 1 AS _ FROM %s.%s WHERE 1=0"
        schema
        table)])

Looking at the Docker logs, the default way Metabase was querying metadata was through a "zero row probe" which queried the name and the type of the columns of a table. This was done with an SQL statement that looked something like this:

SELECT TRUE FROM "schema"."table" WHERE 1 <> 1 LIMIT 0

This call doesn't work on IRIS for two reasons: the use of the TRUE keyword and 1 <> 1. This was done in the describe-database method before calling the fallback-metadata-query method and failing. Overriding the fallback-metadata-query method was the only way I could find that helped with this issue and I changed the SQL query to something the IRIS JDBC could run on IRIS.

A similar call:

SELECT TRUE AS _ FROM "schema"."table" WHERE 1 <> 1

was being used by Metabase when checking if a user had select privileges for a given table, which failed for similar reasons

Whats interesting is that overriding the underlying fallback query seems to be the easiest way to get around this issue, at least for IRIS. However, all other drivers that are available in the Metabase repo have their own versions of describe-database instead which makes me believe that there is a cleaner and more efficient way to achieve the same result.

Holdability

IRIS also doesn't support holdability over CLOSE_CURSORS_ON_COMMIT whereas Metabase expects a database to support it by default. While IRIS does support holdability over HOLD_CURSORS_ON_COMMIT, I decided to take inspiration from other drivers that faced the same issue by disabling holdability all together until I could figure out how to implement holdability over HOLD_CURSORS_ON_COMMIT only.

(defmethod sql-jdbc.execute/statement :iris-jdbc
    [driver ^Connection conn]
    (.createStatement conn))

(defmethod sql-jdbc.execute/prepared-statement :iris-jdbc
    [driver ^Connection conn ^String sql]
    (.prepareStatement conn sql))

Preventing system schemas/tables

By design, IRIS comes preloaded with a bunch of system schemas and tables that provide some very useful functionality but are not needed when it comes to business analytics. The simplest way to prevent schemas from syncing with Metabase would be to override the sql-jdbc.sync/excluded-schemas method which consists of returning a set of strings that contain schema names you want to exclude.

(defmethod sql-jdbc.sync/excluded-schemas :iris-jdbc [_]
   #{"Ens"})

While this is useful, IRIS simply has too many system schemas for this to practically work. Instead I chose to override the sql-jdbc.sync/filtered-syncable-schemas method instead.

(def ^:private sql-jdbc-default
    (get-method sync.interface/filtered-syncable-schemas :sql-jdbc))

(defmethod sync.interface/filtered-syncable-schemas :iris-jdbc
    [driver ^java.sql.Connection conn ^java.sql.DatabaseMetaData meta
    ^String incl-pat ^String excl-pat]

    (let [filtered-schemas
        (into []
            (remove (fn [schema]
                (or
                (str/starts-with? schema "%")
                (str/starts-with? schema "EnsLib_")
                (str/starts-with? schema "Ens_")
                (str/starts-with? schema "EnsPortal")
                (= schema "INFORMATION_SCHEMA")
                (= schema "Ens"))))
            (sql-jdbc-default driver conn meta incl-pat excl-pat))]


    (doseq [schema filtered-schemas]
        (log/infof "[IRIS-DRIVER] Remaining schema → %s" schema))

    filtered-schemas))

The default implementation of this method is to fetch all available schemas and remove schemas that are included in the set returned by the sql-jdbc.sync/excluded-schemas method. Simply overriding the method would mean that I would need to write the code for fetching all the available schemas again. To prevent this I defined a private method sql-jdbc-default that saved the default implementation of the method first before overriding it. This way, I could call the default implementation in my overridden method allowing me to dynamically filter schemas.

Future plans

My eventual goal is to get this driver officially integrated into the main Metabase repository, which would make it available to the broader community without requiring manual installation. To make that happen, I'll need to develop a comprehensive test suite that covers all the edge cases and ensures the driver works reliably across different IRIS versions and configurations. This means writing unit tests for the type mappings, integration tests for various SQL operations, and probably some performance benchmarks to make sure we're not introducing any regressions.

Beyond just getting it merged, there are definitely some areas where the current implementation could be improved. The type mapping system, while functional, could be more nuanced, especially around how we handle IRIS's more specialised data types. To do this I would need to take a look at the IRIS JDBC implementation to figure out the exact IRIS to Java type mappings.

Finally the DateTime and date arithmetic function implementations are something I believe can be improved starting with some testing to figure out if the current implementation even works.

Conclusion

I'll definitely be chipping away at these improvements whenever I get free time. The community seems pretty interested in having better IRIS support in Metabase, so it feels like a worthwhile investment. Please feel free to comment ideas or submit pull requests :)

Special thanks to @Martyn Lee and @Bryan Hoon from the Singapore office for giving me the opportunity to work on this as well as help me iron out some kinks along the way.