ClickHouse的详细使用

最新推荐文章于 2024-05-30 11:13:03 发布

code@fzk

最新推荐文章于 2024-05-30 11:13:03 发布

阅读量920

点赞数

分类专栏：大数据文章标签：大数据

本文链接：https://blog.csdn.net/qq_44002865/article/details/119448019

版权

大数据专栏收录该内容

25 篇文章 0 订阅

订阅专栏

ClickHouse

1、安装

第一步：修改 /etc/security/limits.conf 文件并同步到其他服务器

# 在后面添加以下内容
* soft nofile 65536 
* hard nofile 65536 
* soft nproc 131072 
* hard nproc 131072

第二步：修改 /etc/security/limits.d/20-nproc.conf 文件并同步到其他服务器

# 在后面添加以下内容
* soft nofile 65536 
* hard nofile 65536 
* soft nproc 131072 
* hard nproc 131072

第三步：在三台机器上安装依赖

yum install -y libtool

yum install -y *unixODBC*

第四步：修改 /etc/selinux/config 文件并同步到其他服务器
```
################### 注意：别改错了
SELINUX=disabled
```
第五步：重启三台服务器

第六步：将以下文件上传到服务器，并同步到其他服务器

clickhouse-client-20.4.5.36-2.noarch.rpm
clickhouse-common-static-20.4.5.36-2.x86_64.rpm
clickhouse-common-static-dbg-20.4.5.36-2.x86_64.rpm
clickhouse-server-20.4.5.36-2.noarch.rpm

第七步：分别在三台服务器上安装
```
rpm -ivh *.rpm
```

第八步：修改 /etc/clickhouse-server/config.xml 文件，并同步到其他服务器

<!-- 把 <listen_host>::</listen_host> 的注释打开，让 ClickHouse 被除本机以外的服务器访问 -->
<listen_host>::</listen_host>


<!--############## 以下是默认的，可以不修改 ################-->
<!-- 数据文件路径 -->
<path>/var/lib/clickhouse/</path>
<!-- 日志文件路径 -->
<log>/var/log/clickhouse-server/clickhouse-server.log</log>

第九步：启动/停止

#===============启动
# 启动 ClickHouse 服务
systemctl start clickhouse-server
# 设置开机自启
systemctl enable clickhouse-server


#===============停止
systemctl stop clickhouse-server
# 关闭开机自启
systemctl disable clickhouse-server

第十步：使用 client 连接 server

# -m :可以在命令窗口输入多行命令
clickhouse-client -m

2、数据类型

整型

有符号整型范围（-2^n-1 —— 2^n-1-1）
- Int8 ： [-128 : 127]
- Int16 ： [-32768 : 32767]
- Int32 ： [-2147483648 : 2147483647]
- Int64 ： [-9223372036854775808 : 9223372036854775807]
无符号整型范围（0 —— 2^n-1）
- UInt8 ： [0 : 255]
- UInt16 ：[0 : 65535]
- UInt32 ：[0 : 4294967295]
- UInt64 ：[0 : 18446744073709551615]

浮点型

Float32 - float
Float64 – double

布尔型

没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1

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

字符串

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

时间类型

三种时间类型
- Date 接受年-月-日的字符串比如 ‘2019-12-16’
- Datetime 接受年-月-日时:分:秒的字符串比如 ‘2019-12-16 20:50:10’
- Datetime64 接受 年 - 月 - 日时 : 分 : 秒 . 亚秒 的字符串比如 ‘ 2019-12-16 20:50:10.66’

数组

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

3、表引擎

TinyLog（不适用）

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

创建

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

Memory（不适用）

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引。简单查询下有非常非常高的性能表现（超过 10G/s）。
一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太大（上限大概 1 亿行）的场景

MergeTree（推荐）

ClickHouse 中最强大的表引擎当属 MergeTree（合并树）引擎及该系列（MergeTree）中的其他引擎，支持索引和分区，地位可以相当于 innodb 之于 Mysql。而且基于 MergeTree，还衍生除了很多小弟，也是非常有特色的引擎
建表语句
```
-- partition by：分区（可选）
-- primary key：主键（可选）
-- order by：排序（必选）
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);
```
- partition by 分区（可选项）
  - 作用：分区的目的主要是降低扫描的范围，优化查询速度
  - 并行：分区后，面对涉及跨分区的查询统计，ClickHouse 会以分区为单位并行处理
  - 任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻（大概 10-15 分钟后），ClickHouse 会自动执行合并操作（等不及也可以手动通过 optimize 执行），把临时分区的数据，合并到已有分区中
```
-- 手动合并
optimize table 表名 final;
```
- primary key 主键(可选)
  - ClickHouse 中的主键，和其他数据库不太一样，它只提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同 primary key 的数据的
  - 主键的设定主要依据是查询语句中的 where 条件
- order by（必选）
  - order by 设定了分区内的数据按照哪些字段顺序进行有序保存
  - order by 是 MergeTree 中唯一一个必填项，甚至比 primary key 还重要，因为当用户不设置主键的情况，很多处理会依照 order by 的字段进行处理（比如后面会讲的去重和汇总）
  - 要求：主键必须是 order by 字段的前缀字段

手动合并

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

二级索引

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

set allow_experimental_data_skipping_indices=1;

建表语句

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

数据 TTL

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

列级别 TTL

-- TTL create_time+interval 10 SECOND：生命周期10秒
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

-- TTL create_time + INTERVAL 10 SECOND：生命周期10秒
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
去重时机：数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理
去重范围：如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重所以 ReplacingMergeTree 能力有限，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现
结论
- 实际上是使用 order by 字段作为唯一键
- 去重不能跨分区
- 只有合并分区才会进行去重
- 认定重复的数据保留，版本字段值最大的
- 如果版本字段相同则按插入顺序保留最后一笔

创建语句

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

SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的 MergeTree 的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大
结论
- 以 SummingMergeTree（）中指定的列作为汇总数据列
- 可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列
- 以 order by 的列为准，作为维度列
- 其他的列按插入顺序保留第一行
- 不在一个分区的数据不会被聚合
开发建议
- 设计聚合表的话，唯一键值、流水号可以去掉，所有字段全部是维度、度量或者时间戳

创建语句

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

4、SQL 操作

-- ========================== DDL 操作
-- 新增字段（在 tableName 表的 col1 字段后面 添加类型一个类型为 String 的 newcolname 字段）
alter table tableName add column newcolname String after col1;

-- 修改字段类型（修改 newcolname 字段类型为 String）
alter table tableName modify column newcolname String;

-- 删除字段（删除 newcolname 字段）
alter table tableName drop column newcolname;




-- ========================== 导出数据
-- 将 select * from t_order_mt where create_time='2020-06-01 12:00:00 查询出来的数据以 CSVWithNames 类型存储到 /opt/module/data/rs1.csv 中
clickhouse-client --query "select * from t_order_mt where create_time='2020-06-01 12:00:00'" --format CSVWithNames> /opt/module/data/rs1.csv




-- ========================== DML 操作
--增（和 MySQL 操作一样）
insert into 表名 values()

--删
alter table 表名 delete where sku_id ='sku_001';

--改
alter table 表名 update total_amount=toDecimal32(2000.00,2) where id =102;

--查询（和 MySQL 操作一样）

5、副本

配置步骤

第一步：启动 zookeeper 集群

第二步：在 /etc/clickhouse-server/config.d 目录下创建 metrika.xml 文件，并同步到其他服务器中

<?xml version="1.0"?>
<yandex>
    <zookeeper-servers>
        <node index="1">
            <host>hadoop151</host>
            <port>2181</port>
        </node>
        <node index="2">
            <host>hadoop152</host>
            <port>2181</port>
        </node>
        <node index="3">
            <host>hadoop153</host>
            <port>2181</port>
        </node>
    </zookeeper-servers>
</yandex>

第三步：修改 /etc/clickhouse-server/config.xml 文件，并同步到其他服务器中

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

第四步：分别在 hadoop151、 hadoop152、hadoop153 上启动 clickhouse-server 服务
第五步：在 hadoop151、 hadoop152、hadoop153 上分别建表
- 副本只能同步数据，不能同步表结构，所以我们需要在每台机器上自己手动建表

6、分片集群

简介

副本虽然能够提高数据的可用性，降低丢失风险，但是每台服务器实际上必须容纳全量数据，对数据的横向扩容没有解决
要解决数据水平切分的问题，需要引入分片的概念。通过分片把一份完整的数据进行切分，不同的分片分布到不同的节点上，再通过 Distributed 表引擎把数据拼接起来一同使用
Distributed 表引擎本身不存储数据，有点类似于 MyCat 之于 MySql，成为一种中间件，通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据
注意：ClickHouse 的集群是表级别的，实际企业中，大部分做了高可用，但是没有用分片，避免降低查询性能以及操作集群的复杂性

3 分片 2 副本共 6 个节点集群配置

集群：hadoop201、hadoop202、hadoop203、hadoop204、hadoop205、hadoop206

第一步：在 /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>

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

集群及副本规划（2 个分片，只有第一个分片有副本）
集群：hadoop151、hadoop152、hadoop153

第一步： /etc/clickhouse-server/config.d 目录下创建 metrika.xml 文件，并同步到其他服务器中

<?xml version="1.0"?>
<yandex>
    <clickhouse_remote_servers>
        <gmall_cluster> <!-- 集群名称--> 
            <shard> <!--集群的第一个分片-->
                <internal_replication>true</internal_replication>
                <replica> <!--该分片的第一个副本-->
                    <host>hadoop151</host>
                    <port>9000</port>
                </replica>
                <replica> <!--该分片的第二个副本-->
                    <host>hadoop152</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard> <!--集群的第二个分片-->
                <internal_replication>true</internal_replication>
                <replica> <!--该分片的第一个副本-->
                    <host>hadoop153</host>
                    <port>9000</port>
                </replica>
            </shard>
        </gmall_cluster>
    </clickhouse_remote_servers>
    <zookeeper-servers>
        <node index="1">
            <host>hadoop151</host>
            <port>2181</port>
        </node>
        <node index="2">
            <host>hadoop152</host>
            <port>2181</port>
        </node>
        <node index="3">
            <host>hadoop153</host>
            <port>2181</port>
        </node>
    </zookeeper-servers>
    <macros>
        <shard>01</shard> <!--不同机器放的分片数不一样-->
        <replica>rep_1_1</replica> <!--不同机器放的副本数不一样-->
    </macros>
</yandex>

第二步：修改 hadoop152 和 hadoop153 中 metrika-shard.xml 宏的配置

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

<!-- hadoop153 -->
<macros>
    <shard>02</shard> <!--不同机器放的分片数不一样-->
    <replica>rep_2_1</replica> <!--不同机器放的副本数不一样-->
</macros>

第三步：修改 /etc/clickhouse-server/config.xml 文件，并同步到其他服务器中

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

第四步：分别在 hadoop151、 hadoop152、hadoop153 上启动 Zookeeper 和 clickhouse-server 服务

第五步：在 hadoop151 上执行建表语句会自动同步到 hadoop152 和 hadoop153 上

-- 集群名字要和配置文件中的一致（gmall_cluster：/etc/clickhouse-server/config.d文件中的集群名称）
-- ReplicatedMergeTree('/clickhouse/tables/{shard}/st_order_mt_0105','{replica}')
	-- {shard}：分片
	-- {replica}：副本
	-- 分片和副本名称从配置文件的宏定义中获取
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);

第六步：在 hadoop151 上创建 Distribute 分布式表

-- Distributed(gmall_cluster,default, st_order_mt,hiveHash(sku_id));
	-- Distributed(集群名称，库名，本地表名，分片键)
	-- 分片键必须是整型数字，所以用 hiveHash 函数转换，也可以 rand()
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));

code@fzk

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
ClickHouse的详细使用

ClickHouse文章目录ClickHouse1、安装2、数据类型整型浮点型布尔型Decimal 型字符串时间类型数组3、表引擎TinyLog（不适用）Memory（不适用）MergeTree（推荐）手动合并二级索引数据 TTLReplacingMergeTreeSummingMergeTree4、SQL 操作5、副本配置步骤6、分片集群简介3 分片 2 副本共 6 个节点集群配置配置三节点版本集群及副本1、安装第一步：修改 /etc/security/limits.conf 文件并同步到其他服
复制链接

扫一扫