All Products
Search

This Product

    E-MapReduce:Use OSS for hot/cold data separation

    Document Center

    E-MapReduce:Use OSS for hot/cold data separation

    Last Updated:Sep 21, 2026

    This topic describes how to use OSS to implement hot/cold data separation on a ClickHouse cluster in Alibaba Cloud E-MapReduce (EMR). This method lets you automatically manage hot and cold data, maintaining performance while optimizing compute and storage resources to reduce costs.

    Prerequisites

    You have created a ClickHouse cluster running EMR V5.7.0 or later in the E-MapReduce (EMR) console. For more information, see Create a ClickHouse cluster.

    Limitations

    The operations described in this topic apply only to ClickHouse clusters running EMR V5.7.0 or later.

    Procedure

    Step 1: Add a disk via EMR console

    1. Go to the configuration page of the ClickHouse service.

      1. Log on to the EMR on ECS console.

      2. In the top navigation bar, select the region and a resource group.

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

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

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

    3. Modify the parameters of storage_configuration.

      1. In disks, add a disk.

        The following code shows the required configuration.

        <disk_oss>
    <type>s3</type>
    <endpoint>http(s)://${yourBucketName}.${yourEndpoint}/${yourFlieName}</endpoint>
    <access_key_id>${YOUR_ACCESS_KEY_ID}</access_key_id>
    <secret_access_key>${YOUR_ACCESS_KEY_SECRET}</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>

        The following table describes the parameters.

        Parameter

        Required

        Description

        disk_oss

        Yes

        A custom name for the disk.

        type

        Yes

        The disk type. Set this to s3.

        endpoint

        Yes

        The OSS service endpoint. The format is http(s)://${yourBucketName}.${yourEndpoint}/${yourFlieName}.

        Note

        The value of the endpoint parameter must start with http or https. In the format, ${yourBucketName} is the name of your OSS bucket, ${yourEndpoint} is the OSS endpoint, and ${yourFlieName} is the path prefix in OSS. Example: http://clickhouse.oss-cn-hangzhou-internal.aliyuncs.com/test.

        access_key_id

        Yes

        Your Alibaba Cloud account's AccessKey ID.

        For information about how to obtain an AccessKey ID, see Obtain an AccessKey pair.

        secret_access_key

        Yes

        Your Alibaba Cloud account's AccessKey Secret.

        This key encrypts and verifies the OSS signature string. For information about how to obtain an AccessKey Secret, see Obtain an AccessKey pair.

        send_metadata

        No

        Specifies whether to add metadata when you perform operations on OSS files. Valid values:

        • true: Adds metadata.

        • false (default): Does not add metadata.

        metadata_path

        No

        The storage path for mappings between local files and OSS objects.

        Default value: ${path}/disks/<disk_name>/.

        Note

        <disk_name> is the disk name, which is disk_oss in this example.

        cache_enabled

        No

        Specifies whether to enable caching. Valid values:

        • true (default): Enables caching.

        • false: Disables caching.

        The cache works as follows:

        • The cache is used only for local caching of files with the following extensions: .idx, .mrk, .mrk2, .mrk3, .txt, and .dat. Files with other extensions are read directly from OSS instead of from the cache.

        • The local cache has no capacity limit. The maximum capacity is the capacity of the storage disk.

        • The local cache is not cleared by policies such as least recently used (LRU). Instead, it persists for the file's lifecycle.

        • When data is first read, the file is downloaded from OSS to the cache if it is not already present.

        • When data is first written, it is cached locally before being uploaded to OSS.

        • If an object is deleted from OSS, it is also cleared from the local cache. If an object is renamed in OSS, it is also renamed in the local cache.

        cache_path

        No

        The cache path.

        Default value: ${path}/disks/<disk_name>/cache/.

        skip_access_check

        No

        Specifies whether to skip checking for read and write permissions when the disk is loaded. Valid values:

        • true: Skips the check.

        • false (default): Performs the check.

        min_bytes_for_seek

        No

        The minimum number of bytes for a seek operation. If the value is below this threshold, a read-and-skip operation is performed instead of a direct seek. Default value: 1048576.

        thread_pool_size

        No

        The size of the thread pool used by the disk to execute the restore command. Default value: 16.

        list_object_keys_size

        No

        The maximum number of objects returned per list request for a given prefix. Default value: 1000.

      2. Add a new policy in policies.

        The policy content is as follows.

        <oss_ttl>
    <volumes>
      <local>
        <!-- Includes all disks under 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>
        Note

        You can also add this configuration directly to the default policy.

    4. Deploy the client configuration.

      1. On the Configure page of the ClickHouse service, click Deploy Client Configuration.

      2. In the Deploy CLICKHOUSE Client Configuration dialog box, enter a reason for the action and click OK.

      3. In the Confirm dialog box, click OK.

    5. Deploy the client configuration.

      1. On the Configure page of the ClickHouse service, click Deploy Client Configuration.

      2. In the Deploy CLICKHOUSE Client Configuration dialog box, enter a reason for the action and click OK.

      3. In the Confirm dialog box, click OK.

    Step 2: Verify the configuration

    1. Log on to the ClickHouse cluster using SSH. For more information, see Log on to a cluster.

    2. Run the following command to start the ClickHouse client:

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

      This example logs on to the core-1-1 node. If you have multiple core nodes, you can log on to any one of them.

    3. Run the following command to view disk information.

      select * from system.disks;

      Example output:

      ┌─name─────┬─path────────────────────────────────┬───────────free_space─┬──────────total_space─┬─keep_free_space─┬─type──┐
│ default  │ /var/lib/clickhouse/                │          83868921856 │          84014424064 │               0 │ local  │
│ disk1    │ /mnt/disk1/clickhouse/              │          83858436096 │          84003938304 │        10485760 │ local  │
│ disk2    │ /mnt/disk2/clickhouse/              │          83928215552 │          84003938304 │        10485760 │ local  │
│ disk3    │ /mnt/disk3/clickhouse/              │          83928301568 │          84003938304 │        10485760 │ local  │
│ disk4    │ /mnt/disk4/clickhouse/              │          83928301568 │          84003938304 │        10485760 │ local  │
│ disk_oss │ /var/lib/clickhouse/disks/disk_oss/ │ 18446744073709551615 │ 18446744073709551615 │               0 │ oss   │
└──────────┴─────────────────────────────────────┴──────────────────────┴──────────────────────┴─────────────────┴───────┘

    4. Run the following command to view the disk storage policies.

      select * from system.storage_policies;

      Example output:

      ┌─policy_name─┬─volume_name─┬─volume_priority─┬─disks─────────────────────────────┬─volume_type─┬─max_data_part_size─┬─move_factor─┬─prefer_not_to_merge─┐
│ default     │ single      │               1 │ ['disk1','disk2','disk3','disk4']          │JBOD        │                  0 │           0 │                   0 │
│ oss_ttl     │ local       │               1 │ ['disk1','disk2','disk3','disk4']          │JBOD        │                  0 │          0.2 │                   0 │
│ oss_ttl     │ remote      │               2 │ ['disk_oss']                         │JBOD        │                  0 │          0.2 │                   0 │
└─────────────┴─────────────┴─────────────────┴───────────────────────────────────┴─────────────┴────────────────────┴─────────────┴─────────────────────┘

      If your output is similar to the example, the disk is configured correctly.

    Step 3: Implement hot/cold data separation

    Modify an existing table

    1. Run the following command to check the table's current storage policy:

      SELECT
  storage_policy
FROM system.tables
WHERE database='<yourDatabaseName>' AND name='<yourTableName>';

      In this command, <yourDatabaseName> is the database name and <yourTableName> is the table name.

      If the command returns default, the table is using the default policy. You can now extend this policy in the next step.

      <default>
  <volumes>
    <single>
      <disk>disk1</disk>
      <disk>disk2</disk>
      <disk>disk3</disk>
      <disk>disk4</disk>
    </single>
  </volumes>
</default>

    2. Extend the current storage policy.

      In the EMR console, add a remote volume to the default storage policy in the ClickHouse configuration:

      <default>
  <volumes>
    <single>
      <disk>disk1</disk>
      <disk>disk2</disk>
      <disk>disk3</disk>
      <disk>disk4</disk>
    </single>
    <!-- The following is the new remote volume -->
    <remote>
      <disk>disk_oss</disk>
    </remote>
  </volumes>
  <!-- You must specify move_factor when there are multiple volumes -->
  <move_factor>0.2</move_factor>
</default>

    3. Run the following command to set a TTL rule that moves old data to the remote volume:

      ALTER TABLE <yourDataName>.<yourTableName>
  MODIFY TTL toStartOfMinute(addMinutes(t, 5)) TO VOLUME 'remote';

    4. Run the following command to check the distribution of data parts across disks:

      select partition,name,path from system.parts where database='<yourDataName>' and table='<yourTableName>' and active=1

      Example output:

      
┌─partition───────────┬─name─────────────────────┬─path──────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 2022-01-11 19:55:00 │ 1641902100_1_90_3_193    │ /var/lib/clickhouse/disks/disk_oss/store/fc5/fc50a391-4c16-406b-a396-6e1104873f68/1641902100_1_90_3_193/  │
│ 2022-01-11 19:55:00 │ 1641902100_91_96_1_193   │ /var/lib/clickhouse/disks/disk_oss/store/fc5/fc50a391-4c16-406b-a396-6e1104873f68/1641902100_91_96_1_193/ │
│ 2022-01-11 20:00:00 │ 1641902400_97_124_2_193  │ /mnt/disk3/clickhouse/store/fc5/fc50a391-4c16-406b-a396-6e1104873f68/1641902400_97_124_2_193/             │
│ 2022-01-11 20:00:00 │ 1641902400_125_152_2_193 │ /mnt/disk2/clickhouse/store/fc5/fc50a391-4c16-406b-a396-6e1104873f68/1641902400_125_152_2_193/            │
│ 2022-01-11 20:00:00 │ 1641902400_153_180_2_193 │ /mnt/disk4/clickhouse/store/fc5/fc50a391-4c16-406b-a396-6e1104873f68/1641902400_153_180_2_193/            │
│ 2022-01-11 20:00:00 │ 1641902400_181_186_1_193 │ /mnt/disk3/clickhouse/store/fc5/fc50a391-4c16-406b-a396-6e1104873f68/1641902400_181_186_1_193/            │
│ 2022-01-11 20:00:00 │ 1641902400_187_192_1_193 │ /mnt/disk4/clickhouse/store/fc5/fc50a391-4c16-406b-a396-6e1104873f68/1641902400_187_192_1_193/            │
└─────────────────────┴──────────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────┘

7 rows in set. Elapsed: 0.002 sec.
      Note

      If the output is similar to the example, data is separated into hot and cold tiers based on time. Hot data is stored on local disks, and cold data is stored in OSS.

      In the output, /var/lib/clickhouse/disks/disk_oss is the path for cold data (corresponding to the metadata_path parameter). Paths like /mnt/disk{1..4}/clickhouse point to local disks (hot data).

    Create a new table

    • Syntax

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

      In the command, <yourPartitionKey> is the partition key for ClickHouse, and <yourTtlKey> is the TTL expression that determines when data becomes cold.

    • Example

      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';
      Note

      In this example, the table stores data generated within the last 5 minutes on local disks. After 5 minutes, ClickHouse moves the data to the remote volume, which is OSS.

    Related configurations

    • server-config

      merge_tree.allow_remote_fs_zero_copy_replication: When set to true, this allows Replicated*MergeTree tables on remote storage (like DiskOSS) to share data parts. Instead of copying data between replicas, each replica stores only its own metadata, which points to the shared data on OSS.

    • server-users

      • profile.${your-profile-name}.s3_min_upload_part_size: The minimum size of a single part for a multipart upload to OSS.

      • profile.${your-profile-name}.s3_max_single_part_upload_size: If the amount of data in the write buffer exceeds this value, ClickHouse uses multipart upload. For more information, see .