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经典中文文档分享

参考文章

• ClickHouse(01)什么是ClickHouse,ClickHouse适用于什么场景
• ClickHouse(02)ClickHouse架构设计介绍概述与ClickHouse数据分片设计
• ClickHouse(03)ClickHouse怎么安装和部署
• ClickHouse(04)如何搭建ClickHouse集群
• ClickHouse(05)ClickHouse数据类型详解
• ClickHouse(06)ClickHouse建表语句DDL详细解析