ClickHouse|ClickHouse 入门
1 概述
什么是 ClickHouse?
ClickHouse 是俄罗斯的Yandex于2016年开源的列式存储数据库(DBMS),主要用于在线分析处理查询(OLAP),能够使用SQL查询实时生成分析数据报告。
2 安装部署
CentOS 取消打开文件数限制
[root@hadoop102 ~]# vim /etc/security/limits.conf
在文件末尾添加:
- soft nofile 65536
- hard nofile 65536
- soft nproc 131072
- hard nproc 131072
[root@hadoop102 ~]# vim /etc/security/limits.d/90-nproc.conf
在文件末尾添加:- soft nofile 65536
- hard nofile 65536
- soft nproc 131072
- hard nproc 131072
[root@hadoop102 ~]# ulimit -n
65536
CentOS 取消 SELINUX
[root@hadoop102 ~]# vim /etc/selinux/config
SELINUX=disabled
关闭防火墙
[root@hadoop102 ~]# systemctl stop firewall
安装依赖
[root@hadoop102 ~]# yum install -y libtool unixODBC
2.1 单机模式
下载 ClickHouse
wget http://repo.red-soft.biz/repos/clickhouse/stable/el7/clickhouse-client-1.1.54236-4.el7.x86_64.rpm
wget http://repo.red-soft.biz/repos/clickhouse/stable/el7/clickhouse-compressor-1.1.54236-4.el7.x86_64.rpm
wget http://repo.red-soft.biz/repos/clickhouse/stable/el7/clickhouse-debuginfo-1.1.54236-4.el7.x86_64.rpm
wget http://repo.red-soft.biz/repos/clickhouse/stable/el7/clickhouse-server-1.1.54236-4.el7.x86_64.rpm
wget http://repo.red-soft.biz/repos/clickhouse/stable/el7/clickhouse-server-common-1.1.54236-4.el7.x86_64.rpm
分别安装这 5 个 rpm
[root@hadoop102 software]# rpm -ivh *.rpm
启动 ClickHouse
前台启动:
[root@hadoop102 software]# clickhouse-server --config-file=/etc/clickhouse-server/config.xml
[root@hadoop102 software]# nohup clickhouse-server --config-file=/etc/clickhouse-server/config.xml >null 2>&1 &
使用 Client 连接 Server
[root@hadoop102 software]# clickhouse-client
?
ClickHouse client version 1.1.54236.
Connecting to localhost:9000.
Connected to ClickHouse server version 1.1.54236.
?
:)
2.2 分布式集群安装
三台机器修改配置文件 config.xml
[root@hadoop102 ~]# vim /etc/clickhouse-server/config.xml
?
::
在三台机器的 etc 目录下新建 metrika.xml 文件
[root@hadoop102 ~]# vim /etc/metrika.xml
添加如下内容:
true
hadoop102
9000
true
hadoop103
9000
true
hadoop104
9000
?
hadoop102
2181
hadoop103
2181
hadoop104
2181
?
hadoop102
::/0
?
10000000000
0.01
lz4
首先开启 Zookeeper
3 数据类型 3.1 整型
固定长度的整型,包括有符号整型或无符号整型。
整型范围
- Int8 - [-128 : 127]
- 【ClickHouse|ClickHouse 入门】Int16 - [-32768 : 32767]
- Int32 - [-2147483648 : 2147483647]
- Int64 - [-9223372036854775808 : 9223372036854775807]
- UInt8 - [0 : 255]
- UInt16 - [0 : 65535]
- UInt32 - [0 : 4294967295]
- UInt64 - [0 : 18446744073709551615]
Float32
-float
Float64
-double
使用浮点数
- 对浮点数进行计算可能引起四舍五入的误差
SELECT 1 - 0.9
┌───────minus(1, 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘
NaN and Inf
与标准 SQL 相比,ClickHouse 支持以下类别的浮点数:
-
Inf
– 正无穷
SELECT 0.5 / 0
┌─divide(0.5, 0)─┐
│ inf │
└────────────────┘
-
-Inf
– 负无穷
SELECT -0.5 / 0
┌─divide(-0.5, 0)─┐
│ -inf │
└─────────────────┘
-
NaN
– 非数字
SELECT 0 / 0
┌─divide(0, 0)─┐
│ nan │
└──────────────┘
3.3 布尔型
有符号的定点数,可在加、减和乘法运算过程中保持精度。对于除法,最低有效数字会被丢弃(不舍入)。
3.4 字符串
String
- 字符串可以任意长度的,它可以包含任意的字节集,包含空字节
- 固定长度 N 的字符串,N 必须是严格的正自然数,当服务端读取长度小于 N 的字符串时候,通过在字符串末尾添加空字节来达到 N 字节长度,当服务端读取长度大于 N 的字符串时候,将返回错误消息
与 String 相比,极少会使用 FixedString,因为使用起来不是很方便
包括 Enum8 和 Enum16 类型,Enum 保存 string= integer 的对应关系
Enum8 用 String= Int8 对描述。Enum16 用 String= Int16 对描述
创建一个带有一个枚举 Enum8('hello' = 1, 'world' = 2) 类型的列:
CREATE TABLE t_enum(x Enum8('hello' = 1, 'world' = 2))ENGINE = TinyLog
这个
x
列只能存储类型定义中列出的值:'hello'
或'world'
。如果尝试保存任何其他值,ClickHouse 抛出异常从表中查询数据时,ClickHouse 从
Enum
中输出字符串值SELECT * FROM t_enum
┌─x─────┐
?
│ hello │
?
│ world │
?
│ hello │
?
└───────┘
如果需要看到对应行的数值,则必须将
Enum
值转换为整数类型SELECT CAST(x, 'Int8') FROM t_enum
?
┌─CAST(x, 'Int8')─┐
?
│ 1 │
?
│ 2 │
?
│ 1 │
?
└─────────────────┘
数组
Array(T):由 T 类型元素组成的数组
T 可以是任意类型,包含数组类型。 但不推荐使用多维数组,ClickHouse 对多维数组的支持有限。例如,不能在 MergeTree 表中存储多维数组
可以使用 array 函数来创建数组
也可以使用方括号
创建数组案例:
:) SELECT array(1, 2) AS x, toTypeName(x)
?
SELECT
[1, 2] AS x,
toTypeName(x)
?
┌─x─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8) │
└───────┴─────────────────────────┘
?
1 rows in set. Elapsed: 0.002 sec.
?
:) SELECT [1, 2] AS x, toTypeName(x)
?
SELECT
[1, 2] AS x,
toTypeName(x)
?
┌─x─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8) │
└───────┴────────────────────┘
?
1 rows in set. Elapsed: 0.002 sec.
3.7 元组
Tuple(T1, T2, ...):元组,其中每个元素都有单独的类型
创建元组的示例:
:) SELECT tuple(1,'a') AS x, toTypeName(x)
?
SELECT
(1, 'a') AS x,
toTypeName(x)
?
┌─x───────┬─toTypeName(tuple(1, 'a'))─┐
│ (1,'a') │ Tuple(UInt8, String) │
└─────────┴───────────────────────────┘
?
1 rows in set. Elapsed: 0.021 sec.
3.8 Date
日期类型,用两个字节存储,表示从 1970-01-01 (无符号) 到当前的日期值
还有很多数据结构,可以参考官方文档:https://clickhouse.yandex/docs/zh/data_types/
4 表引擎 表引擎(即表的类型)决定了:
- 数据的存储方式和位置,写到哪里以及从哪里读取数据
- 支持哪些查询以及如何支持
- 并发数据访问
- 索引的使用(如果存在)
- 是否可以执行多线程请求
- 数据复制参数
- 最简单的表引擎,用于将数据存储在磁盘上,每列都存储在单独的压缩文件中,写入时,数据将附加到文件末尾
- 该引擎没有并发控制
- 如果同时从表中读取和写入数据,则读取操作将抛出异常;
- 如果同时写入多个查询中的表,则数据将被破坏
创建一个 TinyLog 引擎的表并插入一条数据
:)create table t (a UInt16, b String) ENGINE=TinyLog;
:)insert into t (a, b) values (1, 'abc');
此时我们到保存数据的目录 /var/lib/clickhouse/data/default/t 中可以看到如下目录结构:
[root@hadoop102 t]# ls
a.bin b.bin sizes.json
a.bin 和 b.bin 是压缩过的对应的列的数据,sizes.json 中记录了每个 *.bin 文件的大小:
[root@hadoop102 t]# cat sizes.json
{"yandex":{"a%2Ebin":{"size":"28"},"b%2Ebin":{"size":"30"}}}
4.2 Memory
内存引擎,数据以未压缩的原始形式直接保存在内存当中,服务器重启数据就会消失,读写操作不会相互阻塞,不支持索引,简单查询下有非常非常高的性能表现(超过 10G/s)
一般用到它的地方不多,除了用来测试,就是在需要非常高的性能,同时数据量又不太大(上限大概 1 亿行)的场景
4.3 Merge
Merge 引擎(不要跟 MergeTree 引擎混淆)本身不存储数据,但可用于同时从任意多个其他的表中读取数据,读是自动并行的,不支持写入,读取时,那些被真正读取到数据的表的索引(如果有的话)会被使用
Merge 引擎的参数:一个数据库名和一个用于匹配表名的正则表达式
Merge 引擎(不要跟 MergeTree 引擎混淆)本身不存储数据,但可用于同时从任意多个其他的表中读取数据。 读是自动并行的,不支持写入,读取时,那些被真正读取到数据的表的索引(如果有的话)会被使用
Merge 引擎的参数:一个数据库名和一个用于匹配表名的正则表达式
:)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, 'first');
:)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;
┌─id─┬─name─┐
│ 2 │ second │
└────┴──────┘
┌─id─┬─name──┐
│ 1 │ first │
└────┴───────┘
┌─id─┬─name───────┐
│ 3 │ i am in t3 │
└────┴────────────┘
4.4 MergeTree
Clickhouse 中最强大的表引擎当属 MergeTree(合并树)引擎及该系列(MergeTree)中的其他引擎
MergeTree 引擎系列的基本理念如下,当你有巨量数据要插入到表中,你要高效地一批批写入数据片段,并希望这些数据片段在后台按照一定规则合并,相比在插入时不断修改(重写)数据进存储,这种策略会高效很多
格式:
ENGINE [=] MergeTree(date-column [, sampling_expression], (primary, key), index_granularity)
参数解读:
- date-column:类型为 Date 的列名,ClickHouse 会自动依据这个列按月创建分区。分区名格式为 "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');
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 下可以看到:
[root@hadoop102 mt_table]# ls
20190501_20190501_2_2_0 20190503_20190503_6_6_0 20190601_20190601_4_4_0 detached
?
随便进入一个目录:
[root@hadoop102 20190601_20190601_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 保存主键索引
这个引擎是在 MergeTree 的基础上,添加了处理重复数据的功能,该引擎和 MergeTree 的不同之处在于它会删除具有相同主键的重复项,数据的去重只会在合并的过程中出现,合并会在未知的时间在后台进行,所以你无法预先作出计划,有一些数据可能仍未被处理,因此,ReplacingMergeTree 适用于在后台清除重复的数据以节省空间,但是它不保证没有重复的数据出现
格式:
ENGINE [=]ReplacingMergeTree(date-column [, sampling_expression], (primary, key),index_granularity, [ver])
- 可以看出比 MergeTree 只多了一个 ver,这个 ver 指代版本列,他和时间一起配置,区分哪条数据是最新的
create table rmt_table (date Date, id UInt8, name String,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 into rmt_table values ('2019-07-11', 1, 'a', 10);
?
等待一段时间或optimize table rmt_table手动触发merge,后查询
:) select * from rmt_table;
┌───────date─┬─id─┬─name─┬─point─┐
│ 2019-07-11 │ 1 │ a │ 30 │
└────────────┴────┴──────┴───────┘
4.6 SummingMergeTree
该引擎继承自 MergeTree,区别在于,当合并 SummingMergeTree 表的数据片段时,ClickHouse 会把所有具有相同主键的行合并为一行,该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键值对应于大量的行,则可以显著的减少存储空间并加快数据查询的速度,对于不可加的列,会取一个最先出现的值。
语法:
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, name), 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, 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
?
┌───────date─┬─name─┬─a─┬─b─┐
│ 2019-07-10 │ a │ 1 │ 2 │
│ 2019-07-10 │ b │ 2 │ 1 │
│ 2019-07-11 │ a │ 3 │ 1 │
│ 2019-07-11 │ b │ 6 │ 8 │
│ 2019-07-12 │ c │ 1 │ 3 │
└────────────┴──────┴───┴───┘
4.7 Distributed
分布式引擎,本身不存储数据, 但可以在多个服务器上进行分布式查询。 读是自动并行的。读取时,远程服务器表的索引(如果有的话)会被使用
格式:
Distributed(cluster_name, database, table [, sharding_key])
- cluster_name:服务器配置文件中的集群名,在 /etc/metrika.xml 中配置的
- database:数据库名
- table:表名
- 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, 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 │
└─────────┘
5 SQL 语法 5.1 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] [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 UInt16,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
┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ id │ UInt16 │ │ │
│ name │ String │ │ │
└──────┴────────┴──────────────┴────────────────────┘
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
┌─id─┬─name─────┐
│ 1 │ zhangsan │
│ 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')
:) insert into t2 select * from t3
5.3 ALTER
ALTER 只支持 MergeTree 系列,Merge 和 Distributed 引擎的表
基本语法:
ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|MODIFY COLUMN ...
参数解析:
- ADD COLUMN:向表中添加新列
- DROP COLUMN:在表中删除列
- MODIFY COLUMN:更改列的类型
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_table values ('2019-05-03', 3, 'wangwu');
3)在末尾添加一个新列 age
:)alter table mt_table add column age UInt8
:)desc mt_table
┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ date │ Date │ │ │
│ id │ UInt8 │ │ │
│ name │ String │ │ │
│ age │ UInt8 │ │ │
└──────┴────────┴──────────────┴────────────────────┘
:) select * from mt_table
┌───────date─┬─id─┬─name─┬─age─┐
│ 2019-06-01 │ 2 │ lisi │ 0 │
└────────────┴────┴──────┴─────┘
┌───────date─┬─id─┬─name─────┬─age─┐
│ 2019-05-01 │ 1 │ zhangsan │ 0 │
│ 2019-05-03 │ 3 │ wangwu │ 0 │
└────────────┴────┴──────────┴─────┘
4)更改 age 列的类型
:)alter table mt_table modify column age UInt16
:)desc mt_table
┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ date │ Date │ │ │
│ id │ UInt8 │ │ │
│ name │ String │ │ │
│ age │ UInt16 │ │ │
└──────┴────────┴──────────────┴────────────────────┘
5)删除刚才创建的 age 列
:)alter table mt_table drop column age
:)desc mt_table
┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ date │ Date │ │ │
│ id │ UInt8 │ │ │
│ name │ String │ │ │
└──────┴────────┴──────────────┴────────────────────┘
5.4 DESCRIBE TABLE
查看表结构
:)desc mt_table
┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ date │ Date │ │ │
│ id │ UInt8 │ │ │
│ name │ String │ │ │
└──────┴────────┴──────────────┴────────────────────┘
5.5 CHECK TABLE
检查表中的数据是否损坏,会返回两种结果:
0 – 数据已损坏
1 – 数据完整
该命令只支持 Log、TinyLog 和 StripeLog 引擎
推荐阅读
- 深入理解Go之generate
- typeScript入门基础介绍
- Android|Android sqlite3数据库入门系列
- Android下的IO库-Okio源码解析(一)|Android下的IO库-Okio源码解析(一) 入门
- Flutter自定义view|Flutter自定义view —— 闯关进度条
- 深度学习-入门
- 第三章|第三章 进校园重拾旧梦 登讲台初为人师第一节 接乱班面临考验 ,遇高师指点入门
- iOS开发技术之美—iOS入门技术的基础学习
- OpenCV|OpenCV-Python实战(18)——深度学习简介与入门示例
- 2018年|2018年 Bible study 01/02/2018