Configuring Managed Service for ClickHouse® for Graphite

Yandex Managed Service for ClickHouse® can be used as data storage for Graphite.

The GraphiteMergeTree table engine enables you to decimate and aggregate or average out the contents of a database specifically for Graphite. The engine reduces the data storage volume and improves the efficiency of queries from Graphite.

To configure a database to work with Graphite:

1. Prepare the environment.
2. Create a cluster.
3. Register the rollup configuration in a cluster.
4. Create and set up a virtual machine.
5. Connect the virtual machine to a database.
6. Create a GraphiteMergeTree table.
7. Configure Graphite.

If you no longer need the resources you created, delete them.

Prepare the environmentPrepare the environment

Sign up in Yandex Cloud and create a billing account:

1. Navigate to the management console and log in to Yandex Cloud or register a new account.
2. On the Yandex Cloud Billing page, make sure you have a billing account linked and it has the ACTIVE or TRIAL_ACTIVE status. If you do not have a billing account, create one and link a cloud to it.

If you have an active billing account, you can navigate to the cloud page to create or select a folder for your infrastructure to operate in.

Learn more about clouds and folders.

Required paid resourcesRequired paid resources

The cost of maintaining your ClickHouse® database for Graphite includes:

• Fee for the cluster computing resources, storage, and backups (see Yandex Managed Service for ClickHouse® pricing).
• Fee for a running VM to manage a database (see Yandex Compute Cloud pricing).

Create a clusterCreate a cluster

1. Create a Managed Service for ClickHouse® cluster in any suitable configuration with the db1 database and public access to all of its hosts. Save the database name, username, and password.
2. In the management console, select Managed Service for ClickHouse® and go to the new cluster. Save the cluster ID in the Overview tab.
3. In the top-right corner, click Connect. In the Shell tab, save the --host parameter from the Sample connection string field, e.g., rc1a-2sqal8f0********.mdb.yandexcloud.net. This is the cluster host FQDN you will need later on.

Register the rollup configuration in a clusterRegister the rollup configuration in a cluster

Register the rollup configuration in your cluster to decimate and aggregate or average out the database contents for Graphite:

"graphiteRollup": [
          {
            "name": "test_rollup",
            "patterns": [
              {
                "regexp": "click_cost",
                "function": "max",
                "retention": [
                  {
                    "age": "86400",
                    "precision": "60"
                  }
                ]
              }
            ]
          }
        ]

Create and set up a virtual machineCreate and set up a virtual machine

1. In the same cloud network where the cluster is located, create a VM on Linux.

2. Connect to the VM via SSH.

3. Connect the ClickHouse® DEB repository:

   sudo apt update && sudo apt install -y apt-transport-https ca-certificates dirmngr && \
   sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4 && \
   echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee \
   /etc/apt/sources.list.d/clickhouse.list
   

4. Install the dependencies and the clickhouse-client client application:

   sudo apt update && sudo apt install -y clickhouse-client
   

5. Download the configuration file for clickhouse-client:

   mkdir -p ~/.clickhouse-client && \
   wget "https://storage.yandexcloud.net/doc-files/clickhouse-client.conf.example" \
     --output-document ~/.clickhouse-client/config.xml
   

6. Get an SSL certificate:

   sudo mkdir --parents /usr/local/share/ca-certificates/Yandex/ && \
   sudo wget "https://storage.yandexcloud.net/cloud-certs/RootCA.pem" \
        --output-document /usr/local/share/ca-certificates/Yandex/RootCA.crt && \
   sudo chmod 655 /usr/local/share/ca-certificates/Yandex/RootCA.crt && \
   sudo update-ca-certificates
   

Connect the virtual machine to a databaseConnect the virtual machine to a database

1. If you are using security groups for a cloud network, configure them to allow all required traffic between the cluster and the VM.

2. Connect.

3. Run the ClickHouse® CLI with the following parameters: replace <host_FQDN>, <DB_name>, <DB_username>, and <DB_user_password> with the previously saved values.

   clickhouse-client --host <host_FQDN> \
                     --secure \
                     --user <DB_username> \
                     --database <DB_name> \
                     --password <DB_user_password> \
                     --port 9440
   

Create a GraphiteMergeTree tableCreate a GraphiteMergeTree table

CREATE TABLE GraphiteTable
(        
    Path String, 
    Time DateTime, 
    Value Int64, 
    Version UInt64
)
ENGINE = GraphiteMergeTree('test_rollup')
PARTITION BY Time
ORDER BY cityHash64(Version, Path)

Configure GraphiteConfigure Graphite

Configure Graphite to save metrics in the ClickHouse® cluster. In this case, data is decimated automatically by the ClickHouse® server according to the parameters you specified.

1. Install the carbon-clickhouse utility in the system running Graphite:

   wget https://github.com/go-graphite/carbon-clickhouse/releases/download/v0.11.2/carbon-clickhouse_0.11.2_amd64.deb && \
   sudo apt-get install $(pwd)/carbon-clickhouse_0.11.2_amd64.deb
   

2. Configure carbon-clickhouse.

3. Run carbon-clickhouse:

   sudo systemctl enable carbon-clickhouse && \
   sudo systemctl start carbon-clickhouse
   

4. Install the graphite-clickhouse utility:

   wget https://github.com/go-graphite/graphite-clickhouse/releases/download/v0.13.2/graphite-clickhouse_0.13.2_amd64.deb && \
   sudo apt-get install $(pwd)/graphite-clickhouse_0.13.2_amd64.deb
   

5. Configure graphite-clickhouse.

6. Run graphite-clickhouse:

   sudo systemctl enable graphite-clickhouse && \
   sudo systemctl start graphite-clickhouse
   

   For more information about configuring Graphite, see the relevant documentation.

How to delete the resources you createdHow to delete the resources you created

To remove the rollup configuration from a cluster:

1. Delete all the tables that use this configuration.
2. Run the yc managed-clickhouse cluster remove-graphite-rollup command.

For more information about the command, see the CLI reference.

Delete the resources you no longer need to avoid paying for them:

• Delete the ClickHouse® cluster.
• Delete the VM.

ClickHouse® is a registered trademark of ClickHouse, Inc.