这次ClickHouse的主题讨论。我会分三篇来写~
上篇从实操出发谈及ClickHouse的基础功能,并分享自己在实际业务中的经验心得。
中篇会探究一下ClickHouse组成、原理和技术细节,并横向对比例如Kudu、ES等实时数据查询解决方案,分析它们之间的异同与优劣。
下篇是基于某新一线大厂的分享,结合自身经历和对ClickHouse的理解,分(xia)享(bian)一些脱敏后的实践经历。
1、ClickHouse是俄罗斯Yandex于2016年开源的列式存储数据库,主要用于OLAP。
2、命令行模式下的ClickHouse的打开方式
clickhouse-client \
--host 127.0.0.1 \
--port 7000 \
--user root \
--password asdf \
--database test \
--query "select * from abc"
在实际业务场景中,会有在命令行界面中执行SQL的需要,因此有时要添加一些额外的参数,如下:
clickhouse-client -m #允许多行
clickhouse-client -t #打印执行时间
clickhouse-client -f #指定数据输出格式
clickhouse-client --stacktace #打印堆栈跟踪信息
3、数据类型
(1)Int8、Int16、Int32、Int64:整数
其中,IntN的范围是[-2^(N-1),2^(N-1)-1],N=8,16,32,64
(2)UInt8、UInt16:正整数
U代表无符号
(3)String:字符串
如果需要限定长度,用FixedString(N),可类比varchar
(4)Date、Datetime:时间
(5)Float32、Float64:浮点数
对应float、double,不建议用,会有精度损失(1-0.9 = 0.09999999)
(6)其他
Enum8、Enum16,枚举类型
Array,数组,内部只能单一类型
Tuple,元组,内部可有多个类型
(7)注意
没有Boolean类型,可用枚举替代:Enum8('true'=0,'false'=1)
4、数据引擎的比较维度
(1)内存还是磁盘
(2)支持哪些语句
(3)是否支持并发多线程
(4)是否可以使用索引
(5)是否高可用可复制
5、数据引擎
(1)TinyLog
存在磁盘,不支持索引,支持并发读不支持并发写(不支持并发控制),节省空间,只用于查询
文件结构是列式存储,一列一个文件;还有一个sizes.json存储每一列的大小
(2)Memory
内存中,每秒10G,不支持索引,用于测试,受限于内存大小
(3)Merge
不存数据;合并其他表,后面带两个参数,数据库名、表名的正则
(4)MergeTree
树结构,多个相同枝干自动合并,按主键排序,支持分区,支持副本,支持采样
(5)ReplacingMergeTree
MergeTree基础上,会基于主键删除重复数据,自动定时执行,不保证没有重复数据
(6)SummingMergeTree
MergeTree基础上,会基于主键汇总重复数据,若无法汇总则保留第一条记录
(7)Distrubuted
不存数据,合并集群中的表
(8)HDFS、MySQL等
外部表
(9)ReplicatedMergeTree
在MergeTree前加Replicated的前缀,可以建立副本,需要配置zk,并且需要修改配置
6、建表语句
CREATE TABLE tbl(
edate Date
,boo Enum8('true'=0,'false'=1)
)
ENGINE=TinyLog;
7、不同引擎的语法规则
(1)MergeTree
ENGINE=MergeTree()
PARTITION BY date
ORDER BY (id,name)
PRIMARY KEY ccc #如果不写,默认是orderby的字段
SAMPLE BY dd
SETTINGS
index_granularity = 8192 #索引粒度
use_minimalistic_part_header_in_zookeeper=1 #数据片段头在zk中的存储方式
min_merge_bytes_to_use_direct_io=1 #使用直接IO,适合大量数据,linux中有缓存IO(数据到缓存再到磁盘)和直接IO(数据直接到磁盘)
(2)ReplacingMergeTree
ENGINE=ReplacingMergeTree(col=Int) #col不指定,保留最后一条,指定则保留值最大的那条
PARTITION BY date
ORDER BY (id,name)
PRIMARY KEY ccc
SAMPLE BY dd
SETTINGS
index_granularity = 8192
use_minimalistic_part_header_in_zookeeper=1
min_merge_bytes_to_use_direct_io=1
(3)SummingMergeTree
ENGINE=SummingMergeTree(col=Int) #加总字段
PARTITION BY date
ORDER BY (id,name)
PRIMARY KEY ccc
SAMPLE BY dd
SETTINGS
index_granularity = 8192
use_minimalistic_part_header_in_zookeeper=1
min_merge_bytes_to_use_direct_io=1
(4)Distributed
ENGINE=Distributed(clickhouse_cluser,default,t,id) #根据插入数据的id字段,均匀分发到每个分布表中
(5)外部表
ENGINE=HDFS('hdfs://hadoop:9000/t.csv','CSV')
(6)刷新语句
optimize table tbl
一般不建议使用,因为大数据量级下,刷新需要较长时间和计算的开销
8、常用DDL
(1)SHOW TABLE FROM dbname
(2)CREATE DATABASE dbname
(3)CREATE TABLE tbl
(4)ALTER TABLE
只支持MergeTree、Merge和Distributed引擎
(5)ALTER TABLE tbl ADD|DROP|MODIFY col
(6)DESCRIBE TABLE
(7)CHECK TABLE
只支持Log类引擎
(8)DROP TABLE tbl ON CLUSTER hadoop
批量删除集群中的相同表
9、使用经验
(1)嵌套表时,必须使用AS
select id,name
from (
select a.id as id #这里必须用as,否则外层表读到的是a.id而不是id
,a.name as name #同理
,count(b.subject) as s_cnt
from tbl1 a join tbl2 b on a.id = b.id
group by a.id,a.name
)t
(2)从ClickHouse导出数据到MySQL,选择建外部表的方案时,会遇到MySQL自增主键的问题,当插入行自增主键列的数据为空时会报错。
解决方案是在ClickHouse的建表语句里不加id字段,并在MySQL实体表建表语句中设置id字段为自增,这样当我们执行INSERT INTO ClickHouse外表的时候,id字段会以自增的形式自动填充数据。
(3)判断时间是否为今天
WHERE toDate(substring(toString(created_at),1,10)) = today()
(4)建表语句报错Expected one of: column declaration, identifier, list of elements, INDEX, column or index declaration, columns or indices declaration list
检查建表语句中出现多余的逗号
(5)argMax函数
当表中数据出现同一个id多行记录时,只需要取最新的那行数据对应的字段,此时可使用argMax函数,具体方式如下:
select id
,argMax(name,version) as name
,argMax(score,version) as score
from tbl
group by id
在离线Hive场景,通常是用开窗函数row_number()来处理,但ClickHouse贴心地用聚合函数帮我们解决了这个问题。