Connecting ClickHouse as an analytical DB
Adding a separate analytical DB to the Data Lakehouse (DLH) architecture makes it possible to quickly process the incoming stream of events without the need to store data in S3 storage. Such a DB can be PostgreSQL, where operational data arrives as a message stream or in batches, or Cloud ClickHouse.
The following describes a variant of configuring the Data Lakehouse service with an analytical database based on the ClickHouse product.
Data queries to the analytical DB are performed by the Cloud Trino service, therefore deploying a Data Lakehouse configuration with an analytical DB includes pre-deploying the basic architecture of the service, as well as additionally connecting the used Cloud Trino service instance to the analytical DB.
Deploy the basic architecture of the Data Lakehouse service.
-
Go to the Data Platform → Service instances section.
-
Click the Create instance button.
-
At the Configuration step:
- Select the
Cloud ClickHouseservice type. - In the Version field, leave the default value.
- Select the
Clusterconfiguration.
- Select the
-
Click the Next step button.
-
At the Parameters step:
-
Set the instance parameters:
- Name: specify a name for the service instance.
- Description: add a description if necessary.
- Database name: specify the name of the database that Cloud ClickHouse will connect to.
- Assign external IP: enable the option so that the Cloud ClickHouse instance is accessible via the Internet.
- Kubernetes cluster: select from the drop-down list the cluster that you specified when creating the Cloud Iceberg Metastore service instance at the stage of deploying the basic configuration.
-
Select the
Basiccluster configuration and specify one of the configuration templates:Small with replication– suitable for a test configuration.Medium with replication– for a low-load PROD cluster.2-shard with replication– for a medium-load PROD cluster.3-shard with replication– for a high-load PROD cluster.
-
-
Click the Next step button.
-
At the Maintenance step:
-
Select the days of the week and the start time of maintenance, considering the time zone specified in the block.
The duration of maintenance, including backup, is 4 hours. During this time, the service may be unavailable.
-
Set the maximum number of full backups. This number depends on the data archival retention requirements or may be dictated by regulatory requirements. When the maximum number is exceeded, older backups will be overwritten.
A full backup includes files, databases, settings, and configurations necessary to restore the system in case of failure, data loss, or other issues.
-
Set the incremental backup schedule in crontab format or select an existing one from the list.
An incremental backup contains only the data that has changed since the previous backup run (full or incremental). This backup is smaller in size than a full one.
-
Set the maximum number of incremental backups. When the maximum number is exceeded, older backups will be overwritten.
-
-
Click the Next step button.
-
At the Credentials step:
-
In the Login field, leave the default value. Write down the administrator login for accessing Cloud ClickHouse; you will need it later.
-
In the Password field, click the Generate button to generate an administrator password. Save the password; you will need it later
-
-
Click the Create button.
Wait for the operation of creating the Cloud ClickHouse service instance to complete.
Add a new connection for the Clickhouse source in the settings of the Cloud Trino service instance that was created when deploying the basic configuration. In the connection parameters, specify the data of the Cloud ClickHouse service instance created above.