什么是ClickHouse
lickHouse是一个完全的列式数据库管理系统,允许在运行时创建表和数据库,加载数据和运行查询,而无需重新配置和重新启动服务器,支持线性扩展,简单方便,高可靠性,容错。它在大数据领域没有走 Hadoop 生态,而是采用 Local attached storage 作为存储,这样整个 IO 可能就没有 Hadoop 那一套的局限。它的系统在生产环境中可以应用到比较大的规模,因为它的线性扩展能力和可靠性保障能够原生支持 shard + replication 这种解决方案。它还提供了一些 SQL 直接接口,有比较丰富的原生 client。另外就是它比较快。
选择ClickHouse 的首要原因是它比较快,但其实它的技术没有什么新的地方,为什么会快?
主要有四个方面的因素:
1.它的数据剪枝能力比较强,分区剪枝在执行层,而存储格式用局部数据表示,就可以更细粒度地做一些数据的剪枝。它的引擎在实际使用中应用了一种现在比较流行的 LSM 方式。
2.它对整个资源的垂直整合能力做得比较好,并发 MPP+ SMP 这种执行方式可以很充分地利用机器的集成资源。它的实现又做了很多性能相关的优化,它的一个简单的汇聚操作有很多不同的版本,会根据不同 Key 的组合方式有不同的实现。对于高级的计算指令,数据解压时,它也有少量使用。
3.ClickHouse 是一套完全由 C++ 模板 Code 写出来的实现,代码还是比较优雅的。
4.ClickHouse是一个完全的列式数据库
什么是OLAP
联机分析处理OLAP是一种软件技术,它使分析人员能够迅速、一致、交互地从各个方面观察信息,以达到深入理解数据的目的。它具有FASMI(Fast Analysis of Shared Multidimensional Information),即共享多维信息的快速分析的特征。其中F是快速性(Fast),指系统能在数秒内对用户的多数分析要求做出反应;A是可分析性(Analysis),指用户无需编程就可以定义新的专门计算,将其作为分析的一部 分,并以用户所希望的方式给出报告;M是多维性(Multi—dimensional),指提供对数据分析的多维视图和分析;是信息性(Information),指能及时获得信息,并且管理大容量信息!
OLAP展现在用户面前的是一幅幅多维视图。维(Dimension):是人们观察数据的特定角度,是考虑问题时的一类属性,属性集合构成一个维(时间维、地理维等)。
维的层次(Level):人们观察数据的某个特定角度(即某个维)还可以存在细节程度不同的各个描述方面(时间维:日期、月份、季度、年)。
维的成员(Member):维的一个取值,是数据项在某维中位置的描述。(“某年某月某日”是在时间维上位置的描述)。
度量(Measure):多维数组的取值。(2000年1月,上海,笔记本电脑,0000)。
OLAP的基本多维分析操作有钻取(Drill-up和Drill-down)、切片(Slice)和切块(Dice)、以及旋转(Pivot)等。
钻取:是改变维的层次,变换分析的粒度。它包括向下钻取(Drill-down)和向上钻取(Drill-up)/上卷(Roll-up)。Drill-up是在某一维上将低层次的细节数据概括到高层次的汇总数据,或者减少维数;而Drill-down则相反,它从汇总数据深入到细节数据进行观察或增加新维。
切片和切块:是在一部分维上选定值后,关心度量数据在剩余维上的分布。如果剩余的维只有两个,则是切片;如果有三个或以上,则是切块。
旋转:是变换维的方向,即在表格中重新安排维的放置(例如行列互换)。
clickhouse特征
真正的列式数据库管理系统
数据压缩
数据的磁盘存储
多核心并行处理
多服务器分布式处理
支持SQL
向量引擎
实时的数据更新
索引
适合在线查询
支持近似计算
支持数据复制和数据完整性
优点
1,为了高效的使用CPU,数据不仅仅按列存储,同时还按向量进行处理;
2,数据压缩空间大,减少IO;处理单查询高吞吐量每台服务器每秒最多数十亿行;
3,索引非B树结构,不需要满足最左原则;只要过滤条件在索引列中包含即可;即使在使用的数据不在索引中,由于各种并行处理机制ClickHouse全表扫描的速度也很快;
4,写入速度非常快,50-200M/s,对于大量的数据更新非常适用。
缺点
1.不支持事务,不支持真正的删除/更新;
2.不支持高并发,官方建议qps为100,可以通过修改配置文件增加连接数,但是在服务器足够好的情况下;
3.不支持真正的删除/更新支持 不支持事务(期待后续版本支持)
4.不支持二级索引
5.有限的SQL支持,join实现与众不同
6.不支持窗口功能
7.元数据管理需要人工干预维护
8.SQL满足日常使用80%以上的语法,join写法比较特殊;最新版已支持类似SQL的join,但性能不好;
9.尽量做1000条以上批量的写入,避免逐行insert或小批量的insert,update,delete操作,因为ClickHouse底层会不断的做异步的数据合并,会影响查询性能,这个在做实时数据写入的时候要尽量避开;
10.ClickHouse快是因为采用了并行处理机制,即使一个查询,也会用服务器一半的CPU去执行,所以ClickHouse不能支持高并发的使用场景,默认单查询使用CPU核数为服务器核数的一半,安装时会自动识别服务器核数,可以通过配置文件修改该参数。
ClickHouse安装篇
单机安装
1)安装curl工具
yum install -y curl
2)添加clickhouse的yum镜像
curl -s https://packagecloud.io/install/repositories/altinity/clickhouse/script.rpm.sh | sudo bash
3)检查镜像情况
[root@doit04 yum.repos.d]# yum list | grep clickhouse
clickhouse-client.x86_64 20.5.4.40-1.el7 @Altinity_clickhouse
clickhouse-common-static.x86_64 20.5.4.40-1.el7 @Altinity_clickhouse
4)安装clickhouse的服务端和客户端
yum install -y clickhouse-server clickhouse-client
5)启动服务daunt
service clickhouse-server start
6)启动交互式客户端
clickhouse-client
客户端支持的参数如下:
ClickHouse实践篇
数据类型
整型
固定长度的整型,包括有符号整型或无符号整型。
整型范围(-2n-1~2n-1-1):
Int8 - [-128 : 127]
Int16 - [-32768 : 32767]
Int32 - [-2147483648 : 2147483647]
Int64 - [-9223372036854775808 : 9223372036854775807]
无符号整型范围(0~2n-1):
UInt8 - [0 : 255]
UInt16 - [0 : 65535]
UInt32 - [0 : 4294967295]
UInt64 - [0 : 18446744073709551615]
浮点型
Float32 - float
Float64 – double
建议尽可能以整数形式存储数据。例如,将固定精度的数字转换为整数值,如时间用毫秒为单位表示,因为浮点型进行计算时可能引起四舍五入的误差。
限制小数点后面的位数:
Decimal32/64(9,2) 表示该类型为0-9的数字只会保留两位小数
:) select 1-0.9
┌───────minus(1, 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘
与标准SQL相比,ClickHouse 支持以下类别的浮点数:
Inf-正无穷:
:) select 1/0
┌─divide(1, 0)─┐
│ inf │
└──────────────┘
-Inf-负无穷:
:) select -1/0
┌─divide(1, 0)─┐
│ -inf │
└──────────────┘
NaN-非数字:
:) select 0/0
┌─divide(0, 0)─┐
│ nan │
└──────────────┘
布尔型
没有单独的类型来存储布尔值。可以使用 UInt8 类型,取值限制为 0 或 1。
字符串
1)String
字符串可以任意长度的。它可以包含任意的字节集,包含空字节。
2)FixedString(N)
固定长度 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 抛出异常。
:) INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello')
INSERT INTO t_enum VALUES
Ok.
3 rows in set. Elapsed: 0.002 sec.
:) insert into t_enum values('a')
INSERT INTO t_enum VALUES
Exception on client:
Code: 49. DB::Exception: Unknown element 'a' for type Enum8('hello' = 1, 'world' = 2)
从表中查询数据时,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 类型元素组成的数组。在clickhouse中数组的长度都是从1开始的
T 可以是任意类型,包含数组类型。 但不推荐使用多维数组,ClickHouse 对多维数组的支持有限。例如,不能在 MergeTree 表中存储多维数组。
可以使用array函数来创建数组:
array(T)
也可以使用方括号:
[ ]
创建数组案例:
:) 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.
元组
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.
Date
日期类型,用两个字节存储,表示从 1970-01-01 (无符号) 到当前的日期值。
还有很多数据结构,可以参考官方文档:https://ClickHouse.yandex/docs/zh/data_types/
将日期格式的字符串转换成日期格式:
linux01 :) select toDatetime('2020:10:23');
SELECT toDatetime('2020:10:23')
┌─toDate('2020:10:23')─┐
│ 2020-10-23 │
└──────────────────────┘
Datetime
日期加时间格式
将日期加时间格式的字符串转换成日期加时间格式:
linux01 :) select toDateTime('2020:10:23 10:10:10');
SELECT toDateTime('2020:10:23 10:10:10')
┌─toDateTime('2020:10:23 10:10:10')─┐
│ 2020-10-23 10:10:10 │
└───────────────────────────────────┘
Nested:
嵌套类型:套在里面的数据类型都是数组类型
往里面插入数据时,需要注意的是,嵌套在里面的数组要保证每个类型对应的数组长度要一致,否则无法插入.
linux01 :) create table
if not exists
tb_user10
(users Tuple(String,UInt8,Float32),
id String,name String,
sal Int16)
engine=ReplacingMergeTree(id)
order by id;
CREATE TABLE IF NOT EXISTS tb_user10
(
`users` Tuple(String, UInt8, Float32),
`id` String,
`name` String,
`sal` Int16
)
ENGINE = ReplacingMergeTree(id)
ORDER BY id
Nullable(Int8)
表示放入的数据类型可以为null或int类型的数字
将字符串类型的数字转换成int类型的数字
linux01 :) select toInt8('2312');
SELECT toInt16('2312')
┌─toInt8('2312')─┐
│ 2312 │
└────────────────┘
linux01 :) select cast('213','Int16');
SELECT cast('213', 'Int16')
┌─OrZreo─┐
│ 213 │
└────────┘
数据库的基础操作
查看数据库个数
linux01 :) show databases;
SHOW DATABASES
┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ default │
│ doit │
│ doit02 │
│ system │
└────────────────────────────────┘
查看当前所在的数据库
linux01 :) select currentDatabase();
SELECT currentDatabase()
┌─currentDatabase()─┐
│ doit02 │
└───────────────────┘
切换数据库
linux01 :) use doit02;
USE doit02
Ok.
查看当前数据下所有的表:
show tables;
SHOW TABLES
┌─name───────┐
│ tb_student │
│ tb_teacher │
│ tb_user │
│ tb_user10 │
│ tb_user2 │
│ tb_user3 │
│ tb_user4 │
│ tb_user5 │
│ tb_user6 │
│ tb_user7 │
│ tb_user9 │
└────────────┘