文章目录

第一章 clickhouse概述
- 1.1 clickhouse的定义
- 1.2 clickhouse的来源
- 1.3 clickhouse应用领域
- 1.4 clickhouse存储层
- 1.5 clickhouse优点
- 1.6 clickhouse缺点
第二章单机版安装
- 2.1 rpm安装(也可以在线yum安装)
- 2.2 配置
- 2.3 服务启动
- 2.4 客户端连接
第三章集群安装
- 3.1 安装
- 3.2 配置
- 3.3 服务启动
- 3.4 客户端连接
第四章入门操作
- 4.1 伪分布式基础操作
- - 4.1.1 创建数据库
  - 4.1.2 创建表
- 4.2 分布式DDL操作
- 4.3 数据类型
- - 4.3.1 UUID
  - 4.3.2 Datetime64
  - 4.3.3 Decimal
  - 4.3.4 Float32,Float64
  - 4.3.5 Tuple
  - 4.3.6 UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64
  - 4.3.7 可为空（类型名称)
  - 4.3.8 字符串
  - 4.3.9 布尔值
  - 4.3.10 数组
第五章库表引擎
- 5.1 表引擎作用
- 5.2 表引擎分类
- 5.3 **Log系列表引擎**
- - 5.3.1 应用场景
  - 5.3.2 Log系列表引擎的特点
  - - 共性特点
    - 区别
  - 5.3.3 TinyLog表引擎使用
  - 5.3.4 StripLog表引擎
  - 5.3.5 Log表引擎
- 5.4 **MergeTree系列引擎**
- - 5.4.1 MergeTree表引擎
  - - 建表语法
    - 建表示例
    - 注意点
  - 5.4.2 ReplacingMergeTree表引擎
  - - 建表语法
    - 建表示例
    - 注意点
    - 总结
  - 5.4.3 SummingMergeTree表引擎
  - - 建表语法
    - 建表示例
    - 注意点
    - 总结
  - 5.4.4 Aggregatingmergetree表引擎
  - - 建表语法
    - 建表示例
  - 5.5.5 CollapsingMergeTree表引擎
  - - 建表语法
    - 建表示例
    - 使用方式
    - 注意点
  - 5.5.6 VersionedCollapsingMergeTree表引擎
  - - 建表语法
    - 建表示例
- 5.5 **外部集成表引擎**
- - 5.5.1 HDFS
  - - 使用方式
    - 使用示例
  - 5.5.2 MySQL
  - 5.5.3 JDBC
  - - 使用方式
    - 使用示例
  - 5.5.4 Kakfa
  - - 使用方式
    - 使用示例
    - 注意点
- 5.6 Memory表引擎
- 5.7 Distributed表引擎
- - - 使用方式
    - 使用示例
第六章函数
- 5.6 Memory表引擎
- 5.7 Distributed表引擎
- - - 使用方式
    - 使用示例
第六章函数

第一章 clickhouse概述

英文官网：https://clickhouse.tech/

中文官网：https://clickhouse.tech/docs/zh/

阿里云clickhouse官网：https://www.aliyun.com/product/clickhouse

1.1 clickhouse的定义

ClickHouse是一个开源的用于联机分析(OLAP)的列式数据库管理系统(DBMS)。

1.2 clickhouse的来源

ClickHouse最初是一款名为Yandex.Metrica的产品，主要用于WEB流量分析。ClickHouse的全称是Click Stream,Data WareHouse，简称ClickHouse。也是战斗名族俄罗斯([Russia)的第二款强大开源的高性能产品之一。

1.3 clickhouse应用领域

ClickHouse非常适用于商业智能领域，除此之外，它也能够被广泛应用于广告流量、Web、App流量、电信、金融、电子商务、信息安全、网络游戏、物联网等众多其他领域。

1.4 clickhouse存储层

列式存储

与行存将每一行的数据连续存储不同，列存将每一列的数据连续存储。

【大数据开发】ClickHouse——深入ClickHouse第一章 clickhouse概述第二章单机版安装第三章集群安装第四章入门操作第五章库表引擎第六章函数第六章函数

相比于行式存储，列式存储在分析场景下有着许多优良的特性。

1）如前所述，分析场景中往往需要读大量行但是少数几个列。在行存模式下，数据按行连续存储，所有列的数据都存储在一个block中，不参与计算的列在IO时也要全部读出，读取操作被严重放大。而列存模式下，只需要读取参与计算的列即可，极大的减低了IO cost，加速了查询。

2）同一列中的数据属于同一类型，压缩效果显著。列存往往有着高达十倍甚至更高的压缩比，节省了大量的存储空间，降低了存储成本。

3）更高的压缩比意味着更小的data size，从磁盘中读取相应数据耗时更短。

1.5 clickhouse优点

支持完备的SQL操作
列式存储与数据压缩
向量化执行引擎
关系型模型(与传统数据库类似)
丰富的表引擎
并行处理
在线查询
数据分片

1.6 clickhouse缺点

不支持事务。为啥不支持事务？因面向列
不擅长根据主键按行粒度进行查询（虽然支持），故不应该把ClickHouse当作Key-Value数据库使用。
不擅长按行删除数据（虽然支持）

第二章单机版安装

2.1 rpm安装(也可以在线yum安装)

1 rpm下载地址

https://repo.yandex.ru/clickhouse/rpm/stable/x86_64

2 下载对应版本

[[email protected] home]# ll /home/clickhouse/clickhouse-*
/home/clickhouse/clickhouse-client-20.5.2.7-2.noarch.rpm
/home/clickhouse/clickhouse-common-static-20.5.2.7-2.x86_64.rpm
/home/clickhouse/clickhouse-server-20.5.2.7-2.noarch.rpm
/home/clickhouse/clickhouse-test-20.5.2.7-2.noarch.rpm

3 安装已下载的版本(有顺序关系或者yum -y install /home/clickhouse-*.rpm)

#执行如下命令(不用考虑顺序)
[[email protected] home]# yum -y install /home/clickhouse/clickhouse-*.rpm

#单个安装，需要考虑依赖顺序
[[email protected] home]# yum -y install /home/clickhouse/clickhouse-common-static-20.5.2.7-2.x86_64.rpm
[[email protected] home]# yum -y install /home/clickhouse/clickhouse-client-20.5.2.7-2.noarch.rpm
[[email protected] home]# yum -y install /home/clickhouse/clickhouse-server-20.5.2.7-2.noarch.rpm
[[email protected] home]# yum -y install /home/clickhouse/clickhouse-test-20.5.2.7-2.noarch.rpm

问题1：

--> Finished Dependency Resolution
Error: Package: clickhouse-test-20.5.2.7-2.noarch (/clickhouse-test-20.5.2.7-2.noarch)
           Requires: perl(JSON::XS)
 You could try using --skip-broken to work around the problem
** Found 2 pre-existing rpmdb problem(s), 'yum check' output follows:
2:postfix-2.10.1-7.el7.x86_64 has missing requires of libmysqlclient.so.18()(64bit)
2:postfix-2.10.1-7.el7.x86_64 has missing requires of libmysqlclient.so.18(libmysqlclient_18)(64bit)

解决办法：
缺少perl(JSON::XS)这个包
# 安装依赖
[[email protected] home]# yum install -y epel-release perl-JSON-XS

2.2 配置

配置config.xml

[[email protected] home]# mv /etc/clickhouse-server/config.xml /etc/clickhouse-server/config.xml_bak
[[email protected] home]# vi /etc/clickhouse-server/config.xml
#覆盖内容如下

<?xml version="1.0"?>
<yandex>
    <!-- 配置日志文件 -->
    <logger>
        <level>trace</level>
        <log>/data/clickhouse/logs/server.log</log>
        <errorlog>/data/clickhouse/logs/error.log</errorlog>
        <size>1000M</size>
        <count>10</count>
    </logger>
    
    <!--配置http、tcp端口和监听地址-->
    <http_port>8123</http_port>
    <tcp_port>9009</tcp_port>
    <interserver_http_port>9010</interserver_http_port>
    <listen_host>::</listen_host>

    <path>/data/clickhouse/clickhousedata/</path>
    <tmp_path>/data/clickhouse/tmp/</tmp_path>
    <users_config>users.xml</users_config>

    <default_profile>default</default_profile>
    <default_database>default</default_database>
    <remote_servers incl="clickhouse_remote_servers" />

    <zookeeper incl="zookeeper-servers" optional="true" />
    <macros incl="macros" optional="true" />
    <include_from>/etc/clickhouse-server/metrika.xml</include_from>
    
    <mark_cache_size>5368709120</mark_cache_size>
</yandex>

创建 /etc/clickhouse-server/metrika.xml

[roo[email protected] home]# vi /etc/clickhouse-server/metrika.xml
#覆盖如下内容即可

<yandex>

<clickhouse_remote_servers>
    <news_ck_cluster>
        <shard>
            <weight>1</weight>
            <internal_replication>false</internal_replication>
            <replica>
                <host>192.168.216.111</host>
                <port>9009</port>
                <user>default</user>
                <password>123456</password>
            </replica>
        </shard>
    </news_ck_cluster>
</clickhouse_remote_servers>

<macros>
    <replica>192.168.216.111</replica> <!--默认当前服务器ip即可-->
</macros>

<networks>
   <ip>::/0</ip>
</networks>

<zookeeper-servers>
    <node index="1">
        <host>192.168.216.111</host>
        <port>2181</port>
    </node>
</zookeeper-servers>

<clickhouse_compression>
    <case>
        <min_part_size>10000000000</min_part_size>
        <min_part_size_ratio>0.01</min_part_size_ratio>
        <method>lz4</method>
    </case>
</clickhouse_compression>
</yandex>

**修改/etc/clickhouse-server/users.xml **

[[email protected] home]# mv /etc/clickhouse-server/users.xml /etc/clickhouse-server/users.xml_bak
[[email protected] home]# vi /etc/clickhouse-server/users.xml
#覆盖如下内容

<?xml version="1.0"?>
<yandex>
    <profiles>
        <!-- 读写用户设置  -->
        <default>
            <max_memory_usage>10000000000</max_memory_usage>
            <use_uncompressed_cache>0</use_uncompressed_cache>
            <load_balancing>random</load_balancing>
        </default>
        <!-- 只读用户设置  -->
        <readonly>
            <max_memory_usage>10000000000</max_memory_usage>
            <use_uncompressed_cache>0</use_uncompressed_cache>
            <load_balancing>random</load_balancing>
            <readonly>1</readonly>
        </readonly>
    </profiles>
    <!-- 配额  -->
    <quotas>
        <!-- Name of quota. -->
        <default>
            <interval>
                <duration>3600</duration>
                <queries>0</queries>
                <errors>0</errors>
                <result_rows>0</result_rows>
                <read_rows>0</read_rows>
                <execution_time>0</execution_time>
            </interval>
        </default>
    </quotas>
    <users>
        <!-- 读写用户  -->
        <default>
            <password_sha256_hex>8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92</password_sha256_hex>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
        </default>
        <!-- 只读用户  -->
        <readonly>
            <password_sha256_hex>8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92</password_sha256_hex>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>readonly</profile>
            <quota>default</quota>
        </readonly>
    </users>
</yandex>

2.3 服务启动

后台服务启动

#推荐使用启动命令：
[[email protected] home]# clickhouse-server --config-file=/etc/clickhouse-server/config.xml

# centos6、7命令
[[email protected] home]# service clickhouse-server status
#或者 centos7.x命令
[[email protected] home]# systemctl status/start/stop clickhouse-server

2.4 客户端连接

[[email protected] clickhouse-server]# clickhouse-client \
--host=localhost \
--port=9009 \
--user=default \
--password=123456

node1 :) show databases;
node1 :) show tables;
node1 :) use default;
node1 :) select * from system.clusters;

SELECT *
FROM system.clusters

┌─cluster─────────┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name───────┬─host_address────┬─port─┬─is_local─┬─user────┬─default_database─┬─errors_count─┬─estimated_recovery_time─┐
│ news_ck_cluster │         1 │            1 │           1 │ 192.168.216.111 │ 192.168.216.111 │ 9009 │        0 │ default │                  │            0 │                       0 │
│ news_ck_cluster │         1 │            1 │           2 │ 192.168.216.112 │ 192.168.216.112 │ 9000 │        0 │ default │                  │            0 │                       0 │
│ news_ck_cluster │         2 │            1 │           1 │ 192.168.216.112 │ 192.168.216.112 │ 9009 │        1 │ default │                  │            0 │                       0 │
│ news_ck_cluster │         2 │            1 │           2 │ 192.168.216.113 │ 192.168.216.113 │ 9009 │        0 │ default │                  │            0 │                       0 │
│ news_ck_cluster │         3 │            1 │           1 │ 192.168.216.111 │ 192.168.216.111 │ 9009 │        0 │ default │                  │            0 │                       0 │
│ news_ck_cluster │         3 │            1 │           2 │ 192.168.216.113 │ 192.168.216.113 │ 9009 │        0 │ default │                  │            0 │                       0 │
└─────────────────┴───────────┴──────────────┴─────────────┴─────────────────┴─────────────────┴──────┴──────────┴─────────┴──────────────────┴──────────────┴─────────────────────────┘

6 rows in set. Elapsed: 0.010 sec.

第三章集群安装

3.1 安装

其它服务器和单机版一样，执行clickhouse的安装操作。

[[email protected] home]# scp -r /home/clickhouse/ hadoop02:/home/
[[email protected] home]# scp -r /home/clickhouse/ hadoop03:/home/

# 安装依赖
[[email protected] home]# yum install -y epel-release
[[email protected] home]# yum install -y perl-JSON-XS
[[email protected] home]# yum install -y epel-release
[[email protected] home]# yum install -y perl-JSON-XS

# 安装clickhouse
[[email protected] home]# yum -y install /home/clickhouse/clickhouse-*.rpm
[[email protected] home]# yum -y install /home/clickhouse/clickhouse-*.rpm

3.2 配置

创建 /etc/clickhouse-server/metrika.xml

[roo[email protected] home]# vi /etc/clickhouse-server/metrika.xml
#覆盖如下内容即可

<yandex>

<clickhouse_remote_servers>
    <news_ck_cluster>
        <shard>
            <weight>1</weight>
            <internal_replication>false</internal_replication>
            <replica>
                <host>192.168.216.111</host>
                <port>9009</port>
                <user>default</user>
                <password>123456</password>
            </replica>
            <replica>
                <host>192.168.216.112</host>
                <port>9000</port>
                <user>default</user>
                <password>123456</password>
            </replica>
        </shard>
        <shard>
            <weight>1</weight>
            <internal_replication>false</internal_replication>
            <replica>
                <host>192.168.216.112</host>
                <port>9009</port>
                <user>default</user>
                <password>123456</password>
            </replica>
            <replica>
                <host>192.168.216.113</host>
                <port>9009</port>
                <user>default</user>
                <password>123456</password>
            </replica>
        </shard>
    <shard>
            <weight>1</weight>
            <internal_replication>false</internal_replication>
            <replica>
                <host>192.168.216.111</host>
                <port>9009</port>
                <user>default</user>
                <password>123456</password>
            </replica>
            <replica>
                <host>192.168.216.113</host>
                <port>9009</port>
                <user>default</user>
                <password>123456</password>
            </replica>
        </shard>
    </news_ck_cluster>
</clickhouse_remote_servers>

<macros>
    <replica>192.168.216.111</replica> <!--默认当前服务器ip即可-->
</macros>

<networks>
   <ip>::/0</ip>
</networks>

<zookeeper-servers>
    <node index="1">
        <host>192.168.216.111</host>
        <port>2181</port>
    </node>
    <node index="2">
        <host>192.168.216.112</host>
        <port>2181</port>
    </node>
    <node index="3">
        <host>192.168.216.113</host>
        <port>2181</port>
    </node>
</zookeeper-servers>

<clickhouse_compression>
    <case>
        <min_part_size>10000000000</min_part_size>
        <min_part_size_ratio>0.01</min_part_size_ratio>
        <method>lz4</method>
    </case>
</clickhouse_compression>
</yandex>

复制config.xml users.xml metrika.xml到其它服务器

# copy配置文件
[[email protected] clickhouse-server]#  scp -r config.xml users.xml metrika.xml hadoop02:/etc/clickhouse-server
[[email protected] clickhouse-server]#  scp -r config.xml users.xml metrika.xml hadoop03:/etc/clickhouse-server

3.3 服务启动

推荐使用启动命令：
[[email protected] home]# clickhouse-server --config-file=/etc/clickhouse-server/config.xml
[[email protected] home]# clickhouse-server --config-file=/etc/clickhouse-server/config.xml
[[email protected] home]# clickhouse-server --config-file=/etc/clickhouse-server/config.xml

3.4 客户端连接

[[email protected] clickhouse-server]# clickhouse-client \
--host=localhost \
--port=9009 \
--user=default \
--password=123456

注：

集群模式需要依赖zk来存储元数据，所以需要先启动zk，才能启动clickhouse。

第四章入门操作

4.1 伪分布式基础操作

4.1.1 创建数据库

语法

例子

默认情况下，ClickHouse使用的是原生的数据库引擎Ordinary(在此数据库下可以使用任意类型的表引擎，在绝大多数情况下都只需使用默认的数据库引擎)。当然也可以使用Lazy引擎和MySQL引擎，比如使用MySQL引擎，可以直接在ClickHouse中操作MySQL对应数据库中的表。假设MySQL中存在一个名为clickhouse的数据库，可以使用下面的方式连接MySQL数据库。

-- 语法
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

--操作
node1 :) CREATE DATABASE mysql_db ENGINE = MySQL('192.168.216.111:3306', 'sales_source', 'root', 'root');

node1 :) use mysql_db;
node1 :) show tables;

SHOW TABLES

┌─name────────┐
│ customer    │
│ product     │
│ sales_order │
└─────────────┘
node1 :) select * from product limit 1;

SELECT *
FROM product
LIMIT 1

┌─product_code─┬─product_name─┬─product_category─┐
│            1 │ Hard Disk    │ Storage          │
└──────────────┴──────────────┴──────────────────┘

注:

mysql数据库引擎的库下是不支持创建表。

4.1.2 创建表

语法

示例

-- 注意首字母大写
-- 建表
node1 :) use default;
node1 :) create table test(id Int32,name String) engine=Memory; --内存引擎表
node1 :) insert into test(id,name) values(110,'goudan');   --字符串不能使用双引号
node1 :) select * from test;

SELECT *
FROM test

┌──id─┬─name───┐
│ 110 │ goudan │
└─────┴────────┘

上面命令创建了一张内存表，即使用Memory引擎。表引擎决定了数据表的特性，也决定了数据将会被如何存储及加载。Memory引擎是ClickHouse最简单的表引擎，数据只会被保存在内存中，在服务重启时数据会丢失。一般应用于中间表或者临时表。

4.2 分布式DDL操作

默认情况下，CREATE、DROP、ALTER、RENAME操作仅仅在当前执行该命令的server上生效。

在集群环境下，可以使用ON CLUSTER语句，这样就可以在整个集群发挥作用。

比如创建一张分布式表：

node1 :) CREATE TABLE IF NOT EXISTS user_cluster ON CLUSTER news_ck_cluster(
    id Int32, 
    name String) ENGINE = Distributed(news_ck_cluster, default, user_local,id);

Distributed表引擎的定义形式如下所示：关于ClickHouse的表引擎，后续章节做详细解释。

Distributed(cluster_name, database_name, table_name[, sharding_key])

各个参数的含义分别如下：

cluster_name：集群名称，与集群配置中的自定义名称相对应。
database_name：数据库名称
table_name：表名称,映射到每台服务器中的表名称。
sharding_key：可选的，用于分片的key值，在数据写入的过程中，分布式表会依据分片key的规则，将数据分布到各个节点的本地表。

创建完成上面的分布式表时，在每台机器上查看表，发现每台机器上都存在一张刚刚创建好的表。

接下来就需要创建本地表了，在每台机器上分别创建一张本地表：

-- 没有自动创建可以手动创建
node1 :) CREATE TABLE IF NOT EXISTS user_local 
(
    id Int32,
    name String
)ENGINE = MergeTree()
ORDER BY id
PARTITION BY id
PRIMARY KEY id;

我们先在一台机器上，对user_local表进行插入数据，然后再查询user_cluster表:

-- 插入数据
node1 :) INSERT INTO user_local VALUES(1,'tom'),(2,'jack');
-- 查询user_cluster表,可见通过user_cluster表可以操作所有的user_local表
node1 :) select * from user_cluster;
┌─id─┬─name─┐
│  2 │ jack │
└────┴──────┘
┌─id─┬─name─┐
│  1 │ tom  │
└────┴──────┘

接下来，我们再向user_cluster中插入一些数据，观察user_local表数据变化，可以发现数据被分散存储到了其他节点上了。

-- 向user_cluster插入数据
node1 :)  INSERT INTO user_cluster VALUES(3,'lilei'),(4,'lihua'); 
-- 查看user_cluster数据
node1 :) select * from user_cluster;
┌─id─┬─name─┐
│  2 │ jack │
└────┴──────┘
┌─id─┬─name──┐
│  3 │ lilei │
└────┴───────┘
┌─id─┬─name─┐
│  1 │ tom  │
└────┴──────┘
┌─id─┬─name──┐
│  4 │ lihua │
└────┴───────┘

-- 在node1上查看user_local
node1 :) select * from user_local;
┌─id─┬─name─┐
│  2 │ jack │
└────┴──────┘
┌─id─┬─name──┐
│  3 │ lilei │
└────┴───────┘
┌─id─┬─name─┐
│  1 │ tom  │
└────┴──────┘
-- 在hadoop02上查看user_local
hadoop02 :) select * from user_local;
┌─id─┬─name──┐
│  4 │ lihua │
└────┴───────┘

注：

1、创建分布式表是读时检查的机制，也就是说对创建分布式表和本地表的顺序并没有强制要求。

2、在上面的语句中使用了ON CLUSTER分布式DDL，这意味着在集群的每个分片节点上，都会创建一张Distributed表，这样便可以从其中任意一端发起对所有分片的读、写请求。

3、分布式表映射到每台服务器一张本地表。

4.3 数据类型

4.3.1 UUID

-- 创建表
CREATE TABLE t_uuid (id UUID, name String) ENGINE=TinyLog
-- 插入数据
INSERT INTO t_uuid SELECT generateUUIDv4(), '张三'
-- 查询数据
SELECT * FROM t_uuid
-- 测试函数
SELECT min(id),name FROM t_uuid group by id,name;
┌───────────────────────────────────id─┬─name─┐
│ d1a17b22-cfe9-4382-b9f3-6d34e5509a19 │ 张三 │
└──────────────────────────────────────┴──────┘
SELECT sum(id),name FROM t_uuid
--- 如下出错
Code: 43. DB::Exception: Received from localhost:9009. DB::Exception: Illegal type UUID of argument for aggregate function sum.

注意：

UUID数据类型仅支持以下功能字符串数据类型也支持（例如, min, max，和计数).

算术运算不支持UUID数据类型（例如, abs）或聚合函数，例如 sum 和 avg.

4.3.2 Datetime64

--创建表
CREATE TABLE dt (`timestamp` DateTime64(3, 'Asia/Shanghai'),`event_id` UInt8) ENGINE = TinyLog；
-- 插入时间
INSERT INTO dt Values (1604321946067, 1), ('2020-11-02 20:58:54', 2)
--查询
SELECT * FROM dt
┌───────────────timestamp─┬─event_id─┐
│ 2020-11-02 20:59:06.067 │        1 │
│ 2020-11-02 20:58:54.000 │        2 │
└─────────────────────────┴──────────┘
--获取一个时区 DateTime64-类型值:
SELECT toDateTime64(now(), 3, 'Asia/Shanghai') AS column, toTypeName(column) AS x
-- 时区转换
SELECT toDateTime64(timestamp, 3, 'Europe/Moscow') as mos_time,FROM dt
-- 过滤出 DateTime64 值
SELECT * FROM dt WHERE timestamp = toDateTime64('2020-11-02 20:58:54', 3, 'Asia/Shanghai')

4.3.3 Decimal

有符号的定点数，可在加、减和乘法运算过程中保持精度。对于除法，最低有效数字会被丢弃（不舍入）。

参数

P - 精度。有效范围：[1:38]，决定可以有多少个十进制数字（包括分数）。
S - 规模。有效范围：[0：P]，决定数字的小数部分中包含的小数位数。

4.3.4 Float32,Float64

将固定精度的数字转换为整数值，例如货币数量或页面加载时间用毫秒为单位表示

Float32 - float
Float64 - double

对浮点数进行计算可能引起四舍五入的误差。
SELECT 1 - 0.9

4.3.5 Tuple

元组，其中每个元素都有单独的类型。

不能在表中存储元组（除了内存表）。它们可以用于临时列分组。

-- 创建元组
SELECT tuple(1,'a') AS x, toTypeName(x)

┌─x───────┬─toTypeName(tuple(1, 'a'))─┐
│ (1,'a') │ Tuple(UInt8, String)      │
└─────────┴───────────────────────────┘
-- 自动数据类型检测示例
SELECT tuple(1, NULL) AS x, toTypeName(x)

┌─x────────┬─toTypeName(tuple(1, NULL))──────┐
│ (1,NULL) │ Tuple(UInt8, Nullable(Nothing)) │
└──────────┴─────────────────────────────────┘

4.3.6 UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64

整型范围

Int8-[-128:127]
Int16-[-32768:32767]
Int32-[-2147483648:2147483647]
Int64-[-9223372036854775808:9223372036854775807]

无符号整型范围

UInt8-[0:255]
UInt16-[0:65535]
UInt32-[0:4294967295]
UInt64-[0:18446744073709551615]

4.3.7 可为空（类型名称)

允许用特殊标记 (NULL) 表示«缺失值»，可以与

TypeName

的正常值存放一起。例如，

Nullable(Int8)

类型的列可以存储

Int8

类型值，而没有值的行将存储

NULL

。

-- 创建表
CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE TinyLog
-- 插入数据
INSERT INTO t_null VALUES (1, NULL), (2, 3)
-- 查询
SELECT x + y FROM t_null

┌─plus(x, y)─┐
│       ᴺᵁᴸᴸ │
│          5 │
└────────────┘

4.3.8 字符串

字符串可以任意长度的。它可以包含任意的字节集，包含空字节。

4.3.9 布尔值

没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。

4.3.10 数组

由

类型元素组成的数组。

可以是任意类型，包含数组类型。但不推荐使用多维数组，ClickHouse 对多维数组的支持有限。

-- 创建数组
array(T) 或者[]

SELECT array(1, 2) AS x, toTypeName(x)
-- 自动数据类型检测示例：
SELECT array(1, 2, NULL) AS x, toTypeName(x)
-- 如果数据值不同会出错
SELECT array(1, 'a')

SELECT [1, 'a']

Received exception from server (version 20.5.2):
Code: 386. DB::Exception: Received from localhost:9009. DB::Exception: There is no supertype for types UInt8, String because some of them are String/FixedString and some of them are not.

第五章库表引擎

官网地址：https://clickhouse.tech/docs/en/engines/table-engines/

库表引擎分类如下图：

【大数据开发】ClickHouse——深入ClickHouse第一章 clickhouse概述第二章单机版安装第三章集群安装第四章入门操作第五章库表引擎第六章函数第六章函数

表引擎(table engine)。类似mysql中的InnoDB和MyISAM存储引擎。不同的存储引擎提供不同的存储机制、索引方式、锁定水平等功能，也可以称之为表类型。ClickHouse提供了丰富的表引擎，这些不同的表引擎也代表着不同的表类型。比如数据表拥有何种特性、数据以何种形式被存储以及如何被加载。本章会对ClickHouse中常见的表引擎进行介绍，主要包括以下内容：

表引擎的作用是什么
MergeTree系列引擎
Log家族系列引擎
外部集成表引擎
其他特殊的表引擎

5.1 表引擎作用

决定表存储在哪里以及以何种方式存储
支持哪些查询以及如何支持
并发数据访问
索引的使用
是否可以执行多线程请求
数据复制参数

5.2 表引擎分类

本章第一张图说明过。

引擎分类	引擎名称
MergeTree系列	MergeTree 、ReplacingMergeTree 、SummingMergeTree 、 AggregatingMergeTree CollapsingMergeTree 、 VersionedCollapsingMergeTree 、GraphiteMergeTree
Log系列	TinyLog 、StripeLog 、Log
Integration Engines	Kafka 、MySQL、ODBC 、JDBC、HDFS
Special Engines	Distributed 、MaterializedView、 Dictionary 、Merge 、File、Null 、Set 、Join 、 URL View、Memory 、 Buffer

5.3 Log系列表引擎

5.3.1 应用场景

Log系列表引擎功能相对简单，主要用于快速写入小表(1百万行左右的表)，然后全部读出的场景。即一次写入多次查询。

5.3.2 Log系列表引擎的特点

共性特点

数据存储在磁盘上
当写数据时，将数据追加到文件的末尾
不支持并发读写，当向表中写入数据时，针对这张表的查询会被阻塞，直至写入动作结束
不支持索引
不支持原子写：如果某些操作(异常的服务器关闭)中断了写操作，则可能会获得带有损坏数据的表
不支持ALTER操作(这些操作会修改表设置或数据，比如delete、update等等)

区别

TinyLog

TinyLog是Log系列引擎中功能简单、性能较低的引擎。它的存储结构由数据文件和元数据两部分组成。其中，数据文件是按列独立存储的，也就是说每一个列字段都对应一个文件。除此之外，TinyLog不支持并发数据读取。
StripLog支持并发读取数据文件，当读取数据时，ClickHouse会使用多线程进行读取，每个线程处理一个单独的数据块。另外，StripLog将所有列数据存储在同一个文件中，减少了文件的使用数量。
Log支持并发读取数据文件，当读取数据时，ClickHouse会使用多线程进行读取，每个线程处理一个单独的数据块。Log引擎会将每个列数据单独存储在一个独立文件中。

5.3.3 TinyLog表引擎使用

该引擎适用于一次写入，多次读取的场景。对于处理小批数据的中间表可以使用该引擎。值得注意的是，使用大量的小表存储数据，性能会很低。

CREATE TABLE emp_tinylog (emp_id UInt16 COMMENT '员工id',name String COMMENT '员工姓名',work_place String COMMENT '工作地点',age UInt8 COMMENT '员工年龄',depart String COMMENT '部门',salary Decimal32(2) COMMENT '工资') ENGINE=TinyLog();

INSERT INTO emp_tinylog VALUES (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_tinylog VALUES (3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000);

进入默认数据存储目录，查看底层数据存储形式,可以看出：TinyLog引擎表每一列都对应的文件：

[[email protected] ~]# ll /data/clickhouse/clickhousedata/data/default/emp_tinylog/
total 28
-rw-r----- 1 root root  56 Sep 24 00:59 age.bin
-rw-r----- 1 root root  97 Sep 24 00:59 depart.bin
-rw-r----- 1 root root  60 Sep 24 00:59 emp_id.bin
-rw-r----- 1 root root  70 Sep 24 00:59 name.bin
-rw-r----- 1 root root  68 Sep 24 00:59 salary.bin
-rw-r----- 1 root root 185 Sep 24 00:59 sizes.json
-rw-r----- 1 root root  80 Sep 24 00:59 work_place.bin

## 在sizes.json文件内使用JSON格式记录了每个．bin文件内对应的数据大小的信息
## 查看sizes.json数据
[[email protected] ~]# cat /data/clickhouse/clickhousedata/data/default/emp_tinylog/sizes.json
{"yandex":{"age%2Ebin":{"size":"56"},"depart%2Ebin":{"size":"97"},"emp_id%2Ebin":{"size":"60"},"name%2Ebin":{"size":"70"},"salary%2Ebin":{"size":"68"},"work_place%2Ebin":{"size":"80"}}}

当我们执行ALTER操作时会报错，说明该表引擎不支持ALTER操作

node1 :) ALTER TABLE emp_tinylog DELETE WHERE emp_id = 5;

ALTER TABLE emp_tinylog
    DELETE WHERE emp_id = 5

Received exception from server (version 20.5.2):
Code: 48. DB::Exception: Received from localhost:9009. DB::Exception: Mutations are not supported by storage TinyLog.

5.3.4 StripLog表引擎

相比TinyLog而言，StripeLog拥有更高的查询性能（拥有.mrk标记文件，支持并行查询），同时其使用了更少的文件描述符（所有数据使用同一个文件保存）。

--创建StripeLog表
CREATE TABLE emp_stripelog (emp_id UInt16 COMMENT '员工id',name String COMMENT '员工姓名',work_place String COMMENT '工作地点',age UInt8 COMMENT '员工年龄',depart String COMMENT '部门',salary Decimal32(2) COMMENT '工资') ENGINE=StripeLog;

-- 插入数据  
INSERT INTO emp_stripelog VALUES (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_stripelog VALUES (3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000);
-- 查询数据
-- 由于是分两次插入数据，所以查询时会有两个数据块
select * from emp_stripelog;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 财务部   │ 50000.00 │
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

进入默认数据存储目录，查看底层数据存储形式

[[email protected] ~]# ll /data/clickhouse/clickhousedata/data/default/emp_stripelog/
total 12
-rw-r----- 1 root root 673 Sep 24 01:07 data.bin
-rw-r----- 1 root root 281 Sep 24 01:07 index.mrk
-rw-r----- 1 root root  69 Sep 24 01:07 sizes.json

可以看出StripeLog表引擎对应的存储结构包括三个文件：

data.bin：数据文件，所有的列字段使用同一个文件保存，它们的数据都会被写入data.bin。
index.mrk：数据标记，保存了数据在data.bin文件中的位置信息(每个插入数据块对应列的offset)，利用数据标记能够使用多个线程，以并行的方式读取data.bin内的压缩数据块，从而提升数据查询的性能。
sizes.json：元数据文件，记录了data.bin和index.mrk大小的信息

注：

1、
StripeLog
引擎将所有数据都存储在了一个文件中，对于每次的INSERT操作，ClickHouse会将数据块追加到表文件的末尾

2、StripeLog引擎同样不支持
ALTER UPDATE 和 ALTER DELETE 操作

5.3.5 Log表引擎

Log引擎表适用于临时数据，一次性写入、测试场景。Log引擎结合了TinyLog表引擎和StripeLog表引擎的长处，是Log系列引擎中性能最高的表引擎。

CREATE TABLE emp_log (emp_id UInt16 COMMENT '员工id',name String COMMENT '员工姓名',work_place String COMMENT '工作地点',age UInt8 COMMENT '员工年龄',depart String COMMENT '部门',salary Decimal32(2) COMMENT '工资')ENGINE=Log;
  
INSERT INTO emp_log VALUES (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_log VALUES (3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000);
-- 查询数据，
-- 由于是分两次插入数据，所以查询时会有两个数据块
select * from emp_log;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 财务部   │ 50000.00 │
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

进入默认数据存储目录，查看底层数据存储形式

[[email protected] ~]# ll /data/clickhouse/clickhousedata/data/default/emp_log/
total 32
-rw-r----- 1 root root  56 Sep 24 01:11 age.bin
-rw-r----- 1 root root  97 Sep 24 01:11 depart.bin
-rw-r----- 1 root root  60 Sep 24 01:11 emp_id.bin
-rw-r----- 1 root root  96 Sep 24 01:11 __marks.mrk
-rw-r----- 1 root root  70 Sep 24 01:11 name.bin
-rw-r----- 1 root root  68 Sep 24 01:11 salary.bin
-rw-r----- 1 root root 215 Sep 24 01:11 sizes.json
-rw-r----- 1 root root  80 Sep 24 01:11 work_place.bin

Log引擎的存储结构包含三部分：

列.bin：数据文件，数据文件按列单独存储
__marks.mrk：数据标记，统一保存了数据在各个.bin文件中的位置信息。利用数据标记能够使用多个线程，以并行的方式读取。.bin内的压缩数据块，从而提升数据查询的性能。
sizes.json：记录了.bin和__marks.mrk大小的信息

注：

Log表引擎会将每一列都存在一个文件中，对于每一次的INSERT操作，都会对应一个数据块

5.4 MergeTree系列引擎

在所有的表引擎中，最为核心的当属MergeTree系列表引擎，这些表引擎拥有最为强大的性能和最广泛的使用场合。对于非MergeTree系列的其他引擎而言，主要用于特殊用途，场景相对有限。而MergeTree系列表引擎是官方主推的存储引擎，支持几乎所有ClickHouse核心功能。

5.4.1 MergeTree表引擎

MergeTree在写入一批数据时，数据总会以数据片段的形式写入磁盘，且数据片段不可修改。为了避免片段过多，ClickHouse会通过后台线程，定期合并这些数据片段，属于相同分区的数据片段会被合成一个新的片段。这种数据片段往复合并的特点，也正是合并树名称的由来。

MergeTree作为家族系列最基础的表引擎，主要有以下特点：

存储的数据按照主键排序：允许创建稀疏索引，从而加快数据查询速度
支持分区，可以通过PRIMARY KEY语句指定分区字段。
支持数据副本
支持数据采样

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

ENGINE：ENGINE = MergeTree()，MergeTree引擎没有参数
ORDER BY：排序字段。比如ORDER BY (Col1, Col2)，值得注意的是，如果没有指定主键，默认情况下 sorting key(排序字段)即为主键。如果不需要排序，则可以使用**ORDER BY tuple()**语法，这样的话，创建的表也就不包含主键。这种情况下，ClickHouse会按照插入的顺序存储数据。必选。
PARTITION BY：分区字段，可选。
PRIMARY KEY：指定主键，如果排序字段与主键不一致，可以单独指定主键字段。否则默认主键是排序字段。可选。
SAMPLE BY：采样字段，如果指定了该字段，那么主键中也必须包含该字段。比如 SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)) 。可选。
TTL：数据的存活时间。在MergeTree中，可以为某个列字段或整张表设置TTL。当时间到达时，如果是列字段级别的TTL，则会删除这一列的数据；如果是表级别的TTL，则会删除整张表的数据。可选。
SETTINGS：额外的参数配置。可选。
合并完过一段时间会把原来的数据删除，只保留合并后的数据

建表示例

CREATE TABLE emp_mergetree (emp_id UInt16 COMMENT '员工id',name String COMMENT '员工姓名',work_place String COMMENT '工作地点',age UInt8 COMMENT '员工年龄',depart String COMMENT '部门',salary Decimal32(2) COMMENT '工资')ENGINE=MergeTree() ORDER BY emp_id PARTITION BY work_place;

 -- 插入数据 
INSERT INTO emp_mergetree VALUES (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_mergetree VALUES (3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000); 

-- 查询数据
-- 按work_place进行分区
select * from emp_mergetree;

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

查看一下数据存储格式，可以看出，存在三个分区文件夹，每一个分区文件夹内存储了对应分区的数据。

[[email protected] ~]# ll /data/clickhouse/clickhousedata/data/default/emp_mergetree/
total 16
drwxr-x--- 2 root root 4096 Sep 24 01:17 1c89a3ba9fe5fd53379716a776c5ac34_3_3_0
drwxr-x--- 2 root root 4096 Sep 24 01:17 40d45822dbd7fa81583d715338929da9_1_1_0
drwxr-x--- 2 root root 4096 Sep 24 01:17 a6155dcc1997eda1a348cd98b17a93e9_2_2_0
drwxr-x--- 2 root root    6 Sep 24 01:15 detached
-rw-r----- 1 root root    1 Sep 24 01:15 format_version.txt

进入一个分区目录查看

【大数据开发】ClickHouse——深入ClickHouse第一章 clickhouse概述第二章单机版安装第三章集群安装第四章入门操作第五章库表引擎第六章函数第六章函数

checksums.txt：校验文件，使用二进制格式存储。它保存了余下各类文件(primary. idx、count.txt等)的size大小及size的哈希值，用于快速校验文件的完整性和正确性。

columns.txt：列信息文件，使用明文格式存储。用于保存此数据分区下的列字段信息，例如

[[email protected] ~]# cat /data/clickhouse/clickhousedata/data/default/emp_mergetree/1c89a3ba9fe5fd53379716a776c5ac34_3_3_0/columns.txt
columns format version: 1
6 columns:
`emp_id` UInt16
`name` String
`work_place` String
`age` UInt8
`depart` String
`salary` Decimal(9, 2)

count.txt：计数文件，使用明文格式存储。用于记录当前数据分区目录下数据的总行数
primary.idx：一级索引文件，使用二进制格式存储。用于存放稀疏索引，一张MergeTree表只能声明一次一级索引，即通过ORDER BY或者PRIMARY KEY指定字段。借助稀疏索引，在数据查询的时能够排除主键条件范围之外的数据文件，从而有效减少数据扫描范围，加速查询速度。
列.bin：数据文件，使用压缩格式存储，默认为LZ4压缩格式，用于存储某一列的数据。由于MergeTree采用列式存储，所以每一个列字段都拥有独立的 .bin 数据文件，并以列字段名称命名。
列.mrk2：列字段标记文件，使用二进制格式存储。标记文件中保存了 .bin 文件中数据的偏移量信息
partition.dat与minmax_[Column].idx：如果指定了分区键，则会额外生成partition.dat与minmax索引文件，它们均使用二进制格式存储。partition.dat用于保存当前分区下分区表达式最终生成的值，即分区字段值；而minmax索引用于记录当前分区下分区字段对应原始数据的最小和最大值。比如当使用EventTime字段对应的原始数据为2020-09-17、2020-09-30，分区表达式为PARTITION BY toYYYYMM(EventTime)，即按月分区。partition.dat中保存的值将会是2019-09，而minmax索引中保存的值将会是2020-09-17 2020-09-30。

注意点

多次插入数据，会生成多个分区文件

-- 新插入两条数据
INSERT INTO emp_mergetree VALUES (5,'robin','北京',35,'财务部',50000),(6,'lilei','北京',38,'销售事部',50000);

-- 查询结果
select * from emp_mergetree;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name──┬─work_place─┬─age─┬─depart───┬───salary─┐
│      5 │ robin │ 北京       │  35 │ 财务部   │ 50000.00 │
│      6 │ lilei │ 北京       │  38 │ 销售事部 │ 50000.00 │
└────────┴───────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

可以看出，新插入的数据新生成了一个数据块，并没有与原来的分区数据在一起，我们可以执行optimize命令，执行合并操作

-- 执行合并操作
node1 :) OPTIMIZE TABLE emp_mergetree PARTITION '北京';
-- 再次执行查询
select * from emp_mergetree;   
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name──┬─work_place─┬─age─┬─depart───┬───salary─┐
│      3 │ bob   │ 北京       │  33 │ 财务部   │ 50000.00 │
│      5 │ robin │ 北京       │  35 │ 财务部   │ 50000.00 │
│      6 │ lilei │ 北京       │  38 │ 销售事部 │ 50000.00 │
└────────┴───────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

执行上面的合并操作之后，会新生成一个该分区的文件夹，原理的分区文件夹不变。

在MergeTree中主键并不用于去重，而是用于索引，加快查询速度

-- 插入一条相同主键的数据
 INSERT INTO emp_mergetree VALUES (1,'sam','杭州',35,'财务部',50000);
-- 会发现该条数据可以插入，由此可知，并不会对主键进行去重
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name──┬─work_place─┬─age─┬─depart───┬───salary─┐
│      3 │ bob   │ 北京       │  33 │ 财务部   │ 50000.00 │
│      5 │ robin │ 北京       │  35 │ 财务部   │ 50000.00 │
│      6 │ lilei │ 北京       │  38 │ 销售事部 │ 50000.00 │
└────────┴───────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ sam  │ 杭州       │  35 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

5.4.2 ReplacingMergeTree表引擎

上文提到MergeTree表引擎无法对相同主键的数据进行去重，ClickHouse提供了ReplacingMergeTree引擎，可以针对相同主键的数据进行去重，它能够在合并分区时删除重复的数据。值得注意的是，ReplacingMergeTree只是在一定程度上解决了数据重复问题，但是并不能完全保障数据不重复。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

[ver]：可选参数，列的版本，可以是UInt、Date或者DateTime类型的字段作为版本号。该参数决定了数据去重的方式。
当没有指定[ver]参数时，保留最新的数据；如果指定了具体的值，保留最大的版本数据。

建表示例

CREATE TABLE emp_replacingmergetree (emp_id UInt16 COMMENT '员工id',name String COMMENT '员工姓名',work_place String COMMENT '工作地点',age UInt8 COMMENT '员工年龄',depart String COMMENT '部门',salary Decimal32(2) COMMENT '工资')ENGINE=ReplacingMergeTree() ORDER BY emp_id PRIMARY KEY emp_id PARTITION BY work_place;

 -- 插入数据 
INSERT INTO emp_replacingmergetree VALUES (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_replacingmergetree VALUES (3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000); 

select * from emp_replacingmergetree;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

注意点

当我们再次向该表插入具有相同主键的数据时，观察查询数据的变化

INSERT INTO emp_replacingmergetree VALUES (1,'tom','上海',25,'技术部',50000);
-- 查询数据，由于没有进行合并，所以存在主键重复的数据
select * from emp_replacingmergetree;   
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

-- 执行合并操作
optimize table emp_replacingmergetree final;
-- 再次查询，相同主键的数据，保留最近插入的数据，旧的数据被清除
select * from emp_replacingmergetree; 
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 50000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

从上面的示例中可以看出，ReplacingMergeTree是支持对数据去重的，那么是根据什么进行去重呢？答案是：ReplacingMergeTree在去除重复数据时，是以ORDERBY排序键为基准的，而不是PRIMARY KEY。我们在看一个示例：

CREATE TABLE emp_replacingmergetree1 (emp_id UInt16 COMMENT '员工id',name String COMMENT '员工姓名',work_place String COMMENT '工作地点',age UInt8 COMMENT '员工年龄',depart String COMMENT '部门',salary Decimal32(2) COMMENT '工资')ENGINE=ReplacingMergeTree() ORDER BY (emp_id,name) PRIMARY KEY emp_id PARTITION BY work_place;

ORDER BY (emp_id,name) -- 注意排序key是两个字段
PRIMARY KEY emp_id     -- 主键是一个字段

 -- 插入数据 
INSERT INTO emp_replacingmergetree1 VALUES (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_replacingmergetree1 VALUES (3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000);

再次向该表中插入相同emp_id和name的数据，并执行合并操作，再观察数据

-- 插入数据
INSERT INTO emp_replacingmergetree1 VALUES (1,'tom','上海',25,'技术部',50000),(1,'sam','上海',25,'技术部',20000);
-- 执行合并操作
optimize table emp_replacingmergetree1 final;
-- 再次查询，可见相同的emp_id和name数据被去重，而形同的主键emp_id不会去重
-- ReplacingMergeTree在去除重复数据时，是以ORDERBY排序键为基准的，而不是PRIMARY KEY
select * from emp_replacingmergetree1; 
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ sam  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 50000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

至此，我们知道了ReplacingMergeTree是支持去重的，并且是按照ORDERBY排序键为基准进行去重的。细心的你会发现，上面的重复数据是在一个分区内的，那么如果重复的数据不在一个分区内，会发生什么现象呢？我们再次向上面的emp_replacingmergetree1表插入不同分区的重复数据

-- 插入数据
INSERT INTO emp_replacingmergetree1 VALUES (1,'tom','北京',26,'技术部',10000);
-- 执行合并操作
optimize table emp_replacingmergetree1 final;
-- 再次查询
-- 发现  1 │ tom  │ 北京       │  26 │ 技术部 │ 10000.00
-- 与    1 │ tom  │ 上海       │  25 │ 技术部 │ 50000.00
-- 数据重复，因为这两行数据不在同一个分区内
-- 这是因为ReplacingMergeTree是以分区为单位删除重复数据的。
-- 只有在相同的数据分区内重复的数据才可以被删除，而不同数据分区之间的重复数据依然不能被剔除
select * from emp_replacingmergetree1; 
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ sam  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 50000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 北京       │  26 │ 技术部 │ 10000.00 │
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

总结

如何判断数据重复

ReplacingMergeTree在去除重复数据时，是以ORDERBY排序键为基准的，而不是PRIMARY KEY。

何时删除重复数据

在执行分区合并时，会触发删除重复数据。optimize的合并操作是在后台执行的，无法预测具体执行时间点，除非是手动执行。

不同分区的重复数据不会被去重

ReplacingMergeTree是以分区为单位删除重复数据的。只有在相同的数据分区内重复的数据才可以被删除，而不同数据分区之间的重复数据依然不能被剔除。

数据去重的策略是什么

如果没有设置**[ver]版本号**，则保留同一组重复数据中的最新插入的数据；如果设置了**[ver]版本号**，则保留同一组重复数据中ver字段取值最大的那一行。

optimize命令使用

一般在数据量比较大的情况，尽量不要使用该命令。因为在海量数据场景下，执行optimize要消耗大量时间

5.4.3 SummingMergeTree表引擎

该引擎继承了MergeTree引擎，当合并

SummingMergeTree

表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值，即如果存在重复的数据，会对对这些重复的数据进行合并成一条数据，类似于group by的效果。

推荐将该引擎和

MergeTree

一起使用。例如，将完整的数据存储在

MergeTree

表中，并且使用

SummingMergeTree

来存储聚合数据。这种方法可以避免因为使用不正确的主键组合方式而丢失数据。

如果用户只需要查询数据的汇总结果，不关心明细数据，并且数据的汇总条件是预先明确的，即GROUP BY的分组字段是确定的，可以使用该表引擎。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = SummingMergeTree([columns]) -- 指定合并汇总字段
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

建表示例

CREATE TABLE emp_summingmergetree (emp_id UInt16 COMMENT '员工id',name String COMMENT '员工姓名',work_place String COMMENT '工作地点',age UInt8 COMMENT '员工年龄',depart String COMMENT '部门',salary Decimal32(2) COMMENT '工资')ENGINE=SummingMergeTree(salary) ORDER BY (emp_id,name) PRIMARY KEY emp_id PARTITION BY work_place;
  
ORDER BY (emp_id,name) -- 注意排序key是两个字段
PRIMARY KEY emp_id     -- 主键是一个字段
 -- 插入数据 
INSERT INTO emp_summingmergetree VALUES (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_summingmergetree VALUES (3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000);

当我们再次插入具有相同emp_id,name的数据时，观察结果

INSERT INTO emp_summingmergetree VALUES (1,'tom','上海',25,'信息部',10000),(1,'tom','北京',26,'人事部',10000);

select * from emp_summingmergetree;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 北京       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

-- 执行合并操作
optimize table emp_summingmergetree final;
select * from emp_summingmergetree; 
-- 再次查询，新插入的数据 1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 
-- 原来的数据 ：        1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00
-- 这两行数据合并成：    1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 北京       │  26 │ 人事部 │ 10000.00 │
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

注意点

要保证PRIMARY KEY expr指定的主键是ORDER BY expr 指定字段的前缀，比如

-- 允许
ORDER BY (A,B,C) 
PRIMARY KEY A  


-- 会报错
--  DB::Exception: Primary key must be a prefix of the sorting key
ORDER BY (A,B,C) 
PRIMARY KEY B

这种强制约束保障了即便在两者定义不同的情况下，主键仍然是排序键的前缀，不会出现索引与数据顺序混乱的问题。

总结

SummingMergeTree是根据什么对两条数据进行合并的

用ORBER BY排序键作为聚合数据的条件Key。即如果排序key是相同的，则会合并成一条数据，并对指定的合并字段进行聚合。

仅对分区内的相同排序key的数据行进行合并

以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合Key相同的数据会被合并汇总，而不同分区之间的数据则不会被汇总。

如果没有指定聚合字段，会怎么聚合

如果没有指定聚合字段，则会按照非主键的数值类型字段进行聚合

对于非汇总字段的数据，该保留哪一条

如果两行数据除了排序字段相同，其他的非聚合字段不相同，那么在聚合发生时，会保留最初的那条数据，新插入的数据对应的那个字段值会被舍弃

-- 新插入的数据:        1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 
-- 最初的数据 ：        1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00
-- 聚合合并的结果：      1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00

5.4.4 Aggregatingmergetree表引擎

该表引擎继承自MergeTree，可以使用

AggregatingMergeTree

表来做增量数据统计聚合。如果要按一组规则来合并减少行数，则使用

AggregatingMergeTree

是合适的。AggregatingMergeTree是通过预先定义的聚合函数计算数据并通过二进制的格式存入表内。

与SummingMergeTree的区别在于：SummingMergeTree对非主键列进行sum聚合，而AggregatingMergeTree则可以指定各种聚合函数。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

建表示例

CREATE TABLE emp_aggregatingmergeTree (emp_id UInt16 COMMENT '员工id',name String COMMENT '员工姓名',work_place String COMMENT '工作地点',age UInt8 COMMENT '员工年龄',depart String COMMENT '部门',salary  AggregateFunction(sum,Decimal32(2)) COMMENT '工资')ENGINE=AggregatingMergeTree() ORDER BY (emp_id,name) PRIMARY KEY emp_id PARTITION BY work_place;
  
  
ORDER BY (emp_id,name) -- 注意排序key是两个字段
PRIMARY KEY emp_id     -- 主键是一个字段

对于AggregateFunction类型的列字段，在进行数据的写入和查询时与其他的表引擎有很大区别，在写入数据时，需要调用**-State函数；而在查询数据时，则需要调用相应的-Merge函数。对于上面的建表语句而言，需要使用sumState**函数进行数据插入

-- 插入数据，
-- 注意：需要使用INSERT…SELECT语句进行数据插入
INSERT INTO TABLE emp_aggregatingmergeTree SELECT 1,'tom','上海',25,'信息部',sumState(toDecimal32(10000,2));
INSERT INTO TABLE emp_aggregatingmergeTree SELECT 1,'tom','上海',25,'信息部',sumState(toDecimal32(20000,2));
-- 查询数据
SELECT emp_id,name,sumMerge(salary) FROM emp_aggregatingmergeTree GROUP BY emp_id,name;
-- 结果输出
┌─emp_id─┬─name─┬─sumMerge(salary)─┐
│      1 │ tom  │         30000.00 │
└────────┴──────┴──────────────────┘

上面演示的用法非常的麻烦，其实更多的情况下，我们可以结合物化视图一起使用，将它作为物化视图的表引擎。而这里的物化视图是作为其他数据表上层的一种查询视图。

AggregatingMergeTree通常作为物化视图的表引擎，与普通MergeTree搭配使用。

-- 创建一个MereTree引擎的明细表
-- 用于存储全量的明细数据
-- 对外提供实时查询
CREATE TABLE emp_mergetree_base (emp_id UInt16 COMMENT '员工id',name String COMMENT '员工姓名',work_place String COMMENT '工作地点',age UInt8 COMMENT '员工年龄',depart String COMMENT '部门',salary Decimal32(2) COMMENT '工资')ENGINE=MergeTree() ORDER BY (emp_id,name) PARTITION BY work_place;
  
-- 创建一张物化视图
-- 使用AggregatingMergeTree表引擎
CREATE MATERIALIZED VIEW view_emp_agg ENGINE = AggregatingMergeTree() PARTITION BY emp_id ORDER BY (emp_id,name) AS SELECT emp_id,name,sumState(salary) AS salary FROM emp_mergetree_base GROUP BY emp_id,name;

-- 向基础明细表emp_mergetree_base插入数据
INSERT INTO emp_mergetree_base VALUES (1,'tom','上海',25,'技术部',20000),(1,'tom','上海',26,'人事部',10000);

-- 查询物化视图
SELECT emp_id,name,sumMerge(salary) FROM view_emp_agg GROUP BY emp_id,name;
-- 结果
┌─emp_id─┬─name─┬─sumMerge(salary)─┐
│      1 │ tom  │         50000.00 │
└────────┴──────┴──────────────────┘

5.5.5 CollapsingMergeTree表引擎

CollapsingMergeTree就是一种通过以增代删的思路，支持行级数据修改和删除的表引擎。它通过定义一个sign标记位字段，记录数据行的状态。如果sign标记为1，则表示这是一行有效的数据；如果sign标记为-1，则表示这行数据需要被删除。当CollapsingMergeTree分区合并时，同一数据分区内，sign标记为1和-1的一组数据会被抵消删除。

每次需要新增数据时，写入一行sign标记为1的数据；需要删除数据时，则写入一行sign标记为-1的数据。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = CollapsingMergeTree(sign)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

建表示例

上面的建表语句使用CollapsingMergeTree(sign)，其中字段sign是一个Int8类型的字段

使用方式

CollapsingMergeTree同样是以ORDER BY排序键作为判断数据唯一性的依据。

-- 插入新增数据,sign=1表示正常数据
INSERT INTO emp_collapsingmergetree VALUES (1,'tom','上海',25,'技术部',20000,1);

-- 更新上述的数据
-- 首先插入一条与原来相同的数据(ORDER BY字段一致),并将sign置为-1
INSERT INTO emp_collapsingmergetree VALUES (1,'tom','上海',25,'技术部',20000,-1);

-- 再插入更新之后的数据
INSERT INTO emp_collapsingmergetree VALUES (1,'tom','上海',25,'技术部',30000,1);

-- 查看一下结果
select * from emp_collapsingmergetree ;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │   -1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │    1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00 │    1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘

-- 执行分区合并操作
optimize table emp_collapsingmergetree;
-- 再次查询，sign=1与sign=-1的数据相互抵消了，即被删除
select * from emp_collapsingmergetree ;

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00 │    1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘

注意点

分区合并

分数数据折叠不是实时的，需要后台进行Compaction操作，用户也可以使用手动合并命令，但是效率会很低，一般不推荐在生产环境中使用。

当进行汇总数据操作时，可以通过改变查询方式，来过滤掉被删除的数据

SELECT emp_id,name,sum(salary * sign)FROM emp_collapsingmergetree GROUP BY emp_id, name HAVING sum(sign) > 0;
┌─emp_id─┬─name─┬─sum(multiply(salary, sign))─┐
│      1 │ tom  │                    30000.00 │
└────────┴──────┴─────────────────────────────┘

只有相同分区内的数据才有可能被折叠。其实，当我们修改或删除数据时，这些被修改的数据通常是在一个分区内的，所以不会产生影响。

数据写入顺序

值得注意的是：CollapsingMergeTree对于写入数据的顺序有着严格要求，否则导致无法正常折叠。

-- 建表
CREATE TABLE emp_collapsingmergetree_order (emp_id UInt16 COMMENT '员工id',name String COMMENT '员工姓名',work_place String COMMENT '工作地点',age UInt8 COMMENT '员工年龄',depart String COMMENT '部门',salary Decimal32(2) COMMENT '工资',sign Int8)ENGINE=CollapsingMergeTree(sign) ORDER BY (emp_id,name) PARTITION BY work_place; 
  
-- 先插入需要被删除的数据，即sign=-1的数据
INSERT INTO emp_collapsingmergetree_order VALUES (1,'tom','上海',25,'技术部',20000,-1);
-- 再插入sign=1的数据
INSERT INTO emp_collapsingmergetree_order VALUES (1,'tom','上海',25,'技术部',20000,1);
-- 查询表
SELECT * FROM emp_collapsingmergetree_order;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │   -1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │    1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘

-- 执行合并操作
optimize table emp_collapsingmergetree_order;
-- 再次查询表
-- 旧数据依然存在
SELECT * FROM emp_collapsingmergetree_order;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │   -1 │
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │    1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘

如果数据的写入程序是单线程执行的，则能够较好地控制写入顺序；如果需要处理的数据量很大，数据的写入程序通常是多线程执行的，那么此时就不能保障数据的写入顺序了。在这种情况下，CollapsingMergeTree的工作机制就会出现问题。但是可以通过VersionedCollapsingMergeTree的表引擎得到解决。

5.5.6 VersionedCollapsingMergeTree表引擎

上面提到CollapsingMergeTree表引擎对于数据写入乱序的情况下，不能够实现数据折叠的效果。VersionedCollapsingMergeTree表引擎的作用与CollapsingMergeTree完全相同，它们的不同之处在于，VersionedCollapsingMergeTree对数据的写入顺序没有要求，在同一个分区内，任意顺序的数据都能够完成折叠操作。

VersionedCollapsingMergeTree使用version列来实现乱序情况下的数据折叠。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = VersionedCollapsingMergeTree(sign, version)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

可以看出：该引擎除了需要指定一个sign标识之外，还需要指定一个UInt8类型的version版本号。

建表示例

CREATE TABLE emp_versioned (emp_id UInt16 COMMENT '员工id',name String COMMENT '员工姓名',work_place String COMMENT '工作地点',age UInt8 COMMENT '员工年龄',depart String COMMENT '部门',salary Decimal32(2) COMMENT '工资',sign Int8,version Int8)ENGINE=VersionedCollapsingMergeTree(sign, version) ORDER BY (emp_id,name) PARTITION BY work_place;
  
-- 先插入需要被删除的数据，即sign=-1的数据
INSERT INTO emp_versioned VALUES (1,'tom','上海',25,'技术部',20000,-1,1);
-- 再插入sign=1的数据
INSERT INTO emp_versioned VALUES (1,'tom','上海',25,'技术部',20000,1,1);
-- 在插入一个新版本数据
INSERT INTO emp_versioned VALUES (1,'tom','上海',25,'技术部',30000,1,2);

-- 先不执行合并，查看表数据
select * from emp_versioned;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │    1 │       1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │   -1 │       1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00 │    1 │       2 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘

-- 获取正确查询结果
SELECT emp_id,name,sum(salary * sign) FROM emp_versioned GROUP BY emp_id,name HAVING sum(sign) > 0;
┌─emp_id─┬─name─┬─sum(multiply(salary, sign))─┐
│      1 │ tom  │                    30000.00 │
└────────┴──────┴─────────────────────────────┘

-- 手动合并
optimize table emp_versioned;

-- 再次查询
select * from emp_versioned;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00 │    1 │       2 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘

可见上面虽然在插入数据乱序的情况下，依然能够实现折叠的效果。之所以能够达到这种效果，是因为在定义version字段之后，VersionedCollapsingMergeTree会自动将version作为排序条件并增加到ORDER BY的末端，就上述的例子而言，最终的排序字段为ORDER BY emp_id,name，version desc。

5.5 外部集成表引擎

ClickHouse提供了许多与外部系统集成的方法，包括一些表引擎。这些表引擎与其他类型的表引擎类似，可以用于将外部数据导入到ClickHouse中，或者在ClickHouse中直接操作外部数据源。

例如直接读取HDFS的文件或者MySQL数据库的表。这些表引擎只负责元数据管理和数据查询，而它们自身通常并不负责数据的写入，数据文件直接由外部系统提供。目前ClickHouse提供了下面的外部集成表引擎：

ODBC：通过指定odbc连接读取数据源
JDBC：通过指定jdbc连接读取数据源；
MySQL：将MySQL作为数据存储，直接查询其数据
HDFS：直接读取HDFS上的特定格式的数据文件；
Kafka：将Kafka数据导入ClickHouse
RabbitMQ：与Kafka类似

5.5.1 HDFS

使用方式

ENGINE = HDFS(URI, format)

URI：HDFS文件路径
format：文件格式，比如CSV、JSON、TSV等

使用示例

-- 建表
CREATE TABLE hdfs_engine_table(emp_id UInt16 COMMENT '员工id',name String COMMENT '员工姓名',work_place String COMMENT '工作地点',age UInt8 COMMENT '员工年龄',depart String COMMENT '部门',salary Decimal32(2) COMMENT '工资') ENGINE=HDFS('hdfs://node1:9000/ch/hdfs_engine_table', 'CSV');

-- 写入数据
INSERT INTO hdfs_engine_table VALUES (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
-- 查询数据
select * from hdfs_engine_table;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

vi ch.csv
--添加不同内容
3,'tom','上海',25,'技术部',20000
hdfs dfs -appendToFile ch.csv /ch/hdfs_engine_table

--再在HDFS上其对应的文件，添加几条数据，再次查看
select * from hdfs_engine_table;

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
│      3 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

在添加HDFS如果出现此错误

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mSaxANin-1605512285334)(assets/1604316991031.png)]

要在hdfs-site.xml中添加如下配置

<property>
<name>
dfs.client.block.write.replace-datanode-on-failure.enable
</name>
<value>true</value>
</property>
<property>
<name>
dfs.client.block.write.replace-datanode-on-failure.policy
</name>
<value>never</value>
</property>

可以看出，这种方式与使用Hive类似，我们直接可以将HDFS对应的文件映射成ClickHouse中的一张表，这样就可以使用SQL操作HDFS上的文件了。

值得注意的是：ClickHouse并不能够删除HDFS上的数据，当我们在ClickHouse客户端中删除了对应的表，只是删除了表结构，HDFS上的文件并没有被删除，这一点跟Hive的外部表十分相似。

注意：要手动先创建目录

5.5.2 MySQL

在第四章中介绍了MySQL数据库引擎，即ClickHouse可以创建一个MySQL数据引擎，这样就可以在ClickHouse中操作其对应的数据库中的数据。其实，ClickHouse同样支持MySQL表引擎，即映射一张MySQL中的表到ClickHouse中。

注意：对于MySQL表引擎，不支持UPDATE和DELETE操作，比如执行下面命令时，会报错：

-- 执行更新
ALTER TABLE mysql_engine_table UPDATE name = 'hanmeimei' WHERE id = 1;
-- 执行删除
ALTER TABLE mysql_engine_table DELETE WHERE id = 1;
-- 报错
DB::Exception: Mutations are not supported by storage MySQL.

5.5.3 JDBC

使用方式

JDBC表引擎不仅可以对接MySQL数据库，还能够与PostgreSQL等数据库。为了实现JDBC连接，ClickHouse使用了clickhouse-jdbc-bridge的查询代理服务。

首先我们需要下载clickhouse-jdbc-bridge，然后按照ClickHouse的github中的步骤进行编译，编译完成之后会有一个clickhouse-jdbc-bridge-1.0.jar的jar文件，除了需要该文件之外，还需要JDBC的驱动文件，本文使用的是MySQL，所以还需要下载MySQL驱动包。将MySQL的驱动包和clickhouse-jdbc-bridge-1.0.jar文件放在了/usr/local/clickhouse/softwares路径下，执行如下命令：

其中

--driver-path

是MySQL驱动的jar所在的路径，

listen-host

是代理服务绑定的主机。默认情况下，绑定的端口是：9019。

然后我们再配置

/etc/clickhouse-server/config.xml

，在文件中添加如下配置，然后重启服务。

<jdbc_bridge>    
<host>node1</host>    
<port>9019</port>
</jdbc_bridge>

使用示例

直接查询MySQL中对应的表

创建一张映射表

-- 语法
CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    columns list...
)
ENGINE = JDBC(dbms_uri, external_database, external_table)

-- MySQL建表
CREATE TABLE jdbc_table_mysql (order_id INT NOT NULL AUTO_INCREMENT,amount FLOAT NOT NULL,PRIMARY KEY (order_id));
-- MySQL插入数据
INSERT INTO jdbc_table_mysql VALUES (1,200);

-- 在ClickHouse中建表
CREATE TABLE jdbc_table(order_id Int32,amount Float32) ENGINE JDBC('jdbc:mysql://192.168.216.111:3306/?user=root&password=root','sales_source','jdbc_table_mysql');

-- clickhouse查询数据
select * from jdbc_table;
┌─order_id─┬─amount─┐
│        1 │    200 │
└──────────┴────────┘

5.5.4 Kakfa

使用方式

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'host:port',
    kafka_topic_list = 'topic1,topic2,...',
    kafka_group_name = 'group_name',
    kafka_format = 'data_format'[,]
    [kafka_row_delimiter = 'delimiter_symbol',]
    [kafka_schema = '',]
    [kafka_num_consumers = N,]
    [kafka_max_block_size = 0,]
    [kafka_skip_broken_messages = N,]
    [kafka_commit_every_batch = 0,]
    [kafka_thread_per_consumer = 0]

kafka_broker_list ：逗号分隔的brokers地址 (localhost:9092).
kafka_topic_list ：Kafka 主题列表，多个主题用逗号分隔.
kafka_group_name ：消费者组.
kafka_format – Message format. 比如 JSONEachRow 、JSON、CSV等等

使用示例

在kafka中创建ck_topic主题，并向该主题写入数据

CREATE TABLE kafka_table (id UInt64,name String) ENGINE = Kafka() SETTINGS kafka_broker_list = 'node1:9092',kafka_topic_list = 'test',kafka_group_name = 'group1',kafka_format = 'JSONEachRow';
--添加数据
[root@node1 kafka_2.11-1.1.1]# ./bin/kafka-console-producer.sh --topic test --broker-list node1:9092
{"id":2,"name":"ww"}
-- 查询
select * from kafka_table ;

注意点

当我们一旦查询完毕之后，ClickHouse会删除表内的数据，其实Kafka表引擎只是一个数据管道，我们可以通过物化视图的方式访问Kafka中的数据。

首先创建一张Kafka表引擎的表，用于从Kafka中读取数据
然后再创建一张普通表引擎的表，比如MergeTree，面向终端用户使用
最后创建物化视图，用于将Kafka引擎表实时同步到终端用户所使用的表中

--  创建Kafka引擎表
CREATE TABLE kafka_table_consumer (id UInt64,name String) ENGINE = Kafka() SETTINGS kafka_broker_list = 'node1:9092',kafka_topic_list = 'test',kafka_group_name = 'group2',kafka_format = 'JSONEachRow';

-- 创建一张终端用户使用的表
CREATE TABLE kafka_table_mergetree (id UInt64 ,name String)ENGINE=MergeTree() ORDER BY id;
  
-- 创建物化视图，同步数据
CREATE MATERIALIZED VIEW consumer TO kafka_table_mergetree AS SELECT id,name FROM kafka_table_consumer;
-- 查询，多次查询，已经被查询的数据依然会被输出
select * from kafka_table_mergetree;

┌─id─┬─name─┐
│  2 │ ww   │
└────┴──────┘

5.6 Memory表引擎

Memory表引擎直接将数据保存在内存中，数据既不会被压缩也不会被格式转换。当ClickHouse服务重启的时候，Memory表内的数据会全部丢失。一般在测试时使用。

5.7 Distributed表引擎

使用方式

Distributed表引擎是分布式表的代名词，它自身不存储任何数据，数据都分散存储在某一个分片上，能够自动路由数据至集群中的各个节点，所以Distributed表引擎需要和其他数据表引擎一起协同工作。

所以，一张分布式表底层会对应多个本地分片数据表，由具体的分片表存储数据，分布式表与分片表是一对多的关系

Distributed表引擎的定义形式如下所示

各个参数的含义分别如下：

cluster_name：集群名称，与集群配置中的自定义名称相对应。
database_name：数据库名称
table_name：表名称
sharding_key：可选的，用于分片的key值，在数据写入的过程中，分布式表会依据分片key的规则，将数据分布到各个节点的本地表。

注：

创建分布式表是读时检查的机制，也就是说对创建分布式表和本地表的顺序并没有强制要求。

同样值得注意的是，在上面的语句中使用了ON CLUSTER分布式DDL，这意味着在集群的每个分片节点上，都会创建一张Distributed表，这样便可以从其中任意一端发起对所有分片的读、写请求。

使用示例

参考第四章中的使用即可。

第六章函数

ClickHouse Bitmap官网：

中文：https://clickhouse.tech/docs/zh/sql-reference/functions/bitmap-functions/

,kafka_group_name = ‘group1’,kafka_format = ‘JSONEachRow’;

–添加数据

[[email protected] kafka_2.11-1.1.1]# ./bin/kafka-console-producer.sh --topic test --broker-list node1:9092

{“id”:2,“name”:“ww”}

– 查询

select * from kafka_table ;

#### 注意点

当我们一旦查询完毕之后，ClickHouse会删除表内的数据，其实Kafka表引擎只是一个数据管道，我们可以通过物化视图的方式访问Kafka中的数据。

- 首先创建一张Kafka表引擎的表，用于从Kafka中读取数据
- 然后再创建一张普通表引擎的表，比如MergeTree，面向终端用户使用
- 最后创建物化视图，用于将Kafka引擎表实时同步到终端用户所使用的表中



```sql
--  创建Kafka引擎表
CREATE TABLE kafka_table_consumer (id UInt64,name String) ENGINE = Kafka() SETTINGS kafka_broker_list = 'node1:9092',kafka_topic_list = 'test',kafka_group_name = 'group2',kafka_format = 'JSONEachRow';

-- 创建一张终端用户使用的表
CREATE TABLE kafka_table_mergetree (id UInt64 ,name String)ENGINE=MergeTree() ORDER BY id;
  
-- 创建物化视图，同步数据
CREATE MATERIALIZED VIEW consumer TO kafka_table_mergetree AS SELECT id,name FROM kafka_table_consumer;
-- 查询，多次查询，已经被查询的数据依然会被输出
select * from kafka_table_mergetree;

┌─id─┬─name─┐
│  2 │ ww   │
└────┴──────┘

5.6 Memory表引擎

5.7 Distributed表引擎

使用方式

所以，一张分布式表底层会对应多个本地分片数据表，由具体的分片表存储数据，分布式表与分片表是一对多的关系

Distributed表引擎的定义形式如下所示

各个参数的含义分别如下：

cluster_name：集群名称，与集群配置中的自定义名称相对应。
database_name：数据库名称
table_name：表名称
sharding_key：可选的，用于分片的key值，在数据写入的过程中，分布式表会依据分片key的规则，将数据分布到各个节点的本地表。

注：

创建分布式表是读时检查的机制，也就是说对创建分布式表和本地表的顺序并没有强制要求。

同样值得注意的是，在上面的语句中使用了ON CLUSTER分布式DDL，这意味着在集群的每个分片节点上，都会创建一张Distributed表，这样便可以从其中任意一端发起对所有分片的读、写请求。

使用示例

参考第四章中的使用即可。

第六章函数

ClickHouse Bitmap官网：

中文：https://clickhouse.tech/docs/zh/sql-reference/functions/bitmap-functions/

英文：https://clickhouse.tech/docs/en/sql-reference/functions/bitmap-functions/

【大数据开发】ClickHouse——深入ClickHouse第一章 clickhouse概述第二章 单机版安装第三章 集群安装第四章 入门操作第五章 库表引擎第六章 函数第六章 函数

文章目录

第一章 clickhouse概述

1.1 clickhouse的定义

1.2 clickhouse的来源

1.3 clickhouse应用领域

1.4 clickhouse存储层

1.5 clickhouse优点

1.6 clickhouse缺点

第二章 单机版安装

2.1 rpm安装(也可以在线yum安装)

2.2 配置

2.3 服务启动

2.4 客户端连接

第三章 集群安装

3.1 安装

3.2 配置

3.3 服务启动

3.4 客户端连接

第四章 入门操作

4.1 伪分布式基础操作

4.1.1 创建数据库

4.1.2 创建表

4.2 分布式DDL操作

4.3 数据类型

4.3.1 UUID

4.3.2 Datetime64

4.3.3 Decimal

4.3.4 Float32,Float64

4.3.5 Tuple

4.3.6 UInt8,UInt16,UInt32,UInt64,Int8,Int16,Int32,Int64

4.3.7 可为空（类型名称)

4.3.8 字符串

4.3.9 布尔值

4.3.10 数组

第五章 库表引擎

5.1 表引擎作用

5.2 表引擎分类

5.3 Log系列表引擎

5.3.1 应用场景

5.3.2 Log系列表引擎的特点

共性特点

区别

5.3.3 TinyLog表引擎使用

5.3.4 StripLog表引擎

5.3.5 Log表引擎

5.4 MergeTree系列引擎

5.4.1 MergeTree表引擎

建表语法

建表示例

注意点

5.4.2 ReplacingMergeTree表引擎

建表语法

建表示例

注意点

总结

5.4.3 SummingMergeTree表引擎

建表语法

建表示例

注意点

总结

5.4.4 Aggregatingmergetree表引擎

建表语法

建表示例

5.5.5 CollapsingMergeTree表引擎

建表语法

建表示例

使用方式

注意点

5.5.6 VersionedCollapsingMergeTree表引擎

建表语法

建表示例

5.5 外部集成表引擎

5.5.1 HDFS

使用方式

使用示例

5.5.2 MySQL

5.5.3 JDBC

使用方式

使用示例

【大数据开发】ClickHouse——深入ClickHouse第一章 clickhouse概述第二章单机版安装第三章集群安装第四章入门操作第五章库表引擎第六章函数第六章函数

第二章单机版安装

第三章集群安装

第四章入门操作

第五章库表引擎

第六章函数

第六章函数