[Yandex Cloud documentation](../../index.md) > [Tutorials](../index.md) > [Building a data platform](index.md) > Using hybrid storage in Managed Service for ClickHouse®

# Using hybrid storage in Yandex Managed Service for ClickHouse®

# Using hybrid storage in Managed Service for ClickHouse®


Hybrid storage enables you to store frequently used data on the Managed Service for ClickHouse® cluster’s network drives while keeping rarely used data in Yandex Object Storage. Your hybrid storage will automatically create a bucket and connect it to ClickHouse®. Automatically moving data between these storage tiers is only supported for [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) tables. For more information, see [Storage in Managed Service for ClickHouse®](../../managed-clickhouse/concepts/storage.md).

To use hybrid storage:

1. [Create a table](#create-table).
1. [Populate the table with data](#fill-table-with-data).
1. [Check data placement within your cluster](#check-table-tiering).
1. [Run a test query](#submit-test-query).

If you no longer need the resources you created, [delete them](#clear-out).


## Required paid resources {#paid-resources}

The support cost for this solution includes:

* Managed Service for ClickHouse® cluster fee: use of computing resources allocated to hosts (including ZooKeeper hosts) and disk space (see [Managed Service for ClickHouse® pricing](../../managed-clickhouse/pricing.md)).
* Fee for using public IP addresses if public access is enabled for cluster hosts (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).


## Getting started {#before-you-begin}

### Set up your infrastructure {#deploy-infrastructure}

{% list tabs group=instructions %}

- Manually {#manual}

    1. [Create a Managed Service for ClickHouse® cluster](../../managed-clickhouse/operations/cluster-create.md):

        
        * **Disk type**: Standard (`network-hdd`), fast (`network-ssd`), or non-replicated (`network-ssd-nonreplicated`) network disks.


        * **Size**: At least 15 GB.
        * **User management via SQL**: Disabled.
        * **DB name**: `tutorial`.
        * **Hybrid storage**: Enabled.

    1. [Configure access permissions](../../managed-clickhouse/operations/cluster-users.md#update-settings) to allow read and write queries to your database.

- Using Terraform {#tf}

    1. If you do not have Terraform yet, [install it](../infrastructure-management/terraform-quickstart.md#install-terraform).
    1. [Get the authentication credentials](../infrastructure-management/terraform-quickstart.md#get-credentials). You can add them to environment variables or specify them later in the provider configuration file.
    1. [Configure and initialize a provider](../infrastructure-management/terraform-quickstart.md#configure-provider). There is no need to create a provider configuration file manually, you can [download it](https://github.com/yandex-cloud-examples/yc-terraform-provider-settings/blob/main/provider.tf).
    1. Place the configuration file in a separate working directory and [specify the parameter values](../infrastructure-management/terraform-quickstart.md#configure-provider). If you did not add the authentication credentials to environment variables, specify them in the configuration file.

    1. Download the [clickhouse-hybrid-storage.tf](https://github.com/yandex-cloud-examples/yc-clickhouse-hybrid-storage/blob/main/clickhouse-hybrid-storage.tf) configuration file to your current working directory.

        This file describes:

        * [Network](../../vpc/concepts/network.md#network).
        * [Subnet](../../vpc/concepts/network.md#subnet).
        * [Default security group](../../vpc/concepts/security-groups.md) and rules enabling connections to the cluster from the internet.
        * Managed Service for ClickHouse® cluster with hybrid storage enabled.

    1. In the `clickhouse-hybrid-storage.tf` file, specify the username and password you will use to access the Managed Service for ClickHouse® cluster.

    1. Make sure the Terraform configuration files are correct using this command:

        ```bash
        terraform validate
        ```

        Terraform will display any configuration errors detected in your files.

    1. Create the required infrastructure:

       1. Run this command to view the planned changes:
       
          ```bash
          terraform plan
          ```
       
          If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.
       
       1. If everything looks correct, apply the changes:
          1. Run this command:
       
             ```bash
             terraform apply
             ```
       
          1. Confirm updating the resources.
          1. 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](https://console.yandex.cloud).

{% endlist %}

### Set up the command line tools {#set-instruments}

1. Install `curl` and `unxz`:

    ```bash
    apt-get update && apt-get install curl xz-utils
    ```

1. [Set up the clickhouse-client](../../managed-clickhouse/operations/connect/clients.md#clickhouse-client) and use it to connect to the database.

### Optionally, explore the test dataset {#explore-dataset}

To demonstrate how the hybrid storage works, we will use anonymized hit data (`hits_v1`) from Yandex Metrica. This [dataset](https://clickhouse.com/docs/enen/getting-started/example-datasets/metrica) contains nearly 9 million hits recorded between March 17, 2014, to March 23, 2014.

When creating the `tutorial.hits_v1` table, we will [configure it](#create-table) to place all recent data (starting from March 21, 2014) in the network storage, while moving older data (from March 17, 2014 to March 20, 2014) to the object storage.

## Create a table {#create-table}

Create the `tutorial.hits_v1` table configured for hybrid storage by running the following SQL query. Replace the `<schema>` placeholder with a table schema from [this ClickHouse® guide](https://clickhouse.com/docs/enen/getting-started/example-datasets/star-schema#create-tables):

```sql
CREATE TABLE tutorial.hits_v1
(
   <schema>
)
ENGINE = MergeTree()
PARTITION BY EventDate
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
TTL EventDate + toIntervalDay(dateDiff('day', toDate('2014-03-20'), now())) TO DISK 'object_storage'
SETTINGS index_granularity = 8192
```

{% cut "Query with a populated schema" %}

```sql
CREATE TABLE tutorial.hits_v1
(
    `WatchID` UInt64,
    `JavaEnable` UInt8,
    `Title` String,
    `GoodEvent` Int16,
    `EventTime` DateTime,
    `EventDate` Date,
    `CounterID` UInt32,
    `ClientIP` UInt32,
    `ClientIP6` FixedString(16),
    `RegionID` UInt32,
    `UserID` UInt64,
    `CounterClass` Int8,
    `OS` UInt8,
    `UserAgent` UInt8,
    `URL` String,
    `Referer` String,
    `URLDomain` String,
    `RefererDomain` String,
    `Refresh` UInt8,
    `IsRobot` UInt8,
    `RefererCategories` Array(UInt16),
    `URLCategories` Array(UInt16),
    `URLRegions` Array(UInt32),
    `RefererRegions` Array(UInt32),
    `ResolutionWidth` UInt16,
    `ResolutionHeight` UInt16,
    `ResolutionDepth` UInt8,
    `FlashMajor` UInt8,
    `FlashMinor` UInt8,
    `FlashMinor2` String,
    `NetMajor` UInt8,
    `NetMinor` UInt8,
    `UserAgentMajor` UInt16,
    `UserAgentMinor` FixedString(2),
    `CookieEnable` UInt8,
    `JavascriptEnable` UInt8,
    `IsMobile` UInt8,
    `MobilePhone` UInt8,
    `MobilePhoneModel` String,
    `Params` String,
    `IPNetworkID` UInt32,
    `TraficSourceID` Int8,
    `SearchEngineID` UInt16,
    `SearchPhrase` String,
    `AdvEngineID` UInt8,
    `IsArtifical` UInt8,
    `WindowClientWidth` UInt16,
    `WindowClientHeight` UInt16,
    `ClientTimeZone` Int16,
    `ClientEventTime` DateTime,
    `SilverlightVersion1` UInt8,
    `SilverlightVersion2` UInt8,
    `SilverlightVersion3` UInt32,
    `SilverlightVersion4` UInt16,
    `PageCharset` String,
    `CodeVersion` UInt32,
    `IsLink` UInt8,
    `IsDownload` UInt8,
    `IsNotBounce` UInt8,
    `FUniqID` UInt64,
    `HID` UInt32,
    `IsOldCounter` UInt8,
    `IsEvent` UInt8,
    `IsParameter` UInt8,
    `DontCountHits` UInt8,
    `WithHash` UInt8,
    `HitColor` FixedString(1),
    `UTCEventTime` DateTime,
    `Age` UInt8,
    `Sex` UInt8,
    `Income` UInt8,
    `Interests` UInt16,
    `Robotness` UInt8,
    `GeneralInterests` Array(UInt16),
    `RemoteIP` UInt32,
    `RemoteIP6` FixedString(16),
    `WindowName` Int32,
    `OpenerName` Int32,
    `HistoryLength` Int16,
    `BrowserLanguage` FixedString(2),
    `BrowserCountry` FixedString(2),
    `SocialNetwork` String,
    `SocialAction` String,
    `HTTPError` UInt16,
    `SendTiming` Int32,
    `DNSTiming` Int32,
    `ConnectTiming` Int32,
    `ResponseStartTiming` Int32,
    `ResponseEndTiming` Int32,
    `FetchTiming` Int32,
    `RedirectTiming` Int32,
    `DOMInteractiveTiming` Int32,
    `DOMContentLoadedTiming` Int32,
    `DOMCompleteTiming` Int32,
    `LoadEventStartTiming` Int32,
    `LoadEventEndTiming` Int32,
    `NSToDOMContentLoadedTiming` Int32,
    `FirstPaintTiming` Int32,
    `RedirectCount` Int8,
    `SocialSourceNetworkID` UInt8,
    `SocialSourcePage` String,
    `ParamPrice` Int64,
    `ParamOrderID` String,
    `ParamCurrency` FixedString(3),
    `ParamCurrencyID` UInt16,
    `GoalsReached` Array(UInt32),
    `OpenstatServiceName` String,
    `OpenstatCampaignID` String,
    `OpenstatAdID` String,
    `OpenstatSourceID` String,
    `UTMSource` String,
    `UTMMedium` String,
    `UTMCampaign` String,
    `UTMContent` String,
    `UTMTerm` String,
    `FromTag` String,
    `HasGCLID` UInt8,
    `RefererHash` UInt64,
    `URLHash` UInt64,
    `CLID` UInt32,
    `YCLID` UInt64,
    `ShareService` String,
    `ShareURL` String,
    `ShareTitle` String,
    `ParsedParams` Nested(
        Key1 String,
        Key2 String,
        Key3 String,
        Key4 String,
        Key5 String,
        ValueDouble Float64),
    `IslandID` FixedString(16),
    `RequestNum` UInt32,
    `RequestTry` UInt8
)
ENGINE = MergeTree()
PARTITION BY EventDate
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
TTL EventDate + toIntervalDay(dateDiff('day', toDate('2014-03-20'), now())) TO DISK 'object_storage'
SETTINGS index_granularity = 8192
```

{% endcut %}

{% note info %}

This table will use the `default` [storage policy](../../managed-clickhouse/concepts/storage.md#storage-policies).

{% endnote %}

{#ttl}

The `TTL ...` expression defines the policy for managing aging data:
1. TTL sets the lifetime of a table row. In our example, it is the number of days from the current date to March 20, 2014. 
1. The system manages table data based on the `EventDate` value:
   * Records where the elapsed time, in days, since `EventDate` is less than the TTL value are retained on the network disk storage.
   * Records where the elapsed time since `EventDate` meets or exceeds the TTL value are moved to the object storage, as per the `TO DISK 'object_storage'` policy.

While specifying TTL for hybrid storage is optional, doing so gives you explicit control over what data is moved to Object Storage. Without a defined TTL, data is only moved to the object storage when you run out of space in your network disk storage. For more information, see [Storage in Managed Service for ClickHouse®](../../managed-clickhouse/concepts/storage.md).

{% note info %}

The TTL expression in our example is complex because the test dataset we use requires splitting historical, static data across various storage tiers. For the majority of frequently updated tables, you can use a simple TTL expression, such as `EventDate + INTERVAL 5 DAY`, which moves data to the object storage after a five-day period.

{% endnote %}

Data is moved between the network disk storage and object storage in [parts](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-multiple-volumes), rather than per row. For optimal performance, align your TTL expression with your [partitioning key](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/custom-partitioning-key) to ensure that all rows within a part share the same expiration time. This prevents situations where a data part contains a mix of rows destined for different storage tiers, which can block expired data from moving to the object storage. At a minimum, the TTL expression should use the same columns as the partitioning key, e.g., `EventDate` in the example above.

For more details on configuring TTL, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-ttl).

## Populate the table with data {#fill-table-with-data}

1. Disconnect from the database.
1. Download the test dataset:

   
   ```bash
   curl https://storage.yandexcloud.net/doc-files/managed-clickhouse/hits_v1.tsv.xz | unxz --threads=`nproc` > hits_v1.tsv
   ```


   The downloaded dataset size is about 10 GB.

1. Load this dataset into ClickHouse® using `clickhouse-client`:

   ```bash
   clickhouse-client \
       --host <ClickHouse®_host_FQDN> \
       --secure \
       --user <username> \
       --database tutorial \
       --port 9440 \
       --ask-password \
       --query "INSERT INTO tutorial.hits_v1 FORMAT TSV" \
       --max_insert_block_size=100000 < hits_v1.tsv
   ```

   You can get the host FQDN with the [list of hosts in the cluster](../../managed-clickhouse/operations/hosts.md#list-hosts).

1. Wait for the operation to complete, as importing the data may take some time.

For more information, see [this ClickHouse® guide](https://clickhouse.com/docs/enen/getting-started/example-datasets/star-schema#import-data).

## Check data placement within a cluster {#check-table-tiering}

1. [Connect to the database](../../managed-clickhouse/operations/connect/clients.md#clickhouse-client).
1. Check the location of the table rows:

   ```sql
   SELECT
       table,
       partition,
       name,
       rows,
       disk_name
   FROM system.parts
   WHERE active AND (table = 'hits_v1') AND (database = 'tutorial')
   ```

   Table partitions with `EventDate` older than the [TTL](#ttl) allows should reside on the `object_storage` disk. All other partitions should reside on the `default` disk:

   ```text
   ┌─table───┬─partition──┬─name───────────────┬───rows─┬─disk_name──────┐
   │ hits_v1 │ 2014-03-17 │ 20140317_6_80_2    │ 571657 │ object_storage │
   │ hits_v1 │ 2014-03-17 │ 20140317_86_125_1  │ 287545 │ object_storage │
   │ ...                                                                 │
   │ hits_v1 │ 2014-03-20 │ 20140320_109_145_1 │ 250484 │ object_storage │
   │ hits_v1 │ 2014-03-20 │ 20140320_149_200_1 │ 420081 │ object_storage │
   │ hits_v1 │ 2014-03-21 │ 20140321_3_57_1    │ 612616 │ default        │
   │ hits_v1 │ 2014-03-21 │ 20140321_65_65_0   │  53382 │ default        │
   │ ...                                                                 │
   │ hits_v1 │ 2014-03-23 │ 20140323_191_191_0 │  11145 │ default        │
   │ hits_v1 │ 2014-03-23 │ 20140323_197_197_0 │  98910 │ default        │
   └─────────┴────────────┴────────────────────┴────────┴────────────────┘
   ```

1. Check the number of rows stored on each storage tier:

   ```sql
   SELECT
    sum(rows),
    disk_name
   FROM system.parts
   WHERE active AND (database = 'tutorial') AND (table = 'hits_v1')
   GROUP BY disk_name
   ```
   
   The result will show the distribution of table rows across the storage tiers:
   
   ```text
   ┌─sum(rows)─┬─disk_name──────┐
   │   2711246 │ default        │
   │   6162652 │ object_storage │
   └───────────┴────────────────┘
   ```
   
As you can see, the SQL results confirm the successful distribution of data across the hybrid storage tiers.

## Run a test query {#submit-test-query}

Run a test query against the `tutorial.hits_v1` table, accessing data from multiple storage tiers simultaneously:

```sql
SELECT
    URLDomain AS Domain,
    AVG(SendTiming) AS AvgSendTiming
FROM tutorial.hits_v1
WHERE (EventDate >= '2014-03-19') AND (EventDate <= '2014-03-22')
GROUP BY Domain
ORDER BY AvgSendTiming DESC
LIMIT 10
```

Result:

```text
┌─Domain──────────────────────────────┬──────AvgSendTiming─┐
│ realty.ru.msn.com.travel            │ 101166.85714285714 │
│ podbor.ru.msn.com.uazbukatusprosima │  76429.16666666667 │
│ club.metalia-woman                  │ 64872.333333333336 │
│ avito.rusfootki                     │              51099 │
│ papas.drimmirkvart                  │ 50325.642857142855 │
│ apps.oyunuoyna.com.uazbukadelight   │ 32761.666666666668 │
│ voyeurhit                           │          31501.625 │
│ yandex.ru.com.travesti.net          │            31427.5 │
│ sozcu.com.ua.alm.slands             │              29439 │
│ hasters.ru                          │ 18365.666666666668 │
└─────────────────────────────────────┴────────────────────┘
```

As the SQL result shows, the user interacts with a single logical table. ClickHouse® successfully handles the complexity of querying data across different storage tiers.

## Monitor the volume of data in Object Storage (optional step) {#metrics}

To monitor the amount of space [MergeTree](https://clickhouse.com/docs/enen/engines/table-engines/mergetree-family/mergetree) table parts occupy in Object Storage, use the `ch_s3_disk_parts_size` metric in Yandex Monitoring.

1. Open the [management console](https://console.yandex.cloud).
1. Navigate to **Monitoring**.
1. Navigate to the **Metric Explorer** section.
1. Run this query:

    ```text
    "ch_s3_disk_parts_size"{service="managed-clickhouse", resource_type="cluster", node="by_host", resource_id="<cluster_ID>", subcluster_name="clickhouse_subcluster"}
    ```

## Delete the resources you created {#clear-out}

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

{% list tabs group=instructions %}

- Manually {#manual}

    [Delete the Managed Service for ClickHouse® cluster](../../managed-clickhouse/operations/cluster-delete.md).

- Terraform {#tf}

    1. In the terminal window, go to the directory containing the infrastructure plan.
    
        {% note warning %}
    
        Make sure the directory has no Terraform manifests with the resources you want to keep. Terraform deletes all resources that were created using the manifests in the current directory.
    
        {% endnote %}
    
    1. Delete resources:
    
        1. Run this command:
    
            ```bash
            terraform destroy
            ```
    
        1. Confirm deleting the resources and wait for the operation to complete.
    
        All the resources described in the Terraform manifests will be deleted.

{% endlist %}

_ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._