网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
15、修改clickhouse用户密码
①方法一:配置 /etc/clickhouse-server/users.xml 文件
vim /etc/clickhouse-server/users.xml
123456
②方法二:配置 /etc/clickhouse-client/config.xml 文件
vim /etc/clickhouse-client/config.xml
username
password
False
五、ClickHouse集群
ClickHouse集群部署:使用教程 | ClickHouse文档
ClickHouse集群配置:https://clickhouse.tech/docs/zh/operations/configuration-files
ClickHouse副本引擎:数据副本 | ClickHouse文档
ClickHouse分布式配置:分布 | ClickHouse文档
该集群配置为分片副本集群, ClickHouse只有 MergeTree 系列里的表可支持副本
副本配置提供高可用,分片配置提供数据的横向扩展和容灾
ClickHouse在单个节点创建表,表只会创建在单个节点上。如果想要使用复制表,
在建表时必须指定带 Replicated 前缀的复制表引擎,然后在每个节点上创建相同表
副本只能同步数据,不能同步表结构,所以我们需要在每台机器上自己手动建表
每台机器都相同的配置文件:/etc/clickhouse-server/config.xml(不引入外部metrika.xml不相同)
每台机器不相同的配置文件:/etc/metrika.xml
1、 ClickHouse目录文件介绍
数据存储目录 | /var/lib/clickhouse |
日志存储目录 | /var/log/clickhouse-server |
默认分片集群配置 | /etc/metrika.xml |
服务器配置文件 | /etc/clickhouse-server/config.xml |
客户端配置文件 | /etc/clickhouse-client/config.xml |
定时任务配置 | /etc/cron.d/clickhouse-server |
系统服务配置文件 | /etc/systemd/system/clickhouse-server.service |
文件句柄数量配置 | /etc/security/limits.d/clickhouse.conf |
主程序可执行文件 | /var/lib/clickhouse |
客户端连接可执行文件 | /usr/bin/clickhouse-client |
服务端可执行文件 | /usr/bin/clickhouse-server |
数据压缩可执行文件 | /usr/bin/clickhouse-compressor |
服务器正常日志文件 | /var/log/clickhouse-server/clickhouse-server.log |
服务端错误日志文件 | /var/log/clickhouse-server/clickhouse-server.err.log |
2、ClickHouse集群规划
zookeeper | clickhouse | 分片 | 副本 | |
hadoop001 | √ | √ | shard01 | replica_01_02 |
hadoop002 | √ | √ | shard02 | replica_02_02 |
hadoop003 | √ | √ | shard03 | replica_03_02 |
hadoop004 | √ | shard01 | replica_01_01 | |
hadoop005 | √ | shard02 | replica_02_01 | |
hadoop006 | √ | shard03 | replica_03_01 |
3、ClickHouse核心配置
cp /etc/clickhouse-server/config.xml /etc/clickhouse-server/config.xml.init
chmod 664 /etc/clickhouse-server/config.xml
chown -R clickhouse:clickhouse /etc/clickhouse-server
vim /etc/clickhouse-server/config.xml
<listen_host>0.0.0.0</listen_host>
<tmp_path>/home/clickhouse/tmp/</tmp_path>
<user_files_path>/home/clickhouse/data/user_files/</user_files_path>
mkdir -p /home/clickhouse/data/
mkdir -p /home/clickhouse/tmp/
mkdir -p /home/clickhouse/data/user_files/
chown -R clickhouse:clickhouse /home/clickhouse/
4、ClickHouse集群配置(rpm版本)
**注意:**集群配置在创建分布式表时可以使用{shard}和{replac}方便创建表,在建表时也可以直接自定义shard和replac变量并且不局限于集群配置的变量,可由开发者灵活定义。集群定义的元数据在zookeeper中保存,如果修改了已定义好的集群表的集群配置可能会导致表变成只读状态,这时需要去zookeeper上查看clickhouse的元数据信息是否和当前表匹配。
在rmp安装的版本中,clickhouse服务端默认配置的 /etc/clickhouse-server/config.xml 中表明
会默认加载 /etc/metrika.xml 文件作为远程服务的替换文件,这里手动配置在其它目录
其中默认的集群服务名称为标签 incl 指定的 clickhouse_remote_servers
<remote_servers incl=“clickhouse_remote_servers” />
在 /etc/clickhouse-server/config.d/ 目录下手动配置分片副本集群文件 metrika.xml
chmod 664 /etc/clickhouse-server/config.d/metrika.xml
chown clickhouse:clickhouse /etc/clickhouse-server/config.d/metrika.xml
vim /etc/clickhouse-server/config.d/metrika.xml
每台集群的配置文件都不一样,区别在于标签的和标签
详情查看上文 ClickHouse集群规划
<clickhouse_remote_servers>
<cluster_3shards_2replicas>
<internal_replication>true</internal_replication>
hadoop004
9000
hadoop001
9000
<internal_replication>true</internal_replication>
hadoop005
9000
hadoop002
9000
<internal_replication>true</internal_replication>
hadoop006
9000
hadoop003
9000
</cluster_3shards_2replicas>
</clickhouse_remote_servers>
hadoop001
2181
hadoop002
2181
hadoop003
2181
shard01
replica_01_02
::/0
<clickhouse_compression>
<min_part_size>10000000000</min_part_size>
<min_part_size_ratio>0.01</min_part_size_ratio>
lz4
</clickhouse_compression>
每台机器上只有 标签不一样,这里配置的3分片2副本的不同节点参数如下
shard01
replica_01_02
shard02
replica_02_02
shard03
replica_03_02
shard01
replica_01_01
shard02
replica_02_01
shard03
replica_03_01
sed -n ‘78, 81p’ /etc/clickhouse-server/config.d/metrika.xml
clickhouse-client -u default --password “” --query “SELECT * FROM system.clusters”
配置之后,使用过一次该集群,clickhouse会在根目录下将本机配置写入macros文件
cat /home/clickhouse/macros
[root@hadoop001 ~]# cat /home/clickhouse/macros
export shard=shard01
export replica=replica_01_02
…
[root@hadoop006 ~]# cat /home/clickhouse/macros
export shard=shard03
export replica=replica_03_01
5、ClickHouse主集群配置(tgz版本)
在 tgz 安装的版本中,clickhouse服务端默认配置的 /etc/clickhouse-server/config.xml 中已经默认配置了三个本地测试的分片副本集群,分别为
本地分片测试集群:test_shard_localhost
本地两个分片测试集群:test_cluster_two_shards_localhost
两分片测试集群:test_cluster_two_shards
两分片内部复制测试集群 :test_cluster_two_shards_internal_replication
本地分片安全集群:test_shard_localhost_secure
测试不推荐的分片集群:test_unavailable_shard
clickhouse-client -u default --password “” --query “SELECT * FROM system.clusters”
①修改 /etc/clickhouse-server/config.xml 文件,把 remote_servers 标签的默认配置注释掉
②在 /etc/clickhouse-server/config.xml 文件中添加以下配置,
<include_from>/etc/metrika.xml</include_from>
<remote_servers>
<cluster_3shards_2replicas>
<internal_replication>true</internal_replication>
hadoop004
9000
hadoop001
9000
<internal_replication>true</internal_replication>
hadoop005
9000
hadoop002
9000
<internal_replication>true</internal_replication>
hadoop006
9000
hadoop003
9000
</cluster_3shards_2replicas>
</remote_servers>
shard01
replica_01_02
hadoop001
2181
hadoop002
2181
hadoop003
2181
③查看标签配置
sed -n ‘751, 755p’ /etc/clickhouse-server/config.xml
sed -n ‘78, 81p’ /etc/clickhouse-server/config.d/metrika.xml
chmod 664 /etc/clickhouse-server/config.xml
chown clickhouse:clickhouse /etc/clickhouse-server/config.xml
ll /etc/clickhouse-server/config.xml
④添加 /etc/metrika.xml 文件,配置信息和4.1中的rpm安装的配置一致
chmod 664 /etc/metrika.xml
chown clickhouse:clickhouse /etc/metrika.xml
vim /etc/metrika.xml
6、ClickHouse目录赋权
这里配置了数据存储目录和临时目录在 /home/clickhouse下,如有其它目录自行修改
chown -R clickhouse:clickhouse /home/clickhouse/
六、ClickHouseSQL
clickhouse-client -m --query “SELECT VERSION()”
clickhouse-client -m --query “SHOW DATABASES”
clickhouse-client -m --query “SELECT * FROM system.tables”
clickhouse-client -m --query “SELECT * FROM system.functions WHERE name LIKE ‘%arr%’;”
clickhouse-client -m --query “SELECT * FROM system.clusters”
echo “SHOW TABLES” | clickhouse-client
clickhouse-client --query=“SELECT database,name,engine FROM system.tables WHERE database != ‘system’”
echo -e “1\n2\n3” | clickhouse-local -q “CREATE TABLE test_table (id Int64) ENGINE = File(CSV, stdin); SELECT id FROM test_table;”
七、ClickHouse可视化
-
HouseOps GitHub - HouseOps/HouseOps: A simple ClickHouse client.
-
LightHouse GitHub - VKCOM/lighthouse: Lightweight interface for ClickHouse
-
Holistics Holistics | Self-service BI Platform
-
DataGrip DataGrip: The Cross-Platform IDE for Databases & SQL by JetBrains
1、Tabix
①查看端口是否正常
curl http://127.0.0.1:8123
②修改 /etc/clickhouse-server/config.xml 文件
vim /etc/clickhouse-server/config.xml
③把以下注释去掉
④修改clickhouse密码
vim /etc/clickhouse-server/users.xml
123456
⑤重启clickhouse服务
systemctl restart clickhouse-server.service
⑥验证clickhouse密码
clickhouse-client -u default --password 123456 --query “SHOW DATABASES”
⑦登录Tabix
八、ClickHouse卸载
1、关闭ClickHouse
sudo systemctl stop clickhouse-server.service
2、rpm命令卸载
rpm -qa | grep clickhouse
rpm -e clickhouse*
3、yum命令卸载
yum list installed | grep clickhouse
yum -y remove clickhouse-*
yum list | grep clickhouse | awk ‘{print $1}’
yum -y remove clickhouse-client.x86_64
yum -y remove clickhouse-client.noarch
yum -y remove clickhouse-common-dbg.x86_64
yum -y remove clickhouse-common-static.x86_64
yum -y remove clickhouse-common-static-dbg.x86_64
yum -y remove clickhouse-server.x86_64
yum -y remove clickhouse-server.noarch
yum -y remove clickhouse-server-base.x86_64
yum -y remove clickhouse-server-common.x86_64
yum -y remove clickhouse-server-common.noarch
yum -y remove clickhouse-compressor.x86_64
yum -y remove clickhouse-debuginfo.x86_64
yum -y remove clickhouse-test.x86_64
yum -y remove clickhouse-test.noarch
4、删除配置文件
rm -rf /clickhouse
rm -rf /var/lib/clickhouse
网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
s=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxMjYyNTkzNDIx,size_16,color_FFFFFF,t_70)
yum list | grep clickhouse | awk ‘{print $1}’
yum -y remove clickhouse-client.x86_64
yum -y remove clickhouse-client.noarch
yum -y remove clickhouse-common-dbg.x86_64
yum -y remove clickhouse-common-static.x86_64
yum -y remove clickhouse-common-static-dbg.x86_64
yum -y remove clickhouse-server.x86_64
yum -y remove clickhouse-server.noarch
yum -y remove clickhouse-server-base.x86_64
yum -y remove clickhouse-server-common.x86_64
yum -y remove clickhouse-server-common.noarch
yum -y remove clickhouse-compressor.x86_64
yum -y remove clickhouse-debuginfo.x86_64
yum -y remove clickhouse-test.x86_64
yum -y remove clickhouse-test.noarch
4、删除配置文件
rm -rf /clickhouse
rm -rf /var/lib/clickhouse
[外链图片转存中…(img-8k2QRwDP-1715057717167)]
[外链图片转存中…(img-8eBKErSv-1715057717167)]
网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!