Creating a ClickHouse® cluster

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To create a Managed Service for ClickHouse® cluster:

1. Check whether the folder has any subnets for the cluster hosts:

   yc vpc subnet list
   

   If there are no subnets in the folder, create the required subnets in VPC.

2. View the description of the create cluster CLI command:

   yc managed-clickhouse cluster create --help
   

3. Specify cluster parameters in the create command (the list of supported parameters in the example is not exhaustive):

   yc managed-clickhouse cluster create \
     --name <cluster_name> \
     --environment <environment> \
     --network-name <network_name> \
     --host type=<host_type>,`
          `zone-id=<availability_zone>,`
          `subnet-id=<subnet_ID>,`
          `assign-public-ip=<public_access_to_host> \
     --clickhouse-resource-preset <host_class> \
     --clickhouse-disk-type <network-hdd|network-ssd|network-ssd-nonreplicated|local-ssd> \
     --clickhouse-disk-size <storage_size_in_GB> \
     --user name=<username>,password=<user_password> \
     --database name=<DB_name> \
     --security-group-ids <list_of_security_group_IDs> \
     --websql-access=<true_or_false> \
     --deletion-protection
   

   You need to specify the subnet-id if the selected availability zone has two or more subnets.

   Where:

   • --environment: Cluster environment, prestable or production.

   • --host: Host parameters:

     • type: Host type: clickhouse or zookeeper.
     • zone-id: Availability zone.
     • assign-public-ip: Internet access to the host via a public IP address, true or false.

   • --clickhouse-disk-type: Disk type.

     Warning

     You cannot change disk type after you create a cluster.

   • --websql-access: Enables SQL queries against cluster databases from the Yandex Cloud management console using Yandex WebSQL. The default value is false.

   • --deletion-protection: Cluster deletion protection.

   Enabled deletion protection will not prevent a manual connection with the purpose to delete database contents.

   You can manager cluster users and databases via SQL.

   Alert

   You can't disable activated settings to manage users and databases via SQL. You can enable these as required later when editing cluster settings.

   1. To enable SQL user management:

      • set --enable-sql-user-management to true.
      • Set a password for the admin user in the --admin-password parameter.

      yc managed-clickhouse cluster create \
        ...
        --enable-sql-user-management true \
        --admin-password "<admin_user_password>"
      

   2. To enable SQL database management:

      • Set --enable-sql-user-management and --enable-sql-database-management to true;
      • Set a password for the admin user in the --admin-password parameter.

      yc managed-clickhouse cluster create \
        ...
        --enable-sql-user-management true \
        --enable-sql-database-management true \
        --admin-password "<admin_user_password>"
      

   3. To allow access to the cluster from Yandex Cloud Functions, provide the --serverless-access parameter. For more information about setting up access, see the Cloud Functions documentation.

   4. To allow access to the cluster from Yandex Query, provide the --yandexquery-access=true parameter. This feature is at the Preview stage.

   5. To enable ClickHouse® Keeper in a cluster, set the --embedded-keeper parameter to true.

      yc managed-clickhouse cluster create \
        ...
        --embedded-keeper true
      

      Alert

      You can't disable ClickHouse® Keeper after you create a cluster. ZooKeeper hosts will also become unavailable.

   6. To configure hybrid storage settings:

      • Set the --cloud-storage parameter to true to enable hybrid storage.

        Note

        Once enabled, hybrid storage cannot be disabled.

      • Provide the hybrid storage settings in the relevant parameters:

        • --cloud-storage-data-cache: Allows you to cache files in cluster storage. This setting is enabled by default (set to true).
        • --cloud-storage-data-cache-max-size: Sets the maximum cache size (in bytes) allocated in cluster storage for files. The default value is 1073741824 (1 GB).
        • --cloud-storage-move-factor: Sets the minimum share of free space in cluster storage. If the actual value is less than this setting value, the data is moved to Yandex Object Storage. The minimum value is 0, the maximum one is 1, and the default one is 0.01.
        • --cloud-storage-prefer-not-to-merge: Disables data part merges in cluster and object storage. To disable merges, set the parameter to true or provide it with no value. To keep merges enabled, set the parameter to false or do not provide it in the CLI command when creating a cluster.

      yc managed-clickhouse cluster create \
         ...
         --cloud-storage=true \
         --cloud-storage-data-cache=<file_storage> \
         --cloud-storage-data-cache-max-size=<memory_size_in_bytes> \
         --cloud-storage-move-factor=<percentage_of_free_space> \
         --cloud-storage-prefer-not-to-merge=<merge_data_parts>
        ...
      

      Where:

      • --cloud-storage-data-cache: Store files in cluster storage, true or false.
      • --cloud-storage-prefer-not-to-merge: Disables merging of data parts in cluster and object storage, true or false.

Terraform allows you to quickly create a cloud infrastructure in Yandex Cloud and manage it using configuration files. Configuration files store the infrastructure description in the HashiCorp Configuration Language (HCL). Terraform and its providers are distributed under the Business Source License.

For more information about the provider resources, see the documentation on the Terraform website or the mirror.

If you change the configuration files, Terraform automatically detects which part of your configuration is already deployed, and what should be added or removed.

To create a Managed Service for ClickHouse® cluster:

1. Using the command line, navigate to the folder that will contain the Terraform configuration files with an infrastructure plan. Create the directory if it does not exist.

2. If you don't have Terraform, install it and configure the Yandex Cloud provider.

3. Create a configuration file describing the cloud network and subnets.

   • Network: Description of the cloud network where the cluster will be hosted. If you already have a suitable network, you do not need to describe it again.
   • Subnets: Subnets to connect the cluster hosts to. If you already have suitable subnets, you do not need to describe them again.

   Example structure of a configuration file that describes a cloud network with a single subnet:

   resource "yandex_vpc_network" "<network_name_in_Terraform>" { name = "<network_name>" }
   
   resource "yandex_vpc_subnet" "<subnet_name_in_Terraform>" {
     name           = "<subnet_name>"
     zone           = "<availability_zone>"
     network_id     = yandex_vpc_network.<network_name_in_Terraform>.id
     v4_cidr_blocks = ["<subnet>"]
   }
   

4. Create a configuration file with a description of the cluster and its hosts.

   • Database cluster: Description of the cluster and its hosts. Also as required here:

     • Specify DBMS settings. You can specify them later.

       Using the Yandex Cloud interfaces, you can manage a limited number of settings. Using SQL queries, you can apply ClickHouse® settings at the query level.

     • Enable deletion protection.

       Enabled deletion protection will not prevent a manual connection with the purpose to delete database contents.

   Example structure of a configuration file that describes a cluster with a single host:

   resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
     name                = "<cluster_name>"
     environment         = "<environment>"
     network_id          = yandex_vpc_network.<network_name_in_Terraform>.id
     security_group_ids  = ["<list_of_security_group_IDs>"]
     deletion_protection = <cluster_deletion_protection>
   
     clickhouse {
       resources {
         resource_preset_id = "<host_class>"
         disk_type_id       = "<disk_type>"
         disk_size          = <storage_size_in_GB>
       }
     }
   
     database {
       name = "<DB_name>"
     }
   
     user {
       name     = "<DB_user_name>"
       password = "<password>"
       permission {
         database_name = "<name_of_DB_to_create_user_in>"
       }
     }
   
     host {
       type             = "CLICKHOUSE"
       zone             = "<availability_zone>"
       subnet_id        = yandex_vpc_subnet.<subnet_name_in_Terraform>.id
       assign_public_ip = <public_access_to_host>
     }
   }
   

   Enabled deletion protection will not prevent a manual connection with the purpose to delete database contents.

   1. To set up the maintenance window (for disabled clusters as well), add the maintenance_window block to the cluster description:

      resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
        ...
        maintenance_window {
          type = <maintenance_type>
          day  = <day_of_week>
          hour = <hour>
        }
        ...
      }
      

      Where:

      • type: Maintenance type. The possible values include:
        • anytime: Anytime.
        • weekly: By schedule.
      • day: Day of the week for the weekly type in DDD format, e.g., MON.
      • hour: Hour of the day for the weekly type in the HH format, e.g., 21.

   2. To enable access from other services and allow running SQL queries from the management console using Yandex WebSQL, add a section named access with the settings you need:

      resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
        ...
        access {
          data_lens    = <access_from_DataLens>
          metrika      = <access_from_Metrica_and_AppMetrica>
          serverless   = <access_from_Cloud_Functions>
          yandex_query = <access_from_Yandex_Query>
          web_sql      = <run_SQL_queries_from_management_console>
        }
        ...
      }
      

      Where:

      • data_lens: Access from DataLens, true or false.

      • metrika: Access from Yandex Metrica and AppMetrica, true or false.

      • serverless: Access from Cloud Functions, true or false.

      • yandex_query: Access from Yandex Query, true or false.

      • web_sql: Execution of SQL queries from the management console, true or false.

   3. You can manager cluster users and databases via SQL.

      Alert

      You can't disable activated settings to manage users and databases via SQL. You can enable these as required later when editing cluster settings.

      • To enable user management via SQL, expand the cluster description to include a sql_user_management field set to true and an admin_password field containing the password for the admin account:

        resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
          name                = "<cluster_name>"
          ...
          admin_password      = "<admin_password>"
          sql_user_management = true
          ...
        }
        

      • To enable database management via SQL, expand the cluster description to include a sql_user_management field and a sql_database_management field, both set to true, as well as the admin_password field containing the password for the admin account:

        resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
          name                    = "<cluster_name>"
          ...
          admin_password          = "<admin_password>"
          sql_database_management = true
          sql_user_management     = true
          ...
        }
        

   For more information about the resources you can create with Terraform, see the provider documentation.

5. Check that the Terraform configuration files are correct:

   1. Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.

   2. Run the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

6. Create a cluster:

   1. Run the command to view planned changes:

      terraform plan
      

      If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

   2. If you are happy with the planned changes, apply them:

      1. Run the command:

         terraform apply
         

      2. Confirm the update of resources.

      3. Wait for the operation to complete.

   All the required resources will be created in the specified folder. You can check resource availability and their settings in the management console.

resource "yandex_mdb_clickhouse_cluster" "<cluster_name>" {
  ...
  timeouts {
    create = "1h30m" # 1 hour 30 minutes
    update = "2h"    # 2 hours
    delete = "30m"   # 30 minutes
  }
}

To create a Managed Service for ClickHouse® cluster, use the create REST API method for the Cluster resource or the ClusterService/Create gRPC API call and provide the following in the request:

• ID of the folder to host the cluster, in the folderId parameter.

• Cluster name in the name parameter.

• Cluster environment in the environment parameter.

• Cluster configuration in the configSpec parameter.

• Configuration of the cluster hosts in one or more hostSpecs parameters.

• Network ID in the networkId parameter.

• Security group IDs in the securityGroupIds parameter.

• Settings for access from other services in the configSpec.access parameter.

To allow connection to cluster hosts from the internet, provide the true value in the hostSpecs.assignPublicIp parameter.

Enable user and database management via SQL, if required:

• configSpec.sqlUserManagement: Set to true to enable user management through SQL.
• configSpec.sqlDatabaseManagement: Set to true to enable database management through SQL. For that, you also need to enable user management through SQL.
• configSpec.adminPassword: Set a password for the admin account used for management.

To configure hybrid storage settings:

• Set true for the configSpec.cloudStorage.enabled parameter to enable hybrid storage.

• Provide hybrid storage settings in the configSpec.cloudStorage parameters:

  • configSpec.cloudStorage.dataCacheEnabled: Allows you to cache files in cluster storage. This setting is enabled by default (set to true).
  • configSpec.cloudStorage.dataCacheMaxSize: Sets the maximum cache size (in bytes) allocated in cluster storage for files. The default value is 1073741824 (1 GB).
  • configSpec.cloudStorage.moveFactor: Sets the minimum share of free space in cluster storage. If the actual value is less than this setting value, the data is moved to Yandex Object Storage. The minimum value is 0, the maximum one is 1, and the default one is 0.01.
  • configSpec.cloudStorage.preferNotToMerge: Disables data part merges in cluster and object storage. To disable merges, set it to true. To keep merges enabled, set the parameter to false or do not provide it in your API request when creating a cluster.

When creating a cluster with multiple hosts:

• If embeddedKeeper is true, replication will be managed using ClickHouse® Keeper.

  Alert

  You can't disable ClickHouse® Keeper after you create a cluster. ZooKeeper hosts will also become unavailable.

• If embeddedKeeper is undefined or false, replication and query distribution will be managed using ZooKeeper.

  If the cluster cloud network has subnets in each availability zone, and ZooKeeper host settings are not specified, one such host will automatically be added to each subnet.

  If only some availability zones in the cluster's network have subnets, explicitly specify the ZooKeeper host settings.

Create a Managed Service for ClickHouse® cluster and a network for it with the following test specifications:

• Name: mych.

• Environment: PRESTABLE.

• Cloud ID: b1gq90dgh25bebiu75o.

• Folder ID: b1gia87mbaomkfvsleds.

• New cloud network: cluster-net.

• New default security group: cluster-sg (in the cluster-net network). It must allow connections to any cluster host from any network (including the internet) on ports 8443 and 9440.

• Number of s2.micro class hosts in a new subnet named cluster-subnet-ru-central1-a: 1.

  Subnet parameters:

  • Address range: 172.16.1.0/24.
  • Network: cluster-net.
  • Availability zone: ru-central1-a.

• Network SSD storage (network-ssd): 32 GB.

• Database name: db1.

• User: user1, with password user1user1.

The configuration files for this cluster are as follows:

1. Configuration file with a description of provider settings:

   provider.tf

   terraform {
     required_providers {
       yandex = {
         source = "yandex-cloud/yandex"
       }
     }
   }
   
   provider "yandex" {
     token     = "<service_account_OAuth_or_static_key>"
     cloud_id  = "b1gq90dgh25bebiu75o"
     folder_id = "b1gia87mbaomkfvsleds"
   }
   

   To get an OAuth token or a static access key, see the Yandex Identity and Access Management instructions.

2. Configuration file with a description of the cloud network and subnet:

   networks.tf

   resource "yandex_vpc_network" "cluster-net" { name = "cluster-net" }
   
   resource "yandex_vpc_subnet" "cluster-subnet-a" {
     name           = "cluster-subnet-ru-central1-a"
     zone           = "ru-central1-a"
     network_id     = yandex_vpc_network.cluster-net.id
     v4_cidr_blocks = ["172.16.1.0/24"]
   }
   

3. Configuration file with a description of the security group:

   security-groups.tf

   resource "yandex_vpc_default_security_group" "cluster-sg" {
     network_id = yandex_vpc_network.cluster-net.id
   
     ingress {
       description    = "HTTPS (secure)"
       port           = 8443
       protocol       = "TCP"
       v4_cidr_blocks = ["0.0.0.0/0"]
     }
   
     ingress {
       description    = "clickhouse-client (secure)"
       port           = 9440
       protocol       = "TCP"
       v4_cidr_blocks = ["0.0.0.0/0"]
     }
   
     egress {
       description    = "Allow all egress cluster traffic"
       protocol       = "TCP"
       v4_cidr_blocks = ["0.0.0.0/0"]
     }
   }
   

4. Configuration file with a description of the cluster and cluster host:

   cluster.tf

   resource "yandex_mdb_clickhouse_cluster" "mych" {
     name               = "mych"
     environment        = "PRESTABLE"
     network_id         = yandex_vpc_network.cluster-net.id
     security_group_ids = [yandex_vpc_default_security_group.cluster-sg.id]
   
     clickhouse {
       resources {
         resource_preset_id = "s2.micro"
         disk_type_id       = "network-ssd"
         disk_size          = 32
       }
     }
   
     host {
       type      = "CLICKHOUSE"
       zone      = "ru-central1-a"
       subnet_id = yandex_vpc_subnet.cluster-subnet-a.id
     }
   
     database {
       name = "db1"
     }
   
     user {
       name     = "user1"
       password = "user1user1"
       permission {
         database_name = "db1"
       }
     }
   }