Connect to ClickHouse (Beta)

This documentation describes one or more public beta features that are in development. Beta features are subject to quick, iterative changes; therefore the current user experience in the Sigma service can differ from the information provided in this page.

This page should not be considered official published documentation until Sigma removes this notice and the beta flag on the corresponding feature(s) in the Sigma service. For the full beta feature disclaimer, see Beta features.

Sigma supports secure connections to ClickHouse. This document explains how to connect your Sigma organization to your ClickHouse server, and describes relevant feature limitations.

User requirements

You must be assigned the Admin account type or an account type with the Manage connections feature permission enabled.
Your ClickHouse version should be actively supported by ClickHouse (stable or lts versions). See the ClickHouse documentation on ClickHouse releases. Your ClickHouse version must be v24.2 or above.
Your ClickHouse server must be accessible over the public internet using basic authentication (username and password).

Limitations

The following limitations apply to ClickHouse connections:

Connection-level OAuth authentication is not supported.
Private link connections and secure Shell (SSH) protocol connections are not supported.
Input table and write back functionality is not supported, including: CSV uploads, use of input tables, running Python code.
Using stored procedures is not supported.
Materialization and warehouse views is only supported for ClickHouse Cloud instances and single node self-hosted ClickHouse instances.
Dataset (deprecated) warehouse views are not supported.
Export to cloud storage is not supported.
Query ID cache is not supported.
User attributes for role and warehouse switching are not supported.
Using sample connections is not supported.
Using column values with strings that are not in UTF-8 format
The following functions are not supported:
- All Geography functions
- All Array functions except for Array, ArrayContains, and ArrayLength
- The following Passthrough functions: CallGeography, AggGeography

Create a ClickHouse connection

Click the user icon at the top right of your screen. The user icon is usually composed of your initials.
In the drop-down menu, select Add connection. The Add new connection page appears.
In the Connection Details section, specify the following:


Name	Enter a Name for the new connection. Sigma displays this name in the connection list.
Type	Select ClickHouse.

In the Connection credentials section, specify the following:


Host	The address of your ClickHouse server as an IP address or URL.
Port	The port through which Sigma should connect to your ClickHouse server. The default is `8443`. Connections are only supported for ports `8443` (if you want to use HTTPS with TLS enabled), or `8123` (if you want to use HTTP with TLS disabled).
User	The username for connecting to your ClickHouse server.
Password	The password for connecting to your ClickHouse server.
Enable TLS	Turn the toggle on or off to enable or disable TLS (transport layer security) encryption for your ClickHouse connection. TLS is recommended for all connections. You cannot disable TLS if you are using port `8443`, or the connection will be unsuccessful.

In the Connection features section, specify the following:


Connection timeout	The time before timeout (or cancellation), in seconds, that Sigma waits for the query to return results. The default is 120, or 2 minutes. The maximum is 600, or 10 minutes.
Connection queue size	The number of interactive queries Sigma can run on this connection concurrently. The default is 8.
Use friendly names	Turn the toggle on or off. Friendly names make column names from the data source more readable. For example, a database column ORDER_NUMBER or OrderNumber appears as Order Number.

Select Create.
Once your connection has been created, select Browse Connection. You can then select Add permission to grant data access for users in your organization. See Data access overview.

You can also configure write access for your connection. For more information, see Configure write access.

(Optional) Configure write access

When you designate a schema as the write access destination, Sigma reserves it for internal write-back objects and doesn’t expose it as a data source in the connection explorer (data catalog). To avoid restricting user access to analytical data, use a dedicated write-back database or schema that doesn’t store data used for analysis and reports.

Write access is necessary to use features like materialization and warehouse views.

To configure write access:

Grant the ClickHouse user that you use to configuring the Sigma connection a role with the following privileges:

Object	Privilege
Database	`CREATE TABLE`, `DROP TABLE`, `CREATE VIEW`, `DROP VIEW`, `INSERT`, `SELECT`

The statement to grant these privileges might look like:

1   GRANT CREATE TABLE, DROP TABLE, CREATE VIEW, DROP VIEW, INSERT, SELECT
2     ON <example_writeback_database>.* TO <example_user>;

In Sigma, go to Connections, select your ClickHouse connection, then select Edit.
Turn on the Enable write access toggle.
In the Write database field, enter the database where Sigma should store write-back data.