ClickHouse入门
列式存储数据库(DBMS),主要用于在线分析处理查询(OLAP),能够使用SQL查询实时生成分析数据报告。
特点
列式存储
DBMS的功能
多样化引擎
高吞吐写入能力
数据分区与线程级并行
ClickHouse安装(单机版)
准备工作
确定防火墙关闭状态(关闭)
sudo systemctl status firewalld
CentOS取消打开文件数限制
[atguigu@hadoop102 ~]$ sudo vim /etc/security/limits.conf
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
[atguigu@hadoop102 ~]$ sudo vim /etc/security/limits.d/20-nproc.conf
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
[atguigu@hadoop102 ~]$ sudo /home/atguigu/bin/xsync /etc/security/limits.conf
[atguigu@hadoop102 ~]$ sudo /home/atguigu/bin/xsync /etc/security/limits.d/20-nproc.conf
安装依赖
[atguigu@hadoop102 ~]$ sudo yum install -y libtool
[atguigu@hadoop102 ~]$ sudo yum install -y *unixODBC*
CentOS取消SELINUX
[atguigu@hadoop102 ~]$ sudo vim /etc/selinux/config
SELINUX=disabled
[atguigu@hadoop102 ~]$ sudo /home/atguigu/bin/xsync /etc/selinux/config
注意:重启三台服务器
安装
将安装文件同步到hadoop103、hadoop104
[atguigu@hadoop102 software]$ xsync clickhouse/
分别在三台机子上安装这4个rpm文件
[atguigu@hadoop102 clickhouse]$ sudo rpm -ivh *.rpm
查看安装情况
[atguigu@hadoop102 clickhouse]$ sudo rpm -qa | grep clickhouse
修改配置文件
[atguigu@hadoop102 clickhouse]$ sudo vim /etc/clickhouse-server/config.xml
<listen_host>::</listen_host>
把#的注释去掉,这样的话才能让ClickHouse被除本机以外的服务器访问
分发配置文件
sudo /home/atguigu/bin/xsync /etc/clickhouse-server/config.xml
几个重要配置项
(1)数据文件路径:<path>/var/lib/clickhouse/</path>
(2)日志文件路径:<log>/var/log/clickhouse-server/clickhouse-server.log</log>
启动
1)启动ClickServer
[atguigu@hadoop102 clickhouse]$ sudo systemctl start clickhouse-server
2)使用client连接server
[atguigu@hadoop102 clickhouse]$ clickhouse-client -m
-m :可以在命令窗口输入多行命令
3)三台机器上关闭开机自启
[atguigu@hadoop102 clickhouse]$sudo systemctl disable clickhouse-server
数据类型
整型
浮点型
布尔型
Decimal型
字符型
枚举类型
时间类型
数值
表引擎
表引擎是ClickHouse的一大特色。可以说, 表引擎决定了如何存储标的数据。
TinyLog
以列文件的形式保存在磁盘上,不支持索引,没有并发控制。(不用)
Memory
内存引擎,数据以未压缩的原始形式直接保存在内存当中,服务器重启数据就会消失。(内存有限,不用)
MergeTree
ClickHouse中最强大的表引擎当属MergeTree(合并树)引擎及该系列(*MergeTree)中的其他引擎,支持索引和分区,地位可以相当于innodb之于Mysql。而且基于MergeTree,还衍生除了很多小弟,也是非常有特色的引擎。
例:
create table t_order_mt(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time DateTime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');
select * from t_order_mt ;
partition by 分区(可选)
1 作用
分区的目的主要是降低扫描的范围,优化查询速度,如果没有指定partition by ,只会使用一个分区。
2 分区目录
MergeTree 是以列文件+索引文件+表定义文件组成的,但是如果设定了分区那么这些文件就会保存到不同的分区目录中。
3 并行
分区后,面对涉及跨分区的查询统计,ClickHouse会以分区为单位并行处理。
4 数据写入与分区合并
任何一个批次的数据写入都会产生一个临时分区,不会纳入任何一个已有的分区。写入后的某个时刻(大概10-15分钟后),ClickHouse会自动执行合并操作(等不及也可以手动通过optimize执行),把临时分区的数据,合并到已有分区中。
optimize table xxxx final;
primary key主键(可选)
ClickHouse中的主键,和其他数据库不太一样,它只提供了数据的一级索引,但是却不是唯一约束。这就意味着是可以存在相同primary key的数据的。
主键的设定主要依据是查询语句中的where 条件。根据条件通过对主键进行某种形式的二分查找,能够定位到对应的index granularity,避免了全表扫描。
index granularity:直接翻译的话就是索引粒度,指在稀疏索引中两个相邻索引对应数据的间隔。ClickHouse中的MergeTree默认是8192。官方不建议修改这个值,除非该列存在大量重复值,比如在一个分区中几万行才有一个不同数据。
稀疏索引的好处就是可以用很少的索引数据,定位更多的数据,代价就是只能定位到索引粒度的第一行,然后再进行进行一点扫描。
order by(必选)
order by 设定了分区内的数据按照哪些字段顺序进行有序保存。(分区内有序)
order by是MergeTree中唯一一个必填项,甚至比primary key 还重要,因为当用户不设置主键的情况,很多处理会依照order by的字段进行处理(比如后面会讲的去重和汇总)。
要求:主键必须是order by字段的前缀字段。
比如order by 字段是(id,sku_id) 那么主键必须是id 或者(id,sku_id)
二级索引
目前在ClickHouse的官网上二级索引的功能是被标注为实验性的。
数据TTL
列级TTL
建表语句:
create table t_order_mt3(
id UInt32,
sku_id String,
total_amount Decimal(16,2) ttl create_time+interval 10 second ,
create_time DateTime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
表级TTL
建表语句
create table t_order_mt4(
id Uint32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD (create_time)
primary key (id)
order by (id,sku_id)
TTL create_time + INTERVAL 10 SECOND;
修改已有的表
alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;
时间周期
能够使用的时间周期:
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
ReplacingMergeTree
ReplacingMergeTree是MergeTree的一个扩展,它存储特性完全继承MergeTree,只是多了一个去重的功能。尽管MergeTree可以设置主键,但是primary key其实没有唯一约束的功能。如果你想处理掉重复的数据,可以借助这个ReplacingMergeTree。
1 去重时机
数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行,所以你无法预先作出计划。有一些数据可能仍未被处理。
2 去重范围
如果表经过了分区,去重只会在分区内部进行去重,不能执行跨分区的去重。
所以ReplacingMergeTree能力有限, ReplacingMergeTree 适用于在后台清除重复的数据以节省空间,但是它不保证没有重复的数据出现。
3 用法演示
创建语句
create table t_order_rmt(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time DateTime
)engine =ReplacingMergeTree(create_time)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
ReplacingMergeTree() 填入的参数为版本字段,重复数据保留版本字段值最大的。
如果不填版本字段,默认按照插入顺序保留最后一条。
小总结
实际上是使用order by 字段作为唯一键
去重不能跨分区
只有合并分区才会进行去重
认定重复的数据保留,版本字段值最大的
如果版本字段相同则按插入顺序保留最后一笔
SummingMergTree
对于不查询明细,只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话,无论是存储空间的开销,还是查询时临时聚合的开销都比较大。
ClickHouse 为了这种场景,提供了一种能够“预聚合”的引擎SummingMergeTree(分区内聚合)
建表语句
create table t_order_smt(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time DateTime
) engine =SummingMergeTree(total_amount)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
小总结
以SummingMergeTree()中指定的列作为汇总数据列
可以填写多列必须数字列,如果不填,以所有非维度列且为数字列的字段为汇总数据列
以order by 的列为准,作为维度列
其他的列按插入顺序保留第一行
不在一个分区的数据不会被聚合
开发建议
设计聚合表的话,唯一键值、流水号可以去掉,所有字段全部是维度、度量或者时间戳。
在求和的时候还是运行下面的语句,直接用表中的数据还是不行,因为有可能还没有聚合
select sum(total_amount) from province_name=’’ and create_date=’xxx’
SQL操作
sql语句与hive的sql基本上一致,不一致的如下:
Update 和Delete
ClickHouse提供了Delete和Update的能力,这类操作被称为Mutation(突变)查询,它可以看做Alter 的一种。
虽然可以实现修改和删除,但是和一般的OLTP数据库不一样,Mutation语句是一种很“重”的操作,而且不支持事务。
“重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区,重建新分区。所以尽量做批量的变更,不要进行频繁小数据的操作。
1)删除操作
alter table t_order_smt delete where sku_id ='sku_001';
2)修改操作
alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id =102;
由于操作比较“重”,所以 Mutation语句分两步执行,同步执行的部分其实只是进行新增数据新增分区和把旧分区打上逻辑上的失效标记。直到触发分区合并的时候,才会删除旧数据释放磁盘空间,一般不会开放这样的功能给用户,由管理员完成。
GROUP BY 操作增加了with rollup\with cube\with totals 用来计算小计和总计(hive也有,回顾一下)
with rollup:从右至左去掉维度进行小计
hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with rollup;
结果:
┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │ 600.00 │
│ 109 │ sku_002 │ 2000.00 │
│ 107 │ sku_002 │ 2000.00 │
│ 106 │ sku_001 │ 1000.00 │
│ 102 │ sku_002 │ 2000.00 │
│ 104 │ sku_002 │ 2000.00 │
│ 103 │ sku_004 │ 2500.00 │
│ 108 │ sku_004 │ 2500.00 │
│ 105 │ sku_003 │ 600.00 │
│ 101 │ sku_001 │ 1000.00 │
└─────┴─────────┴───────────────────┘
┌──id─┬─sku_id─┬─sum(total_amount)─┐
│ 110 │ │ 600.00 │
│ 106 │ │ 1000.00 │
│ 102 │ │ 2000.00 │
│ 105 │ │ 600.00 │
│ 109 │ │ 2000.00 │
│ 107 │ │ 2000.00 │
│ 104 │ │ 2000.00 │
│ 103 │ │ 2500.00 │
│ 108 │ │ 2500.00 │
│ 101 │ │ 1000.00 │
└─────┴────────┴───────────────────┘
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│ 0 │ │ 16200.00 │
└────┴────────┴───────────────────┘
21 rows in set. Elapsed: 0.010 sec.
with cube : 从右至左去掉维度进行小计,再从左至右去掉维度进行小计
hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with cube;
┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │ 600.00 │
│ 109 │ sku_002 │ 2000.00 │
│ 107 │ sku_002 │ 2000.00 │
│ 106 │ sku_001 │ 1000.00 │
│ 102 │ sku_002 │ 2000.00 │
│ 104 │ sku_002 │ 2000.00 │
│ 103 │ sku_004 │ 2500.00 │
│ 108 │ sku_004 │ 2500.00 │
│ 105 │ sku_003 │ 600.00 │
│ 101 │ sku_001 │ 1000.00 │
└─────┴─────────┴───────────────────┘
┌──id─┬─sku_id─┬─sum(total_amount)─┐
│ 110 │ │ 600.00 │
│ 106 │ │ 1000.00 │
│ 102 │ │ 2000.00 │
│ 105 │ │ 600.00 │
│ 109 │ │ 2000.00 │
│ 107 │ │ 2000.00 │
│ 104 │ │ 2000.00 │
│ 103 │ │ 2500.00 │
│ 108 │ │ 2500.00 │
│ 101 │ │ 1000.00 │
└─────┴────────┴───────────────────┘
┌─id─┬─sku_id──┬─sum(total_amount)─┐
│ 0 │ sku_003 │ 1200.00 │
│ 0 │ sku_004 │ 5000.00 │
│ 0 │ sku_001 │ 2000.00 │
│ 0 │ sku_002 │ 8000.00 │
└────┴─────────┴───────────────────┘
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│ 0 │ │ 16200.00 │
└────┴────────┴───────────────────┘
25 rows in set. Elapsed: 0.009 sec.
with totals: 只计算合计
hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with totals;
┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │ 600.00 │
│ 109 │ sku_002 │ 2000.00 │
│ 107 │ sku_002 │ 2000.00 │
│ 106 │ sku_001 │ 1000.00 │
│ 102 │ sku_002 │ 2000.00 │
│ 104 │ sku_002 │ 2000.00 │
│ 103 │ sku_004 │ 2500.00 │
│ 108 │ sku_004 │ 2500.00 │
│ 105 │ sku_003 │ 600.00 │
│ 101 │ sku_001 │ 1000.00 │
└─────┴─────────┴───────────────────┘
Totals:
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│ 0 │ │ 16200.00 │
└────┴────────┴───────────────────┘
10 rows in set. Elapsed: 0.010 sec.
导出数据
clickhouse-client --query "select * from t_order_mt where create_time='2020-06-01 12:00:00'" --format CSVWithNames> /opt/module/data/rs1.csv
副本(解决单点故障)
副本的目的主要是保障数据的高可用性,即使一台ClickHouse节点宕机,那么也可以从其他服务器获得相同的数据。
副本写入流程
配置步骤
1)启动zookeeper集群
2)在hadoop102的/etc/clickhouse-server/config.d目录下创建一个名为metrika.xml的配置文件,内容如下:
<?xml version="1.0"?>
<yandex>
<zookeeper-servers>
<node index="1">
<host>hadoop102</host>
<port>2181</port>
</node>
<node index="2">
<host>hadoop103</host>
<port>2181</port>
</node>
<node index="3">
<host>hadoop104</host>
<port>2181</port>
</node>
</zookeeper-servers>
</yandex>
3)同步到hadoop103和hadoop104上
sudo /home/atguigu/bin/xsync /etc/clickhouse-server/config.d/metrika.xml
4)在 hadoop102的/etc/clickhouse-server/config.xml中增加
<include_from>/etc/clickhouse-server/config.d/metrika.xml</include_from>
5)同步到hadoop103和hadoop104上
sudo /home/atguigu/bin/xsync /etc/clickhouse-server/config.xml
6)分别在hadoop102和hadoop103上启动ClickHouse服务
[atguigu@hadoop102|3 ~]$ sudo systemctl start clickhouse-server
注意:我们演示副本操作只需要在hadoop102和hadoop103两台服务器即可,上面的操作,我们hadoop104可以不用同步,我们这里为了保证集群中资源的一致性,做了同步。
7)在hadoop102和hadoop103上分别建表
副本只能同步数据,不能同步表结构,所以我们需要在每台机器上自己手动建表
hadoop102
create table t_order_rep (
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time DateTime
) engine =ReplicatedMergeTree('/clickhouse/tables/01/t_order_rep','rep_102')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
hadoop103
create table t_order_rep (
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time DateTime
) engine =ReplicatedMergeTree('/clickhouse/tables/01/t_order_rep','rep_103')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
8)参数解释
ReplicatedMergeTree 中,
第一个参数是分片的zk_path一般按照: /clickhouse/table/{shard}/{table_name} 的格式写,如果只有一个分片就写01即可。
第二个参数是副本名称,相同的分片副本名称不能相同。
9)在hadoop102上执行insert语句
insert into t_order_rep values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 12:00:00'),
(103,'sku_004',2500.00,'2020-06-01 12:00:00'),
(104,'sku_002',2000.00,'2020-06-01 12:00:00'),
(105,'sku_003',600.00,'2020-06-02 12:00:00');
10)在hadoop103上执行select,可以查询出结果,说明副本配置正确
SELECT *
FROM t_order_rep
分片集群(资源不够,扩容)
注意:ClickHouse的集群是表级别的,实际企业中,大部分做了高可用,但是没有用分片,避免降低查询性能以及操作集群的复杂性。
集群写入流程(3分片2副本共6个节点)
集群读取流程(3分片2副本共6个节点)
分片2副本共6个节点集群配置(供参考)
配置的位置还是在之前的/etc/clickhouse-server/config.d/metrika.xml,内容如下
<yandex>
<clickhouse_remote_servers>
<gmall_cluster> <!-- 集群名称-->
<shard> <!--集群的第一个分片-->
<internal_replication>true</internal_replication>
<!--该分片的第一个副本-->
<replica>
<host>hadoop102</host>
<port>9000</port>
</replica>
<!--该分片的第二个副本-->
<replica>
<host>hadoop103</host>
<port>9000</port>
</replica>
</shard>
<shard> <!--集群的第二个分片-->
<internal_replication>true</internal_replication>
<replica> <!--该分片的第一个副本-->
<host>hadoop104</host>
<port>9000</port>
</replica>
<replica> <!--该分片的第二个副本-->
<host>hadoop105</host>
<port>9000</port>
</replica>
</shard>
<shard> <!--集群的第三个分片-->
<internal_replication>true</internal_replication>
<replica> <!--该分片的第一个副本-->
<host>hadoop106</host>
<port>9000</port>
</replica>
<replica> <!--该分片的第二个副本-->
<host>hadoop107</host>
<port>9000</port>
</replica>
</shard>
</gmall_cluster>
</clickhouse_remote_servers>
</yandex>
7.4.1 集群及副本规划(2个分片,只有第一个分片有副本)
配置步骤
1)在hadoop102的/etc/clickhouse-server/config.d目录下创建metrika-shard.xml文件
<?xml version="1.0"?>
<yandex>
<clickhouse_remote_servers>
<gmall_cluster> <!-- 集群名称-->
<shard> <!--集群的第一个分片-->
<internal_replication>true</internal_replication>
<replica> <!--该分片的第一个副本-->
<host>hadoop102</host>
<port>9000</port>
</replica>
<replica> <!--该分片的第二个副本-->
<host>hadoop103</host>
<port>9000</port>
</replica>
</shard>
<shard> <!--集群的第二个分片-->
<internal_replication>true</internal_replication>
<replica> <!--该分片的第一个副本-->
<host>hadoop104</host>
<port>9000</port>
</replica>
</shard>
</gmall_cluster>
</clickhouse_remote_servers>
<zookeeper-servers>
<node index="1">
<host>hadoop102</host>
<port>2181</port>
</node>
<node index="2">
<host>hadoop103</host>
<port>2181</port>
</node>
<node index="3">
<host>hadoop104</host>
<port>2181</port>
</node>
</zookeeper-servers>
<macros>
<shard>01</shard> <!--不同机器放的分片数不一样-->
<replica>rep_1_1</replica> <!--不同机器放的副本数不一样-->
</macros>
</yandex>
将hadoop102的metrika-shard.xml同步到103和104
[atguigu@hadoop102 ~]$ sudo /home/atguigu/bin/xsync /etc/clickhouse-server/config.d/metrika-shard.xml
修改103中 metrika-shard.xml宏的配置
[atguigu@hadoop103 ~]$ sudo vim /etc/clickhouse-server/config.d/metrika-shard.xml
<macros>
<shard>01</shard> <!--不同机器放的分片数不一样-->
<replica>rep_1_2</replica> <!--不同机器放的副本数不一样-->
</macros>
修改104中 metrika-shard.xml宏的配置
[atguigu@hadoop104 ~]$ sudo vim /etc/clickhouse-server/config.d/metrika-shard.xml
<macros>
<shard>02</shard> <!--不同机器放的分片数不一样-->
<replica>rep_2_1</replica> <!--不同机器放的副本数不一样-->
</macros>
在hadoop102上修改/etc/clickhouse-server/config.xml
[atguigu@hadoop102 ~]$ sudo vim /etc/clickhouse-server/config.xml
<include_from>/etc/clickhouse-server/config.d/metrika-shard.xml</include_from>
同步/etc/clickhouse-server/config.xml到103和104
[atguigu@hadoop102 ~]$ sudo /home/atguigu/bin/xsync /etc/clickhouse-server/config.xml
重启三台服务器上的ClickHouse服务
[atguigu@hadoop102 clickhouse-server]$ sudo systemctl stop clickhouse-server
[atguigu@hadoop102 clickhouse-server]$ sudo systemctl start clickhouse-server
测试
在hadoop102中执行:
CREATE TABLE st_order_mt ON CLUSTER gmall_cluster
(
`id` UInt32,
`sku_id` String,
`total_amount` Decimal(16, 2),
`create_time` Datetime
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/st_order_mt', '{replica}')
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id, sku_id)
CREATE TABLE st_order_mt_all ON CLUSTER gmall_cluster
(
`id` UInt32,
`sku_id` String,
`total_amount` Decimal(16, 2),
`create_time` Datetime
)
ENGINE = Distributed(gmall_cluster, default, st_order_mt, hiveHash(sku_id))
insert into st_order_mt_all values
(201,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(202,'sku_002',2000.00,'2020-06-01 12:00:00'),
(203,'sku_004',2500.00,'2020-06-01 12:00:00'),
(204,'sku_002',2000.00,'2020-06-01 12:00:00'),
(205,'sku_003',600.00,'2020-06-02 12:00:00');
在hadoop103和hadoop104中测试
show tables;