Configuring Managed Service for ClickHouse® for Graphite

You can use Yandex Managed Service for ClickHouse® to store Graphite data.

The GraphiteMergeTree table engine is designed for thinning and aggregating/averaging Graphite data. The engine reduces storage volume and improves the efficiency of queries from Graphite.

To configure a database to work with Graphite:

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

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

Set up your environmentSet up your environment

Sign up for Yandex Cloud and create a billing account:

1. Navigate to the management console and log in to Yandex Cloud or create 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.

Learn more about clouds and folders here.

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 managing the database (see Yandex Compute Cloud pricing).

Create a clusterCreate a cluster

1. Create a Managed Service for ClickHouse® cluster with your preferred 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 navigate to your new cluster. Save the cluster ID from the Overview tab.
3. In the top-right corner, click Connect. In the Shell tab, save the --host value from the Sample connection string field, e.g., rc1a-2sqal8f0********.mdb.yandexcloud.net. This is the cluster host’s FQDN you will need later on.

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

Register the rollup configuration in your cluster for thinning, then aggregating or averaging Graphite data:

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

Create and configure a virtual machineCreate and configure a virtual machine

1. Create a Linux VM in your cluster’s cloud network.

2. Connect to the VM over SSH.

3. Add 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 application:

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

5. Download the clickhouse-client configuration file:

   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 your virtual machine to the databaseConnect your virtual machine to the database

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

2. Connect.

3. Run the ClickHouse® CLI, replacing the following placeholders with your saved values: <host_FQDN>, <DB_name>, <DB_user_name>, and <DB_user_password>.

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

Create a table using the GraphiteMergeTree engineCreate a table using the GraphiteMergeTree engine

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 store metrics in your ClickHouse® cluster. The ClickHouse® server will automatically handle data thinning based on your configured retention rules.

1. Install carbon-clickhouse utility on your Graphite server:

   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 graphite-clickhouse:

   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 on configuring Graphite, see these guides.

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

To remove the rollup configuration from your 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 this 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.