Working with Managed Service for ClickHouse® databases

This section provides the basic information about working with Managed Service for ClickHouse®.

To work with a Managed Service for ClickHouse® database, follow these steps:

1. Create a connection containing your database connection credentials.
2. Run a query to the database.

Example of a query for reading data from Managed Service for ClickHouse®:

SELECT * FROM clickhouse_mdb_connection.my_table

Where:

• clickhouse_mdb_connection: Name of the DB connection you created.
• my_table: Name of the table in the database.

Setting up a connectionSetting up a connection

To create a connection to Managed Service for ClickHouse®:

1. In the management console, select the folder where you want to create a connection.

2. In the list of services, select Yandex Query.

3. In the left-hand panel, go to the Connections tab.

4. Click Create new.

5. Specify the connection parameters:

   1. Under General parameters:

      • Name: Name of the connection to Managed Service for ClickHouse®.
      • Type: Managed Service for ClickHouse.

   2. Under Connection type parameters:

      • Cluster: Select an existing Managed Service for ClickHouse® cluster or create a new one.

      • Service account: Select an existing Managed Service for ClickHouse® service account, or create a new one with the managed-clickhouse.viewer role, and use it to connect to Managed Service for ClickHouse® clusters.

        To use a service account, the iam.serviceAccounts.user role is required.

      • Database: Select the database you will use when working with the ClickHouse® cluster.

      • Login: Username to use when connecting to ClickHouse® databases.

      • Password: User password to use when connecting to ClickHouse® databases.

6. Click Create.

You need a service account to detect Managed Service for ClickHouse® cluster connection points inside Yandex Cloud. You need a separate login/password pair to access data.

Query syntaxQuery syntax

Here is the SQL query format used to access ClickHouse®:

SELECT * FROM <connection>.<table_name>

Where:

• <connection>: Name of the DB connection you created.
• <table_name>: Name of the table in the database.

LimitationsLimitations

There are several restrictions when working with ClickHouse® clusters.

Limitations:

1. External sources are only available for data reading through SELECT requests. Yandex Query does not currently support requests that modify tables in external sources.

2. YQ uses the Yandex Managed Service for YDB type system. However, the ranges of acceptable values for types used in YDB for date and time operations (Date, Datetime, and Timestamp) often turn out to be insufficiently wide to cover the values of the relevant ClickHouse® types (Date, Date32, Datetime, and Datetime64).
   Therefore, YQ returns date and time values read from ClickHouse® as plain strings (the Utf8 type for regular columns or the Optional<Utf8> type for nullable columns) in ISO-8601 format.

Filter pushdownFilter pushdown

Yandex Query can transfer processing of request parts to the data source system. This means that filter expressions are provided through Yandex Query directly to the database for processing; these are typically the query conditions specified in WHERE. This kind of processing is called filter pushdown.

Filter pushdown is possible when using:

Supported data types for filter pushdown:

Supported data typesSupported data types

In ClickHouse®, columns cannot physically contain the NULL value by default; however, the user can create a table with columns of optional or nullable types. The column types Yandex Query displays when extracting data from the external ClickHouse® source will depend on whether the ClickHouse® table uses primitive or optional types.

The tables below show how ClickHouse® and Yandex Query types map. All other data types except those listed are not supported.

Primitive data typesPrimitive data types

Optional data typesOptional data types

ClickHouse® is a registered trademark of ClickHouse, Inc.