ClickHouse操作指南

72 阅读 0 评论 48 点赞

我是靠谱客的博主激动小懒虫，最近开发中收集的这篇文章主要介绍ClickHouse操作指南，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1 ClickHouse简介

clickhouse是俄罗斯Yandex于2016年开源的列式存储数据库，使用C++语言编写，主要用于在线分析处理查询（OLAP），能够使用SQL查询实时生成分析数据报告。

1.1 ClickHouse特点

1.1.1 列式存储

以下面的表为例：

Id	Name	Age
1	张三	18
2	李四	22
3	王五	34

（1）采用行式存储，数据在磁盘上的组织结构为：

优点是：向查询某个人的所有属性的时候，可以通过一次磁盘查找加顺序读取就可以。

但是要查询所有人的年龄时，就需要不停的查找，或者需要全表扫描才行。

（2）采用列式存储，数据在磁盘上的组织结构为：

优点是：查询所有人的年龄就很方便了。

对于列的聚合，计数，求和等统计操作优于行式存储
由于某一列的数据类型都是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重。
由于数据压缩比更好，一方面节省了磁盘空间，另一方面对于cache也有了更大的发挥空间。

HBase是面向列族式的数据库。

1.1.2 DBMS的功能

几乎覆盖了标准SQL的大部分语法，包括DDL和DML，以及配套的各种函数，用户管理及权限管理，数据的备份于恢复。

1.1.3 多样化引擎

ClickHouse和MySQL类似，把表级的存储引擎插件化，根据表的不同需求可以设定不同的存储引擎。目前包括MergeTree合并树、TinyLog日志、接口和其他四大类20多种引擎。

1.1.4 高吞吐写入能力

ClickHouse采用类似于LSM Tree的结构（预写日志），数据写入后在后台Compaction。通过类LSMTree的结构，顺序写入（append写），写入后数据不可修改，在后台compaction时也是多个段merge sort后顺序写回磁盘。

类似于HBase，HBase的WAL预写日志，就是顺序写入，同时有刷写和合并功能，Flush + Compaction

1.1.5 数据分区与线程级并行

ClickHouse将数据划分为多个partition，每个partition再进一步划分为多个index granularity，然后通过多个CPU核心分别处理其中的一部分来实现并行数据处理。在这种设计下，单条Query就能利用整机所有CPU。极致的并行处理能力，极大的降低了查询延时。

1.1.6 总结

ClickHouse像很多OLAP数据库一样，单表查询速度由于关联查询，而且ClickHouse的两者差距更为明显。

需要在ClickHouse查询之前，将数据就准备成不需要关联的宽表。

2 ClickHouse安装部署

2.1 准备工作

2.1.1 确定防火墙处于关闭状态

sudo systemctl status firewalld
# 关闭防火墙
sudo systemctl stop firewalld
sudo systemctl disable firewalld

2.1.2 CentOS取消文件数限制

（1）在hadoop102，hadoop103，hadoop104的/etc/security/limits.conf文件的末尾加入以下内容

[atguigu@hadoop102 ~]$ sudo vim /etc/security/limits.conf

* soft nofile 65536 
* hard nofile 65536 
* soft nproc 131072 
* hard nproc 131072

（2）在hadoop102，hadoop103，hadoop104的/etc/security/limits.d/20-nproc.conf文件的末尾加入以下内容

[atguigu@hadoop102 ~]$ sudo vim /etc/security/limits.d/20-nproc.conf

* soft nofile 65536 
* hard nofile 65536 
* soft nproc 131072 
* hard nproc 131072

（3）可以在hadoop102上修改，分发修改后的文件

[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

2.1.3 安装依赖

（1）在hadoop102,hadoop103,hadoop104上安装：

[atguigu@hadoop102 ~]$ sudo yum install -y libtool
[atguigu@hadoop103 ~]$ sudo yum install -y libtool
[atguigu@hadoop104 ~]$ sudo yum install -y libtool

（2）在hadoop102,hadoop103,hadoop104上安装

[atguigu@hadoop102 ~]$ sudo yum install -y *unixODBC*	
[atguigu@hadoop103 ~]$ sudo yum install -y *unixODBC*	
[atguigu@hadoop104 ~]$ sudo yum install -y *unixODBC*

2.1.4 CentOS取消SELINUX安全检查

（1）修改/etc/selinux/config中的SELINUX=disabled

[atguigu@hadoop102 ~]$ sudo vim /etc/selinux/config 

SELINUX=disabled

（2）在三台节点上都修改，分发hadoop102修改后的文件

[atguigu@hadoop102 ~]$ sudo /home/atguigu/bin/xsync /etc/selinux/config

（3）重启三台服务器

2.2 单机安装ClickHouse

官网：https://clickhouse.yandex/

下载地址：http://repo.red-soft.biz/repos/clickhouse/stable/el6/

2.2.1 在hadoop102的/opt/software下创建clickhouse目录

[atguigu@hadoop102 software]$ mkdir clickhouse

2.2.2 将clickhouse的4个rpm包上传到hadoop102的/opt/software/clickhouse目录下

2.2.3 将安装文件同步到hadoop103，hadoop104

[atguigu@hadoop102 software]$ xsync clickhouse/

2.2.4 分别在三台服务器上安装这4个rpm包

[atguigu@hadoop102 clickhouse]$ sudo rpm -ivh *.rpm
[atguigu@hadoop103 clickhouse]$ sudo rpm -ivh *.rpm
[atguigu@hadoop104 clickhouse]$ sudo rpm -ivh *.rpm

# 查看rpm包安装情况
[atguigu@hadoop202 clickhouse]$ sudo rpm -qa | grep clickhouse

2.2.5 修改配置文件

[atguigu@hadoop102 clickhouse]$ sudo vim /etc/clickhouse-server/config.xml

把 <listen_host>::</listen_host> 的注释打开，这样的话才能让ClickHouse被除本机以外的服务器访问

分发修改后的配置文件到hadoop103，hadoop104

sudo /home/atguigu/bin/xsync /etc/clickhouse-server/config.xml

这个配置文件种有几个属性比较重要！

数据文件路径： /var/lib/clickhouse/

日志文件路径：/var/log/clickhouse-server/clickhouse-server.log

2.3 启动ClickHouse

[atguigu@hadoop102 clickhouse]$ sudo systemctl start clickhouse-server

2.4 三台机器上关闭开机自启

[atguigu@hadoop102 clickhouse]$sudo systemctl disable clickhouse-server

2.5 使用client连接server

-m 表示可以在命令行窗口输入多行命令。

[atguigu@hadoop102 clickhouse]$ clickhouse-client -m

3 ClickHouse数据类型

3.1 整型

固定长度的整型分为：

有符号整型, 整型范围（-2^n-1~2^n-1-1）
无符号整型, 整型范围（0~2ⁿ-1）

使用场景：个数、数量、也可以存储id

ClickHouse 整型:
    	-- 有符号整型
    		Int8		表示范围[-128~127]
            Int16		表示范围[-2^16-1 ~ 2^16-1 -1]
            Int32		
            Int64		
    	-- 无符号整型
            UInt8		表示范围[0~255]也就是[2^8 -1]
            UInt16
            UInt32
            UInt64
            
            
Java 中的基本数据类型:
		-- 整型:
			- byte			1字节 = 8bit (1bit=2个01值)		表示范围[-2^8-1 ~ 2^8-1 - 1] 
            - short			2字节						表示范围[-2^16-1 ~ 2^16-1 -1]
            - int			4字节						表示范围[-2^32-1 ~ 2^32-1 -1]
            - long			8字节
		-- 浮点型:
			- float			4字节
            - double		8字节
		-- 布尔型:
			- boolean	
		-- 字符:
			- char			2字节

3.2 浮点型

浮点型具有误差。使用场景：一般数据比较小，不涉及大量的统计计算，精度要求不高的时候。比如保存商品的重量。

ClickHouse中的浮点型:
	-- Float32 		字节数:4(32bit)		表示范围和java float表示范围一样
    -- Float64		字节数:8(64bit)		表示范围和java double表示范围一样
        
        
对应Java中的浮点型:
	-- float		字节数:4		表示范围[-2^32-1 ~ 2^32-1 -1]	
    -- double		字节数:8		表示范围[-2^64-1 ~ 2^64-1 -1]

3.3 布尔型

ClickHouse中的布尔型:
	--正确为: 1
    --错误为: 0

对应Java中的boolean类型:
	-- true
    -- fault

3.4 Decimal型

Decimal型，表示有符号的浮点数，可以在加、减和乘法运算过程中保持精度。对于除法，最低有效数字会被丢弃（不舍入）。

使用场景为：不能精度丢失的金额字段，汇率，利率等保证小数点精度。

ClickHouse中的Decimal型: 
	-- Decimal32(s)，相当于Decimal(9-s,s)，有效位数为1-9
    -- Decimal64(s)，相当于Decimal(18-s,s)，有效位数为1-18
    -- Decimal128(s)，相当于Decimal(38-s,s)，有效位数为1-38
	s标识小数位

3.5 字符串

使用场景：名称、文字描述、字符型编码。

固定长度的可以保存一些定长的内容，比如一些编码，性别等但是考虑到一定的变化风险，带来收益不够明显，所以定长字符串使用意义有限

ClickHouse中的字符串类型:
	-- String
        字符串可以任意长度的。它可以包含任意的字节集，包含空字节。
        
    -- FixedString(N)		一般不用
        固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字符串末尾添加空字节来达到 N 字节长度。 当服务端读取长度大于 N 的字符串时候，将返回错误消息。

3.6 枚举类型

Enum枚举类型包括：Enum保存‘String’ = Integer的对应关系

Enum8类型
Enum16类型

ClickHouse中的枚举类型:	
	-- Enum8	用'String'=Int8对描述
    -- Enum16	用'String'=Int16对描述

用法演示:

创建一个带有枚举Enum8(‘hello’ = 1, ‘world’ = 2)类型的列

create table t_enum
(
	x Enum8('hello'=1, 'world'=2)
)
ENGINE = TinyLog;

（1）这个x只能存储枚举类型定义的值：’hello‘或‘world’

insert into t_enum values('hello'),('world'),('hello');

（2）如果尝试保存任何其他类型值，ClickHouse抛出异常

insert into t_enum values('aaa');

（3）如果需要看到对应行的数值，则必须将Enum值转换为整数类型

select cast(x, 'Int8') from t_enum;

3.7 时间类型

ClickHouse中的时间类型:
	-- Date，		接收 年-月-日 的字符串比如：'2021-02-23'
    -- Datetime，	接收 年-月-日 时:分:秒 的字符串比如：'2021-02-23 16:32:10'
    -- Datetime64，	接收 年-月-日 时:分:秒.亚秒 的字符串比如：'2021-02-23 16:32:10.66'
        
日期类型，用两个字节存储，表示 1970-01-01(无符号) 到当前的日期值

3.8 数组

ClickHouse中的数组：
    -- Array(T)：由T类型元素组成的数组。
    T可以是任意类型，包含数组类型。但不推荐使用多维数组，ClickHouse对多维数组的支持有限。例如不能在MergeTree表中存储多维数组。

创建数组方式1：使用array函数

select array(1,2) as x, toTypeName(x);

创建数组方式2：使用方括号

select [1,2] as x, toTypeName(x);

4 表的引擎 ★

4.1 表引擎的使用

表引擎是ClickHouse的一大特色。可以说，表引擎决定了如何存储数据

引擎的名称大小写敏感！！！

4.2 TinyLog

以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量的小表，生产环境上作用有限。可以用于平时练习测试用。

注意：指定表的引擎为TinyLog，方式如下：

create table t_tinylog(id String, name String, age UInt8) engine=TinyLog;

insert into t_tinylog values('1001', 'Tom', 24),('1002', 'Jerry', 25);

4.3 Memory

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

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

4.4 MergeTree ★

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

（1）建表语句

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);

（2）插入数据

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');

4.4.1 partition by 分区（可选项）

（1）作用

分区的作用是：降低扫描的范围，优化查询速度。

（2）如果不填

默认只有一个分区。

（3）分区目录

MergeTree是以列文件 + 索引文件 + 表定义文件组成的，但是如果设定了分区，那么这些文件就会保存到不同的分区目录中。

（4）并行

分区后，面对涉及跨分区的查询统计，ClickHouse会以分区为单位并行处理。

（5）数据写入与分区合并

任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻（大概10-15分分钟后），ClickHouse会自动执行合并操作（等不及也可以手动通过optimize执行），把临时分区的数据，合并到已有分区中去。

optimize table xxxx final;

例如：再次执行上面的插入操作

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');

查看数据并没有纳入任何分区：

手动optimize之后：

optimize table t_order_mt final;

再次查询：

4.4.2 primary key 主键（可选）

ClickHouse中的主键，和其他数据库的主键不太一样，它只提供了数据的一级索引，但是却不是唯一约束，也就是意味着可以存在相同的primary key的数据。

那么ClickHouse中主键的设定（唯一标识）是根据查询语句中的where条件。
select * from t_order_mt where id=102 and sku_id='sku_004';

根据where条件对主键进行某种程度的二分查找，能够定位到对应的index granularity，避免了全表扫描。

index granularity：索引粒度

索引粒度，指的是稀疏索引中两个相邻索引对应数据的间隔，ClickHouse中的MergeTree默认的是8192。官方不建议修改这个值，除非该列存在大量的重复值，比如一个分区中几万行才有一个不同数据。

稀疏索引的好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行进行一点扫描。

4.4.3 order by（必选）

order by 设定了分区内的数据按照哪些字段顺序进行有序保存。

要求：主键必须是order by字段的前缀字段

比如：order by字段是（id，sku_id）那么主键必须是id或者（id，sku_id）

4.4.4 二级索引

目前ClickHouse的官网上的二级索引功能是被标注为实验性的。

（1）使用二级索引前需要增加设置

是否允许使用实验性的二级索引

set allow_experimental_data_skipping_indices=1;

（2）创建测试表

其中GRANULARITY N 是设定二级索引对于一级索引粒度的粒度。

create table t_order_mt2(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time  Datetime,
	INDEX a total_amount TYPE minmax GRANULARITY 5
 ) engine =MergeTree
   partition by toYYYYMMDD(create_time)
   primary key (id)
   order by (id, sku_id);

（3）插入数据

insert into  t_order_mt2 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');

（4）对比结果

那么在使用下面语句进行测试，可以看出二级索引能够为非主键字段的查询发挥作用。

[atguigu@hadoop102 ~]$ clickhouse-client  --send_logs_level=trace <<< 'select * from t_order_mt2  where total_amount > toDecimal32(900., 2)';

4.4.5 数据TTL

TTL即Time To Live，MergeTree提供了可以管理数据或者列的生命周期的功能。

（1）列级别TTL - 指定的列达到到期时间，指定列会归0

创建测试表

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);

插入数据（注意：根据实际事件改变）

指的是系统时间达到create_time这个字段 + 10s之后，total_amount这个字段就会被清空。

需要手动合并。

insert into  t_order_mt3 values
(106,'sku_001',1000.00,'2021-02-23 22:52:30'),
(107,'sku_002',2000.00,'2021-02-23 22:52:30'),
(110,'sku_003',600.00,'2021-02-23 12:00:00');

手动合并，查看效果，到期后，指定的字段数据归0

optimize table t_order_mt3 final;

select * from t_order_mt3;

（2）表级TTL

下面的这条语句是数据会在create_time之后10s丢失

alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

涉及判断的字段必须是Date或者Datetime类型，推荐使用分区的日期字段。

能够使用的时间周期：

- SECOND

- MINUTE

- HOUR

- DAY

- WEEK

- MONTH

- QUARTER

- YEAR

4.5 ReplacingMergeTree ★

ReplacingMergeTree是MergeTree的一个变种，它存储特性完全继承MergeTree，只是多了一个去重的功能。尽管MergeTree可以设置主键，但是primary key其实没有唯一约束的功能。如果你想处理掉重复的数据，可以借助这个ReplacingMergeTree。

（1）去重时机

数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以无法预先做出计划，有一些数据可能仍未被处理。

（2）去重范围

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

所以ReplacingMergeTree能力有限， ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。、

创建表

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() 填入的参数为版本字段，重复数据保留版本字段值最大的。
如果不填版本字段，默认按照插入顺序保留最后一条。'

向表中插入数据

insert into  t_order_rmt 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_rmt;

手动合并

optimize table t_order_rmt final;

再执行一次查询

select * from t_order_rmt;

（3）通过测试得到结论 ★

实际上是根据order by 后面的字段作为唯一键的！
去重不能跨分区
只有optimize合并后才会进行去重
重复的数据，保留的是ReplactingMergeTree()括号中的版本字段值最大的
如果版本字段值相同，那么按照插入的顺序，保留最后插入的数据

4.6 SummingMergeTree ★

对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的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 );

插入数据

insert into  t_order_smt 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_smt;

手动合并分区

optimize table t_order_smt final;

再执行一次查询

select * from t_order_smt;

通过测试可以得出以下结论

以SummingMergeTree()中指定的列作为汇总数据列。
可以填写多个列，但必须是数字列，如果不填，以所有非维度列且数字列的字段为汇总数据列。
以order by 的列为准，作为维度列。
其他的列按插入数据保留第一行。
不在一个分区的数据不会被聚合。

5 SQL操作

基本上来说传统关系型数据库（以MySQL为例）的SQL语句，ClickHouse基本都支持，这里不会从头讲解SQL语法只介绍ClickHouse与标准SQL（MySQL）不一致的地方。

5.1 insert

基本与标准SQL（MySQL）基本一致

- 标准 

insert into [table_name] values(…),(….) 

- 从表到表的插入

insert into [table_name] select a,b,c from [table_name_2]

5.2 update和delete

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

虽然可以实现修改和删除，但是和一般的OLTP数据库不一样，Mutation语句是一种很“重”的操作，而且不支持事务。

“重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数据的操作。

删除操作

alter table t_order_smt delete where sku_id='sku_001';

修改操作

alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id=102;

由于操作比较“重”，所以 Mutation语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间，一般不会开放这样的功能给用户，由管理员完成。

5.3 查询操作

ClickHouse基本上与标准SQL 差别不大
	支持子查询
	支持CTE(Common Table Expression 公用表表达式 with 子句)
	支持各种JOIN， 但是JOIN操作无法使用缓存，所以即使是两次相同的JOIN语句，ClickHouse也会视为两条新SQL
	不支持窗口函数
	不支持自定义函数
	GROUP BY 操作增加了 with rollupwith cubewith total 用来计算小计和总计。

插入数据

 alter table t_order_mt delete where 1=1;
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 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'),
(106,'sku_001',1000.00,'2020-06-04 12:00:00'),
(107,'sku_002',2000.00,'2020-06-04 12:00:00'),
(108,'sku_004',2500.00,'2020-06-04 12:00:00'),
(109,'sku_002',2000.00,'2020-06-04 12:00:00'),
(110,'sku_003',600.00,'2020-06-01 12:00:00');

with rollup：从右至左去掉维度进行小计

select id , sku_id,sum(total_amount) from  t_order_mt group by id,sku_id with rollup;

with cube : 从右至左去掉维度进行小计，再从左至右去掉维度进行小计

select id , sku_id,sum(total_amount) from  t_order_mt group by id,sku_id with cube;

with totals: 只计算合计

select id , sku_id,sum(total_amount) from  t_order_mt group by id,sku_id with totals;

5.4 alter操作

同MySQL的修改字段基本一致
	新增字段
alter table tableName  add column  newcolname  String after col1;
	修改字段类型
alter table tableName  modify column  newcolname  String;
	删除字段
alter table tableName  drop column  newcolname;


-- 删除表中所有数
alter table tableName delete where 1=1;

5.5 导出数据

clickhouse-client --query "select * from t_order_mt where create_time='2020-06-01 12:00:00'" --format CSVWithNames> /opt/module/data/rs1.csv

更多支持格式参照：

https://clickhouse.tech/docs/v19.14/en/interfaces/formats/#csvwithnames

6 副本

副本的主要目的是保障数据的高可靠性，即使一台ClickHouse节点宕机，那么也可以从其他服务器获得相同的数据。

6.1 副本写入流程

6.2 配置步骤

步骤1：启动zookeeper集群

clickhouse的多副本保证高可靠性，依赖zookeeper集群。

步骤2：添加hadoop102、103、104上的/etc/clickhouse-server/config.d的metrika.xml配置文件

指定clickhouse所依赖的zookeeper集群地址

sudo vim /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>

将上面的这个配置文件同步分发到hadoop103，hadoop104

sudo /home/atguigu/bin/xsync /etc/clickhouse-server/config.d/metrika.xml

步骤3：在clickhouse的配置文件中，添加配置，指定上面的配置文件生效

sudo vim /etc/clickhouse-server/config.xml

在/etc/clickhouse-server/config.xml文件中添加

<include_from>/etc/clickhouse-server/config.d/metrika.xml</include_from>

将上面修改的配置文件同步分发到hadoop103，hadoop104

sudo /home/atguigu/bin/xsync /etc/clickhouse-server/config.xml

步骤4：分别在hadoop102、103、104上重启clickhouse服务

sudo systemctl restart clickhouse-server

6.3 副本同步演示步骤 ★

步骤1：在hadoop102、hadoop103上登录clickhouse客户端

clickhouse-client -m

步骤2：在hadoop102、103上分别建表

副本只能同步数据，不能同步表结构，所以我们需要在每台机器上自己手动建表

hadoop102上:

create table t_order_rep1 (
    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_rep1 (
    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);

参数解释
ReplicatedMergeTree 中，
第一个参数是分片的zk_path一般按照： /clickhouse/table/{shard}/{table_name} 的格式写，如果只有一个分片就写01即可。
第二个参数是副本名称，相同的分片副本名称不能相同。

步骤3：在hadoop102上插入数据

insert into t_order_rep1 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');

步骤4：在hadoop103上查询数据，可以查出

7 分片集群

副本虽然能够提高数据的可用性，降低丢失风险，但是每台服务器实际上必须容纳全量数据，对数据的横向扩容没有解决。

要解决数据水平切分的问题，需要引入分片的概念。通过分片把一份完整的数据进行切分，不同的分片分布到不同的节点上，再通过Distributed表引擎把数据拼接起来一同使用。

Distributed表引擎本身不存储数据，有点类似于MyCat之于MySql，成为一种中间件，通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。

注意：ClickHouse的集群是表级别的，实际企业中，大部分做了高可用，但是没有用分片，避免降低查询性能以及操作集群的复杂性。

测试流程：
	- 在hadoop102上创建A表，
	- 根据集群配置在hadoop103有一个副本，那么hadoop103会创建A表；
	- 根据集群配置在hadoop104上有一个分片，那么hadoop104上会创建A表；
	- 其中hadoop103上的表是hadoop102表的一个备份。
	
	- 3个表通过distribute这个代理表。
	- 查询这个代理表，可以把完整的数据都查询出来（单个查询某一张表 其实是查询的一部分）

7.1 集群写入流程（3分片2副本共6节点）

7.2 集群读取流程（3分片2副本共6节点）

7.3 3分片2副本共6节点集群配置（供参考）

配置的位置还是在之前的/etc/clickhouse-server/config.d/metrika.xml，内容如下

<yandex>
	<clickhouse_remote_servers>
		<gmall_cluster> <!-- 集群名称--> 
			<shard>  <!--集群的第一个分片-->
				<internal_replication>true</internal_replication>
				<!--该分片的第一个副本-->
			    <replica>    
			        <host>hadoop201</host>
			        <port>9000</port>
			     </replica>
			     <!--该分片的第二个副本-->
			     <replica> 
			        <host>hadoop202</host>
			        <port>9000</port>
			     </replica>
			</shard>

			  <shard>  <!--集群的第二个分片-->
			     <internal_replication>true</internal_replication>
			     <replica>    <!--该分片的第一个副本-->
			        <host>hadoop203</host>
			     	<port>9000</port>
			     </replica>
			     <replica>    <!--该分片的第二个副本-->
			        <host>hadoop204</host>
			        <port>9000</port>
			     </replica>
			  </shard>

			  <shard>  <!--集群的第三个分片-->
			     <internal_replication>true</internal_replication>
			     <replica>     <!--该分片的第一个副本-->
			        <host>hadoop205</host>
			        <port>9000</port>
			     </replica>
			     <replica>    <!--该分片的第二个副本-->
			        <host>hadoop206</host>
			        <port>9000</port>
			     </replica>
			  </shard>
		</gmall_cluster>
	</clickhouse_remote_servers>
</yandex>

7.4 配置三节点版本集群及副本

7.4.1 集群及副本规划（2分片，只有第一个分片有副本）

hadoop202	hadoop203	hadoop204
01 rep_1_1	01 rep_1_2	02 rep_2_1

7.4.2 配置步骤

（1）在hadoop202的/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>hadoop202</host>
			        <port>9000</port>
			    </replica>
			    <replica>    <!--该分片的第二个副本-->
			        <host>hadoop203</host>
			        <port>9000</port>
			    </replica>
			</shard>

			<shard>  <!--集群的第二个分片-->
			    <internal_replication>true</internal_replication>
			    <replica>    <!--该分片的第一个副本-->
			        <host>hadoop204</host>
			        <port>9000</port>
			    </replica>
			</shard>
		</gmall_cluster>
	</clickhouse_remote_servers>

	<zookeeper-servers>
		<node index="1">
			<host>hadoop202</host>
		 	<port>2181</port>
		</node>
		<node index="2">
		 	<host>hadoop203</host>
		  	<port>2181</port>
		</node>
		<node index="3">
		  	<host>hadoop204</host>
		  	<port>2181</port>
		</node>
	</zookeeper-servers>

	<macros>
		<shard>01</shard>   <!--不同机器放的分片数不一样-->
		<replica>rep_1_1</replica>  <!--不同机器放的副本数不一样-->
	</macros>
</yandex>

（2）将hadoop202的metrika-shard.xml同步到203和204

sudo /home/atguigu/bin/xsync /etc/clickhouse-server/config.d/metrika-shard.xml

（3）修改203和204中metrika-shard.xml宏的配置

203

[atguigu@hadoop203 ~]$ sudo vim /etc/clickhouse-server/config.d/metrika-shard.xml

204

[atguigu@hadoop204 ~]$ sudo vim /etc/clickhouse-server/config.d/metrika-shard.xml

（4）在hadoop202上修改/etc/clickhouse-server/config.xml

（5）同步/etc/clickhouse-server/config.xml到203和204

[atguigu@hadoop202 ~]$ sudo /home/atguigu/bin/xsync /etc/clickhouse-server/config.xml

（6）重启三台服务器上的ClickHouse服务

[atguigu@hadoop202 clickhouse-server]$ sudo systemctl stop clickhouse-server
[atguigu@hadoop202 clickhouse-server]$ sudo systemctl start clickhouse-server
[atguigu@hadoop202 clickhouse-server]$ ps -ef |grep click

（7）在hadoop202上执行建表语句

会自动同步到hadoop203和hadoop204上
集群名字要和配置文件中的一致
分片和副本名称从配置文件的宏定义中获取

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_0105','{replica}')
   partition by toYYYYMMDD(create_time)
   primary key (id)
   order by (id,sku_id);

可以到hadoop203和hadoop204上查看表是否创建成功

（8）在hadoop202上创建Distribute 分布式表

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));

参数含义
	Distributed(集群名称，库名，本地表名，分片键)
分片键必须是整型数字，所以用hiveHash函数转换，也可以rand()

（9）在hadoop202上插入测试数据

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');

（10）通过查询分布式表和本地表观察输出结果

分布式表

SELECT *  FROM st_order_mt_all;

本地表

select * from st_order_mt;

观察数据的分布

7.5 项目为了节省资源，就使用单节点，不用集群

不需要求改文件引用，因为已经使用集群建表了，如果改为引用metrika-shard.xml的话，启动会报错。我们以后用的时候只启动102即可。zz

最后

以上就是激动小懒虫为你收集整理的ClickHouse操作指南的全部内容，希望文章能够帮你解决ClickHouse操作指南所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：bigdata_08_即席查询
浏览次数：72 次浏览
发布日期：2024-01-15 23:20:25
本文链接：https://www.kaopuke.com/article/k-p-k_13_u_23_ogf4_14__7_c5.html

详解clickhouse分区目录的合并过程数据存储底层分布数据分区目录命名规则分区目录的合并过程

JVM垃圾回收面试题如何判断对象是否死亡（两种方法）。简单的介绍一下强引用、软引用、弱引用、虚引用（虚引用与软引用和弱引用的区别、使用软引用能带来的好处）。如何判断一个常量是废弃常量如何判断一个类是无用的类垃圾收集有哪些算法，各自的特点？HotSpot 为什么要分为新生代和老年代？常见的垃圾回收器有哪些？介绍一下 CMS,G1 收集器。Minor Gc 和 Full GC 有什么不同呢？