一.数据库引擎

默认情况下，ClickHouse使用Atomic数据库引擎。

它支持非阻塞的DROP TABLE和RENAME TABLE查询和原子的EXCHANGE TABLES t1 AND t2查询。

0.MaterializedMySQL

创建ClickHouse数据库，包含MySQL中所有的表，以及这些表中的所有数据。

ClickHouse服务器作为MySQL副本工作。它读取binlog并执行DDL和DML查询。

---MySQL端配置设置:

default_authentication_plugin = mysql_native_password ，因为 MaterializedMySQL 只能授权使用该方法。

gtid_mode = on，因为基于GTID的日志记录是提供正确的 MaterializedMySQL复制的强制要求。

当打开gtid_mode时，您还应该指定enforce_gtid_consistency = on

CREATE DATABASE mysql ENGINE = MaterializedMySQL('localhost:3306', 'db', 'user', '***')
     SETTINGS
        allows_query_when_mysql_lost=true,
        max_wait_time_when_mysql_unavailable=10000;
- max_wait_time_when_mysql_unavailable MySQL不可用时的重试间隔(毫秒)。负值禁用重试。默认值:1000。 
— allows_query_when_mysql_lost —允许在MySQL丢失时查询物化表。默认值:0(false)。

_version — 同步版本。 类型UInt64.
_sign — 删除标记。类型 Int8. Possible values:
1 — 行不会删除,
-1 — 行被删除。

1.Atomic

CREATE DATABASE test[ ENGINE = Atomic];

数据库Atomic中的所有表都有唯一的UUID

2.MySQL

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换

MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此您可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。

但您无法对其执行以下操作：RENAME,  CREATE TABLE,  ALTER

CREATE DATABASE mysql_db ENGINE = MySQL('localhost:3306', 'test', 'my_user', 'user_password')

3.Lazy

在最后一次访问之后，只在RAM中保存expiration_time_in_seconds秒。只能用于*Log表。

它是为存储许多小的*Log表而优化的，对于这些表，访问之间有很长的时间间隔。

CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);

4.PostgreSQL

支持读写操作(SELECT和INSERT查询)，以在ClickHouse和PostgreSQL之间交换数据。

在SHOW TABLES和DESCRIBE TABLE查询的帮助下，从远程PostgreSQL实时访问表列表和表结构。

支持表结构修改(ALTER TABLE ... ADD|DROP COLUMN)。如果use_table_cache参数(参见下面的引擎参数)设置为1，则会缓存表结构，不会检查是否被修改，但可以用DETACH和ATTACH查询进行更新。

二.表引擎

1.合并树家族

1.0 MergeTree - 合并树引擎

用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多

ENGINE MergeTree() 
PARTITION BY toYYYYMM(EventDate) 
ORDER BY (CounterID, EventDate, intHash32(UserID)) 
SAMPLE BY intHash32(UserID) 
SETTINGS index_granularity=8192
-设置了按月进行分区。
-同时我们设置了一个按用户 ID 哈希的抽样表达式。
-这使得您可以对该表中每个 CounterID 和 EventDate 的数据伪随机分布。
-如果您在查询时指定了 SAMPLE 子句。 SETTINGS-ClickHouse会返回对于用户子集的一个均匀的伪随机数据采样

1.1 VersionedCollapsingMergeTree - 版本折叠MergeTree

这个引擎:  允许快速写入不断变化的对象状态。删除后台中的旧对象状态。 这显著降低了存储体积。

Version 列有助于正确折叠行，即使它们以错误的顺序插入。 相比之下, CollapsingMergeTree 只允许严格连续插入。

这是一个非常低效的方式来选择数据。 不要把它用于数据量大的表

要计算数量，请使用 sum(Sign) 而不是 count(). 

要计算的东西的总和，使用 sum(Sign * x) 而不是 sum(x)，并添加 HAVING sum(Sign) > 0

---创建表:
CREATE TABLE UAct
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8,
    Version UInt8
)
ENGINE = VersionedCollapsingMergeTree(Sign, Version)
ORDER BY UserID
---需要聚合
SELECT 
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration,
    Version
FROM UAct
GROUP BY UserID, Version
HAVING sum(Sign) > 0
---不需要聚合，并希望强制折叠，我们可以使用 FINAL 修饰符 FROM 条款
SELECT * FROM UAct FINAL
GROUP BY UserID, Version
HAVING sum(Sign) > 0

1.2 GraphiteMergeTree

该引擎用来对 Graphite数据进行瘦身及汇总。对于想使用CH来存储Graphite数据的开发者来说可能有用

需要对Graphite数据做汇总,它能减少存储空间，同时能提高Graphite数据的查询效率。

1.3 AggregatingMergeTree

将一个数据片段内所有具有相同主键（准确的说是 排序键）的行替换成一行，这一行会存储一系列聚合函数的状态。

做增量数据的聚合统计，包括物化视图的数据聚合

---创建表:
CREATE MATERIALIZED VIEW test.basic
ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
AS SELECT
    CounterID,
    StartDate,
    sumState(Sign)    AS Visits,
    uniqState(UserID) AS Users
FROM test.visits
GROUP BY CounterID, StartDate;
---获取聚合数据，我们需要在 test.basic 视图上执行类似 SELECT ... GROUP BY ... 这样的查询 ：
SELECT
    StartDate,
    sumMerge(Visits) AS Visits,
    uniqMerge(Users) AS Users
FROM test.basic
GROUP BY StartDate
ORDER BY StartDate;

1.4 CollapsingMergeTree

在数据块合并算法中添加了折叠行的逻辑. 这种查询数据的方法是非常低效的。不要在大表中使用它

---建表:
CREATE TABLE UAct
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID
---需要聚合：
SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0
---不需要聚合并想要强制进行折叠，我们可以在 FROM 从句中使用 FINAL 修饰语。
SELECT * FROM UAct FINAL
GROUP BY UserID
HAVING sum(Sign) > 0

1.5 自定义分区键

分区是在一个表中通过指定的规则划分而成的逻辑数据集。可以按任意标准进行分区，如按月，按日或按事件类型。为了减少需要操作的数据，每个分区都是分开存储的。访问数据时，ClickHouse 尽量使用这些分区的最小子集。

分区是在 建表 时通过 PARTITION BY expr 子句指定的。分区键可以是表中列的任意表达式。例如，指定按月分区，表达式为 toYYYYMM(date_column)

CREATE TABLE visits
(
    VisitDate Date,
    Hour UInt8,
    ClientID UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(VisitDate)
ORDER BY Hour;
---分区键也可以是表达式元组（类似 主键 ）。例如：
ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/name', 'replica1', Sign)
PARTITION BY (toMonday(StartDate), EventType)
ORDER BY (CounterID, StartDate, intHash32(UserID));
上例中，我们设置按一周内的事件类型分区

1.6 ReplacingMergeTree

它会删除排序键值相同的重复项。

数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，因此你无法预先作出计划。有一些数据可能仍未被处理。尽管你可以调用 OPTIMIZE 语句发起计划外的合并，但请不要依靠它，因为 OPTIMIZE 语句会引发对数据的大量读写。因此，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现

1.7 数据副本

只有 MergeTree 系列里的表可支持副本

CREATE TABLE table_name
(
    EventDate DateTime,
    CounterID UInt32,
    UserID UInt32
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/table_name', '{replica}')
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)

1.8 SummingMergeTree

当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。

我们推荐将该引擎和 MergeTree 一起使用。例如，在准备做报告的时候，将完整的数据存储在 MergeTree 表中，并且使用 SummingMergeTree 来存储聚合数据。这种方法可以使你避免因为使用不正确的主键组合方式而丢失有价值的数据

CREATE TABLE summtt
(
    key UInt32,
    value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key
---
SELECT key, sum(value) FROM summtt GROUP BY key