ClickHouse

Note

You might want to start with our resources on data connections in the Knowledge Base.

DSS supports the full range of features on ClickHouse:

  • Reading and writing datasets

  • Executing SQL recipes

  • Performing visual recipes in-database

  • Using live engine for charts

Connection setup

The ClickHouse JDBC driver is pre-installed in DSS. You do not need any additional installation when using the Dataiku-managed driver.

To create a ClickHouse connection:

  • Enter the host and database.

  • Select the protocol: HTTP or HTTPS.

  • Enter the port number.

  • Enter the user and password, unless the connection uses per-user credentials.

By default, DSS connects to ClickHouse on port 8123 over HTTP.

Both local ClickHouse deployments and ClickHouse Cloud are supported. For Clickhouse Cloud, the hostname to use is provided in the “Connect” section of the ClickHouse Cloud Console.

Advanced JDBC properties

You can add advanced JDBC properties in the connection settings.

ClickHouse session settings are also supported. Prefix them with clickhouse_setting_ in the advanced properties list.

Secure connections (HTTPS / TLS)

DSS can connect to ClickHouse over HTTPS.

To use HTTPS:

  • Select HTTPS as the protocol in the connection settings.

  • If your ClickHouse server uses a certificate signed by a private CA, add the CA certificate to the JVM truststore used by DSS, following one of the procedures described in Adding SSL certificates to the Java truststore.

Driver customization

If needed, you can switch the connection to a user-provided JDBC driver.

In that case:

  • Set Driver to use to User-provided.

  • Optionally set a driver JARs directory where the ClickHouse JDBC driver JAR file or files have been copied.

  • Optionally set a custom driver class.

If no driver JARs directory is specified, the JAR files must be copied to lib/jdbc in the DSS data directory.

Managed ClickHouse datasets

Managed ClickHouse datasets expose ClickHouse-specific table settings:

  • An ordered list of ORDER BY columns.

The ORDER BY columns are used to generate the ClickHouse table creation ORDER BY clause and the ClickHouse primary key clause when DSS creates the table.

If no ORDER BY column is selected, DSS creates the table with ORDER BY tuple() and no primary key. While this is valid, selecting one or more ORDER BY columns is recommended for query performance on large tables.

DSS-generated schemas use nullable columns, e.g. Nullable(String). If needed, this can be overridden in Dataset > Settings > Advanced by setting the table creation mode to Manually define and providing your own table creation SQL without the Nullable qualifier.

DSS creates tables only using ClickHouse’s default MergeTree engine. As above, this can be overridden using manual table definition.

Caveats

  • Geospatial DSS functionality is not yet supported for ClickHouse datasets.

  • ClickHouse composite types (Map, Array, Tuple, Variant) are not yet supported in DSS.

  • ClickHouse tables containing a BFloat16 column cannot be read because of this ClickHouse JDBC driver issue.