1. Home
  2. Knowledge Base
  3. ClickHouse
  4. How To Move Table To Another Device within ClickHouse

How To Move Table To Another Device within ClickHouse

In this article, we will learn how to move a table to the another device. First, follow the steps which is given below.

Create directory on new device for ClickHouse data.

mkdir /mnt/disk_1/clickhouse

Change ownership of created directory to ClickHouse user.

chown -R clickhouse:clickhouse /mnt/disk_1/clickhouse

Create a special storage policy that includes both old and new disks.

vi /etc/clickhouse-server/config.d/storage.xml

Now fill the file with the following parameters.

 

<yandex>
  <storage_configuration>
    <disks>
      <!--
          default disk is special, it always
          exists even if not explicitly
          configured here, but you can't change
          it's path here (you should use <path>
          on top level config instead)
      -->
      <default>
         <!--
             You can reserve some amount of free space
             on any disk (including default) by adding
             keep_free_space_bytes tag
         -->
      </default>
      <disk_1> <!-- disk name -->
          <path>/mnt/disk_1/clickhouse/</path>
      </disk_1>
    </disks>
    <policies>
      <move_from_default_to_disk_1> <!-- name for new storage policy -->
        <volumes>
          <default>
            <disk>default</disk>
            <max_data_part_size_bytes>10000000</max_data_part_size_bytes>
          </default>
          <disk_1_vol> <!-- name of volume -->
            <!--
                we have only one disk in that volume
                and we reference here the name of disk
                as configured above in <disks> section
            -->
            <disk>disk_1</disk>
          </disk_1_vol>
        </volumes>
        <move_factor>0.99</move_factor>
      </move_from_default_to_disk_1>
    </policies>
  </storage_configuration>
</yandex>

 

Now, connect to ClickHouse Server, then update the storage_policy setting of tables to the new policy.

ALTER TABLE trips MODIFY SETTING storage_policy='move_from_default_to_disk_1';

Check the current disk status.

SELECT
    name,
    disk_name,
    path
FROM system.parts
WHERE (table = 'trips') AND active

Query id: 89f5cad6-9c28-4700-83ad-024df9e5ea39

┌─name─────────┬─disk_name─┬─path────────────────────────────────────────────────────────────────────────────────┐
│ 201507_1_1_0 │ disk_1    │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201507_1_1_0/ │
│ 201507_4_4_0 │ disk_1    │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201507_4_4_0/ │
│ 201508_2_2_0 │ disk_1    │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201508_2_2_0/ │
│ 201508_5_5_0 │ disk_1    │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201508_5_5_0/ │
│ 201509_3_3_0 │ disk_1    │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201509_3_3_0/ │
│ 201509_6_6_0 │ disk_1    │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201509_6_6_0/ │
└──────────────┴───────────┴─────────────────────────────────────────────────────────────────────────────────────┘

6 rows in set. Elapsed: 0.007 sec.

 

Check current disk information.

SELECT
    disk_name,
    path,
    sum(rows),
    sum(bytes_on_disk),
    uniq(partition),
    count()
FROM system.parts
WHERE (table = 'trips') AND active
GROUP BY
    disk_name,
    path
ORDER BY
    disk_name ASC,
    path ASC

Query id: 645ca093-d63b-4a42-9038-991b94c756fa

┌─disk_name─┬─path────────────────────────────────────────────────────────────────────────────────┬─sum(rows)─┬─sum(bytes_on_disk)─┬─uniq(partition)─┬─count()─┐
│ disk_1    │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201507_1_1_0/ │    192866 │           16024134 │               1 │       1 │
│ disk_1    │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201507_4_4_0/ │    192775 │           16018221 │               1 │       1 │
│ disk_1    │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201508_2_2_0/ │    556737 │           45966120 │               1 │       1 │
│ disk_1    │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201508_5_5_0/ │    556491 │           45967817 │               1 │       1 │
│ disk_1    │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201509_3_3_0/ │    250229 │           20809969 │               1 │       1 │
│ disk_1    │ /mnt/disk_1/clickhouse/store/ac1/ac13fd22-4078-4552-af78-0a2124bc962d/201509_6_6_0/ │    250559 │           20845804 │               1 │       1 │
└───────────┴─────────────────────────────────────────────────────────────────────────────────────┴───────────┴────────────────────┴─────────────────┴─────────┘

6 rows in set. Elapsed: 0.005 sec.

 

To change the device, please remove ‘default’ disk from new storage policy.

Connect to storage.xml
vi /etc/clickhouse-server/config.d/storage.xml

Edit file

<yandex>
  <storage_configuration>
    <disks>
      <!--
          default disk is special, it always
          exists even if not explicitly
          configured here, but you can't change
          it's path here (you should use <path>
          on top level config instead)
      -->
      <default>
         <!--
             You can reserve some amount of free space
             on any disk (including default) by adding
             keep_free_space_bytes tag
         -->
      </default>
      <disk_1> <!-- disk name -->
          <path>/mnt/disk_1/clickhouse/</path>
      </disk_1>
    </disks>
    <policies>
      <move_from_default_to_disk_1> <!-- name for new storage policy -->
        <volumes>
          <disk_1_vol> <!-- name of volume -->
            <!--
                we have only one disk in that volume
                and we reference here the name of disk
                as configured above in <disks> section
            -->
            <disk>disk_1</disk>
          </disk_1_vol>
        </volumes>
        <move_factor>0.99</move_factor>
      </move_from_default_to_disk_1>
    </policies>
  </storage_configuration>
</yandex>

To enable changes please restart ClickHouse Server

systemctl restart clickhouse-server

Done!

Make sure that the storage policy uses the right disks.

SELECT *
FROM system.storage_policies
WHERE policy_name = 'move_from_default_to_disk_1'

Query id: 46c7b42e-16bd-44a3-a9c4-0be5cea4b981

┌─policy_name─────────────────┬─volume_name─┬─volume_priority─┬─disks──────┬─volume_type─┬─max_data_part_size─┬─move_factor─┬─prefer_not_to_merge─┐
│ move_from_default_to_disk_1 │ disk_1_vol  │               1 │ ['disk_1'] │ JBOD        │                  0 │        0.99 │                   0 │
└─────────────────────────────┴─────────────┴─────────────────┴────────────┴─────────────┴────────────────────┴─────────────┴─────────────────────┘

1 row in set. Elapsed: 0.002 sec.
root@clickhouse01:/mnt/disk_1/clickhouse/store/ac1# pwd
/mnt/disk_1/clickhouse/store/ac1

root@clickhouse01:/mnt/disk_1/clickhouse/store/ac1# du -sh /mnt/disk_1/clickhouse/store/ac1
160M	/mnt/disk_1/clickhouse/store/ac1

 

Was this article helpful?

Related Articles

CHISTADATA IS COMMITTED TO OPEN SOURCE SOFTWARE AND BUILDING HIGH PERFORMANCE COLUMNSTORES

In the spirit of freedom, independence and innovation. ChistaDATA Corporation is not affiliated with ClickHouse Corporation 

Need Support?

Can't find the answer you're looking for?
Contact Support

ChistaDATA Inc. Knowledge base is licensed under the Apache License, Version 2.0 (the “License”)

Copyright 2022 ChistaDATA Inc

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.