Clickhouse安装及使用

2024-03-06 17:36:20

一、下载安装包

# wget -P 
# 下载到指定目录

$ wget https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/clickhouse-client-21.8.4.51-2.noarch.rpm

$ wget https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/clickhouse-common-static-21.8.4.51-2.x86_64.rpm

$ wget https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/clickhouse-common-static-dbg-21.8.4.51-2.x86_64.rpm

$ wget https://repo.yandex.ru/clickhouse/rpm/stable/x86_64/clickhouse-server-21.8.4.51-2.noarch.rpm

二、单机安装

2.1 安装

$ sudo rpm -ivh {CLICKHOUSE-HOME}/*.rpm

2.2 查看安装情况

$ sudo rpm -qa|grep clickhouse

2.3 修改配置文件

$ sudo vim /etc/clickhouse-server/config.xml

# 1.打开listen_host，二选一
# 如果支持IPV6
<listen_host>::</listen_host>
# 如果支持IPV4
<listen_host>0.0.0.0</listen_host>

# 2.修改端口号，9000被python占用
<tcp_port>9099</tcp_port>

# 默认数据文件路径：<path>/var/lib/clickhouse/</path>
# 默认日志文件路径：<log>/var/log/clickhouse-server/clickhouse-server.log</log>

2.4 启动server

$ sudo systemctl start clickhouse-server

2.5 查看server状态

$ sudo systemctl status clickhouse-server

2.6 关闭server

$ sudo systemctl stop clickhouse-server

2.7 重启server

$ sudo clickhouse restart

2.8 使用client连接server

$ clickhouse-client -m --password {PASSWORD} --port 9099 --user default --host {HOST}

2.9 验证clickhouse是否可用

show databases;

use default;

show tables;

三、一分片两副本集群搭建

3.1 创建 metrika.xml配置文件

# 1. 在/etc/clickhouse-server/config.d 目录下创建一个名为 metrika.xml的配置文件
$ sudo vim /etc/clickhouse-server/config.d/metrika.xml

<?xml version="1.0"?>
<yandex>
 <remote_servers>
  <test_cluster>
   <shard>
    <internal_replication>true</internal_replication>
       <replica>
           <host>{host_01}</host>
           <port>9099</port>
       </replica>
       <replica>
           <host>{host_02}</host>
           <port>9099</port>
       </replica>
   </shard>
  </test_cluster>
 </remote_servers>

 <zookeeper-servers>
     <node index="1">
         <host>{host_01}</host>
         <port>2181</port>
     </node>
     <node index="2">
         <host>{host_02}</host>
         <port>2181</port>
     </node>
     <node index="3">
         <host>{host_03}</host>
         <port>2181</port>
     </node>
 </zookeeper-servers>

 <macros>
  <shard>01</shard>
  <replica>rep_1_1</replica>
 </macros>
</yandex>

3.2 修改/etc/clickhouse-server/config.xml

$ sudo vim /etc/clickhouse-server/config.xml

<zookeeper incl="zookeeper-servers" optional="true" /> 
<include_from>/etc/clickhouse-server/config.d/metrika.xml</include_from>

3.3 主机2安装第二台clickhouse

　　参考步骤2单机安装

3.4 拷贝配置文件到主机2

$ sudo scp /etc/clickhouse-server/config.d/metrika.xml remote_user@remote_host:/etc/clickhouse-server/config.d/metrika.xml

$ sudo scp /etc/clickhouse-server/config.xml remote_user@remote_host:/etc/clickhouse-server/config.xml

3.5 主机2修改配置文件

$ sudo vim /etc/clickhouse-server/config.d/metrika.xml

 <macros>
  <shard>01</shard>
  <replica>rep_1_2</replica>
 </macros>

3.6 重启clickhouse集群

$ sudo clickhouse restart

四、验证集群

4.1 主机1创建本地表

create table test_local on cluster test_cluster (
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time  Datetime
) engine=ReplicatedMergeTree('/clickhouse/tables/{shard}/test_local','{replica}')    
partition by toYYYYMMDD(create_time)    
primary key (id)    
order by (id,sku_id);

4.2 主机1创建分布式表

create table test_local_all on cluster test_cluster (
    id UInt32, 
    sku_id String,
    total_amount Decimal(16,2),
    create_time  Datetime 
) engine = Distributed(test_cluster,default,test_local,hiveHash(sku_id));

4.3 在主机1和主机2验证表创建成功

show tables;

# 主机1和主机2上都有test_local表和test_local_all表，则证明建表成功

4.4 在主机1上向test_local_all表写入数据

insert into test_local_all values (207,'sku_006',700.00,'2020-06-02 12:00:00');

4.5 在主机1和主机2上验证数据写入成功

select * from test_local;

select * from test_local_all;

# 如果主机1和主机2上的test_local和test_local_all共4个表查询结果相同，则证明数据写入成功

# 可以直接向本地表test_local写入数据，来验证4个表数据是否一致。

五、常见问题

5.1 服务器不支持IPV6

解决方案：在/etc/clickhouse-server/config.xml中，注释<listen_host>::</listen_host>，打开<listen_host>0.0.0.0</listen_host>

5.2 端口被占用

解决方案：在/etc/clickhouse-server/config.xml中，修改<tcp_port>9000</tcp_port>为<tcp_port>9099</tcp_port>，在客户端连接时，需要指定 --port 9099

5.3 查看日志

$ tail -n 100 /var/log/clickhouse-server/clickhouse-server.err.log

问题1：在使用副本时，日志报错replicateMergetree异常，提示hostnamenotfound

解决方案：将提示找不到的hostname配置到/etc/hosts文件中，并source /etc/hosts使其生效

问题2：在启动clickhouse-server时，日志报错，提示已有进程在运行中

解决方案：查看 /var/lib/clickhouse/status文件，找到pid后，使用kill -9 pid，然后再启动clickhouse-server

码农公寓