ClickHouse

Connect a ClickHouse database, on ClickHouse Cloud or your own server, to DashX. Once installed, you can browse its tables in Database Hub, run SQL against it, and save parameterized queries as operations.

Prerequisites

Prerequisites

Before setting up the ClickHouse integration, ensure you have:

• An active DashX account
• A ClickHouse service whose HTTP interface is reachable from the internet
• Credentials for a ClickHouse user DashX can connect as

Prepare a database user

Create a dedicated user for DashX and grant it read access only. Database Hub never writes to ClickHouse tables, but SQL you run in Database Hub or in a saved query runs with this user's privileges.

CREATE USER dashx IDENTIFIED BY 'a-strong-password';
GRANT SELECT ON your_database.* TO dashx;

Repeat the GRANT for every database you want to expose. DashX only sees the databases and tables this user can read.

warning

Do not restrict the user with readonly = 1. DashX sets a time limit on every query, and a user with readonly = 1 cannot change any setting, so all of its queries fail. If you want a read-only settings profile, use readonly = 2, which still blocks writes:

ALTER USER dashx SETTINGS readonly = 2;

Allow network access

Your ClickHouse service must accept connections from DashX. DashX connects from a single static IP address; contact DashX support for it. On ClickHouse Cloud, add that address to the service's IP access list. On your own server, allow it through your firewall rule or security group.

DashX connects over ClickHouse's HTTP interface, not the native protocol on ports 9000 or 9440.

Connect to DashX

1. Log in to your DashX workspace
2. Navigate to Settings > Integrations
3. Click [+] to add a new integration and select ClickHouse
4. Choose the environments you want to enable it for
5. Enter the configuration below, test the connection, and save

Configuration

Field	Description
Host	Hostname of the ClickHouse service, without a protocol or port, e.g. abc123.us-east-1.aws.clickhouse.cloud.
Port	Port of the HTTP interface. ClickHouse Cloud uses 8443. A self-hosted server listens on 8123 for HTTP by default.
Protocol	HTTPS or HTTP. Use HTTPS for ClickHouse Cloud and for any server reached over the internet.
User	The ClickHouse user DashX connects as.
Password	Password for that user.
Database Name	The database used by queries that don't name one.

Browse tables in Database Hub

When you save the configuration, DashX reads the schema. Each ClickHouse database appears as a schema in Database Hub, with its tables and views. DashX reads the schema again each time you save the configuration, picking up added, renamed and dropped tables and columns.

Database Hub doesn't tell names apart by case. If two databases, or two tables in one database, have names that differ only in case, such as Events and events, only one is shown and the sync reports the other as skipped.

From Database Hub you can filter, sort and page through a table's rows, count them and export them as CSV.

• ClickHouse tables are read-only in Database Hub. Rows can't be added, edited or deleted.
• Tables that merge rows in the background, such as ReplacingMergeTree and CollapsingMergeTree, are read with FINAL, so you see one row per key.
• Columns of composite or dynamic types, such as Array, Map, Tuple, Nested, JSON, Variant and Dynamic, and aggregate function columns, can only be filtered as text, for example with contains.

ClickHouse tables can't be synced into Resources. Use Database Hub or a saved query instead.

Run SQL

Database Hub's SQL editor runs against ClickHouse like any other database, with a few limits:

• One statement per run.
• Results show the first 10,000 rows.
• A query stops after 2 minutes.
• 64-bit integers are returned as JSON numbers, which lose precision above 2⁵³. Wrap such columns in toString() if you need exact values.

Save a query as an operation

Create an operation with the Database Query behavior and choose your ClickHouse integration. Reference the operation's parameters with ClickHouse's query parameter syntax, {identifier:Type}, where identifier is the parameter's identifier and Type is a ClickHouse type:

SELECT email, first_name, plan
FROM crm.members FINAL
WHERE plan = {plan:String}
  AND renews_on <= {renews_before:Date}
  AND country IN {countries:Array(String)}

DashX sends parameter values to ClickHouse separately from the query, so a value can never change the SQL around it.

• A saved query can return up to 10,000 rows. If it returns more, it fails rather than returning a partial result, so add a LIMIT or narrow the filters.
• A query that starts with SELECT, WITH, SHOW, DESCRIBE, DESC, EXPLAIN or EXISTS becomes a GraphQL query. Anything else becomes a mutation.
• As in the SQL editor, 64-bit integers are returned as JSON numbers. Use toString() for exact values.

Related: the Database Hub app.