您当前的位置:首页 > 电脑百科 > 程序开发 > 编程百科

ClickHouse 技术研究及语法简介

时间:2023-06-21 13:12:55  来源:京东云开发者  作者:

本文对 Clickhouse 架构原理、语法、性能特点做一定研究,同时将其与 MySQL、elasticsearch、tidb 做横向对比,并重点分析与 mysql 的语法差异,为有 mysql 迁移 clickhouse 场景需求的技术预研及参考。

1 基础概念

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

1.1 集群架构

ClickHouse 采用典型的分组式的分布式架构,具体集群架构如下图所示:

  • Shard:集群内划分为多个分片或分组(Shard 0 … Shard N),通过 Shard 的线性扩展能力,支持海量数据的分布式存储计算。
  • Node: 每个 Shard 内包含一定数量的节点(Node,即进程),同一 Shard 内的节点互为副本,保障数据可靠。ClickHouse 中副本数可按需建设,且逻辑上不同 Shard 内的副本数可不同。
  • ZooKeeper Service: 集群所有节点对等,节点间通过 ZooKeeper 服务进行分布式协调。

1.2 数据分区

Clickhouse 是分布式系统,其数据表的创建,与 mysql 是有差异的,可以类比的是在 mysql 上实现分库分表的方式。

Clichhouse 先在每个 Shard 每个节点上创建本地表(即 Shard 的副本),本地表只在对应节点内可见;然后再创建分布式表 [Distributed],映射到前面创建的本地表。

用户在访问分布式表时,ClickHouse 会自动根据集群架构信息,把请求转发给对应的本地表。

1.3 列式存储

相对于关系型数据库(RDBMS),是按行存储的。以 mysql 中 innodb 的主键索引为例,构建主键索引的 B + 树中,每个叶子节点存储的就是一行记录。

而列式数据库,是将一个表,按 column 的维护进行存储,“单次磁盘 I/O 拿到的是一列的数据”。

列式存储的优点

在查询时,只会读取涉及到的列,会大大减少 IO 次数 / 开销。并且 clickhouse 在存储时会按指定顺序排列数据,因此只需要按 where 条件指定列进行顺序扫描、多个列的扫描结果合并,即可找到满足条件的数据。

但由于 insert 数据时,是按行写入的,因此存储的过程会麻烦一些。

查询时的区别:

  • 列存储:仅从存储系统中读取必要的列数据(select + where 涉及到的),无用列不读取,速度非常快。
  • 行存储:从存储系统读取所有满足条件的行数据,然后在内存中过滤出需要的字段,速度较慢。

1.4 数据排序

每个数据分区内部,所有列的数据是按照 排序键(ORDER BY 列)进行排序的。

可以理解为:对于生成这个分区的原始记录行,先按 排序键 进行排序,然后再按列拆分存储。

1.5 数据分块

每个列的数据文件中,实际是分块存储的,方便数据压缩及查询裁剪,每个块中的记录数不超过 index_granularity,默认 8192,当达到 index_granularity 的值,数据会分文件。

1.6 向量化执行

在支持列存的基础上,ClickHouse 实现了一套面向向量化处理的计算引擎,大量的处理操作都是向量化执行的。

向量化处理的计算引擎:

基于数据存储模型,叠加批量处理模式,利用 SIMD 指令集,降低函数调用次数,降低硬件开销(比如各级硬件缓存),提升多核 CPU 利用率。

再加上分布式架构,多机器、多节点、多线程、批量操作数据的指令,最大限度利用硬件资源,提高效率。

注:SIMD 指令,单指令多数据流,也就是说在同一个指令周期可以同时处理多个数据。(例如:在一个指令周期内就可以完成多个数据单元的比较).

1.7 编码压缩

由于 ClickHouse 采用列存储,相同列的数据连续存储,且底层数据在存储时是经过排序的,这样数据的局部规律性非常强,有利于获得更高的数据压缩比。

同时,超高的压缩比又可以降低存储读取开销、提升系统缓存能力,从而提高查询性能。

1.8 索引

前面提到的列式存储,用于裁剪不必要的字段读取;

而索引,则用于裁剪不必要的记录读取(减少未命中数据的 IO)。

简单解释:

以主键索引为例,Clickhouse 存储数据时,会按排序键(ORDER BY) 指定的列进行排序,并按 Index_granularity 参数切分成块,然后会抽取每个数据块的首行,组织为一份稀疏的排序索引。

类比 B + 树的查找过程,如果 where 条件中包含主键列,就可以通过稀疏索引快速的过滤。稀疏索引对于范围查找比较高效。

二级索引,则是采用 bloom filter 来实现的:minmax,set,ngrambf/tokenbf。

1.9 适用场景

OLAP 分析领域有两个典型的方向:

  • ROLAP, 通过列存、索引等各类技术手段,提升查询时性能。
  • 宽表、大表场景,where 条件过多且动态,mysql 无法每列都建索引。
  • MOLAP, 通过预计算提前生成聚合后的结果数据,降低查询读取的数据量,属于计算换性能方式。
  • 复杂的报表查询,聚合、筛选很复杂的场景。

既然是 OLAP 分析,对数据的使用有些基本要求:

  • 绝大多数都是用于读访问
  • 无更新、大批量的更新(大于 1000 行)。(ck 没有高速、低延迟的更新和删除方法)
  • 查询的列尽量少,但行数很多。
  • 不需要事务、可以避免事务(clickhouse 不支持事务)
  • 数据一致性要求较低
  • 多表 join 时,只有一个是大表、大表关联小表
  • 单表的查询、聚合效率最高,建议数据做宽表处理

2 横向对比

搬仓系统面临的是从十几亿数据中进行查询、聚合分析,从世面上可选的支持海量数据读写的中间件中搜集到,能够有支持类似场景、有比较轻量级的产品大概有 Clickhouse、ElasticSearch、TiDB。

2.1 clickhouse 与 ElasticSearch 对比

elastic 生态很丰富,es 作为其中的存储产品,从首个版本算起,已经有 10 年发展历史,主要解决的是搜索问题。es 的底层存储采用 lucene,主要包含行存储、列存储和倒排索引,利用分片与副本机制,解决了集群下搜索性能与高可用的问题。

es 的优势:

  • 支持实时更新,对 update、delete 操作支持更完整。
  • 数据分片更均匀,集群扩展更加方便

es 的局限性:

  • 数据量超过千万或者亿级时,若聚合的列数太多,性能也到达瓶颈;
  • 不支持深度二次聚合,导致一些复杂的聚合需求,需要人工编写代码在外部实现,这又增加很多开发工作量。

ClickHouse 与 Elasticsearch(排序与聚合查询) 一样,都采用列式存储结构,都支持副本分片,不同的是 ClickHouse 底层有一些独特的实现,如下:

  • 合并树表引擎系列(MergeTree ),提供了数据分区、一级索引、二级索引。
  • 向量引擎(Vector Engine),数据不仅仅按列存储,同时还按向量 (列的一部分) 进行处理,这样可以更加高效地使用 CPU

网上资料:聚合查询的性能对比

es 对于在处理大查询,可能导致 OOM 问题,集群虽然能够对异常节点有自动恢复机制,但其查询数据量级不满足搬仓系统需求。

2.2 clickhouse 与 TiDB 对比

TiDB 是一个分布式 NewSQL 数据库。它支持水平弹性扩展、ACID 事务、标准 SQL、MySQL 语法和 MySQL 协议,具有数据强一致的高可用特性,是一个不仅适合 OLTP 场景还适 OLAP 场景的混合数据库。

TiDB 的优势:

  • 兼容 Mysql 协议和绝大多数 Mysql 语法,在大多数情况下,用户无需修改一行代码就可以从 Mysql 无缝迁移到 TiDB
  • 高可用、强制一致性(Raft)
  • 支持 ACID 事务(依赖事务列表),支持二级索引
  • 适合快速的点插入,点更新和点删除

TiDB 的局限性:

  • 更擅长 OLTP
  • 性能依赖硬件和集群规模,单机的读写性能不够出色

TiDB 更加适合作为 MySql 的替代,其对 MySQL 的兼容可以使得我们的应用切换成本较低,并且 TiDB 提供的数据自动分片无需人工维护。

3 为什么是 clickhouse

我们的项目场景是每天要同步十几亿单表数据,基本业务的查询在百万,还包含复杂的聚合分析。而 Clickhouse 在处理单表海量数据的查询分析方面,是十分优秀的,因此选用 clickhouse。

3.1 clickhouse 读写性能验证

官方公开 benchmark 测试显示能够达到 50MB-200MB/s 的写入吞吐能力,按照每行 100Byte 估算,大约相当于 50W-200W 条 /s 的写入速度。

下面是对 Clickhouse 的读写性能的简单测试,数据量越大差距越明显。

1)JDBC 方式单表、单次写入性能测试(性能更好):

2)MyBatis 方式单表、单次写入性能测试:

聚合查询性能举例:下图是搬仓系统一个聚合查询,在 clickhouse 中不同数据量级情况下的表现。这个查询在 mysql 中执行,一百万左右的数据量时,耗时已经是分钟级别。

1)count+distinct 方式聚合:

2)group by 方式聚合:

3.2 不足之处

作为分布式系统,通常包含三个重要组成:1、存储引擎。 2、计算引擎。 3、分布式管控层。

在分布式管控层,CK 显得较为薄弱,导致运营、使用成本较高。

  • 分布式表、本地表、副本的维护,这些都是需要用户自己来定义的,在使用时需要提前学习大量相关内容。
  • 弹性伸缩:ck 虽然可以做到水平增加节点,但不支持自动的数据均衡。也就是说当集群扩容后,需要手动将数据重写分片,或者依赖数据过期,才能保持存储压力的均衡。
  • 故障恢复:在节点故障的情况下,ck 不能利用其他机器补齐缺失的副本数据,需要用户 ian 补齐节点后,才能自动在副本件进行数据同步。

这方面,由于我们直接采用京东云实例,可以省很多事情。

计算引擎,CK 在处理多表关联查询、复杂嵌套子查询等场景,需要人工优化,才能做到明显的性能提升;

实时写入,CK 使用场景并不适合比较分散的插入,因为其没有实现内存表(Memory Table)结构,每批次写入直接落盘,单条记录实时写入会导致底层大量的小文件,影响查询性能。

建议单次大批量写入方式、报表库场景降低小文件产生概率。

集群模式下本地表的写入,需要自定义分片规则,否则随机写入会造成数据不均匀。

依赖分布式表的写入,对网络、资源的占用较高。

从数据量增长情况来看,使用场景:

  • 如果预估自己的业务数据量不大 (日增不到百万行), 那么写分布式表和本地表都可以,但要注意如果选择写本地表,请保证每次写入数据都建立新的连接,且每个连接写入的数据量基本相同,手动保持数据均匀
  • 如果预估自己的业务数据量大 (日增百万以上,并发插入大于 10), 那么请写本地表
  • 建议每次插入 50W 行左右数据,最多不可超过 100W 行。总之 CH 不像 MySQL 要小事务。比如 1000W 行数据,MySQL 建议一次插入 1W 左右,使用小事务,执行 1000 次. CH 建议 20 次,每次 50W. 这是 MergeTree 引擎原理决定的,频繁少量插入会导致 data part 过多,合并不过来.
  • MergeTree 系列:被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入,数据片段在后台按照一定的规则进行合并。相比在插入时不断修改(重写)已存储的数据,这种策略会高效很多。
  • Log 系列:功能相对简单,主要用于快速写入小表(1 百万行左右的表),然后全部读出的场景。
  • Integration 系列:主要用于将外部数据导入到 ClickHouse 中,或者在 ClickHouse 中直接操作外部数据源。
  • Special 系列:大多是为了特定场景而定制的。上面提到的 Distributed 就属于该系列。

4.1 MergeTree 表引擎

主要用于海量数据分析,支持数据分区、存储有序、主键索引、稀疏索引、数据 TTL 等。MergeTree 支持所有 ClickHouse SQL 语法,但是有些功能与 MySQL 并不一致,比如在 MergeTree 中主键并不用于去重。

先看一个创建表的简单语法:

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]

) ENGINE = ReplacingMergeTree([ver])

[PARTITION BY expr] -- 数据分区规则

[ORDER BY expr] -- 排序键

[SAMPLE BY expr] -- 采样键

[SETTINGS index_granularity = 8192, ...] -- 额外参数

先忽略表结构的定义,先看看相比于 mysql 建表的差异项。(指定集群、分区规则、排序键、采样 0-1 数字)

数据分区:每个分片副本的内部,数据按照 PARTITION BY 列进行分区,分区以目录的方式管理,本文样例中表按照时间进行分区。

基于 MergeTree 表引擎,CK 扩展很多解决特殊场景的表引擎,下面介绍几种常用的。

4.1.1 ReplacingMergeTree 引擎

该引擎和 MergeTree 的不同之处在于它会删除排序键值 (ORDER 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 = ReplacingMergeTree([ver])

[PARTITION BY expr]

[ORDER BY expr]

[SAMPLE BY expr]

[SETTINGS name=value, ...]

注意:在设置表引擎时,比 MergeTree 多了一个参数:ver - 版本列,ENGINE = ReplacingMergeTree ([ver]) 。

在数据合并的时候,ReplacingMergeTree 从所有具有相同排序键的行中选择一行留下:

  • 如果 ver 列未指定,保留最后一条。
  • 如果 ver 列已指定,保留 ver 值最大的版本。

ReplacingMergeTree 引擎,在数据写入后,不一定立即进行去重操作,或者不一定去重完毕(官方描述在 10 到 15 分钟内会进行合并)。

由于去重依赖的是排序键,ReplacingMergeTree 引擎是会按照分区键进行分区的,因此相同排序键的数据有可能被分到不同的分区,不同 shard 间可能无法去重。

在图上,分区 1 的文件块,会进行数据合并去重,但是分区 1 与分区 2 之间的数据是不会进行去重的。因此,如果要保证数据最终能够去重,要保证相同排序键的数据,会写入相同分区。

数据验证

下图为 ReplacingMergeTree 引擎,以日期作为分区键,对于重复主键数据的去重测试:

4.1.2 CollapsingMergeTree 引擎

该引擎要求在建表语句中指定一个标记列 Sign,按照 Sign 的值将行分为两类:Sign=1 的行称之为状态行,Sign=-1 的行称之为取消行。每次需要新增状态时,写入一行状态行;需要删除状态时,则写入一行取消行。

使用场景:

  1. 按 clickhouse 的架构,期合并、折叠操作,都是后台独立现场执行的,因此时间上并不能控制,何时折叠完成也无法预知。
  2. 如果插入的状态行与取消行是乱序的,会导致无法正常折叠

4.1.3 VersionedCollapsingMergeTree 表引擎

为了解决 CollapsingMergeTree 乱序写入情况下无法正常折叠问题,VersionedCollapsingMergeTree 表引擎在建表语句中新增了一列 Version,用于在乱序情况下记录状态行与取消行的对应关系。

主键相同,且 Version 相同、Sign 相反的行,在 Compaction 时会被删除。

4.2 数据副本

数据副本放在表引擎这里单独讲一下,是由于只有 MergeTree 系列里的表可支持副本:

  • ReplicatedMergeTree
  • ReplicatedSummingMergeTree
  • ReplicatedReplacingMergeTree
  • ReplicatedAggregatingMergeTree
  • ReplicatedCollapsingMergeTree
  • ReplicatedVersionedCollapsingMergetree
  • ReplicatedGraphiteMergeTree
  • 副本是表级别的,不是整个服务器级的。所以,服务器里可以同时有复制表和非复制表。
  • 副本不依赖分片。每个分片有它自己的独立副本。
  • 要使用副本,必须配置文件中设置 ZooKeeper 集群的地址。 (京东云提供的 clickhouse 已经完成了配置,我们直接使用即可)

<zookeeper>

<node index="1">

<host>example1</host>

<port>2181</port>

</node>

<node index="2">

<host>example2</host>

<port>2181</port>

</node>

<node index="3">

<host>example3</host>

<port>2181</port>

</node>

</zookeeper>

创建数据副本,是通过设置表引擎位置的参数来控制的,语法示例:

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)

定义数据副本,只需要在以上表引擎名字的前面,带上 Replicated 即可。

上方例子中,使用的表引擎为 MergeTree,开启数据副本,关键字 Replicated,参数有 2 个且必填:

  • zoo_path — ZooKeeper 中该表的路径。
  • replica_name — ZooKeeper 中的该表的副本名称

示例中的取值,采用了变量 {layer}、{shard}、{replica},他们的值取得是配置文件中的值,影响的是生成的副本粒度。

<macros>

<layer>05</layer>

<shard>02</shard>

<replica>example05-02-1.yandex.ru</replica>

</macros>

4.3 Special 系列

Special 系列的表引擎,大多是为了特定场景而定制的。

  • Memory:将数据存储在内存中,重启后会导致数据丢失。查询性能极好,适合于对于数据持久性没有要求的 1 亿一下的小表。在 ClickHouse 中,通常用来做临时表;
  • Buffer:为目标表设置一个内存 buffer,当 buffer 达到了一定条件之后会 flush 到磁盘;
  • File:直接将本地文件作为数据存储;
  • Null:写入数据被丢弃、读取数据为空。
  • Distributed:分布式引擎,可以在多个服务器上进行分布式查询

4.3.1 Distributed 引擎

分布式表引擎,本身不存储数据,也不占用存储空间,在定义时需要指定字段,但必须与要映射的表的结构相同。可用于统一查询 * MergeTree 的每个分片,类比 sharding 中的逻辑表。

比如搬仓系统,使用 ReplicatedReplacingMergeTree 与 Distributed 结合,实现通过分布式表实现对本地表的读写(写入操作本地表,读取操作分布式表)。

CREATE TABLE IF NOT EXISTS {distributed_table} as {local_table}

ENGINE = Distributed({cluster}, '{local_database}', '{local_table}', rand())

说明:

  • distributed_table:分布式表的表名
  • local_table:本地表名
  • as local_table:保持分布式表与本地表的表结构一致。此处也可以用 (column dataType)这种定义表结构方式代替
  • cluster:集群名

注意事项:

  • 分布式表本身并不存储数据,只是提供了一个可以分布式访问数据的框架,查询分布式表的时候 clickhouse 会自动去查询对应的每个本地表中的数据,聚合后再返回
  • 注意 AS {local_table},它表明了分布式表所对应的本地表(本地表是存储数据的)
  • 可以配置 Distributed 表引擎中的最后一个参数 rand () 来设置数据条目的分配方式
  • 可以直接往分布式表中写数据,clickhouse 会自动按照上一点所说的方式来分配数据和自平衡,数据实际会写到本地表
  • 也可以自己写分片算法,然后往本地表中写数据【网上资料的场景是每天上千亿写入,性能考虑要直接写本地表】

4.4 Log 系列

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

几种 Log 表引擎的共性是:

  • 数据被顺序 Append 写到磁盘上;
  • 不支持 delete、update;
  • 不支持 index;
  • 不支持原子性写;
  • insert 会阻塞 select 操作。

它们彼此之间的区别是:

  • TinyLog:不支持并发读取数据文件,查询性能较差;格式简单,适合用来暂存中间数据;
  • StripLog:支持并发读取数据文件,查询性能比 TinyLog 好;将所有列存储在同一个大文件中,减少了文件个数;
  • Log:支持并发读取数据文件,查询性能比 TinyLog 好;每个列会单独存储在一个独立文件中。

4.5 Integration 系列

该系统表引擎主要用于将外部数据导入到 ClickHouse 中,或者在 ClickHouse 中直接操作外部数据源。

  • Kafka:将 Kafka Topic 中的数据直接导入到 ClickHouse;
  • MySQL:将 Mysql 作为存储引擎,直接在 ClickHouse 中对 MySQL 表进行 select 等操作;猜测:如果有 join 需求,又不想将 mysql 数据导入 ck 中
  • JDBC/ODBC:通过指定 jdbc、odbc 连接串读取数据源;
  • HDFS:直接读取 HDFS 上的特定格式的数据文件。

5 数据类型

clickhouse 支持的数据类型如下图,分为基础类型、复合类型、特殊类型。

5.1 CK 与 Mysql 数据类型对照

6 SQL 语法 - 常用介绍

6.1 DDL

6.1.1 创建数据库:

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster];

如果 CREATE 语句中存在 IF NOT EXISTS 关键字,则当数据库已经存在时,该语句不会创建数据库,且不会返回任何错误。

ON CLUSTER 关键字用于指定集群名称,在集群环境下必须指定该参数,否则只会在链接的节点上创建。

6.1.2 创建本地表:

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 = engine_name()

[PARTITION BY expr]

[ORDER BY expr]

[PRIMARY KEY expr]

[SAMPLE BY expr]

[SETTINGS name=value, ...];

选项描述:

  • db:指定数据库名称,如果当前语句没有包含‘db’,则默认使用当前选择的数据库为‘db’。
  • cluster:指定集群名称,目前固定为 default。ON CLUSTER 将在每一个节点上都创建一个本地表。
  • type:该列数据类型,例如 UInt32。
  • DEFAULT:该列缺省值。如果 INSERT 中不包含指定的列,那么将通过表达式计算它的默认值并填充它(与 mysql 一致)。
  • MATERIALIZED:物化列表达式,表示该列不能被 INSERT,是被计算出来的; 在 INSERT 语句中,不需要写入该列;在 SELECT * 查询语句结果集不包含该列;需要指定列表来查询(虚拟列)
  • ALIAS :别名列。这样的列不会存储在表中。 它的值不能够通过 INSERT 写入,同时 SELECT 查询使用星号时,这些列也不会被用来替换星号。 但是它们可以用于 SELECT 中,在这种情况下,在查询分析中别名将被替换。
  • 物化列与别名列的区别: 物化列是会保存数据,查询的时候不需要计算,而别名列不会保存数据,查询的时候需要计算,查询时候返回表达式的计算结果

以下选项与表引擎相关,只有 MergeTree 系列表引擎支持:

  • PARTITION BY:指定分区键。通常按照日期分区,也可以用其他字段或字段表达式。(定义分区键一定要考虑清楚,它影响数据分布及查询性能)
  • ORDER BY:指定 排序键。可以是一组列的元组或任意的表达式。
  • PRIMARY KEY: 指定主键,默认情况下主键跟排序键相同。因此,大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。
  • SAMPLE BY :抽样表达式,如果要用抽样表达式,主键中必须包含这个表达式。
  • SETTINGS:影响 性能的额外参数。
  • GRANULARITY :索引粒度参数。

示例,创建一个本地表:

CREATE TABLE ontime_local ON CLUSTER default -- 表名为 ontime_local

Year UInt16,

Quarter UInt8,

Month UInt8,

DayofMonth UInt8,

DayOfWeek UInt8,

FlightDate Date,

FlightNum String,

Div5WheelsOff String,

Div5TAIlNum String

)ENGINE = ReplicatedMergeTree(--表引擎用ReplicatedMergeTree,开启数据副本的合并树表引擎)

'/clickhouse/tables/ontime_local/{shard}', -- 指定存储路径

'{replica}')

PARTITION BY toYYYYMM(FlightDate) -- 指定分区键,按FlightDate日期转年+月维度,每月做一个分区

PRIMARY KEY (intHash32(FlightDate)) -- 指定主键,FlightDate日期转hash值

ORDER BY (intHash32(FlightDate),FlightNum) -- 指定排序键,包含两列:FlightDate日期转hash值、FlightNunm字符串。

SAMPLE BY intHash32(FlightDate) -- 抽样表达式,采用FlightDate日期转hash值

SETTINGS index_granularity= 8192 ; -- 指定index_granularity指数,每个分区再次划分的数量

6.1.3 创建分布式表

基于本地表创建一个分布式表。基本语法:

CREATE TABLE [db.]table_name ON CLUSTER default

AS db.local_table_name

ENGINE = Distributed(<cluster>, <database>, <shard table> [, sharding_key])

参数说明:

  • db:数据库名。
  • local_table_name:对应的已经创建的本地表表名。
  • shard table:同上,对应的已经创建的本地表表名。
  • sharding_key:分片表达式。可以是一个字段,例如 user_id(integer 类型),通过对余数值进行取余分片;也可以是一个表达式,例如 rand (),通过 rand () 函数返回值 /shards 总权重分片;为了分片更均匀,可以加上 hash 函数,如 intHash64 (user_id)。

示例,创建一个分布式表:

CREATE TABLE ontime_distributed ON CLUSTER default -- 指定分布式表的表名,所在集群

AS db_name.ontime_local -- 指定对应的 本地表的表名

ENGINE = Distributed(default, db_name, ontime_local, rand()); -- 指定表引擎为Distributed(固定)

6.1.4 其他建表

clickhouse 还支持创建其他类型的表:

6.1.5 修改表

语法与 mysql 基本一致:

ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN …

支持下列动作:

  • ADD COLUMN — 添加列
  • DROP COLUMN — 删除列
  • CLEAR COLUMN — 重置列的值
  • COMMENT COLUMN — 给列增加注释说明
  • MODIFY COLUMN — 改变列的值类型,默认表达式以及 TTL

举例:ALTER TABLE bd01.table_1 ADD COLUMN browser String AFTER name; – 在 name 列后面追加一列

6.2 DML

注意:

  1. 索引列不支持更新、删除
  2. 分布式表不支持更新、删除

7 复杂查询 JOIN

所有标准 SQL JOIN 支持类型(INNER 和 OUTER 可以省略):

  • INNER JOIN,只返回匹配的行。
  • LEFT OUTER JOIN,除了匹配的行之外,还返回左表中的非匹配行。
  • RIGHT OUTER JOIN,除了匹配的行之外,还返回右表中的非匹配行。
  • FULL OUTER JOIN,除了匹配的行之外,还会返回两个表中的非匹配行。
  • CROSS JOIN,产生整个表的笛卡尔积,“join keys” 是 不 指定。

查询优化:

  1. A join B 的查询,比 from A,B,C 多表的性能高很多
  2. global join 会把书记发送给所有节点参与计算,针对较小的维度表性能较好
  3. JOIN 会在背地节点操作,适合于相同分片字段的两张表关联(A 表与 B 表的分片字段都包含字段 M)
  4. IN 的性能比 JOIN 好,优先使用 JOIN
  5. 先过滤再 join 效率更好(减低每个分片关联数据量级)
  6. 在做多表 join 时,A 表的查询过滤条件中如果能包含与 B 表的 ON expr 中字段过滤条件,性能更好
  7. join 的顺序,大表在左,小表在右;ck 查询时会从右向左执行

对比 JOIN 与 IN 的查询复杂度:

CK 常用的表引擎会是分布式存储,因此查询过程一定是每个分片进行一次查询,这就导致了 sql 的复杂度越高,查询锁扫描的分片数量越多,耗时也就越久。

假设 AB 两个表,分别存储在 10 个分片中,join 则是查询 10 次 A 表的同时,join10 次 B 表,合计要 10*10 次。采用 Global join 则会先查询 10 次并生成临时表,再用临时表取和 B 表 join,合计要 10+10 次。

这算是分布式架构的查询特点,如果能干预数据分片规则,如果查询条件中带有分片列,则可以直接定位到包含数据的分片上,从而减小查询次数。

CK 对于 join 语法上虽然支持,但是性能并不高。当 join 的左边是子查询结果时,ck 是无法进行分布式 join 的。

8 MySQL 迁移到 CK

  • 数据同步成本:clickhouse 可以做到与 mysql 的表结构一致,进而数据同步成本较低,不需要调整数据结构、不需要额外做宽表处理(当然转为宽表效率更高)。
  • SQL 迁移成本:支持 jdbc、mybatis 接入;支持标准 SQL 的语法;支持 join、in、函数,SQL 迁移成本较低。

当然如果花功夫对表结构、SQL、索引等进行优化,能得到更好的查询效率。

官方支持

在 2020 年下半年,Yandex 公司在 ClickHouse 社区发布了 MaterializeMySQL 引擎,支持从 MySQL 全量及增量实时数据同步。MaterializeMySQL 引擎目前支持 MySQL 5.6/5.7/8.0 版本,兼容 Delete/Update 语句,及大部分常用的 DDL 操作。

也就是说,CK 支持作为 MySQL 的从节点存在,依赖订阅 binlog 方式实现。

https://bbs.huaweicloud.com/blogs/238417

9 总结

ClickHouse 更加适合 OLAP 场景,在报表库中有极大性能优势。如果想作为应用数据库,可以灵活采用其表引擎特点,尽量避免数据修改。其实,没有最好的,只有最合适的。

 

作者:京东物流 耿宏宇
来源:京东云开发者社区


Tags:ClickHouse   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
从Clickhouse迁移到Doris,数据仓库性能大提升
从一个OLAP数据库迁移到另一个数据库是一项艰巨的工程。即使能找到一些有用的数据工具,您可能仍会犹豫是否对数据架构进行大手术,因为不确定如何运作。本文分享如何从ClickHou...【详细内容】
2023-11-17  Search: ClickHouse  点击:(202)  评论:(0)  加入收藏
从使用的角度看 ByConity 和 ClickHouse 的差异
Github: https://github.com/ByConity自 ClickHouse Inc 宣布其重要新功能仅在 ClickHouse Cloud 上开放以来,一些关注 ByConity 开源的社区小伙伴也来询问 ByConity 后续开...【详细内容】
2023-10-26  Search: ClickHouse  点击:(246)  评论:(0)  加入收藏
列式数据库ClickHouse,大宽表聚合、报表一下全搞定
一、前言现在数据库的种类也是特别的多,大致的类别包括: 关系型数据库( MySQL、Oracle、PostgreSQL)。 非关系型数据库(Redis、MongoDB、Cassandra、Neo4j)。 全文搜索引擎和分布...【详细内容】
2023-10-08  Search: ClickHouse  点击:(335)  评论:(0)  加入收藏
基于CLICKHOUSE的数据仓库分层规范
数据仓库分层架构数据仓库我们一般分为接入层、明细层、实体层、主题层、应用层。各层存储的数据粒度不同。接入层:一般存储接收的原始数据,并给接入的数据打上接收时间戳。明...【详细内容】
2023-08-05  Search: ClickHouse  点击:(317)  评论:(0)  加入收藏
ClickHouse 技术研究及语法简介
本文对 Clickhouse 架构原理、语法、性能特点做一定研究,同时将其与 mysql、elasticsearch、tidb 做横向对比,并重点分析与 mysql 的语法差异,为有 mysql 迁移 clickhouse 场景...【详细内容】
2023-06-21  Search: ClickHouse  点击:(222)  评论:(0)  加入收藏
字节跳动开源ByConity:基于ClickHouse的存算分离架构云原生数仓
采访嘉宾 | 陈星、翟鹿渊作者 | 蔡芳芳、王一鹏热闹纷繁的 OLAP 赛道,又迎来一个开源新玩家。这几年 OLAP 赛道持续火热,国内外不少开源项目和商业公司活跃其中。在一众玩家中...【详细内容】
2023-05-22  Search: ClickHouse  点击:(297)  评论:(0)  加入收藏
揭秘字节跳动解决ClickHouse复杂查询问题的技术方案
导读:ClickHouse已经成为行业主流且热门的开源引擎。随着业务数据量扩大,场景覆盖变广泛,在复杂query场景下,ClickHouse容易存在查询异常问题,影响业务正常推进。本次主要分享字...【详细内容】
2022-09-05  Search: ClickHouse  点击:(416)  评论:(0)  加入收藏
记一次 ClickHouse 性能测试
前言在工作场景中,我们会采集工厂设备数据用于智能控制,数据的存储用了 InfluxDB,随着数据规模越来越大,InfluxDB 的性能越来越差,故考虑引入 ClickHouse 分担 InfluxDB 大数据分...【详细内容】
2022-08-15  Search: ClickHouse  点击:(358)  评论:(0)  加入收藏
使用IDEA连接ClickHouse OLAP数据库
ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。来自于2011 年在纳斯达克上市的俄罗斯本土搜索引擎企业Yandex公司,诞生之初就是为了服务Yandex公司自家的W...【详细内容】
2022-01-18  Search: ClickHouse  点击:(2290)  评论:(0)  加入收藏
mybatis-plus整合clickhouse
公司使用mybatis-plus版本为3.0.7.1,mybatis-plus3.4.2对clickhouse是支持的,无奈怕升级影响大,只能在现有基础上调整mybatis-plus代码了。 mybatis 在项目中将mybatis-plus源...【详细内容】
2021-05-27  Search: ClickHouse  点击:(5080)  评论:(0)  加入收藏
▌简易百科推荐
即将过时的 5 种软件开发技能!
作者 | Eran Yahav编译 | 言征出品 | 51CTO技术栈(微信号:blog51cto) 时至今日,AI编码工具已经进化到足够强大了吗?这未必好回答,但从2023 年 Stack Overflow 上的调查数据来看,44%...【详细内容】
2024-04-03    51CTO  Tags:软件开发   点击:(6)  评论:(0)  加入收藏
跳转链接代码怎么写?
在网页开发中,跳转链接是一项常见的功能。然而,对于非技术人员来说,编写跳转链接代码可能会显得有些困难。不用担心!我们可以借助外链平台来简化操作,即使没有编程经验,也能轻松实...【详细内容】
2024-03-27  蓝色天纪    Tags:跳转链接   点击:(13)  评论:(0)  加入收藏
中台亡了,问题到底出在哪里?
曾几何时,中台一度被当做“变革灵药”,嫁接在“前台作战单元”和“后台资源部门”之间,实现企业各业务线的“打通”和全域业务能力集成,提高开发和服务效率。但在中台如火如荼之...【详细内容】
2024-03-27  dbaplus社群    Tags:中台   点击:(9)  评论:(0)  加入收藏
员工写了个比删库更可怕的Bug!
想必大家都听说过删库跑路吧,我之前一直把它当一个段子来看。可万万没想到,就在昨天,我们公司的某位员工,竟然写了一个比删库更可怕的 Bug!给大家分享一下(不是公开处刑),希望朋友们...【详细内容】
2024-03-26  dbaplus社群    Tags:Bug   点击:(5)  评论:(0)  加入收藏
我们一起聊聊什么是正向代理和反向代理
从字面意思上看,代理就是代替处理的意思,一个对象有能力代替另一个对象处理某一件事。代理,这个词在我们的日常生活中也不陌生,比如在购物、旅游等场景中,我们经常会委托别人代替...【详细内容】
2024-03-26  萤火架构  微信公众号  Tags:正向代理   点击:(11)  评论:(0)  加入收藏
看一遍就理解:IO模型详解
前言大家好,我是程序员田螺。今天我们一起来学习IO模型。在本文开始前呢,先问问大家几个问题哈~什么是IO呢?什么是阻塞非阻塞IO?什么是同步异步IO?什么是IO多路复用?select/epoll...【详细内容】
2024-03-26  捡田螺的小男孩  微信公众号  Tags:IO模型   点击:(9)  评论:(0)  加入收藏
为什么都说 HashMap 是线程不安全的?
做Java开发的人,应该都用过 HashMap 这种集合。今天就和大家来聊聊,为什么 HashMap 是线程不安全的。1.HashMap 数据结构简单来说,HashMap 基于哈希表实现。它使用键的哈希码来...【详细内容】
2024-03-22  Java技术指北  微信公众号  Tags:HashMap   点击:(11)  评论:(0)  加入收藏
如何从头开始编写LoRA代码,这有一份教程
选自 lightning.ai作者:Sebastian Raschka机器之心编译编辑:陈萍作者表示:在各种有效的 LLM 微调方法中,LoRA 仍然是他的首选。LoRA(Low-Rank Adaptation)作为一种用于微调 LLM(大...【详细内容】
2024-03-21  机器之心Pro    Tags:LoRA   点击:(12)  评论:(0)  加入收藏
这样搭建日志中心,传统的ELK就扔了吧!
最近客户有个新需求,就是想查看网站的访问情况。由于网站没有做google的统计和百度的统计,所以访问情况,只能通过日志查看,通过脚本的形式给客户导出也不太实际,给客户写个简单的...【详细内容】
2024-03-20  dbaplus社群    Tags:日志   点击:(4)  评论:(0)  加入收藏
Kubernetes 究竟有没有 LTS?
从一个有趣的问题引出很多人都在关注的 Kubernetes LTS 的问题。有趣的问题2019 年,一个名为 apiserver LoopbackClient Server cert expired after 1 year[1] 的 issue 中提...【详细内容】
2024-03-15  云原生散修  微信公众号  Tags:Kubernetes   点击:(6)  评论:(0)  加入收藏
站内最新
站内热门
站内头条