E-MapReduce:Separate hot data and cold data by using OSS

Prerequisites

Before you begin, make sure you have:

• An EMR ClickHouse cluster running EMR V5.7.0 or later. See Create a ClickHouse cluster

• An OSS bucket in the same region as your cluster

• An AccessKey pair with read and write access to the bucket. See Obtain an AccessKey pair

How it works

Hot/cold separation relies on two ClickHouse storage concepts:

• Storage policy: Defines a sequence of volumes (storage tiers). ClickHouse writes new data to the first volume and moves it to subsequent volumes based on TTL rules or disk usage thresholds.

• TTL rule with `TO VOLUME`: Specifies when data moves to a named volume. Once the TTL expression evaluates to true for a data part, ClickHouse moves it to the target volume.

The move_factor setting controls threshold-based migration: when a volume's free space drops below this fraction of total capacity (default: 0.2, or 20%), ClickHouse begins moving data to the next volume. ClickHouse sorts data parts from largest to smallest and moves enough parts to satisfy the condition. If the total size of all parts is insufficient, all parts are moved.

Step 1: Add an OSS disk

Configure OSS as a disk in the storage_configuration section of your ClickHouse service.

1. Go to the Configure tab for your ClickHouse service.

   1. Log in to the EMR on ECS console.

   2. In the top navigation bar, select the region where your cluster resides and select a resource group.

   3. On the EMR on ECS page, find your cluster and click Services in the Actions column.

   4. On the Services tab, click Configure in the ClickHouse section.

2. On the Configure tab, click the server-metrika tab.

3. In storage_configuration, add an OSS disk under disks:

   Placeholder	Description	Example
   ${yourBucketName}	OSS bucket name	clickhouse
   ${yourEndpoint}	OSS bucket endpoint	oss-cn-hangzhou-internal.aliyuncs.com
   ${yourFlieName}	Object path prefix in the bucket	test
   ${yourAccessKeyId}	AccessKey ID	LTAI5tXxx
   ${yourAccessKeySecret}	AccessKey secret	—
   ${yourMetadataPath}	Local path for OSS-to-local file mappings	Defaults to ${path}/disks/<disk_name>/
   ${yourCachePath}	Local cache directory	Defaults to ${path}/disks/<disk_name>/cache/

   Required parameters:

   Parameter	Description
   disk_oss	Disk name. Use any custom name — this name is referenced in your storage policy.
   type	Must be s3. ClickHouse uses the S3-compatible protocol to access OSS.
   endpoint	OSS object URL. Must start with http or https.
   access_key_id	AccessKey ID for your Alibaba Cloud account.
   secret_access_key	AccessKey secret for your Alibaba Cloud account.

   Optional parameters:

   Parameter	Default	Description
   send_metadata	false	Whether to attach metadata to OSS operations.
   metadata_path	${path}/disks/<disk_name>/	Local path storing the mapping between local files and OSS objects. <disk_name> corresponds to the value of disk_oss.
   cache_enabled	true	Enables local caching of OSS data. See Local cache behavior.
   cache_path	${path}/disks/<disk_name>/cache/	Local directory for cached data.
   skip_access_check	false	If true, skips the read/write permission check when the disk loads.
   min_bytes_for_seek	1048576	Minimum bytes required to use a seek operation. Below this threshold, ClickHouse skips instead of seeking.
   thread_pool_size	16	Thread pool size for processing restore requests on this disk.
   list_object_keys_size	1000	Maximum number of objects listed in an object directory at once.

   <disk_oss>
       <type>s3</type>
       <endpoint>http(s)://${yourBucketName}.${yourEndpoint}/${yourFlieName}</endpoint>
       <access_key_id>${yourAccessKeyId}</access_key_id>
       <secret_access_key>${yourAccessKeySecret}</secret_access_key>
       <send_metadata>false</send_metadata>
       <metadata_path>${yourMetadataPath}</metadata_path>
       <cache_enabled>true</cache_enabled>
       <cache_path>${yourCachePath}</cache_path>
       <skip_access_check>false</skip_access_check>
       <min_bytes_for_seek>1048576</min_bytes_for_seek>
       <thread_pool_size>16</thread_pool_size>
       <list_object_keys_size>1000</list_object_keys_size>
   </disk_oss>

   Replace the placeholders with your actual values: For example: <endpoint>http://clickhouse.oss-cn-hangzhou-internal.aliyuncs.com/test</endpoint>

4. Add a storage policy under policies:

   You can also add the remote volume to the existing default storage policy instead of creating a new oss_ttl policy.

   <oss_ttl>
       <volumes>
         <local>
           <!-- Include all disks that use the default storage policy. -->
           <disk>disk1</disk>
           <disk>disk2</disk>
           <disk>disk3</disk>
           <disk>disk4</disk>
         </local>
         <remote>
           <disk>disk_oss</disk>
         </remote>
       </volumes>
       <move_factor>0.2</move_factor>
   </oss_ttl>

5. Save the configuration.

   1. Click Save in the upper-right corner of the Service Configuration section.

   2. In the Confirm Changes dialog, fill in Description, turn on Auto-update Configuration, and click OK.

6. Deploy the client configuration.

   1. On the Configure tab, click Deploy Client Configuration.

   2. In the Cluster Activities dialog, fill in Description and click OK.

   3. In the confirmation message, click OK.

Step 2: Verify the configuration

After deploying, confirm that the OSS disk and storage policy are active.

1. Log in to a core node via SSH. See Log on to a cluster.

2. Start the ClickHouse client:

   core-1-1 is the name of the core node you logged in to. If your cluster has multiple core nodes, log in to any one of them.

   clickhouse-client -h core-1-1 -m

3. Check that the OSS disk appears in the disk list:

   SELECT name, type FROM system.disks;

   Confirm that disk_oss appears with type = oss:

   ┌─name─────┬─type──┐
   │ default  │ local │
   │ disk1    │ local │
   │ disk2    │ local │
   │ disk3    │ local │
   │ disk4    │ local │
   │ disk_oss │ oss   │
   └──────────┴───────┘

4. Check that the oss_ttl storage policy is active:

   SELECT policy_name, volume_name, disks FROM system.storage_policies;

   Confirm that oss_ttl appears with volumes local and remote:

   ┌─policy_name─┬─volume_name─┬─disks──────────────────────────────┐
   │ default     │ single      │ ['disk1','disk2','disk3','disk4']   │
   │ oss_ttl     │ local       │ ['disk1','disk2','disk3','disk4']   │
   │ oss_ttl     │ remote      │ ['disk_oss']                        │
   └─────────────┴─────────────┴────────────────────────────────────┘

Step 3: Apply hot/cold separation

Choose the approach that matches your situation.

CREATE TABLE <yourDatabaseName>.<yourTableName> [ON CLUSTER cluster_emr]
(
  column1 Type1,
  column2 Type2,
  ...
) ENGINE = MergeTree()  -- or Replicated*MergeTree()
PARTITION BY <yourPartitionKey>
ORDER BY <yourSortKey>
TTL <yourTtlKey> TO VOLUME 'remote'
SETTINGS storage_policy = 'oss_ttl';

CREATE TABLE test.test
(
    `id`  UInt32,
    `t`   DateTime
)
ENGINE = MergeTree()
PARTITION BY toStartOfFiveMinute(t)
ORDER BY id
TTL toStartOfMinute(addMinutes(t, 5)) TO VOLUME 'remote'
SETTINGS storage_policy = 'oss_ttl';

Advanced configuration

What's next

• Create a ClickHouse cluster

• Log on to a cluster

• Obtain an AccessKey pair