ClickHouse has been validated for use with Wasabi Object Storage. ClickHouse is a high-performance, open-source, column-oriented database management system (DBMS) designed for online analytical processing (OLAP) and real-time data analytics.
Requirements
ClickHouse Server v.23.0 or later
ClickHouse Backup Tool (Optional, but recommended)
ClickHouse Open Source or Paid Version
Active Wasabi Object Storage Account
Wasabi Access Keys
Wasabi Bucket
Reference Architecture Diagram
Configuration
Creating a Wasabi Bucket
Log in to the Wasabi Console.
Click Buckets, then click Create Bucket.
Enter a name (for example, clickhouse-backup).
Select a region (for example, us-east-1).
Be sure to keep Versioning and Object Lock disabled.
Click Create.
Creating an IAM Sub-User
Click Users, then click Create User.
Assign a name for the user (for Example, “clickhouse-s3-user).
Select Programmatic Access.
Create a new Group using the policy below.
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"s3:GetBucketLocation",
"s3:ListAllMyBuckets"
],
"Resource": "arn:aws:s3:::*"
},
{
"Effect": "Allow",
"Action": "s3:*",
"Resource": [
"arn:aws:s3:::clickhouse-backup",
"arn:aws:s3:::clickhouse-backup/*"
]
}
]
}
Finish and download the Access and Secret Keys.
Installing ClickHouse
If ClickHouse is not installed, below is an example for Linux - Ubuntu:
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4
echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt update
sudo apt install clickhouse-server clickhouse-client -y
sudo systemctl start clickhouse-server
Configuring S3 Disk in ClickHouse
Create a file /etc/clickhouse-server/config.d/backup_disk.xml.
<clickhouse>
<storage_configuration>
<disks>
<s3_backup>
<type>s3</type>
<endpoint>https://s3.ap-southeast-1.wasabisys.com/clickhouse-backup/</endpoint>
<access_key_id>YOUR_ACCESS_KEY</access_key_id>
<secret_access_key>YOUR_SECRET_KEY</secret_access_key>
<s3_max_single_part_upload_size>10485760</s3_max_single_part_upload_size>
<upload_part_size>67108864</upload_part_size>
<s3_max_put_rps>25</s3_max_put_rps>
<metadata_path>/var/lib/clickhouse/disks/s3_backup/metadata</metadata_path>
</s3_backup>
</disks>
</storage_configuration>
</clickhouse>
Restart ClickHouse.
sudo systemctl restart clickhouse-server
Check log /var/log/clickhouse-server/clickhouse-server.log for confirmation.
Installing ClickHouse-Backup Tool (Optional)
This tool simplifies backup/export and upload to S3:
wget https://github.com/AlexAkulov/clickhouse-backup/releases/download/v2.5.3/clickhouse-backup
chmod +x clickhouse-backup
sudo mv clickhouse-backup /usr/local/bin/
Configuring ClickHouse-Backup Tool
Edit /etc/ClickHouse-backup/config.yml.
general:
remote_storage: s3
disable_progress_bar: true
s3:
bucket: clickhouse-backup
endpoint: s3.ap-southeast-1.wasabisys.com
access_key: YOUR_ACCESS_KEY
secret_key: YOUR_SECRET_KEY
use_path_style: true
compression_format: tar
compression_level: 1
Running Backup and Restoring Operations
Backup a specific database:
clickhouse-backup create my_backup
clickhouse-backup upload my_backup
Restore a specific database:
clickhouse-backup download my_backup
clickhouse-backup restore my_backup
Verification on Wasabi
Navigate to the Wasabi Console.
Open the clickhouse-backup bucket.
Verify that the backup folder/files exist.