Introduction
In ClickHouse, replication does not protect the system from accidental deletes on tables or some records on the tables. For that reason, backup and recovery strategies are crucial for companies.
These are some of the backup methods that can be used.
- Export / Import
- Duplication Source Table(s)
- Filesystem Snapshots
- Using Tool( clickhouse-copier)
- Manipulations with Parts
In this article, we will discuss the “Export / Import” method for protecting table(s) from accidental deletions.
Environment
OS : Ubuntu Linux 18.04
DB: ClickHouse v22.2.2.1
Steps
Export Phase:
- Use “customerTest” table with 10 records with the following columns:
ChistaDATA :) desc customerTest DESCRIBE TABLE customerTest Query id: 014edd8e-fa06-47d8-b42a-c36569ed2549 ┌─name─────────────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐ │ customerNumber │ Int32 │ │ │ │ │ │ │ customerName │ String │ │ │ │ │ │ │ contactLastName │ String │ │ │ │ │ │ │ contactFirstName │ String │ │ │ │ │ │ └──────────────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘ 4 rows in set. Elapsed: 0.002 sec.
ChistaDATA :) select * from customerTest; SELECT * FROM customerTest Query id: 18cb31c3-e2e1-4994-9ea9-2611c6c7f10e ┌─customerNumber─┬─customerName─────────────────┬─contactLastName─┬─contactFirstName─┐ │ 103 │ Atelier graphique │ Schmitt │ Carine │ │ 112 │ Signal Gift Stores │ King │ Jean │ │ 114 │ Australian Collectors, Co. │ Ferguson │ Peter │ │ 119 │ La Rochelle Gifts │ Labrune │ Janine │ │ 121 │ Baane Mini Imports │ Bergulfsen │ Jonas │ │ 124 │ Mini Gifts Distributors Ltd. │ Nelson │ Susan │ │ 125 │ Havel & Zbyszek Co │ Piestrzeniewicz │ Zbyszek │ │ 128 │ Blauer See Auto, Co. │ Keitel │ Roland │ │ 129 │ Mini Wheels Co. │ Murphy │ Julie │ │ 131 │ Land of Toys Inc. │ Lee │ Kwai │ └────────────────┴──────────────────────────────┴─────────────────┴──────────────────┘ 10 rows in set. Elapsed: 0.004 sec.
- Create directory for exporting table and give permission for access:
root@ubuntu1804:/# mkdir -p /clickhouse/export root@ubuntu1804:/# chown -R clickhouse:clickhouse /clickhouse
- Export data as CSV to given file
ChistaDATA :) select * from customerTest into outfile '/clickhouse/export/customers.csv' FORMAT CSV;
Import Phase:
- Create symbolic link under “/var/lib/clickhouse/user_files” for accessing “customers.csv” file from ClickHouse:
root@ubuntu1804:/var/lib/clickhouse/user_files# ln -s /clickhouse/export/customers.csv customers.csv root@ubuntu1804:/var/lib/clickhouse/user_files# ls -al total 8 drwxr-x--- 2 clickhouse clickhouse 4096 Jul 26 06:48 . drwx------ 15 clickhouse clickhouse 4096 Jul 26 06:06 .. lrwxrwxrwx 1 root root 32 Jul 26 06:48 customers.csv -> /clickhouse/export/customers.csv
- Query “customer.csv” file for access control:
SELECT *
FROM file('customers.csv', 'CSV', '`customerNumber` Int32,`customerName` String, `contactLastName` String, `contactFirstName` String')
- Create new table from “customers.csv” file:
ChistaDATA :) create table backuptest.customers Engine=MergeTree() ORDER BY customerNumber as select * from file ('customers.csv','CSV','`customerNumber` Int32,`customerName` String, `contactLastName` String, `contactFirstName` String');
CREATE TABLE backuptest.customers
ENGINE = MergeTree
ORDER BY customerNumber AS
SELECT *
FROM file('customers.csv', 'CSV', '`customerNumber` Int32,`customerName` String, `contactLastName` String, `contactFirstName` String')
Query id: fb82cfc3-4be7-40b6-a52a-361842776d87
Ok.
0 rows in set. Elapsed: 0.012 sec.
- Query restored table for controlling the records:
ubuntu1804.localdomain :) select * from backuptest.customers; SELECT * FROM backuptest.customers Query id: 705d1356-fa4b-45a0-a545-753b061e4a07 ┌─customerNumber─┬─customerName─────────────────┬─contactLastName─┬─contactFirstName─┐ │ 103 │ Atelier graphique │ Schmitt │ Carine │ │ 112 │ Signal Gift Stores │ King │ Jean │ │ 114 │ Australian Collectors, Co. │ Ferguson │ Peter │ │ 119 │ La Rochelle Gifts │ Labrune │ Janine │ │ 121 │ Baane Mini Imports │ Bergulfsen │ Jonas │ │ 124 │ Mini Gifts Distributors Ltd. │ Nelson │ Susan │ │ 125 │ Havel & Zbyszek Co │ Piestrzeniewicz │ Zbyszek │ │ 128 │ Blauer See Auto, Co. │ Keitel │ Roland │ │ 129 │ Mini Wheels Co. │ Murphy │ Julie │ │ 131 │ Land of Toys Inc. │ Lee │ Kwai │ └────────────────┴──────────────────────────────┴─────────────────┴──────────────────┘ 10 rows in set. Elapsed: 0.003 sec.