ClickHouse

介绍

        Yandex在2016年6月15日开源了一个数据分析的数据库，名字叫做ClickHouse，这对保守俄罗斯人来说是个特大事。更让人惊讶的是，这个列式存储数据库的跑分要超过很多流行的商业MPP数据库软件，例如Vertica。如果你没有听过Vertica，那你一定听过 Michael Stonebraker，2014年图灵奖的获得者，PostgreSQL和Ingres发明者（Sybase和SQL Server都是继承 Ingres而来的）, Paradigm4和SciDB的创办者。Michael Stonebraker于2005年创办Vertica公司，后来该公司被HP收购，HP Vertica成为MPP列式存储商业数据库的高性能代表，Facebook就购买了Vertica数据用于用户行为分析。

        ClickHouse是一种快速的、开源的、用于 OLAP 的列式数据库管理系统，最初是由号称 “ 俄罗斯 Google ” 的 Yandex 公司开发，主要作为世界第二大 Web 流量分析平台 Yandex.Metrica（类 Google Analytic、友盟统计）的核心存储，为 Web 站点、移动 App 实时在线的生成流量统计报表。

 

1、OLTP。这是on-line transaction processing的简写。翻译成联机事务处理。就是在线交易的业务数据。这方面的数据库是关系型数据库。 

2、OLAP。On-Line Analytical Processing 翻译成联机分析处理。通俗理解，就是做数据统计、分析的平台。顺应这个需求产生了数据仓库的概念。

1、来自战斗名族，俄罗斯，Yan dex于2016年开源的列式存储数据库（DBMS），使用C++语言编写，主要用于在线分析处理查询（OLAP）,能够使用SQL查询实时生成分析数据报告。

（"言带X"相当于国内的百度，美国的谷歌，核心业务“搜索”）

行式存储数据库：mysql属于行数据库，一行一行存储数据（适合OLTP业务场景，增删改查），

列式存储数据库：按照列，一列一列存储数据，Hbase也是列式存储，适合OLAP业务场景，适合一次插入，多次查询，更新删除不太擅长,分析型数据库

画图分析。行式和列式区别 ~~~

innodb 有事务

mariadb 没有事务

特点：高吞吐写入能力

数据分区与线程级并行（单条查询就能利用征集所有CPU，比较吃CPU）

缺陷：对于高QPS查询处理能力不强。以后使用中出现问题，大部分都出现在CPU不够，扛不住查询.

不适合做初始存储，适合大量字段宽表场景。

画图对比分析各个数据库单标查询和关联查询数据时间~~ clickHouse 避免做join。不适合做多表查询

1、优点

• 为了高效的使用CPU，数据不仅仅按列存储，同时还按向量进行处理，处理速度很快
• 数据压缩空间大，减少IO；处理单查询高吞吐量每台服务器每秒最多数十亿行
• 索引非B树结构，不需要满足最左原则；只要过滤条件在索引列中包含即可；即使在使用的数据不在索引中，由于各种并行处理机制ClickHouse全表扫描的速度也很快
• 写入速度非常快，50-200M/s，对于大量的数据写入非常适用
• 支持基于SQL的声明式查询语言，该语言大部分情况下是与SQL标准兼容的
• 支持近似计算，提供各种各样在允许牺牲数据精度的情况下对查询进行加速的方法：用于近似计算的各类聚合函数；基于数据的部分样本进行近似查询；不使用全部的聚合条件，通过随机选择有限个数据聚合条件进行聚合
• clickhouse官方提供了许多的函数，包括常规的数学函数，聚合函数，时间函数，逻辑函数，比较函数、机器学习函数等等、还支持一些自定义的逻辑函数、修改源码可以编写自己需要的函数

2、缺点

• 尽量做1000条以上批量的写入，避免逐行insert或小批量的insert，update，delete操作，因为ClickHouse底层会不断的做异步的数据合并，会影响查询性能，这个在做实时数据写入的时候要尽量避开；
• 不支持事务，不支持真正的删除/更新；
• Clickhouse快是因为采用了并行处理机制，即使一个查询，也会用服务器一半的CPU去执行，所以ClickHouse不能支持高并发的使用场景，默认单查询使用CPU核数为服务器核数的一半，安装时会自动识别服务器核数，可以通过配置文件修改该参数
• 虽然能支持 JOIN，但是 JOIN 的性能很不好

 ClickHouse和ES区别

 

Elasticsearch以其优秀的分布式架构与全文搜索引擎等特点在机器数据的存储、分析领域广为使用，但随着数据量的增长，其聚合分析性能已无法满足业务需求。而ClickHouse作为一个高性能的OLAP列式数据库管理系统有望解决这一痛点。

安装准备工作

1、关闭防火墙 (方便使用，关闭防火墙限制，否则单独配置开放端口)

2、CentOS取消文件限制。 ulimit -a ，修改open files（打开文件数）,max user processess(用户最大进程数)，类似ES调整方式。

第一种方式：sudo vim /etc/security/limits.conf 增加4行4列，

1列：星号带表所有用户和用户组

2列：soft 软限制（最小），hard硬限制（最大）（软的值要小于硬的值） -代表软硬一起配置

3列：nofile打开文件数，nproc 打开进程数

4、数值 配置最大数量，nofile 配置65536。一般nproc等于文件数的两倍

第二种方式：sudo vim /etc/security/limits.d/20-nproc.conf 增加4行4列，如果没有该文件，创建一个。

可以再单独创建一个打开文件数的配置，也可以不加，写到nproc配置文件中，如果加的话，就执行以下命令

sudo vim /etc/security/limits.d/20-nofile.conf 增加4行4列，如果没有该文件，创建一个。

重新登录后生效 ulimit -a 查询是否生效，配置时大家细心一些。但是不是像某签那样细。哈哈哈

3、安装依赖

4、CentOS取消SELINUX

修改/etc/selinux/config 中的SELINUX=disabled 禁用安全增强型，修改完以后重启机器生效。

getenforce 查询状态，也可以临时关闭 serenforce 0(关闭)

安装步骤

1、进入官网clickhouse.tech，提供了一个demo

第一个按钮是进入demo界面，里面准备了数据库三张表，第一张1亿行，第二张接近1千万行，第三张一百多万行。可以在里面写语句进行查询。但是没法建表。

数据库：name-datasets ，一亿行数据，做了个简单的时间过滤，查询速度很快。也可切换版本，查看每个版本的查询速度.

第四个按钮进入文档，左侧getting started ,可查看示例数据集，Example Datasets，里面有很多种，数据集都都挺大的。可以根据需求下载测试。

我们直接安装，installation。

乌班图使用apt -get 在线安装

centos使用 sudo yum 在线安装

2、也可以离线安装，下载离线安装包。但是文件包都在一起，看着很不方便。每个版本差别较大。

版本说明区别：

20.5 支持多线程，

20.6.3以后版本支持查看执行计划， 推荐该版本以后

20.8支持实时同步Mysql数据引擎。

使用最新版本2021.7.14发布，我们关注以下四个包

clickhouse-client-21.7.3.14-2.noarch.rpm

clickhouse-common-static-21.7.3.14-2.x86_64.rpm

clickhouse-common-static-dbg-21.7.3.14-2.x86_64.rpm

clickhouse-server-21.7.3.14.2-noarch,rpm

linux创建文件夹上传上去。mkdir clickhouse。同一个目录下面，安装方便

sudo rpm -ivh *.rpm 随后让设置密码，客户端访问安全一些。不设置的话直接回车即可。

rpm -qa|grep clickhouse 即可查询安装状态

3、几个默认目录，

bin/ ->> /usr/bin/

conf/ ->> /etc/clickhouse-server/

lib/ ->> /var/lib/clickhouse/

log/ ->> /var/log/clickhouse/

4、修改配置文件，/etc/clickhouse-server/config,xml

解开注释，使其他机器也可访问，否则，只能通过localhost访问。::

集群安装，跟第一台安装方法一样。

启动服务/操作

sudo clickhouse start(restart/stop) 修改完配置文件需要重启

查询运行状态，sudo clickhouse status或者sudo systemctl status clickhoust-server

端口号默认9000 冲突需要改一下。大概率需要去改

进入bin目录下，使用客户端访问：clickhouse-client -m(-m代表在里面敲得时候可以换行。否则一回车语句就执行了)

其他机器连接客户端：clickhouse-client -h(表示主机名) -p(指定密码) hostname

直接查询clickhouse-client --query ""

进入客户端以后：

查询所有数据库 show databases; 默认包含default和system数据库

进入数据库：use system；

查询所有表：show tables；

查询表：select * from users; 可能会没权限，默认登录的是default用户。切换用户即可

也可以直接使用--query命令查询clickhouse-client --query "show databases"

数据类型

整型（-2(n-1次方)~2(n-1次方)-1）

Int8(-128:127) --> byte 8bit

Int16(-32768:32761) -->> short 16bit

Int32 -->> int 32bit

int64 -->> long 64bit

无符号整型范围（0~2(n次方-1)）

UInt8 - [0:255]

Uint16 - [0:65535]

UInt32 - [0:4294967295]

UInt64 - [0:18446744073709551615]

使用场景：个数，数量，也可以存储整型id

3、浮点型，建议尽可能以整数形式存储。如时间用秒，浮点可能会有四舍五入误差

Float32 ->> float

Float64 ->> double

4、布尔类型

通常使用最小整形UInt8， 取值为0或者1。0表示false，1表示true

5、Decimal型 使用场景：金额，汇率，利率

有符号的浮点型 s表示小数保留几位

Decimal32(s) 相当于Decimal(9-s,s),有效位数为1~9

Decimal64(s) 相当于Decimal(18-s,s),有效位数为1~18

Decimal128(s) 相当于Decimal(38-s,s),有效位数为1~38

5、字符串

String 使用场景：名称，文字描述，字符型编码，使用较多

可以任意长度，可以包含任意的字符集，空字节

FixedString(N) 使用场景,可以保存一些定长的内容，必须一些编码，性别。

固定长度N的字符串，N必须是严格的正自然数，当服务端读取长度小于N的字符串时，通过在字符串末尾添加空字节来达到N的长度，当服务端读取长度大于N的字符串的时候，将返回错误信息

6、枚举类型 保存 创建表的时候需要设置好枚举列内容

create table t_enum(

x Enum8("hello"=1,"world"=2)

)

Enum8 对应 String=Int8

Enum16 对应 String=Int16

使用场景：对一些状态，类型字段算是一种空间优化，也算是一种数据约束。但是事实使用中往往因为一些数据内容的变化增加一定的维护成本,甚至数据丢失的问题，所以谨慎使用。

7、时间类型

Date 接受年-月-日的字符串比如 '2021-08-16'

Datetime 接受年-月-日 时:分:秒的字符串比如：'2021-08-16 20:00:00'

Datetime 64 接受年月日 时:分:秒: 亚秒的字符串 比如：''2021-08-16 20:00:00.66''

日期类型，用两个字节存储，表示从1970-01-01（无符号）到当前的日期值

8、数组 使用中括号也可以 select [1,2] as x

Array(T) 由T类型组成的数组

T可以表示任意类型，包含数组类型。但不推荐使用多维数组。CLickHouse对多维数组支持有限，例如，不能再MergeTree表中存储多维数组

还有一些其他的的很多数据结构。进入clickHouse官网首页点击Documentation文档。进入SQL Reference->data Types 有更多的类型

ClickHouse SQL操作

1、Insert

基本与标准SQL（Mysql）一致,insert into

1、Update和Delete 与标准sql有差

ClickHouse提供了Delete和Update的能力，这类操作被称为Mutation查询，它可以看做Alter的一种。

虽然可以实现修改和删除吗，但是和一般的OLTP数据库不一样，Mutation语句十一中很重的操作，而且不支持事务，重的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数的操作。

删除：alter table t_user delete where id='1001';

修改：alter table t_user update name='小明' where id=102

由于操作比较重，所以mutation语句分两步执行，同步执行的补发其实只是进行新增数据新增分区并吧就风趣打赏逻辑上的失效标记，知道出发分区合并的时候，才会删除旧数据释放磁盘空间，一般不会开放这样的功能给用户，有管理员完成。

#添加列
ALTER TABLE [db].name [ON CLUSTER cluster] ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [AFTER name_after]
#删除列
ALTER TABLE [db].name [ON CLUSTER cluster] DROP COLUMN [IF EXISTS] name
#重置指定分区中列的所有数据
ALTER TABLE [db].name [ON CLUSTER cluster] CLEAR COLUMN [IF EXISTS] name IN PARTITION partition_name
#添加列注解
ALTER TABLE [db].name [ON CLUSTER cluster] COMMENT COLUMN [IF EXISTS] name 'comment'
#修改列类型或者列的默认值
ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY COLUMN [IF EXISTS] name [type] [default_expr]
#添加索引
ALTER TABLE [db].name ADD INDEX name expression TYPE type GRANULARITY value AFTER name [AFTER name2]
#删除索引
ALTER TABLE [db].name DROP INDEX name
#分离分区
ALTER TABLE table_name DETACH PARTITION partition_expr
#删除分区
ALTER TABLE table_name DROP PARTITION partition_expr
#添加被分离的分区
ALTER TABLE table_name ATTACH PARTITION|PART partition_expr
#复制table1中的分区数据到table2
ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1
#重置列值为默认值，默认值为创建表时指定
ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr
#创建指定分区或者所有分区的备份
ALTER TABLE table_name FREEZE [PARTITION partition_expr]
#从其他分片中复制分区数据
ALTER TABLE table_name FETCH PARTITION partition_expr FROM 'path-in-zookeeper'

 

Java连接使用

clickhouse 有两种 JDBC 驱动实现。

官方驱动：

ru.yandex.clickhouse

clickhouse-jdbc

0.1.52

三方提供的驱动：

com.github.housepower

clickhouse-native-jdbc

1.6

两者间的主要区别如下：

驱动类加载路径不同，分别为 ru.yandex.clickhouse.ClickHouseDriver 和 com.github.housepower.jdbc.ClickHouseDriver

默认连接端口不同，分别为 8123 和 9000

连接协议不同，官方驱动使用 HTTP 协议，而三方驱动使用 TCP 协议

mybatis连接clickhouse - 简书

 

表引擎的使用 类似于mysql的innoDB和myisam存储引擎

表引擎是CLickHouse的一大特色，可以说，表引擎决定了如何存储表的数据，包括：

>数据的存储方式和位置，写到哪里以及从哪里读取数据

>支持哪些查询以及如何支持

>并发数据访问

>索引的使用（如果存在）

>是否可以执行多线程请求

>数据复制参数

表引擎的使用方式就是必须显示在创建表时定义该表使用的引擎，以及引擎使用的相关参数

特别注意：引擎的名称大小写铭感

1、TinyLog，Log，StripeLog

以列文件形式保存在磁盘上，不支持索引，没有并发控制，一般保存少量数据的小表

2、memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引，简单查询下非常高的性能表现（超过10G/s），

一般用到的地方不多，除了用来测试，就是在需要非常高性能的，同时数据量又不太大（上限大概一亿行）的场景。

3、mergeTree

ClickHouse中最强大的表引擎当属，合并树引擎及该系列中其他引擎，支持索引和分区，地位相当于innodb中的mysql，而且基于mergeTree，还衍生了很多小弟，也是非常有特色的引擎。

建表语句

mergeTree

create table tb_user(

id UInt32,

name String,

create_time Datetime

)engine=MergeTree

partitiion by toYYYYMMDD(create_time)

parmary key(id) 默认主键未加唯一约束，可重复的

order by(id,name);

4、ReplaceingMergeTree 建表时create table xx()engine=ReplaceingMergeTree(create_time)

相较于mergeTree，多了个一个去重功能

去重时机：只会在合并过程中去重。合并在位置时间的后台运行，所以你无法预先做出计划，有一些数据可能任未被处理

去重范围：如果经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重

填入参数为版本字段，重复数据保留版本字段中最大的，如果不填该版本字段，默认按照插入顺序保留最后一条。

5、SummingMergeTree

对于不查询明细，只关心以纬度进行汇总聚合结果的场景，如果只是用普通的mergeTree的话，无论存储空间的开销，还是查询时临时聚合的开销都比较大。

库引擎

https://clickhouse.tech/docs/en/engines/database-engines/mysql/