clickhouse的介绍与使用_clickhouse远程登库-优快云博客

本文链接：https://blog.youkuaiyun.com/wadjdkdkdjjd/article/details/142531235

1、什么是Clickhouse

Clickhouse是由俄罗斯yandex公司开源的一个用于联机分析OLAP的列式数据库管理系统。他是使用C++语言编写的，支持SQL实时查询的大型数据管理系统。由于Clickhouse在大型数据集查询处理的高效表现，从2016年开源以来，就吸引了全球的目光，甚至一度登上github的关注度头把交椅。

1.1 OLAP

Clickhouse的设计定位就是用于OLAP离线数据处理。相比于OLTP在线事务处理，Clickhouse更关注于对海量数据的计算分析，关注的是数据吞吐、查询速度、计算性能等指标。而对于数据频繁的修改变更，则不太擅长。所以 Clickhouse通常用来构建后端的实时数仓或者离线数仓。

1.2 列式存储

Clickhouse是一个真正意义上的列式存储数据库。传统数据库存储数据都是按照数据行进行存储。

比如常用的MySQL，他的B+树的叶子节点就会整体保留一行数据。这样的好处是，当想要查询某一条数据时，可以通过一次磁盘查找加顺序读取获得这一条完整的数据。

而Clickhouse存储数据的方式则是按照列来存储，将来自不同列的数据进行单独存储。Clickhouse存储一个表数据时，就是以一列为一个文件进行存储的

列式存储的数据库产品其实也有很多，像Druid数据库，InfiniDB等等很多。只是在国内其实用的还是比较少的。列式存储相比于行式存储在很多数据计算方面会体现出很多优势。例如通常一个计算过程都只会用到少数几个列的数据，这时行式存储就需要读取到相关行的所有列数据再进行过滤。而列式存储就可以直接读取这几个列的相关数据，而不用查找其他不关心的数据。

2、Clickhouse适用场景。

1.绝大多数请求都是读请求。对数据的修改比较少或者几乎没有。

2.数据量很大。这个量即包括数据的行数，也包括数据的列数。也就是通常说的宽表。大部分情况下，对分布式表结构的要求是必须的。

3.数据通常以大的批次进行整体更新，而不是单行更新。这需要有很高的数据吞吐量。

4.对事务的要求不是必须的。对于数据一致性的要求不会太高。通常只要求数据最终一致性。

Clickhouse的数据吞吐量相当大，能够存储海量的数据，并能够以水平扩展的方式进行扩容。对大表的查询计算处理效率也非常高，但是Clickhouse高效性能的背后，肯定伴随计算机资源的大量消耗。 Clickhouse对内存和CPU的占用率都非常高，一个很普通的查询都可能需要消耗非常多的资源。因此，Clickhouse的查询频率也不宜太高。过于频繁的连续或者并发查询甚至很容易导致服务直接崩溃。

综合Clickhouse的特点，他就非常适合用于后端数仓的建设。当然，这本身也是 Clickhouse的设计目标

3.clickhouse的安装与连接

ClickHouse的安装-优快云博客

4.Clickhouse使用

4.1、建库

4.1.1 Atomic 库引擎

这是clickhouse默认的库引擎。默认创建的default库就是使用的这种引擎。

Atomic类型的数据库完全由clickhouse自己管理数据。每个数据库对应/var/lib/data/目录下的一个子目录。数据库中的每个表会分配一个唯一的 UUID，数据存储在目录 /var/lib/clickhouse/store/xxx/xxxyyyyy-yyyy-yyyyyyyy-yyyyyyyyyyyy/，其中xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy是该表的 UUID。

4.1.2 MySQL库引擎

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换。MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此您可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。

通过MySQL引擎可以省掉很多ETL的过程。例如下面的语句就可以在clickhouse 中创建一个mysqldb。

CREATE DATABASE IF NOT EXISTS mysqldb ENGINE = MySQL('bigdata04:3306',
'jrxd', 'root', '123456');

对于mysqldb库的操作，会转义成mysql语法，发送到相对应的MySQL中执行。

-- 使用mysqldb
use mysqldb;
-- 列出库中所有的表 --clickhouse中并没有建表
show tables;
-- 查询mysql中的数据
select * from channel_info;

接下来，可以像操作clickhouse自己的表一样进行insert\delete等操作。但是不能进行 RENAME、CREATE TABLE、ALTER操作。

这种库引擎，clickhouse本身并不存储数据，只是将请求转发到mysql。

4.2、建表

4.2.1 数据类型

1、整型 clickhouse中的整型不像其他数据库中区分int，short，long等等这些类型，而是统一表示固定长度的整数，包括有符号整型和无符号整型。统一定义为Int，后面带上数字表示占用的字节数。

整型范围

Int8-[-128:127] 占用8个字节，对应java中的byte

Int16-[-32768:32767] 占用16个字节，对应java中的short

Int32-[-2147483648:2147483647] 占用32个字节，对应java中的int

Int64-[-9223372036854775808:9223372036854775807] 占用64个字节，对应java中的long

无符号整型范围

UInt8-[0:255]

UInt16-[0:65535]

UInt32-[0:4294967295]

UInt64-[0:18446744073709551615]

2、boolean布尔型

clickhouse中没有定义表示true和false的布尔类型数据，通常都是直接使用 UInt8

3、浮点型

Float32 - float

Float64 - doubl

4、Decimal型

有符号的浮点数，可以在加、减和乘法运算过程中保持精度。对于除法，最低有效数字将被抛弃(不进行四舍五入)。

通常有三种声明： Decimal32(s)、 Decimal64(s)、Decimal128(s)。后面的s表示小数点后的数字位数。前面的32， 64, 128表示浮点精度，决定可以有多少个十进制数字(包含小数位)，也就代表不同的取值范围。

5、字符型

clickhouse的字符型数据使用String进行声明。这个字符串可以是任意长度的。他可以包含任意的字节集，包含空字节。因此，字符串类型可以代替其他数据库中的VARCHAR、BLOB、CLOB等类型。

6、时间类型

时间类型是每个数据库都要处理的类型。clickhouse的时间类型声明相对简单很多。在clickhouse中有三种时间类型

Date 可以接受一个年-月-日格式的字符串。例如 '2021-10-13'。

Datetime 可以接受一个年-月-日时:分:秒格式的字符串。例如'2021-10-13 20:50:10'。

Datatime64 可以接受一个年-月-日时:分:秒.毫秒格式的字符串。

例如 '2021- 10-13 20:50:10.232'。

4.2.2数据查询

查询是clickhouse的重头戏，clickhouse除了支持标准SQL外，还提供了非常丰富的查询功能。

在标准SQL查询这一块：

支持子查询支持各种JOIN查询。但是不建议使用。因为JOIN操作无法使用缓存。并且 clickhouse执行join操作的方式是将后面的表全部加载到内存中执行，优化不是很好。表很大时性能影响非常明显。

支持With关键字创建临时表。

另外，clickhouse还提供了非常丰富的特性函数

表函数表函数是clickhouse非常有特色的一类函数。顾名思义，就是可以像表一样使用的函数。例如numbers函数，可以生成一组连续的整数，在测试时非常有用。

select * from numbers(10); --产生一组0~10的整数
select * from numbers(10,20); --从10开始，产生20个整数
select toDate('2021-01-01') + number as d from numbers(365); -- 产生2021-
01-01 ~ 2021-12-31的日期序列

generateRandom函数则可以产生一组随机值。例如下面的SQL语句会产生包含 a,b,c三列的一个表结果。每一列都会产生一个1到10之间的随机值。

SELECT * FROM generateRandom('a Array(Int8), d Decimal32(4), c
Tuple(DateTime64(3), UUID)', 1, 10, 2) LIMIT 3;