Working with Managed Service for ClickHouse® databases
This section describes the basic information about working with Managed Service for ClickHouse®.
Example of reading data from Managed Service for ClickHouse®:
SELECT * FROM clickhouse_mdb_connection.my_table
Where:
clickhouse_mdb_connection
: Name of the established database connection.my_table
: Name of the table in the database.
To work with a Managed Service for ClickHouse® database, follow these steps:
- Create a connection containing your database connection credentials.
- Run a query to the database.
Setting up a connection
To create a connection to Managed Service for ClickHouse®:
-
In the management console
, select the folder where you want to create a connection. -
In the list of services, select Yandex Query.
-
In the left-hand panel, go to the Connections tab.
-
Click
Create new. -
Specify the connection parameters:
-
Under General parameters:
- Name: Name of the connection to Managed Service for ClickHouse®.
- Type:
Managed Service for ClickHouse
.
-
Under Connection type parameters:
- Cluster: Select an existing Managed Service for ClickHouse® cluster or create a new one.
- Service account: Select an existing service account Managed Service for ClickHouse®, or create a new one with the
managed-clickhouse.viewer
role, and use it to connect toManaged Service for ClickHouse®
clusters. - 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.
-
-
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.
Warning
But first allow network access from Yandex Query to Managed Service for ClickHouse® clusters. To do this, enable Yandex Query access in the settings of the database to which you are connecting.
Query syntax
Here is the SQL query format used to access ClickHouse®:
SELECT * FROM clickhouse_mdb_connection.<table>
Where:
clickhouse_mdb_connection
: Name of the established database connection.<table>
: Name of the table in the database.
Limitations
There are several restrictions when working with ClickHouse® clusters.
Warning
Currently, regardless of the choice of filters for reading the ClickHouse® tables specified in the SQL query, all data from the table will be read into Yandex Query and filters will be applied there.
Limitations:
- No query types are supported other than the
SELECT
data read queries. - The maximum supported number of rows in a table is 1,000,000. If this value is exceeded, the query will terminate with an error.
-
If the date falls outside the valid range (all the dates must be between 01/01/1970 and 31/12/2105), the query will result in an error.
Filter 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:
Description | Example |
---|---|
Filters of the IS NULL /IS NOT NULL type |
WHERE column1 IS NULL or WHERE column1 IS NOT NULL |
OR , NOT , AND logical conditions |
WHERE column IS NULL OR column2 is NOT NULL |
= , <> , < , <= , > , >= comparison conditions with other columns or constants |
WHERE column3 > column4 OR column5 <= 10 |
Supported data types for filter pushdown:
Data type Yandex Query |
---|
BOOL |
INT8 |
UINT8 |
INT16 |
UINT16 |
INT32 |
UINT32 |
INT64 |
UINT64 |
FLOAT |
DOUBLE |
Supported data types
Below is a table of correspondence between ClickHouse® types and Yandex Query types.
Data type ClickHouse® | Data type Yandex Query | Notes |
---|---|---|
Bool |
BOOL |
|
Int8 |
INT8 |
|
UInt8 |
UINT8 |
|
Int16 |
INT16 |
|
UInt16 |
UINT16 |
|
Int32 |
INT32 |
|
UInt32 |
UINT32 |
|
Int64 |
INT64 |
|
UInt64 |
UINT64 |
|
Float32 |
FLOAT |
|
Float64 |
DOUBLE |
|
Date |
DATE |
|
Datetime |
DATETIME |
The valid date range is between 01/01/1970 00:00 and 31/12/2105 23:59 |
String |
STRING |
|
FixedString |
STRING |
Null FixedString bytes are transferred to STRING without changes. |
Other data types are not supported.
ClickHouse® is a registered trademark of ClickHouse, Inc