How to Connect Metabase to a ClickHouse Database

Overview

Metabase is an open-source business intelligence tool that lets you build dashboards and run ad-hoc queries through a friendly UI. ClickHouse is a high-performance, column-oriented OLAP database designed for sub-second analytics at scale. Connecting the two enables data teams to explore massive datasets with visual, self-service analytics while leveraging ClickHouse’s speed.

Why Pair Metabase with ClickHouse?

ClickHouse’s superior read performance makes it ideal for time-series, event, and log data. Metabase adds an intuitive layer on top so non-technical stakeholders can build charts without memorizing SQL. Together, they create an analytics stack that is both powerful and accessible.

Prerequisites

• A running ClickHouse server (≥ v21.8 recommended).
• Metabase version 0.46 or later (native ClickHouse driver ships out of the box).
• Network connectivity between Metabase and ClickHouse (default port 8123 for HTTP, 9000 for native TCP).
• Credentials for a ClickHouse user with SELECT permissions on relevant databases.

Step-by-Step Connection Guide

1. Gather Connection Details

You’ll need the ClickHouse host, port, database name, username, and password. If you use TLS, note the HTTPS port (8443 by default) and CA certificates.

2. Verify Network Access

From the Metabase host, test connectivity with:

curl https://clickhouse.example.com:8443/ping
# or non-TLS
curl http://clickhouse.example.com:8123/ping

If you get Ok. as the response, ClickHouse is reachable.

3. Log In to Metabase

Open http://<metabase-host>:3000 in your browser and sign in as an admin.

4. Add ClickHouse as a Data Source

1. Navigate to Admin → Databases → Add database.
2. Select ClickHouse from the list.
3. Fill in:
   • Name: Friendly label, e.g., “Prod ClickHouse”.
   • Host: clickhouse.example.com
   • Port: 8443 for HTTPS or 8123 for HTTP.
   • Database name: Default is default.
   • Username / Password: Your ClickHouse credentials.
4. Optional advanced settings:
   • Use TLS: Check if you enabled HTTPS.
   • CA Certificate: Paste PEM-encoded CA if using self-signed certs.
   • Sample Size: Reduce if schemas are huge to speed up sync.
5. Click Save.

Metabase runs a connection test, then starts a background sync to ingest metadata.

5. Verify Metadata Sync

Open Admin → Databases, click your ClickHouse entry, and confirm tables appear under Browse data. Large schemas may take minutes—watch the progress log at Admin → Troubleshooting → Logs.

Performance Optimization Tips

• Prefer Aggregate Tables: Pre-compute daily or hourly rollups in ClickHouse; Metabase queries become lightning fast.
• Leverage Materialized Views: Funnel raw events into summary tables on insert.
• Use Sampling Keys: For interactive exploration, create tables with SAMPLE clause so Metabase can run fast approximations.
• Set max_bytes_before_external_group_by higher for heavy aggregations.
• Index Time Columns First: Sort order impacts query speed (ORDER BY (timestamp, id)).

Security Considerations

• Enable TLS on ClickHouse’s HTTP interface and supply CA certs in Metabase.
• Create a restricted ClickHouse user with readonly profile.
• Use Metabase’s Row-Level Permissions to limit sensitive data exposure.

Common Pitfalls and Fixes

Schema Sync Seems Stuck

Large schemas or many partitions slow down Metabase’s initial field fingerprinting. Reduce Sample Size to 1000 rows or temporarily disable voluminous tables.

Connection Test Fails with “Code: 516”

Error 516 indicates invalid credentials or user lacks permission on system tables. Grant SELECT on system.* or use a more privileged role.

Dashboard Filters Not Working

Metabase relies on field types. If a ClickHouse column is String but stores ISO dates, Metabase classifies it as Category. Override the field type to Date under Admin → Data Model.

Using Galaxy to Validate ClickHouse Queries

Before wiring a question or dashboard in Metabase, many engineers prototype queries in a dedicated SQL editor. Galaxy offers a native ClickHouse connection with context-aware AI that autocompletes functions like quantileMerge and arrayJoin. You can:

• Iterate quickly on complex aggregations.
• Share vetted queries inside your Galaxy Collection for peer endorsement.
• Copy the final SQL into Metabase’s native editor or a Native Query card, confident it will run.

End-to-End Example

Suppose you store page-view events in ClickHouse:

CREATE TABLE prod.events
(
event_time DateTime,
user_id UInt32,
page String,
duration_ms UInt32
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id);


In Galaxy you test:

SELECT page,
count() AS views,
quantileExact(0.9)(duration_ms) AS p90_load
FROM prod.events
WHERE event_time >= today() - 7
GROUP BY page
ORDER BY views DESC
LIMIT 20;


After validation, paste the SQL into Metabase’s Native Query card, add visualization settings, and pin it to a dashboard. Done!

Best Practices Checklist

• Run Metabase on the same region or VPC as ClickHouse to reduce latency.
• Cap dashboard cards at <10M rows; use rollups for larger data.
• Schedule nightly syncs rather than hourly if schemas change infrequently.
• Document validated queries in Galaxy to create a single source of truth.

Conclusion

Connecting Metabase to ClickHouse is straightforward: supply credentials, pass the connection test, and let Metabase ingest metadata. From there you gain a high-performance, self-service analytics environment over billions of rows. Combine this setup with Galaxy’s developer-friendly SQL editor to prototype and share ClickHouse queries even faster.