clickhouse 部署+介绍

腾讯AI架构师

已于 2022-03-27 16:40:49 修改

阅读量5k

点赞数 4

分类专栏：大数据系列课程文章标签： k8s clickhouse 集群版

于 2019-08-13 22:47:47 首次发布

本文链接：https://blog.csdn.net/luanpeng825485697/article/details/99342314

版权

大数据系列课程专栏收录该内容

63 篇文章 10 订阅

订阅专栏

介绍

Clickhouse是一个用于联机分析处理（OLAP）的列式数据库管理系统（columnar DBMS）。
传统数据库在数据大小比较小，索引大小适合内存，数据缓存命中率足够高的情形下能正常提供服务。但残酷的是，这种理想情形最终会随着业务的增长走到尽头，查询会变得越来越慢。你可能通过增加更多的内存，订购更快的磁盘等等来解决问题（纵向扩展），但这只是拖延解决本质问题。如果你的需求是解决怎样快速查询出结果，那么ClickHouse也许可以解决你的问题。

应用场景：

1.绝大多数请求都是用于读访问的
2.数据需要以大批次（大于1000行）进行更新，而不是单行更新；或者根本没有更新操作
3.数据只是添加到数据库，没有必要修改
4.读取数据时，会从数据库中提取出大量的行，但只用到一小部分列
5.表很“宽”，即表中包含大量的列
6.查询频率相对较低（通常每台服务器每秒查询数百次或更少）
7.对于简单查询，允许大约50毫秒的延迟
8.列的值是比较小的数值和短字符串（例如，每个URL只有60个字节）
9.在处理单个查询时需要高吞吐量（每台服务器每秒高达数十亿行）
10.不需要事务
11.数据一致性要求较低
12.每次查询中只会查询一个大表。除了一个大表，其余都是小表
13.查询结果显著小于数据源。即数据有过滤或聚合。返回结果不超过单个服务器内存大小

相应地，使用ClickHouse也有其本身的限制：

1.不支持真正的删除/更新支持不支持事务（期待后续版本支持）
2.不支持二级索引
3.有限的SQL支持，join实现与众不同
4.不支持窗口功能
5.元数据管理需要人工干预维护

参考:https://www.jianshu.com/p/a5bf490247ea

先来说一下，ClickHouse为啥快

MySQL单条SQL是单线程的，只能跑满一个core，ClickHouse相反，有多少CPU，吃多少资源，所以飞快；
ClickHouse不支持事务，不存在隔离级别。这里要额外说一下，有人觉得，你一个数据库都不支持事务，不支持ACID还玩个毛。ClickHouse的定位是分析性数据库，而不是严格的关系型数据库。又有人要问了，数据都不一致，统计个毛。举个例子，汽车的油表是100%准确么？为了获得一个100%准确的值，难道每次测量你都要停车检查么？统计数据的意义在于用大量的数据看规律，看趋势，而不是100%准确。
IO方面，MySQL是行存储，ClickHouse是列存储，后者在count()这类操作天然有优势，同时，在IO方面，MySQL需要大量随机IO，ClickHouse基本是顺序IO。
有人可能觉得上面的数据导入的时候，数据肯定缓存在内存里了，这个的确，但是ClickHouse基本上是顺序IO，用过就知道了，对IO基本没有太高要求，当然，磁盘越快，上层处理越快，但是99%的情况是，CPU先跑满了（数据库里太少见了，大多数都是IO不够用）。

在这里插入图片描述

一.单机版部署

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4    # optional
echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update
sudo apt-get install -y clickhouse-server clickhouse-client
 安装中会填写默认用户密码
sudo service clickhouse-server start

启动客户端进入sql命令行

clickhouse-client  --host 192.168.121.41 --port 9000 --multiline --password   yourdefaultpassword

二.集群版部署

注意：集群的实现，是将每个节点的本地表组合起来形成分布式表来实现的。

1.按照单机版的模式分别在每台机器上安装
2.修改配置文件
/etc/metrika.xml
其中<perftest_3shards_1replicas> 表示集群名称，可以多个集群名称来实现备份，同一个节点可以在不同集群里面。

<yandex>
<clickhouse_remote_servers>
    <perftest_3shards_1replicas>
        <shard>
             <internal_replication>true</internal_replication>
            <replica>
                <host>isec-hdp01</host>
                <port>9000</port>
            </replica>
        </shard>
         <shard>
             <internal_replication>true</internal_replication>
            <replica>
                <host>isec-hdp02</host>
                <port>9000</port>
            </replica>
        </shard>
        <shard>
             <internal_replication>true</internal_replication>
            <replica>
                <host>isec-hdp03</host>
                <port>9000</port>
            </replica>
        </shard>
    </perftest_3shards_1replicas>
</clickhouse_remote_servers>

其中shard属性内配置了1个replica就表示1副本。表示该节点存储该分片的数据。
分片可以设置internal_replication属性，这个属性是true或者false，默认是false。
如果设置为true，则往本地表写入数据时，总是写入到完整健康的副本里，然后由表自身完成复制，这就要求本地表是能自我复制的。

如果设置为false，则写入数据时，是写入到所有副本中。这时，是无法保证一致性的。

举个栗子，一条数据要insert到ontime_all_2（分布式表）中，假设经过rand()实际是要写入到hadoop1的ontime_local（本地表）表中，此时ontime_local配置了两个副本。
如果internal_replication是false，那么就会分别往两个副本中插入这条数据。注意！！！分别插入，可能一个成功，一个失败，插入结果不检验！这就导致了不一致性；
而如果internal_replication是true，则只往1个副本里写数据，其他副本则是由ontime_local自己进行同步，这样就解决了写入一致性问题。

虽然没有模拟出数据不一致的情况，实际中可能会遇到，所以官方建议使用表自动同步的方式，也就是internal_replication为true。

<zookeeper-servers>
  <node index="1">
    <host>192.168.121.41</host>
    <port>2181</port>
  </node>
 <node index="2">
    <host>192.168.121.42</host>
    <port>2181</port>
  </node>
 <node index="3">
    <host>192.168.121.43</host>
    <port>2181</port>
  </node>
 
</zookeeper-servers>
 
<macros>
    <replica>192.168.121.41</replica>
</macros>

/etc/clickhouse-server/config.xml 70行左右

 <listen_host>::1</listen_host>
 <listen_host>isec-hdp01</listen_host>

/etc/clickhouse-server/users.xml 最大查询内存

 <max_memory_usage>50000000000</max_memory_usage>

3.启动

  service clickhouse-server stop
  service clickhouse-server start 
  clickhouse-client  --host 192.168.121.41 --port 9000 --multiline --password   yourdefaultpassword -u default

参考:https://blog.csdn.net/keyuquan/article/details/82782569

常用SQL语法

-- 查看集群信息
select * from system.clusters;

-- 列出数据库列表
show databases;

-- 列出数据库中表列表
show tables;

-- 创建数据库
create database test;

-- 删除一个表
drop table if exists test.t1;

-- 创建第一个表
create /*temporary*/ table /*if not exists*/ test.m1 (
 id UInt16
,name String
) ENGINE = Memory
;

-- 插入测试数据
insert into test.m1 (id, name) values (1, 'abc'), (2, 'bbbb');

-- 查询
select * from test.m1;

-- 时间戳转换
select toUnixTimestamp('2018-11-25 00:00:02');
select toDateTime(1543075202);

创建库

CREATE/ATTACH DATABASE zabbix ENGINE = Ordinary;

ATTACH 也可以建库，但是metadata目录下不会生成.sql文件，一般用于metadata元数据sql文件被删除后，恢复库表结构使用

创建本地表

CREATE TABLE test02( id UInt16,col1 String,col2 String,create_date date ) ENGINE = MergeTree(create_date, (id), 8192);

ENGINE：是表的引擎类型，

MergeTree：最常用的，MergeTree要求有一个日期字段，还有主键。

Log引擎没有这个限制，也是比较常用。

ReplicatedMergeTree：MergeTree的分支，表复制引擎。

Distributed：分布式引擎。

create_date：是表的日期字段，一个表必须要有一个日期字段。

id：是表的主键，主键可以有多个字段，每个字段用逗号分隔。

8192：是索引粒度，用默认值8192即可。

创建分布式表

先在需要的节点上分别建表：ontime_local

CREATE TABLE `ontime_local` (
 `Year` UInt16,
  `Quarter` UInt8,
  `Month` UInt8,
  `DayofMonth` UInt8,
  `DayOfWeek` UInt8,
  `FlightDate` Date,
) ENGINE = MergeTree(FlightDate, (Year, FlightDate), 8192)

其中MergeTree引擎提供了根据日期进行索引和根据主键进行索引，同时提供了实时更新数据的功能(如，在写入数据的时候就可以对已写入的数据进行查询，不会阻塞。)，mergetree是clickhouse里最先进的表引擎，不要跟merge引擎混淆。

MergeTree参考：https://blog.csdn.net/u013676711/article/details/78862243

在每个节点上建分布表

CREATE TABLE distributed_table AS table ENGINE = Distributed(cluster, db, table, rand());

cluster：配置文件中的群集名称。

db：库名。

table：本地表名。

rand()：分片方式：随机。

intHash64():分片方式：指定字段做hash。

Distribute引擎会选择每个分发到的Shard中的”健康的”副本执行SQL

例如

CREATE TABLE ontime_all AS ontime_local
ENGINE = Distributed(cluster_3shards_1replicas, default, ontime_local, rand())

分布表（Distributed）本身不存储数据，相当于路由，需要指定集群名、数据库名、数据表名、分片KEY，这里分片用rand()函数，表示随机分片。查询分布表，会根据集群配置信息，路由到具体的数据表，再把结果进行合并。

DDL

如果想按集群操作，需要借助zookeeper，在config.xml中添加配置

<distributed_ddl>

<path>/clickhouse/task_queue/ddl</path>

</distributed_ddl>

一个节点创建表，会同步到各个节点

CREATE TABLE db.table [ON CLUSTER cluster] (...)

添加、删除、修改列

ALTER TABLE [db].table [ON CLUSTER cluster] ADD|DROP|MODIFY COLUMN ...

rename 支持*MergeTree和Distributed

rename table db.table1 to db.table2 [ON CLUSTER cluster]

truncate table db.table;不支持Distributed引擎

delete/update 不支持Distributed引擎

ALTER TABLE [db.]table DELETE WHERE filter_expr...

ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE ...

分区表

按时间分区：

toYYYYMM(EventDate)：按月分区

toMonday(EventDate)：按周分区

toDate(EventDate)：按天分区

按指定列分区：

PARTITION BY cloumn_name

对分区的操作：

alter table test1 DROP PARTITION [partition] #删除分区

alter table test1 DETACH PARTITION [partition]#下线分区

alter table test1 ATTACH PARTITION [partition]#恢复分区

alter table .test1 FREEZE PARTITION [partition]#备份分区

数据备份

数据备份与分片没有必然联系，这是两个方面的问题。但在clickhouse中，replica是挂在shard上的，因此要用多副本，必须先定义shard。

最简单的情况：1个分片多个副本。

添加集群
像之前一样，再配置一个集群，叫做cluster_1shards_2replicas，表示1分片2副本，配置信息如下：

<yandex>
        <!-- 1分片2备份 -->
        <cluster_1shards_2replicas>
            <shard>
                <internal_replication>false</internal_replication>
                <replica>
                    <host>hadoop1</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>hadoop2</host>
                    <port>9000</port>
                </replica>
            </shard>
        </cluster_1shards_2replicas>
</yandex>

其中shard属性内配置了两个replica就表示2副本。表示这两个节点存储的是相同的分片数据。
注意，如果配置文件没有问题，是不用重启clickhouse-server的，会自动加载！

分片 + 备份

分片，是为了突破单机上限（存储、计算等上限），备份是为了高可用。

只分片，提升了性能，但是一个分片挂掉，整个服务不可用；只备份，确实高可用了，但是整体还是受限于单机瓶颈（备份1000份与备份2份没什么区别，除了更浪费机器）。

所以，生产中这两方面需要同时满足。

其实，只要把之前的分片和备份整合起来就行了，例如，3分片2备份的配置如下：

...
        <!-- 3分片2备份：使用表备份 -->
        <cluster_3shards_2replicas>
            <shard>
                <internal_replication>true</internal_replication>
                <replica>
                    <host>hadoop1</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>hadoop2</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard>
                <internal_replication>true</internal_replication>
                <replica>
                    <host>hadoop3</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>hadoop4</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard>
                <internal_replication>true</internal_replication>
                <replica>
                    <host>hadoop5</host>
                    <port>9000</port>
                </replica>
                <replica>
                    <host>hadoop6</host>
                    <port>9000</port>
                </replica>
            </shard>
        </cluster_3shards_2replicas>
...

数据同步

采用remote函数
insert into db.table select * from remote(‘目标IP’,db.table,‘user’,‘passwd’)

csv文件导入clickhouse
cat test.csv | clickhouse-client -u user --password password --query=“INSERT INTO db.table FORMAT CSV”

同步mysql库中表
CREATE TABLE tmp ENGINE = MergeTree ORDER BY id AS SELECT * FROM mysql(‘hostip:3306’, ‘db’, ‘table’, ‘user’, ‘passwd’) ;

4） clickhouse-copier 工具

其他事项

clickhouse的cluster环境中，每台server的地位是等价的，即不存在master-slave之说，是multi-master模式。

各replicated表的宿主server上要在hosts里配置其他replicated表宿主server的ip和hostname的映射。

上面描述的在不同的server上建立全新的replicated模式的表，如果在某台server上已经存在一张replicated表，并且表中已经有数据，这时在另外的server上执行完replicated建表语句后，已有数据会自动同步到其他server上面。

如果zookeeper挂掉，replicated表会切换成read-only模式，不再进行数据同步，系统会周期性的尝试与zk重新建立连接。

如果在向一张replicated表insert数据的时候zookeeper挂掉，这时候会抛一个异常，等到与zk重新建立连接以后，系统（其他replicated表所在server）会检查本地文件与预期文件（保存在zk上）的差别，如果是轻微的差别，直接同步覆盖，如果发现有数据块损坏或者识别不了，则将这些数据文件移动到“detached”子目录，然后重新根据zk所记录的文件信息进行副本的同步。

drop掉某一台server上的replicated表，不会对其他server上面的replicated表造成影响。

k8s 部署集群版clickhouse

github地址 https://github.com/626626cdllp/k8s/tree/master/clickhouse

kubectl create ns cloudai-2
kubectl create -f configmap.yaml
kubectl create -f pv-hostpath.yaml
kubectl create -f sa-rbac.yaml
kubectl create -f service.yaml
kubectl create -f sts.yaml

客户端连接

下载二进制文件--port填写的为9000对应的nodeport

clickhouse-client  --host 192.168.11.127 --port 31603 --multiline --password admin -u root

python 操作clickhouse

from clickhouse_driver import Client
import time
import random

client = Client(host='192.168.11.127',port='31603', database='default', user='root', password='admin')
# client.execute('DROP TABLE IF EXISTS test')
try:
    creattable = """CREATE TABLE test (device_id UInt64,age UInt64,gender String)ENGINE=Memory"""
    client.execute(creattable)
except Exception as e:
    print(e)
x = 1000
while (x > 0):

    data = []
    for row in range(10000):
        # row = list(row)
        data.append([random.randint(10,1000),random.randint(10,60),'test'])
    try:
        start = time.time()
        print(data)
        client.execute('INSERT INTO test VALUES',data,types_check=True)
        end = time.time()
        print(str(x) + ' clickhouse插入时间', end - start)
    except Exception as e:
        print('================')
        print(e)
    x = x -1

行业分析

https://zhuanlan.zhihu.com/p/22165241

参考：https://www.jianshu.com/p/2285774f63d9

配置

server 配置参考https://clickhouse.tech/docs/zh/operations/server_settings/settings/
server 的配置直接卸载config.xml的目录中

setting配置参考https://clickhouse.tech/docs/zh/operations/settings/
配置方式
1、添加在<profiles>中，
2、直接在运行是clickhouse的命令行，或者http协议中SET setting=value
3、直接在查询参数中设置