ClickHouse 数据库基础教程

ClickHouse数据库是由俄罗斯的百度[Yandex]开源的列式数据库系统,具有通过SQL语句进行实时分析生成结果,性能强劲不输专业的MPP数据库软件

clickhouse的特点:

  • 支持集群扩展
  • 充分利用硬件性能
  • 容灾:支持多主,多数据中心
  • 支持SQL、丰富的数据结构、支持join
  • 亿级数据处理
  • 高可靠:分布式支持任意单节点故障
  • 简单:相比Hadoop生态简单多了

使用场景:

分析清晰明确的数据流,结构稳定的事件或者日志。

比如:

  • 网页或者App分析
  • 通讯
  • IoT

不能使用的场景:

  • 需要事务的环境
  • Key-value 高请求频率
  • Blog或者文档存储
  • Over-normalized data

安装

系统要求:Linux,X86_64 SSE4.2

Ubuntu/Debian安装包:

1
2
3
4
5
6
7
8
9
10
sudo apt-get install dirmngr    # optional
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4 # optional

echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update

sudo apt-get install -y clickhouse-server clickhouse-client

sudo service clickhouse-server start
clickhouse-client

对于其他操作系统可以使用官方的Docker镜像,或者重新编译源码

引擎

创建表时通过最后的那个 ENGINE 选项指定,对于大多数正式的任务,应该使用MergeTree族中的引擎

MergeTree

它支持一个日期(PARTITION KEY)和一组主键的两层式索引,还可以实时更新数据。同时,索引的粒度可以自定义,外加直接支持采样功能。

以这个引擎为基础,后面几种引擎都是在其基础之上附加某种特定功能而实现的变种。

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

Data Replication

自动数据备份,是表的行为,ReplicatedXXX的表支持自动同步。Replicated表自动同步与集群自动同步不同,是表的行为,与clickhouse_remote_servers配置没有关系,只要有zookeeper配置就行了。可以结合集群进行:分片 + 备份

ReplacingMergeTree

这个引擎是在 MergeTree 的基础上,添加了“处理重复数据”的功能,简直就是在多维数据加工流程中,为“最新值”,“实时数据”场景量身打造的一个引擎啊。这些场景下,如果重复数据不处理,你自己当然可以通过时间倒排,取最新的一条数据来达到目的,但是,至少这样会浪费很多的存储空间。Merge阶段进行操作。

相比 MergeTree , ReplacingMergeTree 在最后加一个“ver”版本列,它跟时间列配合一起,用以区分哪条数据是“新的”,并把旧的丢掉。同时,主键列组用于区分重复的行。

SummingMergeTree

该引擎继承MergeTree。SummingMergeTree 就是在 merge 阶段把数据加起来了,当然,哪些列要加(一般是针对可加的指标)可以配置,不可加的列,会取一个最先出现的值。

AggregatingMergeTree

AggregatingMergeTree 是在 MergeTree 基础之上,针对聚合函数结果,作增量计算优化的一个设计。它会在 merge 时,针对主键预处理聚合的数据。

根据聚合函数进行列值处理。

聚合数据的预计算,实现上,算是一种“空间换时间”的权衡,并且是以减少维度为代价的。统计分析中使用

CollapsingMergeTree

异步删除数据通过Sign字段标记为-1和1

这个引擎,是专门为 OLAP 场景下,一种“变通”存数做法而设计的,要搞明白它,以及什么场景下用它,为什么用它,需要先行了解一些背景。很多OLAP系统不支持删除。

VersionedCollapsingMergeTree

  • 运行快速写入状态持续变化的对象
  • 后台删除旧状态的对象,减少容量使用

与CollapsingMergeTree主要区别是支持多线程,无序写入数据。通过Version标识帮助乱序写入   

GraphiteMergeTree

支持对Graphite数据的聚集操作。也就是数值时间序列数据

StripeLog

所有列的数据存储在一个文件中,使用场景是数据小于100万的场景

一张表只有两个文件

  • data.bin
  • index.mrk

不支持更新与删除

TinyLog

最简单的表引擎,用于将数据存储在磁盘上。每列都存储在单独的压缩文件中。写入时,数据将附加到文件末尾。

并发数据访问不受任何限制: - 如果同时从表中读取并在不同的查询中写入,则读取操作将抛出异常 - 如果同时写入多个查询中的表,则数据将被破坏。

这种表引擎的典型用法是 write-once:首先只写入一次数据,然后根据需要多次读取。查询在单个流中执行。换句话说,此引擎适用于相对较小的表(建议最多1,000,000行)。如果您有许多小表,则使用此表引擎是适合的,因为它比Log引擎更简单(需要打开的文件更少)。当您拥有大量小表时,可能会导致性能低下,但在可能已经在其它 DBMS 时使用过,则您可能会发现切换使用 TinyLog 类型的表更容易。不支持索引。

在 Yandex.Metrica 中,TinyLog 表用于小批量处理的中间数据。

Log

日志与 TinyLog 的不同之处在于,”标记” 的小文件与列文件存在一起。这些标记写在每个数据块上,并且包含偏移量,这些偏移量指示从哪里开始读取文件以便跳过指定的行数。这使得可以在多个线程中读取表数据。对于并发数据访问,可以同时执行读取操作,而写入操作则阻塞读取和其它写入。Log 引擎不支持索引。同样,如果写入表失败,则该表将被破坏,并且从该表读取将返回错误。Log 引擎适用于临时数据,write-once 表以及测试或演示目的。

同 TinyLog 差不多,它适用的场景也是那种写一次之后,后面就是只读的场景,临时数据用它保存也可以。

Kafka

集成Kafka

MySQL

集成MySQL

Distributed

前面说的 Merge 可以看成是单机版的 Distributed ,而真正的 Distributed 具备跨服务器能力,当然,机器地址的配置依赖配置文件中的信息。

与 Merge 类似, Distributed 也是通过一个逻辑表,去访问各个物理表。

External Data for Query Processing

ClickHouse 允许向服务器发送处理查询所需的数据以及 SELECT 查询。这些数据放在一个临时表中(请参阅 “临时表” 一节),可以在查询中使用(例如,在 IN 操作符中)

例如,如果您有一个包含重要用户标识符的文本文件,则可以将其与使用此列表过滤的查询一起上传到服务器。

如果需要使用大量外部数据运行多个查询,请不要使用该特性。最好提前把数据上传到数据库。

可以使用命令行客户端(在非交互模式下)或使用 HTTP 接口上传外部数据。

Dictionary

列出表的数据字典

Merge

不存储数据,允许同时读取任意其他表的数据,不允许写入。一个工具引擎,本身不保存数据,只用于把指定库中的指定多个表链在一起。这样,读取操作可以并发执行,同时也可以利用原表的索引,但是,此引擎不支持写操作。

指定引擎的同时,需要指定要链接的库及表,库名可以使用一个表达式,表名可以使用正则表达式指定。

File

  • 导出Clickouse数据到文件
  • 数据格式转换
  • 编辑磁盘文件更新数据

Memory

Memory 引擎以未压缩的形式将数据存储在 RAM 中。数据完全以读取时获得的形式存储。换句话说,从这张表中读取是很轻松的。并发数据访问是同步的。锁范围小:读写操作不会相互阻塞。不支持索引。阅读是并行化的。在简单查询上达到最大生产率(超过10 GB /秒),因为没有磁盘读取,不需要解压缩或反序列化数据。(值得注意的是,在许多情况下,与 MergeTree 引擎的性能几乎一样高)。重新启动服务器时,表中的数据消失,表将变为空。通常,使用此表引擎是不合理的。但是,它可用于测试,以及在相对较少的行(最多约100,000,000)上需要最高性能的查询。

Memory 引擎是由系统用于临时表进行外部数据的查询(请参阅 “外部数据用于请求处理” 部分),以及用于实现 GLOBAL IN(请参见 “IN 运算符” 部分)。

Null

当写入 Null 类型的表时,将忽略数据。从 Null 类型的表中读取时,返回空。

但是,可以在 Null 类型的表上创建物化视图。写入表的数据将转发到视图中。

Buffer

缓冲数据写入 RAM 中,周期性地将数据刷新到另一个表。在读取操作时,同时从缓冲区和另一个表读取数据。

如果服务器异常重启,缓冲区中的数据将丢失。

当服务器停止时,使用 DROP TABLE 或 DETACH TABLE,缓冲区数据也会刷新到目标表。

是它也跟 Memory 一样,有很多的限制,比如没有索引什么的。

我们只建议在极少数情况下使用 Buffer 表。

Set

始终存在于 RAM 中的数据集。它适用于IN运算符的右侧(请参见 “IN运算符” 部分)。

可以使用 INSERT 向表中插入数据。新元素将添加到数据集中,而重复项将被忽略。但是不能对此类型表执行 SELECT 语句。检索数据的唯一方法是在 IN 运算符的右半部分使用它。

数据始终存在于 RAM 中。对于 INSERT,插入数据块也会写入磁盘上的表目录。启动服务器时,此数据将加载到 RAM。也就是说,重新启动后,数据仍然存在。

对于强制服务器重启,磁盘上的数据块可能会丢失或损坏。在数据块损坏的情况下,可能需要手动删除包含损坏数据的文件。

Join

跟 Set 类似,用在 JOIN 的右边。可以用于  GLOBAL JOINs

URL

类型File引擎,通过HTTP/HTTPS 服务器处理数据

View

用于构建视图。 它不存储数据,仅存储指定的 SELECT 查询。 从表中读取时,它会运行此查询(并从查询中删除所有不必要的列)。

MaterializedView

对于存储数据,它使用在创建视图时指定的不同引擎。当从表中读取数据时,它只使用这个引擎。

常用SQL语句

SELECT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table USING columns_list
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list]
[LIMIT [n, ]m]
[UNION ALL ...]
[INTO OUTFILE filename]
[FORMAT format]
[LIMIT n BY columns]

Table

建表语句:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

查看建表语句:

1
SHOW CREATE [TEMPORARY] TABLE [db.]table [INTO OUTFILE filename] [FORMAT format]

Functions

Functions for working with geographical coordinates 地理空间函数

  • greatCircleDistance 计算两点距离
  • pointInPolygon 点是否在矩形内

数据分片

1
vim /etc/clickhouse-server/config.xml

编辑

1
2
3
<remote_servers incl="clickhouse_remote_servers" >
...
</remote_servers>

可以使用扩展文件扩展配置

1
<include_from>/etc/clickhouse-server/metrika.xml</include_from>

编辑

1
vim /etc/clickhouse-server/metrika.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<yandex>
<!-- 集群配置 -->
<clickhouse_remote_servers>
<!-- 3分片1备份 -->
<cluster_my_test>
<!-- 数据分片1 -->
<shard>
<replica>
<host>lickhouse1</host>
<port>9000</port>
</replica>
</shard>
<!-- 数据分片2 -->
<shard>
<replica>
<host>lickhouse2</host>
<port> 9000</port>
</replica>
</shard>
<!-- 数据分片3 -->
<shard>
<replica>
<host>lickhouse3</host>
<port>9000</port>
</replica>
</shard>
</cluster_my_test>
</clickhouse_remote_servers>
</yandex>
  • clickhouse_remote_servers与config.xml中的incl属性值对应;
  • cluster_my_test是集群名,可以随便取名;
  • 共设置3个分片,每个分片只有1个副本;当然我们也可以配置多个副本

参考