ClickHouse DBMS With Wasabi

How do I use ClickHouse DBMS with Wasabi?

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.

Prerequisites

• 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

Create Wasabi Bucket

1. Log into Wasabi Console

2. Go to Buckets > Create Bucket

3. Enter name - For example “clickhouse-backup”

4. Choose Region (e.g., us-east-1)

5. Keep Versioning and Object Lock Disabled

6. Create bucket.

Create IAM Sub-User

1. Go to Users > Create User

2. Assign Username: For Example “clickhouse-s3-user

3. Choose “Programmatic Access”

4. Create a new Group with 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/*"

     ]

   }

 ]

}

5. Finish and Download Access and Secret Keys

Install 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

Configure S3 Disk in ClickHouse

1. 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>

2. Restart ClickHouse

sudo systemctl restart clickhouse-server

3. Check log /var/log/clickhouse-server/clickhouse-server.log for confirmation

(Optional) Install ClickHouse-Backup Tool

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/

Configure ClickHouse-backup Tool

1. 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

Run Backup & Restore 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

1. Go to Wasabi Console

2. Open “clickhouse-backup” bucket

3. Check if backup folder/files exist