clickhouse 核心知识与集群介绍

  • 2021-12-07
  • Admin

核心知识与集群介绍(基于v21.11版本)

目录

1. 介绍

2. 优缺点

3. 表引擎

3.1 Log

3.2 Engine Families MergeTree

3.3 Integration Engines

3.4 Special Engines

4. 数据类型

5. SQL

6. 集群介绍

1. 介绍

ClickHouse是一款由俄罗斯 Yandex 公司开发的用于联机分析(OLAP)的列式数据库管理系统(DBMS)。

常见的 OLAP 引擎: Hive、Spark SQL、Presto、Kylin、Impala、Druid、Clickhouse、Greeplum等。

2. 优缺点

特性:

  1. 真正的列式数据库管理系统
  2. 数据压缩
  3. 数据存储在磁盘.数据存储在磁盘可以降低成本，clickhouse 即使在普通磁盘上，也可以快速的查询出结果。
  4. 多核心并行处理
  5. 多服务器分布式处理.分布式表，可以利用多个服务器，并行执行查询。
  6. 支持 SQL
  7. 向量引擎
  8. 索引
  9. 适合在线查询
  10. 支持近似计算
  11. 自适应 join
  12. 支持数据副本机制.可以设置数据存储的备份，提高可用性。
  13. 角色控制

缺点:

  1. 没有完整的事务支持
  2. 不能高频、低延迟的修改或删除数据
  3. 不适合单行点查询

3. 表引擎

clickhouse 有多种表引擎，适用于不同的场景。建表语句如下:

  1. CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
  2. (
  3.     name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
  4.     name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|ALIAS expr2] [compression_codec] [TTL expr2],
  5.     ...
  6. ) ENGINE = engine

3.1 Log

  1. 插入数据会锁表
  2. 不支持索引，也就是说范围查询效率不高
  3. 适用于临时数据，一次性写入的表，测试表

3.1.1 TinyLog

  1. 最简单的引擎
  2. 适合一次写入，只读的场景
  3. 数据量在 100w 以内
  4. 没有并发控制，同时读写会报错，并发写入，数据不可用

3.1.2 StripeLog

  1. 可以并发读，写入会阻塞读操作

3.1.3 Log

  1. 可以并发读，性能比 StripeLog 要好一些
  2. 因为使用 __marks.mrk 文件记录每个数据块的偏移，写入数据出现问题，表直接报废

3.2 Engine Families MergeTree

  1. 使用最多的表引擎
  2. 支持索引、分区

3.2.1 MergeTree

  1. CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
  2. (
  3.     name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1], -- MATERIALIZED 从别的列物化出来这一列, 用于减少查询时候的计算量
  4.     name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2], -- ALIAS 不实际存储这一列, 在查询的时候执行
  5.     ...
  6.     INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1, -- 二级索引
  7.     INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2,
  8.     ...
  9.     PROJECTION projection_name_1 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]), -- 实验性功能, part 级别的物化视图, 在查询的时候会自动使用
  10.     PROJECTION projection_name_2 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY])
  11. ) ENGINE = MergeTree()
  12. ORDER BY expr -- 排序字段
  13. [PARTITION BY expr] -- 分区
  14. [PRIMARY KEY expr] -- 主键, 默认和 order by 一致, 一般情况下不设置, 跟 order by 保持一致
  15. [SAMPLE BY expr] -- 抽样, 必须是主键或者排序字段包含的字段, 必须是 UInt 类型字段
  16. [TTL expr -- 数据过期时间
  17.     [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ]
  18.     [WHERE conditions]
  19.     [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ]
  20. [SETTINGS name=value, ...]

使用示例:

  1. -- 示例1
  2. CREATE TABLE test.label_number_local
  3. (
  4.     `tag_code` String,
  5.     `user_id` UInt64 COMMENT '用户 id',
  6.     `value` Float64 COMMENT '标签值'
  7. )
  8. ENGINE = MergeTree
  9. PARTITION BY tag_code
  10. ORDER BY tag_code
  11.  
  12. -- 示例2, TTL
  13. CREATE TABLE test.dw_domestic_hotel_monitor_crm_realtime
  14. (
  15.     `dt` String COMMENT '时间分区',
  16.     `type` String COMMENT '类型',
  17.     `hour` String COMMENT '小时',
  18.     `hotel_seq` String COMMENT 'hotel_seq',
  19.     `device_id` String COMMENT '设备id',
  20.     `platform` String COMMENT '客户端',
  21.     ......
  22. )
  23. ENGINE = MergeTree()
  24. PARTITION BY (dt,
  25.  type)
  26. ORDER BY (dt,
  27.  hour,
  28.  type,
  29.  ......)
  30. -- 删除 dt+2day <= today(now()) and type!='order' 的数据, 即保留最近两天(包括当天)的数据
  31. TTL parseDateTimeBestEffort(dt) + toIntervalDay(2) WHERE type != 'order'
  32.  
  33. -- 示例3, sample
  34. CREATE TABLE test.clicks
  35. (
  36.     `CounterID` UInt64,
  37.     `EventDate` DATE,
  38.     `UserID` UInt64
  39. )
  40. ENGINE = MergeTree()
  41. ORDER BY (CounterID, intHash32(UserID))
  42. SAMPLE BY intHash32(UserID)
  43. -- 查询, 按 intHash32(UserID) 采样, 伪随机, 相同采样率每次查询结果一致
  44. select * from test.clicks sample 0.2

3.2.2 ReplacingMergeTree

会自动删除具有相同 order by 字段值的重复数据。但是他只会在后台合并分区文件的时候删除，所以不可靠。适用于清除重复数据节省空间，但不保证没有重复数据。

  1. CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
  2. (
  3.     name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
  4.     name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
  5.     ...
  6. ) ENGINE = ReplacingMergeTree([ver]) -- 带有版本号的列, 支持的数据类型 UInt*, Date, DateTime, DateTime64
  7. [PARTITION BY expr]
  8. [ORDER BY expr]
  9. [PRIMARY KEY expr]
  10. [SAMPLE BY expr]
  11. [SETTINGS name=value, ...]

3.2.3 SummingMergeTree

会自动合并具有相同 order by 字段值的重复数据，把其他数值类型的列加在一起。

  1. CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
  2. (
  3.     name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
  4.     name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
  5.     ...
  6. ) ENGINE = SummingMergeTree([columns]) -- 数值类型的列, 且不在 order by 字段中, 默认值是所有不在 order by 字段的数值类型列
  7. [PARTITION BY expr]
  8. [ORDER BY expr]
  9. [SAMPLE BY expr]
  10. [SETTINGS name=value, ...]

3.2.4 AggregatingMergeTree

会自动合并具有相同 order by 字段值的重复数据，把其他列聚合到一行。

列的类型，可以指定 AggregateFunction、SimpleAggregateFunction、其他类型，指定其他类型，使用第一次插入的值。

AggregateFunction 使用示例: AggregateFunction | ClickHouse Documentation

  1. CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
  2. (
  3.     name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
  4.     name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
  5.     ...
  6. ) ENGINE = AggregatingMergeTree()
  7. [PARTITION BY expr]
  8. [ORDER BY expr]
  9. [SAMPLE BY expr]
  10. [TTL expr]
  11. [SETTINGS name=value, ...]

使用示例:

  1. CREATE MATERIALIZED VIEW test.basic
  2. ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
  3. AS SELECT
  4.     CounterID,
  5.     StartDate,
  6.     sumState(Sign)    AS Visits,
  7.     uniqState(UserID) AS Users
  8. FROM test.visits
  9. GROUP BY CounterID, StartDate;
  10.  
  11. -- 查询 
  12. SELECT
  13.     StartDate,
  14.     sumMerge(Visits) AS Visits,
  15.     uniqMerge(Users) AS Users
  16. FROM test.basic
  17. GROUP BY StartDate
  18. ORDER BY StartDate;

3.2.5 CollapsingMergeTree

根据一个标识字段，合并 order by 相同的行。

  1. CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
  2. (
  3.     name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
  4.     name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
  5.     ...
  6. ) ENGINE = CollapsingMergeTree(sign) -- 标识字段, 字段类型 Int8, 1 标识有效, -1 标识无效
  7. [PARTITION BY expr]
  8. [ORDER BY expr]
  9. [SAMPLE BY expr]
  10. [SETTINGS name=value, ...]

使用示例:

  1. -- 建表
  2. CREATE TABLE test.UAct
  3. (
  4.     UserID UInt64,
  5.     PageViews UInt8,
  6.     Duration UInt8,
  7.     Sign Int8
  8. )
  9. ENGINE = CollapsingMergeTree(Sign)
  10. ORDER BY UserID
  11.  
  12. -- 插入数据
  13. INSERT INTO test.UAct VALUES (4324182021466249494, 5, 146, 1)
  14. INSERT INTO test.UAct VALUES (4324182021466249494, 5, 146, -1),(4324182021466249494, 6, 185, 1)
  15. OPTIMIZE TABLE test.UAct -- 合并 part 
  16.  
  17. -- 查询数据
  18. SELECT * FROM test.UAct
  19. -- 结果
  20. 4324182021466249494	6	185	1

3.2.6 VersionedCollapsingMergeTree

根据标识字段+version合并 order by 字段相同的行。

  1. CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
  2. (
  3.     name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
  4.     name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
  5.     ...
  6. ) ENGINE = VersionedCollapsingMergeTree(sign, version)
  7. [PARTITION BY expr]
  8. [ORDER BY expr]
  9. [SAMPLE BY expr]
  10. [SETTINGS name=value, ...]

使用示例:

  1. CREATE TABLE test.UAct_VC
  2. (
  3.     UserID UInt64,
  4.     PageViews UInt8,
  5.     Duration UInt8,
  6.     Sign Int8,
  7.     Version UInt8
  8. )
  9. ENGINE = VersionedCollapsingMergeTree(Sign, Version)
  10. ORDER BY UserID
  11.  
  12. -- 插入数据
  13. INSERT INTO test.UAct_VC VALUES 
  14. 	(4324182021466249494, 5, 146, 1, 1), 
  15. 	(4324182021466249494, 5, 146, -1, 1),
  16. 	(4324182021466249494, 6, 185, 1, 2),
  17. 	(4324182021466249494, 7, 185, 1, 3), 
  18. 	(4324182021466249494, 7, 185, -1, 4)
  19.  OPTIMIZE TABLE test.UAct_VC
  20.  
  21.  -- 查询数据
  22.  select * from test.UAct_VC limit 10
  23.  -- 结果, 会把 version 相同的数据进行合并
  24.  4324182021466249494	6	185	1	2
  25. 4324182021466249494	7	185	1	3
  26. 4324182021466249494	7	185	-1	4

3.2.7 GraphiteMergeTree

用来保存 Graphite 数据。

  1. CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
  2. (
  3.     Path String,
  4.     Time DateTime,
  5.     Value <Numeric_type>,
  6.     Version <Numeric_type>
  7.     ...
  8. ) ENGINE = GraphiteMergeTree(config_section)
  9. [PARTITION BY expr]
  10. [ORDER BY expr]
  11. [SAMPLE BY expr]
  12. [SETTINGS name=value, ...]

3.2.8 数据备份

上面的七个 MergeTree 引擎，都有对应带数据备份的引擎。

  • ReplicatedMergeTree
  • ReplicatedSummingMergeTree
  • ReplicatedReplacingMergeTree
  • ReplicatedAggregatingMergeTree
  • ReplicatedCollapsingMergeTree
  • ReplicatedVersionedCollapsingMergeTree
  • ReplicatedGraphiteMergeTree

clickhouse 的数据备份是表级别的，一个集群中，可以既有带备份的表，也可以有不带备份的表。他的使用如下所示:

  1. CREATE TABLE table_name [ON CLUSTER cluster] 
  2. (
  3.     x UInt32
  4. ) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/table_name', '{replica}')
  5. ORDER BY x

shard 和 replica 是配置在 clickhouse 配置文件的变量，每个 ck 节点对这两个变量都有不同的值，也可以不用变量直接写值。

下面的 sql 演示了如何创建一个1分片2备份的表。

  1. -- clickhouse3
  2. create table test.s1r2
  3. (
  4.     t String
  5. ) engine = ReplicatedMergeTree('/clickhouse/tables/test/s1r2', 'r1')
  6. order by t
  7.  
  8. 
			
    
	
    
		
    免费ERP
    
	
    
		
    61ebf17978942.jpg
     注册    登录 
    用户名: demo; 密码: 123456
    
	
    

    
	
    
		
    最新
    
	
    
			
	
    

    
	
    
		
    最热

ERP

联系站长

QQ：769220720

Copyright © SibooSoft All right reserved 津ICP备19011444号