clickhouse

作者: 何亮hook_8285 | 来源:发表于2022-07-21 11:46 被阅读0次

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

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

clickhouse

clickhouse相关内容

单机版安装

端口

安装后的相关目录

用户名和密码

登录WEB页面

集群

描述

集群方案一

集群方案二

集群方案三

集群方案四

cluster_3S

cluster_1S_2R

配置相关用例

keeper_server

数据导入导出

集群参考

常用SQL语句

开窗函数

引擎

MergeTree

SummingMergeTree

ReplacingMergeTree

AggregatingMergeTree

POSTGRES协议

解决分布式集群INSERT和SELECT分发

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

clickhouse

ClickHouse