[Yandex Cloud documentation](../../index.md) > [Yandex Query](../index.md) > Data sources and sinks > Working with Managed Service for ClickHouse® databases

# Working with Managed Service for ClickHouse® databases

This section covers the basics of working with [Managed Service for ClickHouse®](https://yandex.cloud/en/services/managed-clickhouse).

To start working with a Managed Service for ClickHouse® database, follow these steps:
1. Create a [connection](../concepts/glossary.md#connection) containing your database access credentials.
1. [Run a query](#query) against the database {#query}

Query example for reading data from Managed Service for ClickHouse®:

```sql
SELECT * FROM clickhouse_mdb_connection.my_table
```

Where:
* `clickhouse_mdb_connection`: Your database connection name.
* `my_table`: Database table name.


## Setting up a connection {#create_connection}

To create a connection to Managed Service for ClickHouse®:

1. In the [management console](https://console.yandex.cloud), select the folder where you want to create a connection.
1. Navigate to **Yandex Query**.
1. In the left-hand panel, switch to the **Connections** tab.
1. Click ![info](../../_assets/console-icons/plus.svg) **Create new**.
1. Specify the connection settings:

   1. Under **General parameters**:

      * **Name**: Managed Service for ClickHouse® connection name.
      * **Type**: `Managed Service for ClickHouse`.
   1. 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](../../iam/concepts/users/service-accounts.md) or create a new one. Assign it the [`managed-clickhouse.viewer`](../../managed-clickhouse/security.md#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](../../iam/security/index.md#iam-serviceAccounts-user).

      * **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.


1. 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.

{% note warning %}

First, grant network access from Yandex Query to Managed Service for ClickHouse® clusters. To do this, enable **Yandex Query access** in your target database settings.

{% endnote %}


## Query syntax {#query}
ClickHouse® uses the following SQL syntax:

```sql
SELECT * FROM <connection>.<table_name>
```

Where:
* `<connection>`: Your database connection name.
* `<table_name>`: Database table name.

## Limits {#limits}

Working with ClickHouse® clusters comes with certain limitations.

The following limitations apply:
1. External sources are available for read-only access via `SELECT` queries. Yandex Query does not currently support data-modifying queries against external sources.
1. YQ uses the Yandex Managed Service for YDB [type system](https://ydb.tech/docs/en//yql/reference/types/primitive). 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](https://clickhouse.com/docs/enen/sql-reference/data-types/nullable) columns) in [ISO-8601](https://www.iso.org/iso-8601-date-and-time-format.html) format.

## Filter pushdown {#predicate_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:

|Description|Example|
|---|---|
|`NULL` check|`WHERE column1 IS NULL` or `WHERE column1 IS NOT NULL`|
|Logical operators `AND`, `OR`, `NOT`, and parentheses to control operator precedence |`WHERE column1 IS NULL OR (column2 IS NOT NULL AND column3 > 10)`.|
|Comparison operators `=`, `==`, `!=`, `<>`, `>`, `<`, `>=`, and `<=` that compare a column with other columns or constants|`WHERE column1 > column2 OR column3 <= 10`, `WHERE column1 + column2 > 10`, `WHERE column1 = (10 + 10)`|

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:

|Yandex Query data type|
|----|
|`Bool`|
|`Int8`|
|`Uint8`|
|`Int16`|
|`Uint16`|
|`Int32`|
|`Uint32`|
|`Int64`|
|`Uint64`|
|`Float`|
|`Double`|
|`String`|

## Supported data types {#supported_types}

By default, columns in ClickHouse® cannot physically contain `NULL` values. However, you can create a table with columns of optional or [nullable](https://clickhouse.com/docs/enen/sql-reference/data-types/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 listed types are supported.

### Primitive data types {#supported_types_default}

| ClickHouse® data type | Yandex Query data type | Notes |
| :---: | :----: | :--- |
| `Bool` | `Bool` | |
| `Int8` | `Int8` | |
| `UInt8` | `Uint8` | |
| `Int16` | `Int16` | |
| `UInt16` | `Uint16` | |
| `Int32` | `Int32` | |
| `UInt32` | `Uint32` | |
| `Int64` | `Int64` | |
| `UInt64` | `Uint64` | |
| `Float32` | `Float` | |
| `Float64` | `Double` | |
| `Date` | `Utf8` | |
| `Date32` | `Utf8` | |
| `DateTime` | `Utf8` | |
| `DateTime64` | `Utf8` | |
| `String` | `String` | |
| `FixedString` | `String` | Null `FixedString` bytes are transferred to `String` unchanged. |

### Optional data types {#supported_types_nullable}

| ClickHouse® data type | Yandex Query data type | Notes |
| :---: | :----: | :--- |
| `Nullable(Bool)` | `Optional<Bool>` | |
| `Nullable(Int8)` | `Optional<Int8>` | |
| `Nullable(UInt8)` | `Optional<Uint8>` | |
| `Nullable(Int16)` | `Optional<Int16>` | |
| `Nullable(UInt16)` | `Optional<Uint16>` | |
| `Nullable(Int32)` | `Optional<Int32>` | |
| `Nullable(UInt32)` | `Optional<Uint32>` | |
| `Nullable(Int64)` | `Optional<Int64>` | |
| `Nullable(UInt64)` | `Optional<Uint64>` | |
| `Nullable(Float32)` | `Optional<Float>` | |
| `Nullable(Float64)` | `Optional<Double>` | |
| `Nullable(Date)` | `Optional<Utf8>` | |
| `Nullable(Date32)` | `Optional<Utf8>` | |
| `Nullable(DateTime)` | `Optional<Utf8>` | |
| `Nullable(DateTime64)` | `Optional<Utf8>` | |
| `Nullable(String)` | `Optional<String>` | |
| `Nullable(FixedString)` | `Optional<String>` | Null `FixedString` bytes are transferred to `String` unchanged. |

_ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._