一、ClickHouse简介
1、基础简介
Yandex开源的数据分析的数据库,名字叫做ClickHouse,适合流式或批次入库的时序数据。ClickHouse不应该被用作通用数据库,而是作为超高性能的海量数据快速查询的分布式实时处理平台,在数据汇总查询方面(如GROUP BY),ClickHouse的查询速度非常快。
下载仓库:https://repo.yandex.ru/clickhouse
中文文档:https://clickhouse.yandex/docs/zh/
2、数据库特点
列式数据库
列式数据库是以列相关存储架构进行数据存储的数据库,主要适合于批量数据处理和即时查询。
数据压缩
在一些列式数据库管理系统中不是用数据压缩。但是, 数据压缩在实现优异的存储系统中确实起着关键的作用。
数据的磁盘存储
许多的列式数据库只能在内存中工作,这种方式会造成比实际更多的设备预算。ClickHouse被设计用于工作在传统磁盘上的系统,它提供每GB更低的存储成本。
多核心并行处理
大型查询可以以很自然的方式在ClickHouse中进行并行化处理,以此来使用当前服务器上可用的所有资源。
多服务器分布式处理
在ClickHouse中,数据可以保存在不同的shard上,每一个shard都由一组用于容错的replica组成,查询可以并行的在所有shard上进行处理。
支持SQL和索引
ClickHouse支持基于SQL的查询语言,该语言大部分情况下是与SQL标准兼容的。支持的查询包括GROUPBY,ORDERBY,IN,JOIN以及非相关子查询。不支持窗口函数和相关子查询。按照主键对数据进行排序,这将帮助ClickHouse以几十毫秒的低延迟对数据进行特定值查找或范围查找。
向量引擎
为了高效的使用CPU,数据不仅仅按列存储,同时还按向量(列的一部分)进行处理。
实时的数据更新
ClickHouse支持在表中定义主键。为了使查询能够快速在主键中进行范围查找,数据总是以增量的方式有序的存储在MergeTree中。因此,数据可以持续不断高效的写入到表中,并且写入的过程中不会存在任何加锁的行为。
二、ClickHouse安装(离线安装)
1、下载rpm包
https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/
下载对应的版本信息,下载记得修改对应的版本号
clickhouse-client-$ {版本号}.noarch.rpm
clickhouse-common-static-$ {版本号}.x86_64.rpm
clickhouse-server-$ {版本号}.noarch.rpm
2、上传下载的包到服务
3、安装服务
进入rpm包的目录,运行 rpm -ivh * 即可完成安装
4、修改相关目录
/opt/clickhouse_data/log/clickhouse-server/clickhouse-server.log
/opt/clickhouse_data/log/clickhouse-server/clickhouse-server.err.log
<tmp_path>/opt/clickhouse_data/clickhouse/tmp/</tmp_path>
<user_files_path>/opt/clickhouse_data/clickhouse/user_files/</user_files_path>
<listen_host>::</listen_host>
注:
1.如果listen_host不该会报如下错误
Code: 210. DB::NetException: Connection refused (localhost:9000, ::1)
2.对应的目录记得修改相关用户组和权限,默认修改成clickhouse用户组
5、设置密码
如果你需要设置登录密码方式,需要修改配置文件users.xml的内容
找到 users --> default --> 标签下的,请查看对应的说明进行设置,支持多种密码设置方式,为了不让人在服务器上看到明文密码,可以设置密文的密码
6、服务管理
#Cent OS7 系统上测试,需要使用以下命令执行
systemctl start clickhouse-server
systemctl stop clickhouse-server
systemctl status clickhouse-server
7、新增新用户
如果需要新增用户,需要修改配置文件users.xml的内容,在xml中的users下面添加,添加方式如下:
三、创建数据库和表
在ClickHouse中,您可以通过直接在交互式数据库提示符中执行SQL语句来创建和删除数据库。 语句由遵循特定语法的命令组成,这些命令告诉数据库服务器执行所请求的操作以及所需的任何数据。 您可以使用CREATE DATABASE table_name语法创建数据库。 要创建数据库,请首先运行以下命令启动客户端会话:
clickhouse-client --multiline
此命令将使您进入客户端提示符,您可以在其中运行ClickHouse SQL语句以执行以下操作:
创建,更新和删除数据库,表,索引,分区和视图。
执行查询以检索可选地使用各种条件进行过滤和分组的数据。
–multiline标志告诉CLI允许输入跨越多行的查询。
在此步骤中,当ClickHouse客户端准备好插入数据时,您将创建一个数据库和表。 出于本教程的目的,您将创建一个名为test的数据库,在其中您将创建一个名为visits的表来跟踪网站访问持续时间。
现在您已进入ClickHouse命令提示符,通过执行以下命令创建test数据库:
CREATE DATABASE test;
您将看到以下输出,表明您已创建数据库:
localhost : ) CREATE DATABASE test;
CREATE DATABASE test
Ok.
0 rows in set. Elapsed: 0.005 sec.
localhost : )
ClickHouse表类似于其他关系数据库中的表; 它以结构化格式保存相关数据的集合。 您可以指定列及其类型,添加数据行,以及对表执行不同类型的查询。
在ClickHouse中创建表的语法遵循以下示例结构:
CREATE TABLE table_name
(
column_name1 column_type [options],
column_name2 column_type [options],
…
) ENGINE = engine
table_name和column_name值可以是任何有效的ASCII标识符。 ClickHouse支持多种列类型; 一些最受欢迎的是:
UInt64:用于存储0到18446744073709551615范围内的整数值。
Float64 :用于存储浮点数,如2039.23,10.5等。
String :用于存储可变长度字符。 它不需要max length属性,因为它可以存储任意长度。
Date :用于存储遵循YYYY-MM-DD格式的日期。
DateTime :用于存储日期和时间,并遵循YYYY-MM-DD HH:MM:SS格式。
在列定义之后,指定用于表的引擎。 在ClickHouse中, Engines确定底层数据的物理结构,表的查询功能,并发访问模式以及对索引的支持。 不同的发动机类型适合不同的应用要求。 MergeTree是最常用和最广泛应用的引擎类型。
切换数据库
use test;
创建数据库
CREATE TABLE my_table (
id UInt64,
duration Float64,
url String,
created DateTime
) ENGINE = MergeTree()
PRIMARY KEY id
ORDER BY id;
查看已经创建好的表
localhost 😃 show tables;
SHOW TABLES
┌─name─────┐
│ my_table │
└──────────┘
1 rows in set. Elapsed: 0.005 sec.
四、插入,更新和删除数据和列
在此步骤中,您将使用您的visits表来插入,更新和删除数据。 以下命令是将行插入ClickHouse表的语法示例:
INSERT INTO table_name VALUES (column_1_value, column_2_value, …);
现在,通过运行以下每个语句,将几行示例网站访问数据插入到您的my_table表中:
INSERT INTO my_table VALUES (1, 10.5, ‘http://example.com’, ‘2019-01-01 00:01:01’);
INSERT INTO my_table VALUES (2, 40.2, ‘http://example1.com’, ‘2019-01-03 10:01:01’);
INSERT INTO my_table VALUES (3, 13, ‘http://example2.com’, ‘2019-01-03 12:01:01’);
INSERT INTO my_table VALUES (4, 2, ‘http://example3.com’, ‘2019-01-04 02:01:01’);
现在,您将向visits表添加其他列。 在现有表中添加或删除列时,ClickHouse支持ALTER语法。
例如,向表中添加列的基本语法如下:
ALTER TABLE table_name ADD COLUMN column_name column_type;
通过运行以下语句,添加一个名为location的列,该列将存储访问网站的位置:
ALTER TABLE my_table ADD COLUMN location String;
从版本19.4.3开始,由于实现限制,ClickHouse不支持更新和删除单个数据行。 但是,ClickHouse支持批量更新和删除,并且这些操作具有不同的SQL语法,以突出显示其非标准用法。
以下语法是批量更新行的示例:
ALTER TABLE table_name UPDATE column_1 = value_1, column_2 = value_2 … WHERE filter_conditions;
您将运行以下语句来更新duration小于15的所有行的url列。将其输入到数据库提示符中以执行:
ALTER TABLE my_table UPDATE url = ‘http://example2.com’ WHERE duration < 15;
批量删除行的示例语法类似于更新行,并具有以下结构:
ALTER TABLE table_name DELETE WHERE filter_conditions;
要测试删除数据,请运行以下语句以删除duration小于5的所有行:
ALTER TABLE my_table DELETE WHERE duration < 5;
要从表中删除列,语法将遵循以下示例结构:
ALTER TABLE table_name DROP COLUMN column_name;
通过运行以下命令删除先前添加的location列:
ALTER TABLE my_table DROP COLUMN location;
五、查询数据
ClickHouse的查询语言是SQL的自定义方言,具有适用于分析工作负载的扩展和功能。 在此步骤中,您将运行选择和聚合查询以从检索表中检索数据和结果。
通过选择查询,您可以检索按指定条件筛选的数据行和列,以及要返回的行数等选项。 您可以使用SELECT语法SELECT数据的行和列。 SELECT查询的基本语法是:
SELECT func_1(column_1), func_2(column_2) FROM table_name WHERE filter_conditions row_options;
执行以下语句以检索url为http://example.com行的url和duration值:
SELECT url, duration FROM my_table WHERE url = ‘http://example2.com’ LIMIT 2;
聚合查询是对一组值进行操作并返回单个输出值的查询。 在分析数据库中,这些查询经常运行,并且由数据库进行了很好的优化。 ClickHouse支持的一些聚合函数是:
count :返回与指定条件匹配的行数。
sum :返回所选列值的总和。
avg :返回所选列值的平均值。
一些ClickHouse特定的聚合函数包括:
uniq :返回匹配的不同行的近似数量。
topK :使用近似算法返回特定列的最常值的数组。
要演示聚合查询的执行,您可以通过运行sum查询来计算总访问持续时间:
SELECT SUM(duration) FROM my_table ;
现在,通过执行以下方式计算前两个URL:
SELECT topK(2)(url) FROM my_table ;
六、删除表和数据库
删除库和表和一般DB一样
七、数据库卸载
如果安装版本不喜欢需要卸载重装
#卸载及删除安装文件(需root权限)
yum list installed | grep clickhouse
yum remove -y clickhouse-common-static
yum remove -y clickhouse-server-common
rm -rf /var/lib/clickhouse
rm -rf /etc/clickhouse-*
rm -rf /var/log/clickhouse-server