clickhouse
2022-07-21 本文已影响0人
何亮hook_8285
clickhouse相关内容
单机版安装
#存储分区最好使用Ext4
#文件描述符设置
vi /etc/security/limits.conf
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
vi /etc/selinux/config
#修改/etc/selinux/config中的SELINUX=disabled后重启
#关闭掉大分页，CentOS7中禁用Transparent HugePages
vi /etc/default/grub
#在GRUB_CMDLINE_LINUX行中加入 transparent_hugepage=never
#:wq保存
#保存配置
grub2-mkconfig -o /boot/grub2/grub.cfg
#重启电脑
reboot
#查看Transparent HugePages等于never
cat /sys/kernel/mm/transparent_hugepage/enabled
#-------------------------------下面是安装------------------------
#切换到clickhouse存放软件目录
cd /home/linux_soft/db/clickhouse
#安装脚本赋予执行权限
chmod +x install.sh
#执行clickhouse安装脚本
./install.sh
#启动脚本
cp /home/linux_soft/db/clickhouse/amd64/clickhouse-server-22.6.3.35/lib/systemd/system/clickhouse-server.service /lib/systemd/system
#创建clickhouse存储数据和日志文件路径
rm -rf /etc/clickhouse-server/config.d
rm -rf /etc/clickhouse-server/config.xml
cp -R /home/linux_soft/db/clickhouse/config/* /etc/clickhouse-server/
mv /var/lib/clickhouse /data/
rm -rf /data/clickhouse/*
mkdir -p /data/clickhouse/logs
chown clickhouse:clickhouse /data/clickhouse/logs
chown clickhouse:clickhouse /data/clickhouse
mkdir -p /data/clickhouse/coordination
chown clickhouse:clickhouse /data/clickhouse/coordination
#生成证书
openssl req -subj "/CN=localhost" -new -newkey rsa:2048 -days 365 -nodes -x509 -keyout /etc/clickhouse-server/server.key -out /etc/clickhouse-server/server.crt
#pem证书，执行此命令需要4分钟才能执行完成
openssl dhparam -out /etc/clickhouse-server/dhparam.pem 4096
#设置自动启动
systemctl enable clickhouse-server
#启动
systemctl start clickhouse-server
端口
-
9009 端口：用于在ClickHouse服务器之间交换数据的端口。
-
9000 端口：通过TCP协议与客户端通信的端口。
-
8123 端口：用户ODBC和JDBC驱动程序，网页界面(嵌入式UI、Grafana、Redash等)。
-
9004 端口： 模拟mysql协议。
-
9005 端口： 模拟postgres协议。
安装后的相关目录
- /etc/clickhouse-server 目录，存放clickhouse数据库配置文件和用户配置文件，如：users.xml、config.xml等文件。
- /usr/bin 目录存放执行二进制文件，如：clickhouse-server、clickhouse-benchmark、clickhouse-client、clickhouse-compressor、clickhouse-copier、clickhouse-format等文件。
用户名和密码
设置超级管理员
#编写default用户
vi /etc/clickhouse-server/users.d/default-password.xml
#设置为管理员
<access_management>1</access_management>
#clickhouse默认用户名称是default，密码是安装clickhouse输入的密码
#--------------------连接数据库，并且初始化用户---------------------
clickhouse-client --host localhost --user default --password xxx
#进入clickhouse-client命令行，开始创建用户
CREATE USER heliang IDENTIFIED WITH sha256_password BY 'xxx' HOST ANY DEFAULT ROLE ALL;
#设置权限
GRANT ALL ON *.* TO heliang WITH GRANT OPTION
登录WEB页面
#网页访问地址
http://192.168.207.120:8123/play
#用户名：default 密码：安装时设置密码
集群
描述
集群模式采用多主（无中心）架构，集群中的每个节点角色对等，客户端访问任意一个节点都能得到相同的效果。
ClickHouse借助分片将数据进行横向切分，而分片依赖集群，每个集群由1到多个分片组成，每个分片对应了CH的1个服务节点；分片数量的上限取决与节点数量（1个分片只能对应1个服务节点）。
ClickHouse并不像其他分布式系统那样，拥有高度自动化的分片功能；CH提供了本地表与分布式表的概念；一张本地表等同于一个数据分片。而分布式表是张逻辑表，本身不存储任何数据，它是本地表的访问代理，其作用类似分库中间件。借助分布式表，能够代理访问多个数据分片，从而实现分布式查询。当然，也可以在应用层实现数据分发。
ClickHouse同时支持数据副本，其副本概念与Elasticsearch类似，但在CH中分片其实是一种逻辑概念，其物理承载是由副本承担的。
ClickHouse的数据副本一般通过ReplicatedMergeTree复制表系列引擎实现，副本之间借助ZooKeeper实现数据的一致性。此外也可通过分布式表负责同时进行分片和副本的数据写入工作。
集群方案一
20220720143354.png
（上图中shard作为主副本）
在每个节点创建一个数据表，作为一个数据分片，使用ReplicatedMergeTree表引擎实现数据副本，而分布表作为数据写入和查询的入口。
这是最常见的集群实现方式。
集群方案二
20220720143534.png
在每个节点创建一个数据表，作为一个数据分片，分布表同时负责分片和副本的数据写入工作。
这种实现方案下，不需要使用复制表，但分布表节点需要同时负责分片和副本的数据写入工作，它很有可能称为写入的单点瓶颈。
集群方案三
20220720143701.png
在每个节点创建一个数据表，作为一个数据分片，同时创建两个分布表，每个分布表只纳管一半的数据。
副本的实现仍需要借助ReplicatedMergeTree类表引擎。
集群方案四
20220720143823.png
在每个节点创建两个数据表，同一数据分片的两个副本位于不同节点上，每个分布式表纳管一般的数据。
这种方案可以在更少的节点上实现数据分布与冗余，但是部署上略显繁琐。
- CH的分片与副本功能完全靠配置文件实现，无法自动管理，所以当集群规模较大时，集群运维成本较高
- 数据副本依赖ZooKeeper实现同步，当数据量较大时，ZooKeeper可能会称为瓶颈
- 如果资源充足，建议使用方案一，主副本和副副本位于不同节点，以更好地实现读写分离与负载均衡
- 如果资源不够充足，可以使用方案四，每个节点承载两个副本，但部署方式上略复杂
cluster_3S
3分片，也就是数据被打散到3个分片中。这种一般是为了充分利用分布式计算的能力，但不考虑高可用性。
配置ClickHouse
参考本文档的keeper_server内容
在三个ClickHouse节点上，找到remote_servers配置项追加cluster_3S_1R配置。注意，三个节点都需要配置。
vi /etc/clickhouse-server/config.d/cluster_3S_1R.xml
<clickhouse>
<remote_servers>
<cluster_3S_1R>
<shard>
<replica>
<host>ch01</host>
<port>9000</port>
<user>default</user>
<password>xxx</password>
</replica>
</shard>
<shard>
<replica>
<host>ch02</host>
<port>9000</port>
<user>default</user>
<password>xxx</password>
</replica>
</shard>
<shard>
<replica>
<host>ch03</host>
<port>9000</port>
<user>default</user>
<password>xxx</password>
</replica>
</shard>
</cluster_3S_1R>
</remote_servers>
</clickhouse>
重启ClickHouse
systemctl restart clickhouse-server
可以通过系统表查询remote_servers的配置是否生效。
SELECT * FROM system.clusters
创建分布式
#创建分布式数据库
CREATE DATABASE db1 ON CLUSTER 'cluster_3S_1R';
#创建分布式表
CREATE TABLE db1.table1 on cluster 'cluster_3S_1R'
(
`id` UInt64,
`column1` String
)
ENGINE = MergeTree
ORDER BY column1
#插入数据，需要在不同服务器插入数据
#ch01服务器插入数据
INSERT INTO db1.table1(id, column1) VALUES(1, 'abc'),(2, 'def')
#ch02服务器插入数据
INSERT INTO db1.table1(id, column1) VALUES(3, 'zhangsan'),(4, 'lisi')
#ch03服务器插入数据
INSERT INTO db1.table1(id, column1) VALUES(5, 'zhaoliu'),(6, 'qisi')
#在ch01查看数据，只能查看 db1.table1 数据，看不到其他数据
SELECT * FROM db1.table1
#创建分片引擎，可以将ch01、ch02、ch03数据合并在一起
CREATE TABLE db1.dist_table (id UInt64,column1 String) ENGINE = Distributed(cluster_3S_1R,db1,table1)
#通过db1.dist_table就可以查看到所有服务器的数据了
SELECT * FROM db1.dist_table
cluster_1S_2R
1分片2个副本
配置ClickHouse
#ch01和ch02服务器都需要配置
vi /etc/clickhouse-server/config.d/cluster_1S_2R.xml
<clickhouse>
<remote_servers>
<cluster_1S_2R>
<shard>
<replica>
<host>ch01</host>
<port>9000</port>
<user>default</user>
<password>xxx</password>
</replica>
<replica>
<host>ch02</host>
<port>9000</port>
<user>default</user>
<password>xxx</password>
</replica>
</shard>
</cluster_1S_2R>
</remote_servers>
</clickhouse>
systemctl restart clickhouse-server
ch01服务器配置宏
vi /etc/clickhouse-server/config.xml
<macros>
<shard>1</shard>
<replica>replica_1</replica>
</macros>
重启ClickHouse
systemctl restart clickhouse-server
可以通过系统表查询remote_servers的配置是否生效。
SELECT * FROM system.clusters
CH01服务器执行命令
#创建数据库
CREATE DATABASE db_uuid ON CLUSTER 'cluster_1S_2R' ENGINE Atomic;
#使用宏在集群上创建一个表
CREATE TABLE db_uuid.uuid_table1 ON CLUSTER 'cluster_1S_2R'
(
id UInt64,
column1 String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/db_uuid/{uuid}', '{replica}' )
ORDER BY (id);
#创建分布式表
create table db_uuid.dist_uuid_table1 on cluster 'cluster_1S_2R'
(
id UInt64,
column1 String
)
ENGINE = Distributed('cluster_1S_2R', 'db_uuid', 'uuid_table1' );
#CH01服务器插入数据
INSERT INTO db_uuid.uuid_table1( id, column1) VALUES( 1, 'abc');
#CH02服务器插入数据
INSERT INTO db_uuid.uuid_table1( id, column1) VALUES( 2, 'def');
#使用分布式表查看记录
SELECT * FROM db_uuid.dist_uuid_table1;
#获取UUID
SELECT * FROM system.tables WHERE database = 'db_uuid' AND name = 'uuid_table1';
#使用上表的 UUID 获取 Zookeeper 中表信息的示例命令
SELECT * FROM system.zookeeper WHERE path = '/clickhouse/tables/1/db_uuid/9e8a3cc2-0dec-4438-81a7-c3e63ce2a1cf/replicas';
#查看数据库引擎
SELECT name, engine FROM system.databases WHERE name = 'db_uuid';
配置相关用例
ClickHouse/tests/integration at master · ClickHouse/ClickHouse · GitHub
keeper_server
部署参考文档：https://clickhouse.com/docs/en/guides/sre/keeper/clickhouse-keeper
初始化配置文件
#创建存储keeper_server数据目录
mkdir -p /data/clickhouse/coordination
chown clickhouse:clickhouse /data/clickhouse/coordination
vi /etc/clickhouse-server/keeper_server_config.xml
keeper_server_config.xml内容
注意:<server_id>1</server_id> 是唯一值，在不同服务器需要修改
用户名和密码：super:admin
<superdigest>super:xQJmxLMiHGwaqBvst5y6rkB6HQs=</superdigest>
<clickhouse>
<listen_host>0.0.0.0</listen_host>
<keeper_server>
<tcp_port>9181</tcp_port>
<server_id>1</server_id>
<log_storage_path>/data/clickhouse/coordination/log</log_storage_path>
<snapshot_storage_path>/data/clickhouse/coordination/snapshots</snapshot_storage_path>
<superdigest>super:xQJmxLMiHGwaqBvst5y6rkB6HQs=</superdigest>
<coordination_settings>
<operation_timeout_ms>10000</operation_timeout_ms>
<session_timeout_ms>10000</session_timeout_ms>
<raft_logs_level>trace</raft_logs_level>
</coordination_settings>
<raft_configuration>
<server>
<id>1</id>
<hostname>ch01</hostname>
<port>9444</port>
<can_become_leader>true</can_become_leader>
<priority>3</priority>
</server>
<server>
<id>2</id>
<hostname>ch02</hostname>
<port>9444</port>
<can_become_leader>true</can_become_leader>
<start_as_follower>true</start_as_follower>
<priority>2</priority>
</server>
<server>
<id>3</id>
<hostname>ch03</hostname>
<port>9444</port>
<can_become_leader>true</can_become_leader>
<start_as_follower>true</start_as_follower>
<priority>1</priority>
</server>
</raft_configuration>
</keeper_server>
</clickhouse>
启动脚本
cp /home/linux_soft/db/clickhouse/clickhouse-keeper.service /usr/lib/systemd/system/
systemctl enable clickhouse-keeper.service
systemctl start clickhouse-keeper
systemctl restart clickhouse-keeper
clickhouse-server指定元数据存储服务
#在config.xml文件中添加元数据,修改每一个服务的别名
vi /etc/clickhouse-server/config.xml
<interserver_http_host>ch01</interserver_http_host>
vi /etc/clickhouse-server/config.d/use_keeper.xml
<clickhouse>
<zookeeper>
<node index="1">
<host>ch01</host>
<port>9181</port>
</node>
<node index="2">
<host>ch02</host>
<port>9181</port>
</node>
<node index="3">
<host>ch03</host>
<port>9181</port>
</node>
<identity>super:xQJmxLMiHGwaqBvst5y6rkB6HQs=</identity>
</zookeeper>
</clickhouse>
systemctl restart clickhouse-server
命令
#输出可用于监视集群运行状况的变量列表
echo mntr | nc localhost 9181
#列出服务器的完整详细信息
echo srvr | nc localhost 9181
#列出服务器和连接客户机的简要详细信息
echo stat | nc localhost 9181
#重置服务器统计数据。该命令将影响' srvr '， ' mntr '和' stat '的结果
echo srst | nc localhost 9181
#conf: 打印服务配置详细信息。
echo conf | nc localhost 9181
#列出所有连接到此服务器的客户端的完整连接/会话详细信息。包括接收/发送的包数、会话id、操作延迟、最后执行的操作等信息。
echo cons | nc localhost 9181
# 重置所有连接的连接/会话统计信息。
echo crst | nc localhost 9181
#envi: 打印服务环境详细信息
echo envi | nc localhost 9181
#dirs: 以字节为单位显示快照和日志文件的总大小
echo dirs | nc localhost 9181
#isro: 测试服务器是否以只读模式运行。如果处于只读模式，服务器将响应“ro”，如果不是只读模式，则响应“rw”。
echo isro | nc localhost 9181
#wchs: 列出服务器的监视的简要信息。
echo wchs | nc localhost 9181
#wchc: 按会话列出服务器的监视的详细信息。这将输出一个会话(连接)列表和相关的监视(路径)。注意，根据监视的数量，此操作可能会很昂贵(即影响服务器性能)，请谨慎使用。
echo wchc | nc localhost 9181
#wchp: 按路径列出有关服务器的监视的详细信息。这将输出一个带有关联会话的路径(znode)列表。注意，根据监视的数量，此操作可能昂贵(即影响服务器性能)，请谨慎使用。
echo wchp | nc localhost 9181
#dump: 列出未完成的会话和临时节点。这只对领导者有效。
echo dump | nc localhost 9181
#检测keeper网络是否正常，如果返回imok正常
echo ruok | nc ch01 9181; echo
查看zookeeper 节点信息
SELECT *
FROM system.zookeeper
WHERE path IN ('/', '/clickhouse')
查看集群信息
SHOW clusters;
数据导入导出
导入CSV文件
clickhouse-client --query='insert into default.trips FORMAT CSV' < data.csv
导出
clickhouse-client -h 127.0.0.1 --database="db" --port 9100 --password xxxx --query="select * from db_name.table_name FORMAT CSV" > table_name.csv
集群参考
Rebalancing Data | ClickHouse Docs
常用SQL语句
查看数据库
SELECT * FROM system.databases;
查看表结构
desc tbl;
删除表结构
drop table tbl;
重命名表
rename table tbl1 to btl2;
添加列
alter table dsp_statis add column cost UInt32 default 0;
--删除数据,推荐不适用
alter table db1.employee delete where id=1;
--更新数据，推荐不适用
alter table db1.employee update name='heliang' where id =4
开窗函数
show clusters;
CREATE TABLE db1.employee on cluster 'cluster_1S_2R' (
id UInt32,
name String,
group_name String,
salary UInt32
) ENGINE = MergeTree()
ORDER BY id;
CREATE TABLE db1.dist_employee (
id UInt32,
name String,
group_name String,
salary UInt32
) ENGINE = Distributed('cluster_1S_2R','db1','employee')
insert into db1.employee(id,name,group_name,salary) values(1,'小明','开发部',8000),
(4,'小张','开发部',7600),
(5,'小白','开发部',7000),
(8,'小王','财务部',5000),
(9, null,'财务部',NULL),
(15,'小刘','财务部',6000),
(16,'小高','行政部',4500),
(18,'小王','行政部',4000),
(23,'小李','行政部',4500),
(29,'小吴','行政部',4700);
select * from db1.dist_employee de ;
----查看每一个部门的总金额
select sum(salary) over(PARTITION by group_name),* from db1.dist_employee;
----查看每一个部门下累计金额
select sum(salary) over(PARTITION by group_name order by id),* from db1.dist_employee;
---查询每一个部门累计金额
select sum(salary) over(order by id),* from db1.dist_employee;
---查询所有部门的总工资
select sum(salary) over(),* from db1.dist_employee;
---查询每一个部门工资最高的人，排序ROW_NUMBER
select * from (select ROW_NUMBER() over(PARTITION by group_name order by salary desc) as num,* from db1.dist_employee) t where t. num=1;
---排名分组
select rank() over( order by salary desc),* from db1.dist_employee;
---连续排名
select DENSE_RANK() over(order by salary desc),* from db1.dist_employee;
---多维度分组统计,(group_name,name)表示按部门名称和姓名分组 ， （group_name）表示按部门分组，()表示所有分组。使用grouping sets 解决 union all 问题
select name,group_name,sum(salary) from db1.dist_employee group by grouping SETS ((group_name,name),(group_name),())
---所有维度统计
select group_name,name,sum(salary) from db1.dist_employee group by group_name,name WITH cube
---ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
select name,group_name,sum(salary) from db1.dist_employee group by group_name,name WITH rollup
引擎
MergeTree
描述
MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。
主要特点:
-
存储的数据按主键排序。
这使得您能够创建一个小型的稀疏索引来加快数据检索。
-
如果指定了 分区键的话，可以使用分区。
在相同数据集和相同结果集的情况下 ClickHouse 中某些带分区的操作会比普通操作更快。查询中指定了分区键时 ClickHouse 会自动截取分区数据。这也有效增加了查询性能。
主要特点:
-
存储的数据按主键排序。
这使得您能够创建一个小型的稀疏索引来加快数据检索。
-
如果指定了分区键 的话，可以使用分区。
在相同数据集和相同结果集的情况下 ClickHouse 中某些带分区的操作会比普通操作更快。查询中指定了分区键时 ClickHouse 会自动截取分区数据。这也有效增加了查询性能。
drop table db1.users2; create table db1.users2 ( id Int32, counts Int32, create_time DateTime ) ENGINE =MergeTree() PARTITION BY formatDateTime(create_time,'YYYYMMDDhh') --表分区字段 toYYYYMM() toYYYYMMDD() toYYYYMMDDhhmmss() formatDateTime() PRIMARY KEY id --主键 order by id --排序键 select * from db1.users2; insert into db1.users2(id,counts,create_time) values(1,199,'2022-07-23 01:22:13') -
SummingMergeTree
描述
对某些列求和并且合并
create table db1.users1
(
id Int32,
counts Int32
) ENGINE =SummingMergeTree(counts)
order by id
--查看数据
select * from db1.users1;
---插入数据
INSERT INTO db1.users1 VALUES (2,20);
--合并数据
OPTIMIZE TABLE db1.users1 DEDUPLICATE;
ReplacingMergeTree
描述
它会删除排序键值相同的重复项
DROP TABLE db1.users;
create table db1.users
(
id Int32,
username String
) ENGINE =ReplacingMergeTree(id)
order by id
select * from db1.users;
INSERT INTO db1.users VALUES (1,'lsii')
OPTIMIZE TABLE db1.users DEDUPLICATE;
AggregatingMergeTree
描述
该表引擎继承自MergeTree，可以使用 AggregatingMergeTree 表来做增量数据统计聚合。如果要按一组规则来合并减少行数，则使用 AggregatingMergeTree 是合适的。AggregatingMergeTree是通过预先定义的聚合函数计算数据并通过二进制的格式存入表内。
与SummingMergeTree的区别在于：SummingMergeTree对非主键列进行sum聚合，而AggregatingMergeTree则可以指定各种聚合函数。
CREATE TABLE db1.emp_aggregatingmergeTree
(
emp_id UInt16 COMMENT '员工id',
name String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age UInt8 COMMENT '员工年龄',
depart String COMMENT '部门',
salary AggregateFunction(sum, Decimal32(2)) COMMENT '工资'
) ENGINE = AggregatingMergeTree() ORDER BY (emp_id, name) PRIMARY KEY emp_id PARTITION BY work_place;
INSERT INTO TABLE db1.emp_aggregatingmergeTree SELECT 1,'tom','上海',25,'信息部',sumState(toDecimal32(10000,2));
INSERT INTO TABLE db1.emp_aggregatingmergeTree SELECT 1,'tom','上海',25,'信息部',sumState(toDecimal32(20000,2));
select * from db1.emp_aggregatingmergeTree
OPTIMIZE TABLE db1.emp_aggregatingmergeTree DEDUPLICATE;
-- 查询数据
SELECT emp_id,name,sumMerge(salary) FROM emp_aggregatingmergeTree GROUP BY emp_id,name;
POSTGRES协议
clickhouse数据库模拟PostgreSQL协议的端口是9005，并且clickhouse只能密码只支持明文。
#--------clickhouse创建postgres用户名和密码，并且分配权限
#创建用户
CREATE USER heliang IDENTIFIED WITH plaintext_password BY 'XXX' HOST ANY DEFAULT ROLE ALL;
#设置权限
GRANT ALL ON *.* TO heliang WITH GRANT OPTION
#postgres只支持psql连接，不能使用jdbc或dbeaver等
psql -p 9005 -h 127.0.0.1 -U alice default
解决分布式集群INSERT和SELECT分发
INSERT和SELECT分发可使用Chproxy，地址Chproxy - Chproxy
