Working with Managed Service for ClickHouse® databases

This section covers the basics of working with Managed Service for ClickHouse®.

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

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

An example of a query that reads data from Managed Service for ClickHouse®:

SELECT * FROM clickhouse_mdb_connection.my_table

Where:

• clickhouse_mdb_connection: Your database connection name.
• my_table: Database table name.

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 your connection.

2. Navigate to Yandex Query.

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

4. Click Create new.

5. Specify the connection settings:

   1. Under General parameters:

      • Name: Managed Service for ClickHouse® connection name.
      • 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. Assign it the managed-clickhouse.viewer role allowing 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 you will use to connect to ClickHouse® databases.

      • Password: Password you will use to connect to ClickHouse® databases.

6. Click Create.

A service account is necessary to detect Managed Service for ClickHouse® cluster connection endpoints inside Yandex Cloud. To access data, you need a separate username and password.

Query syntaxQuery syntax

ClickHouse® uses the following SQL syntax:

SELECT * FROM <connection>.<table_name>

Where:

• <connection>: Your database connection name.
• <table_name>: Database table name.

LimitationsLimitations

Working with ClickHouse® clusters comes with certain limitations.

The following limitations apply:

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 valid value ranges for YDB date and time types, i.e., Date, Datetime, and Timestamp, are often too narrow to accommodate the values of the corresponding ClickHouse® types, i.e., Date, Date32, Datetime, and Datetime64.
   As a result, when reading date and time values from ClickHouse®, YQ returns them as plain strings (type Utf8 for regular columns or Optional<Utf8> 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:

Other filter types do not support source pushdown: the external table rows will be filtered on the federated Yandex Query side, i.e., Yandex Query will perform a full scan of the external table when processing the query.

Supported data types for filter pushdown:

Supported data typesSupported data types

By default, columns in ClickHouse® cannot physically contain NULL values. However, you can create a table with columns of optional or nullable types. The column types displayed by Yandex Query when extracting data from an external ClickHouse® source will depend on whether the ClickHouse® table uses primitive or optional types.

The tables below show type mapping between ClickHouse® and Yandex Query. Only the data types listed are supported.

Primitive data typesPrimitive data types

Optional data typesOptional data types

ClickHouse® is a registered trademark of ClickHouse, Inc.