ClickHouse driver for Metabase

About

ClickHouse (GitHub) database driver for the Metabase (GitHub) business intelligence front-end.

Compatibility with ClickHouse

The driver aims to support the current stable and LTS releases (see the related docs).

After 1.50.1:

ClickHouse version	Supported?
23.8+	✔
23.6 - 23.7	Best effort

1.50.0 and earlier:

ClickHouse version	Supported?
23.8+	✔
23.3 - 23.7	Best effort

For connection impersonation feature, the minimal required ClickHouse version is 24.4; otherwise, the feature is disabled by the driver.

The CSV Uploads feature currently works only with ClickHouse Cloud (see this issue for more details).

Installation

Run using Metabase Jar

Download a fairly recent Metabase binary release (jar file) from the Metabase distribution page.
Download the ClickHouse driver jar from this repository's Releases page
Create a directory and copy the metabase.jar to it.
In that directory create a sub-directory called plugins.
Copy the ClickHouse driver jar to the plugins directory.
Make sure you are the in the directory where your metabase.jar lives.
Run MB_PLUGINS_DIR=./plugins; java -jar metabase.jar.

For example (using Metabase v0.49.3 and ClickHouse driver 1.4.0):

export METABASE_VERSION=v0.49.3
export METABASE_CLICKHOUSE_DRIVER_VERSION=1.4.0

mkdir -p mb/plugins && cd mb
curl -o metabase.jar https://downloads.metabase.com/$METABASE_VERSION/metabase.jar
curl -L -o plugins/ch.jar https://github.com/ClickHouse/metabase-clickhouse-driver/releases/download/$METABASE_CLICKHOUSE_DRIVER_VERSION/clickhouse.metabase-driver.jar
MB_PLUGINS_DIR=./plugins; java -jar metabase.jar

Run as a Docker container

Alternatively, if you don't want to run Metabase Jar, you can use a Docker image:

export METABASE_DOCKER_VERSION=v0.49.3
export METABASE_CLICKHOUSE_DRIVER_VERSION=1.4.0

mkdir -p mb/plugins && cd mb
curl -L -o plugins/ch.jar https://github.com/ClickHouse/metabase-clickhouse-driver/releases/download/$METABASE_CLICKHOUSE_DRIVER_VERSION/clickhouse.metabase-driver.jar
docker run -d -p 3000:3000 \
  --mount type=bind,source=$PWD/plugins/ch.jar,destination=/plugins/clickhouse.jar \
  metabase/metabase:$METABASE_DOCKER_VERSION

Choosing the Right Version

Metabase Release	Driver Version
0.33.x	0.6
0.34.x	0.7.0
0.35.x	0.7.1
0.37.3	0.7.3
0.38.1+	0.7.5
0.41.2	0.8.0
0.41.3.1	0.8.1
0.42.x	0.8.1
0.44.x	0.9.1
0.45.x	1.1.0
0.46.x	1.1.7
0.47.x	1.2.3
0.47.7+	1.2.5
0.48.x	1.3.4
0.49.x	1.4.0
0.49.14+	1.5.1
0.50.x	1.50.2

After Metabase 0.50.0, a new naming convention exists for the driver's releases. The new one is intended to reflect the Metabase version the driver is supposed to run on. For example, the driver version 1.50.0 means that it should be used with Metabase v0.50.x or Metabase EE 1.50.x only, and it is not guaranteed that this particular version of the driver can work with the previous or the following versions of Metabase.

Creating a Metabase Docker image with ClickHouse driver

You can use a convenience script build_docker_image.sh, which takes three arguments: Metabase version, ClickHouse driver version, and the desired final Docker image tag.

./build_docker_image.sh v0.49.3 1.4.0 my-metabase-with-clickhouse:v0.0.1

where v0.49.3 is Metabase version, 1.4.0 is ClickHouse driver version, and my-metabase-with-clickhouse:v0.0.1 being the tag.

Then you should be able to run it:

docker run -d -p 3000:3000 --name my-metabase my-metabase-with-clickhouse:v0.0.1

or use it with Docker compose, for example:

version: '3.8'
services:
  clickhouse:
    image: 'clickhouse/clickhouse-server:24.3-alpine'
    container_name: 'metabase-clickhouse-server'
    ports:
      - '8123:8123'
      - '9000:9000'
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
  metabase:
    image: 'my-metabase-with-clickhouse:v0.0.1'
    container_name: 'metabase-with-clickhouse'
    environment:
      'MB_HTTP_TIMEOUT': '5000'
      # Replace with a timezone matching your ClickHouse or DateTime columns timezone
      'JAVA_TIMEZONE': 'UTC'
    ports:
      - '3000:3000'

Using certificates

In the "Advanced options", add the following to the "Additional JDBC connection string options" input:

sslrootcert=/path/to/ca.crt

where /path/to/ca.crt is the absolute path to the server CA on the Metabase host or Docker container (depends on your deployment).

Make sure that you tick "Use a secure connection (SSL)" as well.

Operations

The driver should work fine for many use cases. Please consider the following items when running a Metabase instance with this driver:

Create a dedicated user for Metabase, whose profile has readonly set to 2.
Consider running the Metabase instance in the same time zone as your ClickHouse database; the more time zones involved the more issues.
Compare the results of the queries with the results returned by clickhouse-client.
Metabase is a good tool for organizing questions, dashboards etc. and to give non-technical users a good way to explore the data and share their results. The driver cannot support all the cool special features of ClickHouse, e.g. array functions. You are free to use native queries, of course.

Known limitations

As the underlying JDBC driver version does not support columns with AggregateFunction type, these columns are excluded from the table metadata and data browser result sets to prevent sync or data browsing errors.
If the past month/week/quarter/year filter over a DateTime64 column is not working as intended, this is likely due to a type conversion issue. See this report for more details. This issue was resolved as of ClickHouse 23.5.
If introspected ClickHouse version is lower than 23.8, the driver will not use startsWithUTF8 and fall back to its non-UTF8 counterpart instead. There is a drawback in this compatibility mode: potentially incorrect filtering results when working with non-latin strings. If your use case includes filtering by columns with such strings and you experience these issues, consider upgrading your ClickHouse server to 23.8+.

Contributing

Check out our contributing guide.

crisptrutski/metabase-clickhouse-driver