ClickHouse
What is ClickHouse
ClickHouse is an open-source column-oriented database management system designed for online analytical processing (OLAP).
It's known for its exceptional query performance on large datasets, making it ideal for real-time analytics and data warehousing applications.
ClickHouse uses a column-oriented storage format and employs various optimizations like vectorized query execution to achieve high performance.
It supports SQL with extensions and can handle both batch and stream data ingestion, making it versatile for various analytical workloads.
Backup and Restore Guide
Currently, modifying ClickHouse configurations is not supported within the platform, which means certain native backup options (e.g., backing up to a local Disk or using ALTER TABLE ... FREEZE PARTITION ...) are not possible. Instead, the recommended approach is to use S3 for backups.
How to Backup ClickHouse
To backup a table or an entire database, use the following SQL command:
- Backup a Table:
BACKUP TABLE <table_name> TO S3('<your_s3_endpoint_com>/<unique_folder_for_table_backup>', '<s3_access_key>', '<s3_secret_key>')
- Backup a Database:
BACKUP DATABASE <database_name> TO S3('<your_s3_endpoint_com>/<unique_folder_for_database_backup>', '<s3_access_key>', '<s3_secret_key>')
How to Restore ClickHouse
- Restore a Table:
RESTORE TABLE <table_name> FROM S3('<your_s3_endpoint_com>/<unique_folder_from_table_backup>', '<s3_access_key>', '<s3_secret_key>')
- Restore a Database:
RESTORE DATABASE <database_name> FROM S3('<your_s3_endpoint_com>/<unique_folder_from_database_backup>', '<s3_access_key>', '<s3_secret_key>')
Performance Notes
A community member shared that backing up a 145GB database took around 12 minutes, while restoring it took roughly 17 minutes.