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 to the database.

Query example for reading 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 a connection.

2. Navigate to Yandex Query.

3. In the left-hand panel, select Connections.

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 the service account on your behalf, you need the iam.serviceAccounts.user role.

      • Database: Select the database you will use to work 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.

You need a service account to detect Managed Service for ClickHouse® cluster connection endpoints inside Yandex Cloud. To work with data, set a username and password separately.

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.

LimitsLimits

ClickHouse® clusters are subject to the following restrictions:

1. External sources are available for read-only access via SELECT queries. Yandex Query does not currently support data-modifying queries against external sources.

2. YQ uses the Yandex Managed Service for YDB type system. The available value ranges of YDB date and time types (Date, Datetime, Timestamp) do not allow storing all values of relevant ClickHouse® types (Date, Date32, DateTime, DateTime64).

   Therefore, YQ returns the date and time values read from ClickHouse® as strings in ISO-8601 format. Regular columns use type Utf8; nullable columns use Optional<Utf8>.

Filter pushdownFilter pushdown

Yandex Query can push parts of query processing down to the source data system by sending filter expressions, e.g., WHERE conditions, directly to the database. This approach is known as filter pushdown.

Filter pushdown is possible when using:

Other filter types do not support source pushdown: the external table rows are 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, ClickHouse® columns cannot contain NULL. However, you can create a table with columns of optional (nullable) types. The column types displayed by Yandex Query when extracting data from an external ClickHouse® source depend on whether the ClickHouse® table uses primitive or optional types.

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

Primitive data typesPrimitive data types

Optional data typesOptional data types

ClickHouse® is a registered trademark of ClickHouse, Inc.