ClickHouse学习教程

概述

a fast open-source OLAP database management system. It is column-oriented and allows to generate analytical reports using SQL queries in real-time.

Yandex（俄罗斯最大的搜索引擎）开源的一个用于实时数据分析的基于列存储的数据库，其处理数据的速度比传统方法快100-1000 倍。性能超过目前市场上可比的面向列的 DBMS，每秒钟每台服务器每秒处理数亿至十亿多行和数十千兆字节的数据。

核心特性：

完备的DBMS功能
关系模型与SQL查询
在线查询
列式存储与数据压缩：列式存储有助于数据压缩。数据中的重复项越多，则压缩率越高；压缩率越高，则数据体量越小；而数据体量越小，则数据在网络中的传输越快，对网络带宽和磁盘IO的压力也就越小。列式存储可降低IO和存储压力，方便向量化执行。
向量化执行引擎
多样化的表引擎
多线程与分布式
多主架构
数据分片与分布式查询

拥有完备的DBMS管理功能：

DDL：可以动态地创建、修改或删除数据库、表和视图，而无须重启服务
DML ：可以动态查询、插入、修改或删除数据
权限控制：可以按照用户粒度设置数据库或者表的操作权限，保障数据的安全性
数据备份与恢复：提供了数据备份导出与导入恢复机制，满足生产环境的要求
分布式管理：提供集群模式，能够自动管理多个数据库节点

学习资料

book

ClickHouse原理解析与应用实践

个人

朱凯

altinity

官网
 clickhouse-operator
Altinity是国外一家从事CK咨询、服务的公司，该公司高管由CK开发者，以及来自Percona的专家组成。

应用场景

OLAP，无事务
分析结构良好且不可变的事件或日志流
大宽表
读多余写：批量写入，不更新或少量更新

不适合的场景：

OLTP
Blob或文档存储
k-v请求

OLAP-CK应用场景分析
 CK深度揭秘

架构

图？

多主架构
HDFS、Spark、HBase和ES这类分布式系统，都采用Master-Slave主从架构，由一个管控节点作为Leader统筹全局。而CK则采用Multi-Master多主架构，集群中的每个节点角色对等，客户端访问任意一个节点都能得到相同的效果。多主架构优势：对等的角色使系统架构变得更加简单，不用再区分主控节点、数据节点和计算节点，集群中所有节点功能相同。天然规避单点故障问题，非常适合用于多数据中心、异地多活的场景。

Column与Field

作为一款百分之百的列式存储数据库，内存中的一列数据由一个Column对象表示。Column和Field是数据最基础的映射单元。本质上Column和Field就是一个个接口。IColumn是一个抽象接口，包含insertRangeFrom和insertFrom、用于分页的cut，及用于过滤的filter等方法。这些方法的具体实现对象则根据数据类型的不同，由相应的对象实现，例如ColumnString、ColumnArray和ColumnTuple等。

在大多数场合，CK都会以整列的方式操作数据。如果需要操作单个具体的数值，则需要使用Field对象，Field对象代表一个单值。与Column对象的泛化设计思路不同，Field对象使用聚合的设计模式。在Field对象内部聚合Null、UInt64、String和Array等13种数据类型及相应的处理逻辑。

DataType

数据的序列化和反序列化工作由DataType负责。IDataType接口定义许多正反序列化的方法，涵盖常用的二进制、文本、JSON、XML、CSV和Protobuf等多种格式类型。IDataType也使用泛化的设计模式，具体方法的实现逻辑由对应数据类型的实例承载，如DataTypeString、DataTypeArray等。
DataType虽然负责序列化相关工作，但它并不直接负责数据的读取，而是转由从Column或Field对象获取。在DataType的实现类中，聚合相应数据类型的Column对象和Field对象。如DataTypeString会引用字符串类型的ColumnString，而DataTypeArray则会引用数组类型的ColumnArray，以此类推。

Block与Block流

CK内部的数据操作是面向Block对象进行的，并采用流的形式。虽然Column和Filed组成数据的基本映射单元，但对应到实际操作，它们还缺少一些必要的信息，如数据类型及列名称。于是CK设计Block对象，Block对象可以看作数据表的子集。Block对象的本质是由数据对象、数据类型和列名称组成的三元组，即Column、DataType及列名称字符串。
Column提供数据的读取能力，而DataType知道如何正反序列化，所以Block在这些对象的基础之上实现进一步的抽象和封装，从而简化整个使用的过程，仅通过Block对象就能完成一系列的数据操作。在具体实现时，Block并没有直接聚合Column和DataType对象，而是通过ColumnWithTypeAndName对象进行间接引用。
有了Block对象这一层封装之后，对Block流的设计就是水到渠成的事情了。流操作有两组顶层接口：IBlockInputStream负责数据的读取和关系运算，IBlockOutputStream负责将数据输出到下一环节。Block流也使用泛化的设计模式，对数据的各种操作最终都会转换成其中一种流的实现。IBlockInputStream接口定义读取数据的若干个read虚方法，而具体的实现逻辑则交由它的实现类来填充。

IBlockInputStream接口总共有60多个实现类，它们涵盖CK数据摄取的方方面面。这些实现类大致可以分为三类：第一类用于处理数据定义的DDL操作，例如DDLQueryStatusInputStream等；第二类用于处理关系运算的相关操作，例如LimitBlockInput-Stream、JoinBlockInputStream及AggregatingBlockInputStream等；第三类则是与表引擎呼应，每一种表引擎都拥有与之对应的BlockInputStream实现，例如MergeTreeBaseSelect-BlockInputStream ( MergeTree表引擎 )、TinyLogBlockInputStream ( TinyLog表引擎 ) 及KafkaBlockInputStream ( Kafka表引擎 ) 等。

IBlockOutputStream的设计与IBlockInputStream如出一辙。IBlockOutputStream接口同样也定义了若干写入数据的write虚方法。它的实现类比IBlockInputStream要少许多，一共只有20多种。这些实现类基本用于表引擎的相关处理，负责将数据写入下一环节或者最终目的地，例如MergeTreeBlockOutputStream 、TinyLogBlockOutputStream及StorageFileBlock-OutputStream等。

Table

在数据表的底层设计中并没有所谓的Table对象，它直接使用IStorage接口指代数据表。不同的表引擎由不同的子类实现，如系统表、合并树表引擎、日志表引擎。IStorage接口定义DDL（如ALTER、RENAME、OPTIMIZE和DROP）、read和write方法，分别负责数据的定义、查询与写入。在数据查询时，IStorage负责根据AST查询语句的指示要求，返回指定列的原始数据。对Table发起的一次操作通常都会经历这样的过程，接收AST查询语句，根据AST返回指定列的数据，之后再将数据交由Interpreter做进一步处理。

Parser与Interpreter

Parser分析器负责创建AST对象；而Interpreter解释器则负责解释AST，并进一步创建查询的执行管道。它们与IStorage一起，串联起整个数据查询的过程。Parser分析器可以将一条SQL语句以递归下降的方法解析成AST语法树的形式。不同的SQL语句，会经由不同的Parser实现类解析。例如，有负责解析DDL查询语句的ParserRenameQuery、ParserDropQuery和ParserAlterQuery解析器，也有负责解析INSERT语句的ParserInsertQuery解析器，还有负责SELECT语句的ParserSelectQuery等。

Interpreter解释器的作用就像Service服务层一样，起到串联整个查询过程的作用，它会根据解释器的类型，聚合它所需要的资源。首先它会解析AST对象；然后执行"业务逻辑" ( 例如分支判断、设置参数、调用接口等 )；最终返回IBlock对象，以线程的形式建立起一个查询执行管道。

Cluster与Replication

集群由分片 ( Shard ) 组成，而每个分片又通过副本 (Replica ) 组成。与众不同的设计特性：

CK的1个节点只能拥有1个分片，即如果要实现1分片、1副本，则至少需要部署2个服务节点
分片只是一个逻辑概念，其物理承载还是由副本承担的

Functions 与Aggregate Functions

CK主要提供两类函数—普通函数和聚合函数。普通函数由IFunction接口定义，拥有数十种函数实现，如FunctionFormatDateTime、FunctionSubstring等。除了一些常见的函数 ( 诸如四则运算、日期转换等 ) 之外，也不乏一些非常实用的函数，例如网址提取函数、IP地址脱敏函数等。普通函数是没有状态的，函数效果作用于每行数据之上。当然，在函数具体执行的过程中，并不会一行一行地运算，而是采用向量化的方式直接作用于一整列数据。

聚合函数由IAggregateFunction接口定义，相比无状态的普通函数，聚合函数是有状态的。以COUNT聚合函数为例，其AggregateFunctionCount的状态使用整型UInt64记录。聚合函数的状态支持序列化与反序列化，所以能够在分布式节点之间进行传输，以实现增量计算。

安装

CK集群搭建从0到1
CK之集群搭建以及数据复制

端口

默认情况下，clickhouse-server 会在端口 8123 上监控 HTTP 请求

配置

配置文件包括/etc/clickhouse-server/config.xml，users.xml以及集群配置文件/etc/metrika.xml。通过metrika.xml可以看到节点登陆的明文用户名，密码。
配置说明

功能

访问权限控制

数据TTL

通过TTL提供数据生命周期管理的能力。目前支持几种不同粒度的TTL：

列级别TTL：当一列中的部分数据过期后，会被替换成默认值；当全列数据都过期后，会删除该列
行级别TTL：当某一行过期，直接删除该行
分区级别TTL：当分区过期，直接删除该分区

有序存储

CK支持在建表时，指定将数据按照某些列进行sort by。排序后，保证相同sort key的数据在磁盘上连续存储，且有序摆放。在进行等值、范围查询时，where条件命中的数据都紧密存储在一个或若干个连续的Block中，而不是分散的存储在任意多个Block，大幅减少需要IO的block数量。

数据类型与SQL语法

数据类型

数据类型介绍

语法

SQL解析时，CK大小写敏感。

DESCRIBE TABLE：返回建表，存储位置等信息；
CHECK TABLE：检查表中的数据是否损坏，返回两种结果： 0 – 数据已损坏；1：数据完整。该命令只支持 Log，TinyLog 和 StripeLog 引擎。

ORDER BY 决定每个分区中数据的排序规则；
PRIMARY KEY 决定一级索引(primary.idx)；
ORDER BY 可以指代PRIMARY KEY，通常只用声明ORDER BY 即可。
DROP 删除元数据和数据，TRUNCATE 只删除数据。

删除数据：ALTER TABLE [db.]table DELETE WHERE filter_expr

更新数据：ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr

条件：

版本号1.1.54388+，以及mergeTree引擎；
这两条命令是异步执行的，可以通过查看表 system.mutations 来查看命令的是否执行完毕
select * from system.mutations where table='test_update';
不可以用于分布式表，需要在每台机器上的local表中来执行

特殊语法

any
with as

函数有两种类型：

常规函数：可以通过一行数据产生结果
聚合函数：需要一组数据来产生结果

modulo(a, b)：计算两个字段的余数
uniqCombined

http://www.clickhouse.com.cn/topic/5a5f58519d28dfde2ddc5e2c
https://blog.csdn.net/u012111465/article/details/85250030
http://www.mooyle.com/bigdata/clickhouse/

巧用CK快速判断两个集合的相似度

开窗函数

CK开窗函数
 使用CK快速实现同比、环比分析
 如何在CK中实现RANK OVER排序

客户端

支持各种客户端：

curl：URL 的大小会限制在 16 KB
wget：
TCP：
HTTP：使用HTTP客户端的好处，当查询语句中进行group by后，紧跟着WITH TOTALS，并且是FORMAT JSON时能够返回总数，这使得可以计算百分比
各语言客户端

Java

官方提供：

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
</dependency>

系统表

系统表说明
系统表用于实现部分系统功能，并提供途径来获取与系统运行状态相关的信息。
系统表无法删除（但可以执行 DETACH）。
系统表中的数据或者元数据没有以文件的方式存储在磁盘上。server 启动时将创建所有系统表。
系统表是只读的。
系统表位于system数据库中。

主要的系统表：

system.asynchronous_metrics

表引擎

TinyLog

Memory

Merge

Distributed

Distributed(logs, default, hits[, sharding_key])
Distributed(cluster_2shards_2replicas, default, topic_lycf_h5_pageview_ubt, rand());
参数：服务器配置文件中的集群名，远程数据库名，远程表名，数据分片键（可选）。数据分片键的概念就是数据插入时是根据什么原则分配到具体分片上的。

集群的名称是在集群搭建时的metrika.xml文件中配置的，可在配置中配置任意数量的集群。
CK创建分布式表以及表引擎介绍

MergeTree

CK各种MergeTree的关系与作用

MergeTree

数据是由多组part文件组成，每一个part的数据是按照主键进行字典序排列。这些数据片段在后台按照一定规则合并。

SummingMergeTree

ReplacingMergeTree

和MergeTree的不同之处在于它会删除具有相同主键的重复项。但数据的去重只会在merge的过程中出现，merge操作是后台进程异步执行。
OPTIMIZE TABLE 操作可以手动触发merge操作，但会引发对大量数据的读和写操作，降低性能。因此，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，并不保证没有重复的数据出现。

AggregatingMergeTree

CK会将相同主键的行（在一个数据片段内）替换为单个存储一系列聚合函数状态的行。可以使用 AggregatingMergeTree 表来做增量数据统计聚合，包括物化视图的数据聚合。

SummingMergeTree

当合并 SummingMergeTree 表的数据片段时，CK会把所有具有相同主键的行进行汇总，将同一主键的行替换为包含sum后的一行记录。如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。

CollapsingMergeTree

在创建时与 MergeTree 基本一样，除了最后多一个参数，需要指定 Sign 位（必须是 Int8 类型）。CollapsingMergeTree 会异步的删除（折叠）除了特定列 Sign 1 和 -1 值以外的所有字段的值重复的行。

VersionedCollapsingMergeTree

是collapsingmergetree的升级，使用不同的collapsing算法，该算法允许使用多个线程以任何顺序插入数据。

CK不同引擎大比拼

分片与副本

分片

分片机制使得CK可以横向线性扩展，构建大规模集群。在分布式模式下，CK会将数据分为多个分片，并且分布到不同节点上。不同的分片策略在应对不同的SQL Pattern时，各有优势。提供丰富的sharding策略：

random随机分片：写入数据会被随机分发到分布式集群中的某个节点上
constant固定分片：写入数据会被分发到固定一个节点上
hash分片：按照某一列的值进行hash分片，join计算能够避免数据shuffle，可直接在本地local join
自定义表达式分片：指定任意合法表达式，根据表达式被计算后的值进行hash分片。数据分片，让CK可以充分利用整个集群的大规模并行计算能力，快速返回查询结果。

副本

INSERT 和 ALTER 操作副本间会复制。
CREATE，DROP，ATTACH，DETACH 和 RENAME 语句只会在单个服务器上执行，不会被复制。
复制表需要借助Zookeeper。
相比非复制表，写 zk 会导致 INSERT 的延迟略长一些，建议不要每秒一个INSERT，尽量批量写入。
复制是多主异步。
默认INSERT 语句仅等待一个副本写入成功后返回，insert_quorum参数可以指定几个副本写入成功后返回，默认为0。
数据块会自动去重。

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

ReplicatedMergeTree
ReplicatedSummingMergeTree
ReplicatedReplacingMergeTree
ReplicatedAggregatingMergeTree
ReplicatedCollapsingMergeTree
ReplicatedVersionedCollapsingMergeTree
ReplicatedGraphiteMergeTree：应用于Graphite data的数据汇总，减少存储容量，提高Graphite查询的效率。

https://cloud.tencent.com/developer/article/1510152

列式存储

想让查询变得更快，减少数据扫描范围和数据传输时的大小，列式存储和数据压缩是很常规的两个技术。
列式存储和数据压缩通常是伴生的，一般来说列式存储是数据压缩的前提。
按列存储相比按行存储的另一个优势是对数据压缩的友好性。
CK就是一款使用列式存储的数据库，数据按列进行组织，属于同一列的数据会被保存在一起，列与列之间也会由不同的文件分别保存。

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

分析场景中往往需要读大量行但是少数几个列。而列存模式下，只需要读取参与计算的列即可，极大减低IO cost，加速查询
同一列中的数据属于同一类型，压缩效果显著，更小的数据意味着读取也就更快,意味着同等大小的内存能够存放更多数据，系统cache效果更好
自由的压缩算法选择。不同列的数据具有不同的数据类型，适用的压缩算法也就不尽相同。可以针对不同列类型，选择最合适的压缩算法

物化视图

Materialized View，提高查询速度，本质：触发器。

原理
物化视图的原理是服务器觉得空闲的时候，帮你做一次select再insert的动作，可以通过物化视图来实现表间数据复制。
配置parallel_view_processing来实现物化视图是同步还是异步写。
物化视图也是一个普通的表。
pay attention：物化视图不能修改表名

用途
当有需要在同个实例进行多表多写时，就可以使用物化视图来实现CK帮你做表的数据复制，减少多写的带宽消耗，降低集群负载。
适用场景：
1. 需要有多个时间维度的表
2. 同一个表需要建立不同的索引粒度
How
CREATE MATERIALIZED VIEW db.table ENGINE = MergeTree() PARTITION BY day ORDER BY name AS SELECT * FROM db.old-table;
如果需要实现多个时间维度的表，则AS SELECT时对时间字段多处理即可。

http://www.hohode.com/2019/10/16/Clickhouse物化视图/
CREATE MATERIALIZED VIEW app.tracker_log (dayDate,stUInt64,u_iString,d_iString,tk_idString,timeDateTime,catString,actString,e_tString,c_p String) ENGINE = MergeTree() PARTITION BY day ORDER BY (st) POPULATE AS SELECT day, toUInt64(s_t) AS st, u_i, d_i, tk_id, toDateTime(toUInt64(st) / 1000) AS time, cat, act, e_t, multiIf((c_p = 'iOS') OR (c_p = 'IOS') OR (c_p = 'Android'), 'APP', (c_p = 'wap') OR (c_p = 'WAP'), 'WAP', 'PC') AS c_p FROM app.scene_tracker WHERE (s_t != '') AND ((u_i != '') OR (d_i != '')) AND (length(d_i) > 5) AND (length(cat) > 1) AND (length(act) > 1)

实例
https://blog.lzzrpi.xin/index.php/archives/205/

TO关键字指向目标表，缺点：CK不允许在TO中使用POPULATE关键字，需要注意，但可以避免，定义视图的时候可以用where字句，手动insert进物化视图的目标表中。

https://blog.51cto.com/11106335/1869120

http://www.clickhouse.com.cn/topic/5b33777b9d28dfde2ddc6197

向量化执行

寄存器硬件层面的特性，为上层应用程序的性能带来指数级的提升。向量化执行，可简单理解为消除程序的循环的优化方法。为实现向量化执行，需要利用CPU的SIMD指令，即用单条指令操作多条数据。CK目前利用SSE4.2指令集实现向量执行引擎，对内存中的列式数据，一个batch调用一次SIMD指令，减少函数调用次数、降低cache miss，且可以充分发挥SIMD指令的并行能力，大幅缩短计算耗时。

SIMD，Single Instruction Multiple Data，即用单条指令操作多条数据，通过数据并行以提高性能的一种实现方式 ( 其他的还有指令级并行和线程级并行 )，原理是在CPU寄存器层面实现数据的并行操作。

一文了解CK的向量化执行

本地表和分布式表

一张本地表等同于一份数据的分片。而分布式表本身不存储任何数据，它是本地表的访问代理，其作用类似分库中间件。借助分布式表，能够代理访问多个数据分片，从而实现分布式查询。

SQL优化

不要用select *
通过使用 limit 限制返回数据条数
根据需要查询指定范围的数据:where
不要在唯一列或大基数列上进行分组或去重操作：group by id
关联查询时小表在后，大表join小表；无论是Left Join 、Right Join还是Inner Join永远都是拿着右表中的每一条记录到左表中查找该记录是否存在
使用 uniqCombined 替代 distinct：

SELECT count(DISTINCT create_user) from app.scene_model; -- wrong
SELECT uniqCombined(create_user) from app.scene_model; -- right:近似去重

不要在大结果集上构造虚拟列：select id, pv, uv, pv/uv rate from app.scene_model，虚拟列非常消耗资源浪费性能，拿到pv uv后在前端显示时构造比率。
尽量不去使用字符串类型
指定查询分区获取必要的数据

执行计划

目前，CK没有类似MySQL explain查询执行计划的功能。

如何在CK中查看SQL执行计划

数据备份

CK数据目录的配置默认在config.xml中的 path 字段，默认目录为/data/clickhouse/clickhouse-server/data。

某个clickhouse-server的数据目录结构：

test
|-- account
|   |-- all_1_1_0
|   |   |-- checksums.txt
|   |   |-- columns.txt
|   |   |-- count.txt
|   |   |-- name.bin
|   |   |-- name.mrk2
|   |   |-- id.bin
|   |   |-- id.mrk2
|   |   |-- age.bin
|   |   |-- age.mrk2
|   |   |-- primary.idx
|   |-- detached
|-- |-- format_version.txt

第一级目录显示数据库名称如test
第二级目录为表名称如account数据表
第三级目录显示分区目录
checksum.txt 存储数据校验信息
columns.txt 保存数据列的列名和数据类型
count.txt 存储该分区的数据总数
.bin和.mrk2文件保存数据信息
primary.idx？？？
format_version.txt？？？
detached？？？

备份工具

clickhouse-backup 备份工具

监控

https://cloud.tencent.com/document/product/589/43539

官方监控
 搭建CK监控
 CK监控
 CK集群监控
 clickhouse_exporter

虚拟列

CK使用总结

CK的索引结构和查询优化

个例：
http://www.clickhouse.com.cn/topic/5b1e168c9d28dfde2ddc612a
http://www.clickhouse.com.cn/topic/5b1755409d28dfde2ddc60fb

工具

第三方代理服务器

chproxy
KittenHouse
clickhouse-bulk

可视化工具

开源

Data Grip，最强！
DBeaver
Superset，参考使用Superset可视化CK数据
Tabix
HouseOps
灯塔

收费

数据同步

binlog

CK王炸功能来袭之同步Binlog

HDFS to clickhouse

Waterdrop

Hive to clickhouse

集成

使用Superset 可视化CK数据

other

https://zhuanlan.zhihu.com/p/71014268
http://www.clickhouse.com.cn/topic/5a366e48828d76d75ab5d59e

CK特性及底层存储原理

对比

开源OLAP引擎测评报告(SparkSql、Presto、Impala、HAWQ、ClickHouse、GreenPlum)

问题

Missing columns…required columns…

报错信息：

ClickHouse exception, code: 47, host: 10.114.16.138, port: 8123; Code: 47, e.displayText() = DB::Exception: Missing columns: 'bussinessline' while processing query: '', required columns: 'bussinessline', source columns: 'bussinessLine' (version 19.16.8.34 (official build))

仔细看三个column，差别的地方在于大小写不一样，查阅文档得知：clickhouse对于select/SELECT不区分大小写，但是对于业务字段是大小写敏感的。

参考

ClickHouse

posted @ 2020-06-11 21:31 johnny233 阅读(113) 评论(0) 编辑收藏举报来源

刷新页面返回顶部

johnny233

搬自CSDN：https://blog.csdn.net/lonelymanontheway

ClickHouse学习教程

概述

学习资料

book

个人

altinity

应用场景

架构

Column与Field

DataType

Block与Block流

Table

Parser与Interpreter

Cluster与Replication

Functions 与Aggregate Functions

安装

端口

配置

功能

访问权限控制

数据TTL

有序存储

数据类型与SQL语法

数据类型

语法

特殊语法

开窗函数

客户端

Java

系统表

表引擎

TinyLog

Memory

Merge

Distributed

MergeTree

MergeTree

SummingMergeTree

ReplacingMergeTree

AggregatingMergeTree

SummingMergeTree

CollapsingMergeTree

VersionedCollapsingMergeTree

分片与副本

分片

副本

列式存储

物化视图

向量化执行

本地表和分布式表

SQL优化

执行计划

数据备份

备份工具

监控

虚拟列

工具

第三方代理服务器

可视化工具

开源

收费

数据同步

binlog

HDFS to clickhouse

Hive to clickhouse

集成

other

对比

问题

Missing columns…required columns…

参考

公告