大数据资料之ClickHouse.pdf

1、大数据技术之 ClickHouse 第 1 章 ClickHouse 概述 1.1 什么是 ClickHouse ClickHouse 是俄罗斯的 Yandex 于 2016 年开源的列式存储数据库（ DBMS），主要用于在线分析处理查询（ OLAP），能够使用 SQL 查询实时生成分析数据报告。 1.2 什么是列式存储 以下面的表为例： Id Name Age 1 张三 18 2 李四 22 3 王五 34 采用行式存储时，数据在磁盘上的组织结构为： 1 张三 18 2 李四 22 3 王五 34 好处是想查某个人所有的属性时，可以通过一次磁盘查找加顺序读取就可以。但是当想查所有人的年龄时，

2、需要不停的查找，或者全表扫描才行，遍历的很多数据都是不需要的。 而采用列式存储时，数据在磁盘上的组织结构为： 1 2 3 张三 李四 王五 18 22 34 这时想查所有人的年龄只需把年龄那一列拿出来就可以了 1.3 安装前的准备 1.3.1 CentOS 取消打开文件数限制 在 /etc/security/limits.conf、 /etc/security/limits.d/90-nproc.conf 这 2 个文件的末尾加入一下内容： roothadoop102 software# vim /etc/security/limits.conf 在文件末尾添加： * soft nofile

3、65536 * hard nofile 65536 * soft nproc 131072 * hard nproc 131072 roothadoop102 software# vim /etc/security/limits.d/90-nproc.conf 在文件末尾添加： * soft nofile 65536 * hard nofile 65536 * soft nproc 131072 * hard nproc 131072 重启服务器之后生效，用 ulimit -n 或者 ulimit -a 查看设置结果 roothadoop102 # ulimit -n 65536 1.3.2

4、CentOS 取消 SELINUX 修改 /etc/selinux/config 中的 SELINUX=disabled 后重启 roothadoop102 # vim /etc/selinux/config SELINUX=disabled 1.3.3 关闭防火墙 roothadoop102 # service iptables stop roothadoop102 # service ip6tables stop ip6tables：将 chains 设置为 ACCEPT 策略： filter 确定 ip6tables：清除防火墙规则： 确定 ：正在卸载模块： 确定 1.3.4 安装依赖

5、roothadoop102 # yum install -y libtool roothadoop102 # yum install -y *unixODBC* 第 2 章 安装 2.1 网址 官网： https:/clickhouse.yandex/ 下载地址： http:/repo.red-soft.biz/repos/clickhouse/stable/el6/ 2.2 单机模式 2.2.1 上传 5 个文件到 /opt/software/ roothadoop102 software# ls clickhouse-client-1.1.54236-4.el6.x86_64.rpm cl

6、ickhouse-server-1.1.54236-4.el6.x86_64.rpm clickhouse-compressor-1.1.54236-4.el6.x86_64.rpm clickhouse-server-common-1.1.54236-4.el6.x86_64.rpm clickhouse-debuginfo-1.1.54236-4.el6.x86_64.rpm 2.2.2 分别安装这 5 个 rpm 文件 roothadoop102 software# rpm -ivh *.rpm Preparing. # 100% 1:clickhouse-server-commo# 2

7、0% 2:clickhouse-server # 40% 3:clickhouse-client # 60% 4:clickhouse-debuginfo # 80% 5:clickhouse-compressor # 100% 2.2.3 启动 ClickServer 前台启动： roothadoop102 software# clickhouse-server -config-file=/etc/clickhouse-server/config.xml 后台启动： roothadoop102 software# nohup clickhouse-server -config-file=/e

8、tc/clickhouse-server/config.xml null 2 :)insert into t (a, b) values (1, abc); 此时我们到保存数据的目录 /var/lib/clickhouse/data/default/t 中可以看到如下目录结构： roothadoop102 t# ls a.bin b.bin sizes.json a.bin 和 b.bin 是压缩过的对应的列的数据， sizes.json 中记录了每个 *.bin 文件的大小： roothadoop102 t# cat sizes.json “yandex“:“a%2Ebin“:“size“:

9、“28“,“b%2Ebin“:“size“:“30“ 4.2 Memory 内存引擎， 数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。读写操作不会相互阻塞 ， 不支持索引 。 简单查询下有非常非常高的性能表现 （超过 10G/s） 。 一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太大（上限大概 1 亿行）的场景。 4.3 Merge Merge 引擎 (不要跟 MergeTree 引擎混淆 ) 本身不存储数据，但可用于同时从任意多个其他的表中读取数据。 读是自动并行的，不支持写入。读取时，那些被真正读取到数据的表的索引（如果有的话）会被使用。

10、Merge 引擎的参数：一个数据库名和一个用于匹配表名的正则表达式。 案例： 先建 t1， t2， t3 三个表，然后用 Merge 引擎的 t 表再把它们链接起来。 :)create table t1 (id UInt16, name String) ENGINE=TinyLog; :)create table t2 (id UInt16, name String) ENGINE=TinyLog; :)create table t3 (id UInt16, name String) ENGINE=TinyLog; :)insert into t1(id, name) values (1, f

11、irst); :)insert into t2(id, name) values (2, second); :)insert into t3(id, name) values (3, i am in t3); :)create table t (id UInt16, name String) ENGINE=Merge(currentDatabase(), t); :) select * from t; idname 2 second idname 1 first idname 3 i am in t3 4.4 MergeTree Clickhouse 中 最 强 大 的 表 引 擎 当 属 M

12、ergeTree （ 合 并 树 ） 引 擎 及 该 系 列（ *MergeTree）中的其他引擎。 MergeTree 引擎系列的基本理念如下。当你有巨量数据要插入到表中，你要高效地一批批写入数据片段，并希望这些数据片段在后台按照一定规则合并。相比在插入时不断修改（重写）数据进存储，这种策略会高效很多。 格式： ENGINE = MergeTree(date-column , sampling_expression, (primary, key), index_granularity) 参数解读： date-column 类型为 Date 的列名。 ClickHouse 会自动依据这个列按月

13、创建分区。分区名格式为 “YYYYMM“ 。 sampling_expression 采样表达式。 (primary, key) 主键。类型为 Tuple() index_granularity 索引粒度。即索引中相邻 ” 标记 ” 间的数据行数。设为 8192 可以适用大部分场景。 案例： create table mt_table (date Date, id UInt8, name String) ENGINE=MergeTree(date, (id, name), 8192); insert into mt_table values (2019-05-01, 1, zhangsan);

14、 insert into mt_table values (2019-06-01, 2, lisi); insert into mt_table values (2019-05-03, 3, wangwu); 在 /var/lib/clickhouse/data/default/mt_tree 下可以看到： roothadoop102 mt_table# ls 20190501_20190501_2_2_0 20190503_20190503_6_6_0 20190601_20190601_4_4_0 detached 随便进入一个目录： roothadoop102 20190601_2019

15、0601_4_4_0# ls checksums.txt columns.txt date.bin date.mrk id.bin id.mrk name.bin name.mrk primary.idx - *.bin 是按列保存数据的文件 - *.mrk 保存块偏移量 - primary.idx 保存主键索引 4.5 ReplacingMergeTree 这个引擎是在 MergeTree 的基础上，添加了“处理重复数据”的功能，该引擎和MergeTree 的不同之处在于它会删除具有相同主键的重复项。数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行， 所以 你无法预先作出计划。

16、有一些数据可能仍未被处理。因此， ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。 格式： ENGINE = ReplacingMergeTree(date-column , sampling_expression, (primary, key), index_granularity, ver) 可以看出他比 MergeTree 只多了一个 ver，这个 ver 指代版本列 ，他和时间一起配置，区分哪条数据是最新的 。 案例： create table rmt_table (date Date, id UInt8, name Stri

17、ng,point UInt8) ENGINE= ReplacingMergeTree(date, (id, name), 8192,point); 插入一些数据： insert into rmt_table values (2019-07-10, 1, a, 20); insert into rmt_table values (2019-07-10, 1, a, 30); insert into rmt_table values (2019-07-11, 1, a, 20); insert into rmt_table values (2019-07-11, 1, a, 30); insert

18、 into rmt_table values (2019-07-11, 1, a, 10); 等待一段时间或 optimize table rmt_table 手动触发 merge，后查询 :) select * from rmt_table; dateidnamepoint 2019-07-11 1 a 30 4.6 SummingMergeTree 该引擎继承自 MergeTree。区别在于，当合并 SummingMergeTree 表的数据片 段时， ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键

19、值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度 ，对于 不可加的列，会取一个最先出现的值。 语法： ENGINE = SummingMergeTree(date-column , sampling_expression, (primary, key), index_granularity, columns) columns 包含将要被汇总 的列的列名的元组 案例： create table smt_table (date Date, name String, a UInt16, b UInt16) ENGINE=SummingMergeTree(date, (date, nam

20、e), 8192, (a) 插入数据： insert into smt_table (date, name, a, b) values (2019-07-10, a, 1, 2); insert into smt_table (date, name, a, b) values (2019-07-10, b, 2, 1); insert into smt_table (date, name, a, b) values (2019-07-11, b, 3, 8); insert into smt_table (date, name, a, b) values (2019-07-11, b, 3,

21、8); insert into smt_table (date, name, a, b) values (2019-07-11, a, 3, 1); insert into smt_table (date, name, a, b) values (2019-07-12, c, 1, 3); 等待一段时间或 optimize table smt_table 手动触发 merge，后查询 :) select * from smt_table datenameab 2019-07-10 a 1 2 2019-07-10 b 2 1 2019-07-11 a 3 1 2019-07-11 b 6 8

22、2019-07-12 c 1 3 发现 2019-07-11， b 的 a 列合并相加了， b 列取了 8（因为 b 列为 8 的数据最先插入）。 4.7 Distributed 分布式引擎 ， 本身不存储数据 , 但可以在多个服务器上进行分布式查询。 读是自动并行的。读取时，远程服务器表的索引（如果有的话）会被使用。 Distributed(cluster_name, database, table , sharding_key) 参数解析： cluster_name - 服务器配置文件中的集群名 ,在 /etc/metrika.xml 中配置的 database 数据库名 table 表名

23、 sharding_key 数据分片键 案例演示： 1）在 hadoop102， hadoop103， hadoop104 上分别创建一个表 t :)create table t(id UInt16, name String) ENGINE=TinyLog; 2）在 三台机器 的 t 表中插入一些数据 :)insert into t(id, name) values (1, zhangsan); :)insert into t(id, name) values (2, lisi); 3）在 hadoop102 上创建分布式表 :)create table dis_table(id UInt16

24、, name String) ENGINE=Distributed(perftest_3shards_1replicas, default, t, id); 4）往 dis_table 中插入数据 :) insert into dis_table select * from t 5）查看数据量 :) select count() from dis_table FROM dis_table count() 8 :) select count() from t SELECT count() FROM t count() 3 可以看到每个节点大约有 1/3 的数据 第 5 章 SQL 语法 5.1

25、CREATE 5.1.1 CREATE DATABASE 用于创建指定名称的数据库，语法如下： CREATE DATABASE IF NOT EXISTS db_name 如果查询中存在 IF NOT EXISTS，则当数据库已经存在时，该查询不会返回任何错误。 :) create database test; Ok. 0 rows in set. Elapsed: 0.018 sec. 5.1.2 CREATE TABLE 对于创建表，语法如下： CREATE TABLE IF NOT EXISTS db.table_name ON CLUSTER cluster ( name1 type1

26、 DEFAULT|MATERIALIZED|ALIAS expr1, name2 type2 DEFAULT|MATERIALIZED|ALIAS expr2, . ) ENGINE = engine DEFAULT expr 默认值，用法与 SQL 类似。 MATERIALIZED expr 物化表达式， 被该表达式指定的列不能 被 INSERT，因为它总是被计算出来的。 对于 INSERT 而言，不需要考虑这些列。 另外，在 SELECT 查询中如果包含星号，此列不会被 查询。 ALIAS expr 别名。 有三种方式创建表： 1）直接创建 :) create table t1(id UI

27、nt16,name String) engine=TinyLog 2） 创建一个与 其他表 具有相同结构的表 CREATE TABLE IF NOT EXISTS db.table_name AS db2.name2 ENGINE = engine 可以对其指定不同的表引擎声明。如果没有表引擎声明，则创建的表将与 db2.name2使用相同的表引擎。 :) create table t2 as t1 engine=Memory :) desc t2 DESCRIBE TABLE t2 nametypedefault_typedefault_expression id UInt16 name S

28、tring 3） 使用指定的引擎创建一个与 SELECT 子句的结果具有相同结构的表，并使用 SELECT子句的结果填充它。 语法： CREATE TABLE IF NOT EXISTS db.table_name ENGINE = engine AS SELECT . 实例： 先在 t2 中插入几条数据 :) insert into t1 values(1,zhangsan),(2,lisi),(3,wangwu) :) create table t3 engine=TinyLog as select * from t1 :) select * from t3 idname 1 zhangs

29、an 2 lisi 3 wangwu 5.2 INSERT INTO 主要用于向 表中 添加数据 ，基本格式如下： INSERT INTO db.table (c1, c2, c3) VALUES (v11, v12, v13), (v21, v22, v23), . 实例： :) insert into t1 values(1,zhangsan),(2,lisi),(3,wangwu) 还可以使用 select 来写入数据： INSERT INTO db.table (c1, c2, c3) SELECT . 实例： :) insert into t2 select * from t3 :)

30、 select * from t2 idname 1 zhangsan 2 lisi 3 wangwu ClickHouse 不 支 持 的 修 改 数 据 的 查 询 ： UPDATE, DELETE, REPLACE, MERGE, UPSERT, INSERT UPDATE。 5.3 ALTER ALTER 只支持 MergeTree 系列， Merge 和 Distributed 引擎的表，基本语法： ALTER TABLE db.name ON CLUSTER cluster ADD|DROP|MODIFY COLUMN . 参数解析： ADD COLUMN 向表中添加新列 DROP

31、 COLUMN 在表中删除列 MODIFY COLUMN 更改列的类型 案例演示： 1）创建一个 MergerTree 引擎的表 create table mt_table (date Date, id UInt8, name String) ENGINE=MergeTree(date, (id, name), 8192); 2）向表中插入一些值 insert into mt_table values (2019-05-01, 1, zhangsan); insert into mt_table values (2019-06-01, 2, lisi); insert into mt_tabl

32、e values (2019-05-03, 3, wangwu); 3）在末尾添加一个新列 age :)alter table mt_table add column age UInt8 :)desc mt_table nametypedefault_typedefault_expression date Date id UInt8 name String age UInt8 :) select * from mt_table dateidnameage 2019-06-01 2 lisi 0 dateidnameage 2019-05-01 1 zhangsan 0 2019-05-03 3

33、 wangwu 0 4）更改 age 列的类型 :)alter table mt_table modify column age UInt16 :)desc mt_table nametypedefault_typedefault_expression date Date id UInt8 name String age UInt16 5）删除刚才创建的 age 列 :)alter table mt_table drop column age :)desc mt_table nametypedefault_typedefault_expression date Date id UInt8 name String 5.4 DESCRIBE TABLE 查看表结构 :)desc mt_table nametypedefault_typedefault_expression date Date id UInt8 name String 5.5 CHECK TABLE 检查表中的数据是否损坏，他会返回两种结果： 0 数据已损坏 1 数据完整 该命令只支持 Log， TinyLog 和 StripeLog 引擎。