clickhouse hadoop_如何实现大数据场景下的实时查询 | ClickHouse基础、原理与实践（上）...

这次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贴心地用聚合函数帮我们解决了这个问题。