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个副本；当然我们也可以配置多个副本

入库慢问题

1.直接clickhouse对接kafka
2.对于可能需要数据清洗分析之类的可以java程序从kafka获取数据分析后再放回kafka另一个主题，clickhouse还是可以直接对接kafka 主题

还可以使用hangout-output-clickhouse之类的工具包

参考