[ClickHouse]表引擎学习分享

设计理念

Everything is table(万物皆表),数据表就是ClickHouse和外部交互的接口。在数据表背后无论连接的是本地文件、HDFS、Zookeeper还是其它服务,终端用户始终只需要面对数据表,只需要用SQL查询语言。
处理各种数据场景走内部集成的路线,既与外部系统(其它数据库,消息中间件或者是服务接口)的集成直接在数据库内部实现。
面向表编程,一张数据表最终能够提供哪些功能、拥有哪些特性、数据会议什么格式被保存以及数据会怎样被加载,这些都将由它的表引擎决定。

表引擎特性

表引擎 概念 引擎
合并树类型 MergeTree提供主键索引,数据分区,数据副本和数据采样等基本能力,其它表引擎在MergeTree上各有所长;ReplacingMergeTree具有删除重复数据的特性;SummingMergeTree会按照排序键自动聚合数据;Replicated系列支持数据副本 MergeTree,SummingMergeTree ...
外部存储类型 外部存储表引擎直接从其他的存储系统读取数据,例如直接读取HDFS的文件或者MySQL数据库的表。这些引擎只负责元数据管理和数据查询,而它们自身通常并不负责数据的写入,数据文件直接由外部系统提供。 HDFS,MySQL,JDBC,Kafka,File
内存类型 面向内存查询,数据从内存中直接访问。除了Memory引擎之外,其它几款都会将数据写入磁盘以防数据丢失。在数据被加载时,数据会被全部加载到内存、以供查询使用。全部加载到内存:意味着有更好的查询性能;但是如果加载的数据量过大,就会有极大的内存消耗和负担 Memory,Set,Join,Buffer,
日志类型 数据量很小(100万以下),同时查询场景也比较简单,并且是“一次”写入多次查询方式,可以使用日志类型;共性:不支持索引和分区等高级特性;不支持并发读写,当针对一张日志写入数据时,针对这张表的查询会被阻塞,直至写入动作结束;拥有物理存储,数据会被保存到本地文件中 TinyLog,StripeLog,Log
接口类型 本身不保存数据,而是像粘合剂一样整合其它数据表;使用时,不用关心底层复杂性,像接口一样为用户提供统一访问页面 Merge,Dictionary,Distribute
其它类型 扩充ClickHouse的能力边界 Live View,NULL,URL

引擎使用场景

引擎使用场景

表引擎使用

SummingMergeTree(MergeTree家族)

使用

数据聚合

  • 聚合前数据


    聚合前数据
  • 聚合后数据


    聚合后数据

使用说明

  • PRIMARY KEY可与ORDER BY不同,PRIMARY KEY作为主键索引,ORDER BY作为聚合条件。

  • PRIMARY KEY是ORDER BY的前缀,索引和数据仍然具有对应关系;因为数据以ORDER BY排序,索引以PRIMARY KEY排序,PRIMARY KEY是ORDER BY的前缀,那么索引也是ORDER BY有序的,同一个排序标准,产生相同的数据顺序,所以,索引和数据仍然具有对应关系。

  • ORDER BY可以可以减少,GROUP BY(A, B, C, D) --> GROUP BY(A, B); ORDER BY可以增加新的列。

工作中使用

  1. SummingMergeTree聚合ClickHouse Shard中数据,同时通过Distribute表向外提供服务
  2. PARTITION BY toStartOfHour(event_date) + PRIMARY KEY (event_date, network_id)
  3. ORDER BY (event_date, network_id, a, c, d) ...
  4. TTL event_date + INTERVAL 35 DAY DELETE
  5. SETTINGS index_granularity = 8192, replicated_deduplication_window = 0

原理

  1. 用ORDER BY排序键作为聚合数据的条件Key
  2. 只有在合并分区的时候才会触发汇总的逻辑
  3. 以数据分区为单位来聚合数据。当分区合并时,同一个数据分区内聚合Key相同的数据会被合并汇总,而不同分区之间的数据不会被汇总
  4. 如果在定义引擎时指定了columns汇总列(非主键的数值类型字段),则SUM汇总这些列字段;如果未指定,则聚合所有非主键的数值类型字段
  5. 在进行数据汇总时,因为分区内的数据已经基于ORDER BY排序,所以能够找到相邻且拥有相同聚合Key的数据
  6. 在汇总数据时,同一个分区内,相同聚合Key的多行数据会合并成一行。其中,汇总字段进行SUM计算;对于那些非汇总地段,则会使用第一行数据的取值。
  7. 支持嵌套结构,但列字段名称必须以Map后缀结尾。

Kafka(外部存储类型)

使用

kafka环境准备

Kafka docker 环境搭建

生产消息

数据data.json:

{ "id": "A001", "city": "wuhan", "v1": 10, "v2": 20, "create_time": "2019-08-10 17:00:00" }
{ "id": "A001", "city": "wuhan", "v1": 20, "v2": 30, "create_time": "2019-08-20 17:00:00" }
{ "id": "A001", "city": "zhuhai", "v1": 20, "v2": 30, "create_time": "2019-08-10 17:00:00" }
{ "id": "A001", "city": "wuhan", "v1": 10, "v2": 20, "create_time": "2019-02-10 09:00:00" }
{ "id": "A002", "city": "wuhan", "v1": 60, "v2": 50, "create_time": "2019-10-10 17:00:00" }

生产

kafka-console-producer --topic test --bootstrap-server localhost:9092 < data.json

创建kafka表

CREATE TABLE test_kafka
(
    `id` String,
    `city` String,
    `v1` UInt32,
    `v2` Float64,
    `create_time` DateTime
)
ENGINE = Kafka()
SETTINGS kafka_broker_list = '172.18.0.3:9092', kafka_topic_list = 'test', kafka_group_name = 'test', kafka_format = 'JSONEachRow', kafka_skip_broken_messages = 100

消费消息

SELECT * FROM test_kafka

查看kafka消费日志

cat /var/log/clickhouse-server/clickhouse-server.log | grep kafka
2021.06.30 16:23:03.749646 [ 75 ] {af851164-e7ce-46cd-be01-64f10ddec924} <Debug> executeQuery: (from 127.0.0.1:34022) select * from default.test_kafka;
2021.06.30 16:23:03.750176 [ 75 ] {af851164-e7ce-46cd-be01-64f10ddec924} <Debug> StorageKafka (test_kafka): Starting reading 1 streams
2021.06.30 16:23:07.262678 [ 91 ] {af851164-e7ce-46cd-be01-64f10ddec924} <Trace> StorageKafka (test_kafka): Polled batch of 98 messages. Offsets position: [ test[0:98] ]
2021.06.30 16:23:07.282639 [ 91 ] {af851164-e7ce-46cd-be01-64f10ddec924} <Warning> StorageKafka (test_kafka): Parsing of message (topic: test, partition: 0, offset: 77) return no rows.
2021.06.30 16:23:07.285524 [ 91 ] {af851164-e7ce-46cd-be01-64f10ddec924} <Trace> StorageKafka (test_kafka): Polled offset 98 (topic: test, partition: 0)
2021.06.30 16:23:07.339373 [ 91 ] {af851164-e7ce-46cd-be01-64f10ddec924} <Trace> StorageKafka (test_kafka): Committed offset 98 (topic: test, partition: 0)

原理

  1. 只负责元数据的管理和数据查询,不存储数据(外部存储引擎共性),支持从kafa消费消息,也可以向kafka中插入数据(Demo)
  2. 默认情况下,Kafka表引擎每隔500毫秒会拉取一次数据,时间由stream_poll_timeout_ms参数控制,数据首先会被放入缓存,在时机成熟时,缓存数据会被刷新到数据表
  3. 满足下列条件之一,触发刷新动作:
    a. 当完成一个数据块儿的写入(数据块儿的大小由kafka_max_block_size参数控制,默认情况下65536)
    b. 等待时间超过7500毫秒(stream_flush_interval_ms参数控制,默认7500ms)

Join(内存类型)

使用

CREATE TABLE join_tb1 (
    id UInt8,
    name String,
    time DateTime
) ENGINE = Log

INSERT INTO TABLE join_tb1 VALUES(1, 'ClickHouse', '2019-05-01 12:00:00'), 
(2, 'Spark', '2019-05-01 12:30:00'), (3, 'ElasticSearch', '2019-05-01 13:00:00');


CREATE TABLE id_join_tb1 (
    id UInt8,
    price UInt32,
    time DateTime
) ENGINE = Join(ANY, LEFT, id)

INSERT INTO TABLE id_join_tb1 VALUES (1, 100, '2019-05-01 11:55:00'),
(1, 105, '2019-05-01 11:10:00'),
(2, 90, '2019-05-01 12:01:00'),
(3, 80, '2019-05-01 11:55:00'),
(5, 70, '2019-05-01 11:55:00'),
(6, 60, '2019-05-01 11:55:00');

SELECT id, name, price FROM join_tb1 LEFT JOIN id_join_tb1 USING(id);

SELECT joinGet('id_join_tb1', 'price', toUInt8(1));

原理

  1. ENGINE = Join(join_strictness, join_type, key1[, key2, ...])
  2. join_strictness: 连接的精度,它决定了JOIN查询在连接数据时所使用的策略,目前支持ALL,ANY和ASOF三种类型
  3. join_type: 连接左右两个数据集合的策略;交集,并集,笛卡尔积或其他形式,目前支持INNER,OUTER和CROSS;当join_type类型为ANY时,在数据写入时,join_key重复的数据会被自动忽略
  4. join_key: 连接键,决定使用哪个列字段进行关联

Merge(接口类型)

使用

# 数据以年分表,使用Merge引擎进行粘合
CREATE TABLE test_table_2018(
    id String,
    create_time DateTime,
    code String
)ENGINE = MergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY id

CREATE TABLE test_table_2019(
    id String,
    create_time DateTime,
    code String
)ENGINE = Log()
PARTITION BY toYYYYMM(create_time)
ORDER BY id

CREATE TABLE test_table_all as test_table_2018
ENGINE = MergeTree(currentDatebase(), '^test_table_')

原理

  1. 不存储数据,而是像粘合剂一样可以整合其他的数据表
  2. 被代理查询的数据表要在同一个数据库内,且拥有相同的表结构,但是它们可以使用不同的表引擎以及不同的分区定义(对于MergeTree而言)

URL(其它类型)

使用

/* GET users listing. */
router.get('/users', func (req, res, next)  {
    var result = ''
    for(let i=0; i<5; i++){
        result += '{"name":"nauu'+i+'"}\n';
    }
    res.send(result)    
})

/* POST user. */
router.post('/users', func (req, res)  {
    res.sendStatus(200) 
})

CREATE TABLE url_table (
    name String
)
ENGINE = URL('http://localhost:9688/users', JSONEachRow)

SELECT * FROM url_table

INSERT INTO TABLE url_table VALUES('nauu-insert')

原理

  1. URL表引擎等价于HTTP客户端,它可以通过HTTP/HTTPS协议,直接访问远端的REST服务。
  2. SELECT查询会被底层转换为GET请求
  3. INSERT查询会被转换为POST请求

综合使用例子

Kafka + MATERIALIZED VIEW + ReplicateSummingMergeTree + Distributed
Kafka Engine Table: 外部存储表,消费kafka消息
Materialize View: 当数据插入到kafka表时,执行select语句将数据进行transform后,插入到To表
SummingMergeTree: MergeTree家族表,支持partition summing,主键索引,数据分区,replica和数据采样;
Distribute 表: 进行数据粘合,为用户提供统一的数据视图

引用

Docker HDFS
Docker HDFS 2
apt install netcat
Docker中容器之间通讯方式
安装ifconfig apt install net-tools
查看docker容器ip地址 docker inspect kafka-docker_clickhouse-server_1 | grep IP
Kafka引擎
ClickHouse原理解析与应用实践

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 203,456评论 5 477
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 85,370评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 150,337评论 0 337
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,583评论 1 273
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,596评论 5 365
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,572评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,936评论 3 395
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,595评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,850评论 1 297
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,601评论 2 321
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,685评论 1 329
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,371评论 4 318
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,951评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,934评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,167评论 1 259
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 43,636评论 2 349
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,411评论 2 342

推荐阅读更多精彩内容