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

端口

1. 9009 端口：用于在ClickHouse服务器之间交换数据的端口。

2. 9000 端口：通过TCP协议与客户端通信的端口。

3. 8123 端口：用户ODBC和JDBC驱动程序，网页界面(嵌入式UI、Grafana、Redash等)。

4. 9004 端口： 模拟mysql协议。

5. 9005 端口： 模拟postgres协议。

安装后的相关目录

1. /etc/clickhouse-server 目录，存放clickhouse数据库配置文件和用户配置文件，如：users.xml、config.xml等文件。
2. /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