Working with ClickHouse databases

This section describes basic information about working with an external database ClickHouse.

To work with an external ClickHouse database, you need to follow these steps:

1. Create a secret containing the password for connecting to the database.

   CREATE SECRET clickhouse_datasource_user_password WITH (value = "<password>");
   

2. Create an external data source that describes the target database inside the ClickHouse cluster. To connect to ClickHouse, you can use either the native TCP protocol (PROTOCOL="NATIVE") or the HTTP protocol (PROTOCOL="HTTP"). Enable encryption of connections to the external database using the USE_TLS="TRUE" parameter.

   CREATE EXTERNAL DATA SOURCE clickhouse_datasource WITH (
       SOURCE_TYPE="ClickHouse",
       LOCATION="<host>:<port>",
       DATABASE_NAME="<database>",
       AUTH_METHOD="BASIC",
       LOGIN="<login>",
       PASSWORD_SECRET_PATH="clickhouse_datasource_user_password",
       PROTOCOL="NATIVE",
       USE_TLS="TRUE"
   );
   

3. Deploy the connector and configure the YDB dynamic nodes to interact with it. Additionally, ensure network access from the YDB dynamic nodes to the external data source (at the address specified in the LOCATION parameter of the CREATE EXTERNAL DATA SOURCE request). If network connection encryption to the external source was enabled in the previous step, the connector will use the system's root certificates. More details on TLS configuration can be found in the guide on deploying the connector.

4. Execute a query to the database.

Query syntax

The following SQL query form is used to work with ClickHouse:

SELECT * FROM clickhouse_datasource.<table_name>

where:

• clickhouse_datasource: external data source identifier
• <table_name> is the name of the table inside the external data source.

Limitations

When working with ClickHouse clusters, there are a number of limitations:

1. External sources are available only for reading data through SELECT queries. The federated query processing engine currently does not support queries that modify tables in external sources.

2. If the date value stored in the external data source is outside the allowed range for YDB (all dates used must be later than 1970-01-01 but earlier than 2105-12-31), such a value in YDB will be converted to NULL.

3. The YDB federated query processing system is capable of delegating the execution of certain parts of a query to the system acting as the data source. Query fragments are passed through YDB directly to the external system and processed by them. This optimization, known as "predicate pushdown", significantly reduces the amount of data transferred from the source to the federated query processing engine. This reduces network load and saves computational resources for the federated YDB.

   A specific case of predicate pushdown, when the filtering expressions are specified after the WHERE keyword, are passed to the data source, is called "filter pushdown". Filter pushdown is possible when using:

   Description	Example
   NULL checks	WHERE column1 IS NULL or WHERE column1 IS NOT NULL
   Logical conditions OR, NOT, AND and parentheses for controlling calculation priority.	WHERE column1 IS NULL OR (column2 IS NOT NULL AND column3 > 10).
   Comparison operators with other columns or constants.	WHERE column1 > column2 OR column3 <= 10, WHERE column1 + column2 > 10, WHERE column1 = (10 + 10)

   When using other types of filters, pushdown to the data source is not performed: filtering of the external table rows will be executed by the federated YDB, which means that YDB will perform a full scan of the external table when processing the query.

   Supported data types for filter pushdown:

   Data type YDB
   Bool
   Int8
   Uint8
   Int16
   Uint16
   Int32
   Uint32
   Int64
   Uint64
   Float
   Double
   String

Supported data types

By default, ClickHouse columns physically cannot contain the NULL value; however, you can create a table with columns of optional, or nullable, types. The column types displayed by YDB when retrieving data from an external ClickHouse database will depend on whether the ClickHouse table uses primitive or optional types. However, due to the limitations of YDB types used for storing dates and times discussed above, all similar ClickHouse types are displayed in YDB as optional.

Below are the tables of correspondence between ClickHouse and YDB types. All other data types, except those listed, are not supported.

Primitive data types

Optional data types