ClickHouse(07)ClickHouse資料庫引擎解析
這裡會介紹ClickHouse幾種資料庫引擎，已經對應的特點和應用的場景。資料庫引擎允許您處理數據表。默認情況下，ClickHouse使用Atomic資料庫引擎。它提供了可配置的table engines和SQL dialect。
目前的資料庫引擎：
- MySQL
- MaterializeMySQL
- Lazy
- Atomic
- PostgreSQL
- MaterializedPostgreSQL
- Replicated
- SQLite
Atomic
支援非阻塞的DROP TABLE和RENAME TABLE查詢和原子的EXCHANGE TABLES t1 AND t2查詢。默認情況下使用Atomic資料庫引擎。
建表語句
CREATE DATABASE test[ ENGINE = Atomic];
特性
Table UUID
資料庫Atomic中的所有表都有唯一的UUID，並將數據存儲在目錄/clickhouse_path/store/xxx/xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy/，其中xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy是該表的UUID。
通常，UUID是自動生成的，但用戶也可以在創建表時以相同的方式顯式指定UUID(不建議這樣做)。例如:
CREATE TABLE name UUID '28f1c61c-2970-457a-bffe-454156ddcfef' (n UInt64) ENGINE = ...;
RENAME TABLES
RENAME查詢是在不更改UUID和移動表數據的情況下執行的。這些查詢不會等待使用表的查詢完成，而是會立即執行。
DROP/DETACH TABLES
在DROP TABLE上，不刪除任何數據，資料庫Atomic只是通過將元數據移動到/clickhouse_path/metadata_dropped/將表標記為已刪除，並通知後台執行緒。最終表數據刪除前的延遲由database_atomic_delay_before_drop_table_sec設置指定。
可以使用SYNC修飾符指定同步模式。使用database_atomic_wait_for_drop_and_detach_synchronously設置執行此操作。
EXCHANGE TABLES
EXCHANGE以原子方式交換表。
-- 非原子操作
RENAME TABLE new_table TO tmp, old_table TO new_table, tmp TO old_table;
--原子操作
EXCHANGE TABLES new_table AND old_table;
ReplicatedMergeTree in Atomic Database
對於ReplicatedMergeTree表，建議不要在ZooKeeper和副本名稱中指定engine-path的參數。在這種情況下，將使用配置的參數default_replica_path和default_replica_name。
如果要顯式指定引擎的參數，建議使用{uuid}宏。這是非常有用的，以便為ZooKeeper中的每個表自動生成唯一的路徑。
MySQL
MySQL引擎用於將遠程的MySQL伺服器中的表映射到ClickHouse中，並允許您對錶進行INSERT和SELECT查詢，以方便您在ClickHouse與MySQL之間進行數據交換。
MySQL資料庫引擎會將對其的查詢轉換為MySQL語法並發送到MySQL伺服器中，因此您可以執行諸如SHOW TABLES或SHOW CREATE TABLE之類的操作。
但無法對其執行操作：RENAME、CREATE TABLE和ALTER。
創建資料庫
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
引擎參數
- host:port — MySQL服務地址
- database — MySQL資料庫名稱
- user — MySQL用戶名
- password — MySQL用戶密碼
mysql與ClickHouse數據類型對應
| MySQL | ClickHouse |
|---|---|
| UNSIGNED TINYINT | UInt8 |
| TINYINT | Int8 |
| UNSIGNED SMALLINT | UInt16 |
| SMALLINT | Int16 |
| UNSIGNED INT | UInt32 |
| UNSIGNED MEDIUMINT | UInt32 |
| INT,MEDIUMINT | Int32 |
| UNSIGNED BIGINT | UInt64 |
| BIGINT | Int64 |
| FLOAT | Float32 |
| DOUBLE | Float64 |
| DATE | Date |
| DATETIME,TIMESTAMP | DateTime |
| BINARY | FixedString |
其他的MySQL數據類型將全部都轉換為String。
使用例子
MySQL操作:
mysql> USE test;
Database changed
mysql> CREATE TABLE `mysql_table` (
-> `int_id` INT NOT NULL AUTO_INCREMENT,
-> `float` FLOAT NOT NULL,
-> PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0,09 sec)
mysql> insert into mysql_table (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0,00 sec)
mysql> select * from mysql_table;
+------+-----+
| int_id | value |
+------+-----+
| 1 | 2 |
+------+-----+
1 row in set (0,00 sec)
ClickHouse中的資料庫，與MySQL伺服器交換數據:
CREATE DATABASE mysql_db ENGINE = MySQL('localhost:3306', 'test', 'my_user', 'user_password')
SHOW DATABASES
┌─name─────┐
│ default │
│ mysql_db │
│ system │
└──────────┘
SHOW TABLES FROM mysql_db
┌─name─────────┐
│ mysql_table │
└──────────────┘
SELECT * FROM mysql_db.mysql_table
┌─int_id─┬─value─┐
│ 1 │ 2 │
└────────┴───────┘
INSERT INTO mysql_db.mysql_table VALUES (3,4)
SELECT * FROM mysql_db.mysql_table
┌─int_id─┬─value─┐
│ 1 │ 2 │
│ 3 │ 4 │
└────────┴───────┘
PostgreSQL
允許連接到遠程PostgreSQL服務。支援讀寫操作(SELECT和INSERT查詢)，以在ClickHouse和PostgreSQL之間交換數據。
在SHOW TABLES和DESCRIBE TABLE查詢的幫助下，從遠程PostgreSQL實時訪問表列表和表結構。
支援表結構修改(ALTER TABLE … ADD|DROP COLUMN)。如果use_table_cache參數(參見下面的引擎參數)設置為1，則會快取表結構，不會檢查是否被修改，但可以用DETACH和ATTACH查詢進行更新。
使用總體上與mysql引擎類似
創建資料庫
CREATE DATABASE test_database
ENGINE = PostgreSQL('host:port', 'database', 'user', 'password'[, `use_table_cache`]);
引擎參數
- host:port — PostgreSQL服務地址
- database — 遠程資料庫名次
- user — PostgreSQL用戶名稱
- password — PostgreSQL用戶密碼
- schema – PostgreSQL 模式
- use_table_cache — 定義資料庫表結構是否已快取或不進行。可選的。默認值： 0
數據類型對應
| PostgreSQL | ClickHouse |
|---|---|
| DATE | Date |
| TIMESTAMP | DateTime |
| REAL | Float32 |
| DOUBLE | Float64 |
| DECIMAL | Decimal |
| NUMERIC | Decimal |
| SMALLINT | Int16 |
| INTEGER | Int32 |
| BIGINT | Int64 |
| SERIAL | UInt32 |
| BIGSERIAL | UInt64 |
| TEXT | String |
| CHAR | String |
| INTEGER | Nullable(Int32) |
| ARRAY | Array |
SQLite
允許連接到SQLite資料庫，並支援ClickHouse和SQLite交換數據， 執行INSERT和SELECT查詢。
SQLite將整個資料庫(定義、表、索引和數據本身)存儲為主機上的單個跨平台文件。在寫入過程中，SQLite會鎖定整個資料庫文件，因此寫入操作是順序執行的。讀操作可以是多任務的。SQLite不需要服務管理(如啟動腳本)或基於GRANT和密碼的訪問控制。訪問控制是通過授予資料庫文件本身的文件系統許可權來處理的。
創建資料庫
CREATE DATABASE sqlite_database
ENGINE = SQLite('db_path')
引擎參數
- db_path — SQLite 資料庫文件的路徑
數據類型對應
| SQLite | ClickHouse |
|---|---|
| INTEGER | Int32 |
| REAL | Float32 |
| TEXT | String |
| BLOB | String |
Lazy
在最後一次訪問之後，只在RAM中保存expiration_time_in_seconds秒。只能用於Log表。
它是為存儲許多小的Log表而優化的，對於這些表，訪問之間有很長的時間間隔。
創建資料庫
CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);
Replicated
該引擎基於Atomic引擎。它支援通過將DDL日誌寫入ZooKeeper並在給定資料庫的所有副本上執行的元數據複製。
一個ClickHouse伺服器可以同時運行和更新多個複製的資料庫。但是同一個複製的資料庫不能有多個副本。
這是一個實驗性的引擎，不應該在生產中使用。
創建資料庫
CREATE DATABASE testdb ENGINE = Replicated('zoo_path', 'shard_name', 'replica_name') [SETTINGS ...]
MaterializeMySQL
創建ClickHouse資料庫，包含MySQL中所有的表，以及這些表中的所有數據。
ClickHouse伺服器作為MySQL副本工作。它讀取binlog並執行DDL和DML查詢。
這是一個實驗性的引擎，不應該在生產中使用。
創建資料庫
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializeMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]
MaterializedPostgreSQL
使用PostgreSQL資料庫表的初始數據轉儲創建ClickHouse資料庫，並啟動複製過程，即執行後台作業，以便在遠程PostgreSQL資料庫中的PostgreSQL資料庫表上發生新更改時應用這些更改。
ClickHouse伺服器作為PostgreSQL副本工作。它讀取WAL並執行DML查詢。DDL不是複製的，但可以處理（如下所述）。
這是一個實驗性的引擎，不應該在生產中使用。
創建資料庫
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedPostgreSQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]
資料分享
參考文章
- ClickHouse(01)什麼是ClickHouse,ClickHouse適用於什麼場景
- ClickHouse(02)ClickHouse架構設計介紹概述與ClickHouse數據分片設計
- ClickHouse(03)ClickHouse怎麼安裝和部署
- ClickHouse(04)如何搭建ClickHouse集群
- ClickHouse(05)ClickHouse數據類型詳解
- ClickHouse(06)ClickHouse建表語句DDL詳細解析
