ClickHouse学习随笔-简介-202104

一、ClickHouse介绍

ClickHouse（开源）是一个面向列的数据库管理系统(DBMS)，用于在线分析处理查询(OLAP)。

关键词：开源、面向列、联机分析处理（OLAP）

ClickHouse不仅查询速度快（相较于hive等类似的分析型DBMS）,而且硬件使用效率、容错性、可靠性、易用性、线性扩展性等高。

1.1 ClickHouse的独特功能 真正的列式DBMS 除了数据本身外不应该存在其他额外的数据。这意味着为了避免在值旁边存储它们的长度“number”，你必须支持固定长度数值类型。 数据压缩 数据压缩存储在性能方面发挥着关键作用 数据的磁盘存储 工作在传统磁盘上的系统 多核心并行处理 大型查询以一种自然的方式并行化，占用当前服务器上可用的所有必要资源 多服务器分布式处理 在ClickHouse中，数据可以保存在不同的shard上，每一个shard都由一组用于容错的replica组成，查询可以并行的在所有shard上进行处理 支持SQL ClickHouse支持基于SQL的查询语言，该语言大部分情况下是与SQL标准兼容的。 支持的查询包括 GROUP BY，ORDER BY，IN，JOIN以及非相关子查询。 不支持窗口函数和相关子查询 向量引擎 为了高效的使用CPU，数据不仅仅按列存储，同时还按向量(列的一部分)进行处理 实时的数据更新 ClickHouse支持在表中定义主键。为了使查询能够快速在主键中进行范围查找，数据总是以增量的方式有序的存储在MergeTree中。因此，数据可以持续不断高效的写入到表中，并且写入的过程中不会存在任何加锁的行为 索引 按照主键对数据进行排序，使能够以几十毫秒的低延迟对数据进行特定值查找或范围查找 适合在线查询 在线查询意味着在没有对数据做任何预处理的情况下以极低的延迟处理查询并将结果加载到用户的页面中 支持近似计算 ClickHouse提供各种各样在允许牺牲数据精度的情况下对查询进行加速的方法： 1、用于近似计算的各类聚合函数，如：distinct values, medians, quantiles 2、 基于数据的部分样本进行近似查询。这时，仅会从磁盘检索少部分比例的数据。 3、 不使用全部的聚合条件，通过随机选择有限个数据聚合条件进行聚合。这在数据聚合条件满足某些分布条件下，在提供相当准确的聚合结果的同时降低了计算资源的使用。 1.2 OLAP场景特征(适用场景) 绝大多数都是读请求 数据以相当大的批量写入(> 1000行)，而不是单行。 不再修改已添加的数据 每次查询都从数据库中读取大量的行，但是同时又仅需要少量的列 宽表，即每个表包含着大量的列 较少的查询(通常每台服务器每秒数百个查询或更少) 对于简单查询，允许延迟大约50毫秒 列中的数据相对较小： 数字和短字符串(例如，每个URL 60个字节) 处理单个查询时需要高吞吐量（每个服务器每秒高达数十亿行） 事务不是必须的 对数据一致性要求低 每一个查询除了一个大表外，其他都是小表 查询结果明显小于源数据。换句话说，数据被过滤或聚合后能够被存放在单台服务器的内存中 1.3 缺点(不适用场景) 没有完整的事物支持 缺少高频率，低延迟的修改或删除已存在数据的能力。仅能用于批量删除或修改数据，但这符合 GDPR 稀疏索引使得ClickHouse不适合通过其键检索单行的点查询 1.4 官方性能对比 ClickHouse的性能超过了市场上现有的类似的面向列的DBMS。它每秒处理数亿到10亿行，每台服务器每秒处理数百亿字节的数据。单个查询(解压缩后，仅使用列)的处理性能峰值为每秒2 tb以上。

毕竟是官方自己提供的数据，为了更加真实的性能测试，后面会在实际环境中和GreenPlum等做测试对比。

1.5 数据类型支持 UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64 分别对应mysql数据类型smallint,tinyint,int,bigint/long Float32, Float64分别对应mysql数据类型 float,double Decimal Boolean String FixedString(N) UUID Date DateTime 分别对应mysql数据类型datetime/timestamp Enum Array(T) AggregateFunction(name, types_of_arguments…) Tuple(T1, T2, …) Nullable 嵌套数据类型 Nested(Name1 Type1, Name2 Type2, …) 特殊数据类型 Expression Set Nothing 二、安装部署(Centos7) 2.1 环境准备 参考hadoop集群搭建中的一、安装环境准备和二、安装zookeeper

2.2 安装CLickHouse（每台集群）

Step1: 如果从官方仓库安装，需要确保您使用的是x86_64处理器构架的Linux并且支持SSE 4.2指令集 检查是否支持SSE 4.2：

grep -q sse4_2 /proc/cpuinfo && echo “SSE 4.2 supported” || echo “SSE 4.2 not supported” 返回结果应为：“SSE 4.2 supported” Step2: 基于脚本安装

安装依赖

sudo yum install -y curl

下载运行脚本 centos7显式指定os=centos dist=7两个参数

curl -s https://packagecloud.io/install/repositories/altinity/clickhouse/script.rpm.sh | sudo os=centos dist=7 bash

查看clickhouse可用安装包 sudo yum list ‘clickhouse*’ 结果如下列表：有多个可用的包(包括新版本和旧工具)，其中一些已经被弃用，因此不需要安装所有可用的rpm。

Step3: 安装ClickHouse的主要部分——服务器和客户端应用程序

sudo yum install -y clickhouse-server clickhouse-client 查看校验已安装列表

sudo yum list installed ‘clickhouse*’

Step4: 配置目录查看及修改

安装完后默认的配置文件路径在/etc/clickhouse-server/下：config.xml（默认系统配置） 、users.xml（用户相关配置） 通过查看配置文件config.xml，定了默认的数据目录、临时目录、日志目录等; /var/lib/clickhouse /var/lib/clickhouse/tmp/ /var/log/clickhouse-server 实际生产环境中，这些数据目录通常放在数据盘而不是系统盘，所以会做相应调整。

默认启动脚本：/etc/rc.d/init.d/clickhouse-server

最大打开文件数：/etc/security/limits.d/clickhouse.conf clickhouse soft nofile 262144 clickhouse hard nofile 262144

定时任务文件: /etc/cron.d/clickhouse-server(看看就好了。cron进程执行时，就会自动扫描该目录下的所有文件，按照文件中的时间设定执行后面的命令)，文件内容如下：

*/10 * * * * root (which service > /dev/null 2>&1 && (service clickhouse-server condstart ||:)) || /etc/init.d/clickhouse-server condstart > /dev/null 2>&1

安装二进制文件目录：/usr/bin/clickhouse*,共享文件目录：/usr/share/clickhouse/

大都软链接到了clickhouse这个二进制文件

更多服务端配置（/etc/clickhouse-server/config.xml ）及说明文档

Step5: 确保ClickHouse服务器正在运行

sudo /etc/init.d/clickhouse-server restart Step6： 客户端连接命令

clickhouse-client 至此单机安装完毕。

2.3 集群配置 这一部分官方文档整理的不够清晰条理，个人觉得。 首先查看配置文件的文档，里面大致介绍了服务端配置的覆盖（替代）文件和用户配置文件的创建和配置。然后找到分布式文档参考配置新的内容。具体整理如下步骤。

2.3.1 创建扩展配置文件（metrika.xml） 配置对应config.xml的中的标签进行复制扩展，新的标签名应和config.xml中 incl="替换的标签名"一致。 每个clickhouse-server实例默认配置下对应只能被配置为某个数据分片（shard）的唯一副本（replica）。或添加内部复制端口配置。 默认本节点实例1： <http_port>8123</http_port> <tcp_port>9000</tcp_port> <interserver_http_port>9009</interserver_http_port> 添加本节点实例2 ： <http_port>8124</http_port> <tcp_port>9001</tcp_port> <interserver_http_port>9010</interserver_http_port> 参考：https://www.cnblogs.com/freeweb/p/9352947.html

添加zookeeper配置需要重启集群服务，此时会在zk客户端查看生产/clickhouse目录 配置需添加在中。 完整配置示例如下：

<yandex>
<!-- 集群配置 -->
<clickhouse_remote_servers>
    <!-- 集群名称  -->
    <hdc_3s1r_cluster>
        <!-- 数据分片1  -->
        <shard>
            <weight>1</weight>
            <internal_replication>true</internal_replication>
            <replica>
                <host>hdc-data4</host>
                <port>9000</port>
                <user>default</user>
                <password>Hdc2019</password>
            </replica>
        </shard>

        <!-- 数据分片2  -->
        <shard>
            <weight>1</weight>
            <internal_replication>true</internal_replication>
            <replica>
                <host>hdc-data5</host>
                <port>9000</port>
                <user>default</user>
                <password>Hdc2019</password>
            </replica>
        </shard>

        <!-- 数据分片3  -->
        <shard>
            <weight>1</weight>
            <internal_replication>true</internal_replication>
            <replica>
                <host>hdc-data6</host>
                <port>9000</port>
                <user>default</user>
                <password>Hdc2019</password>
            </replica>
        </shard>
    </hdc_3s1r_cluster>
</clickhouse_remote_servers>

<!-- 本节点副本名称 (这里只配置单副本)-->
<macros>
    <layer>01</layer>
    <shard>03</shard>
    <replica>hdc-data6</replica>
</macros>

<!-- 监听网络 -->
<networks>
   <ip>::/0</ip>
</networks>

<!-- ZK  -->
<zookeeper-servers>
  <node index="1">
    <host>hdc-data4</host>
    <port>2181</port>
  </node>
  <node index="2">
    <host>hdc-data5</host>
    <port>2181</port>
  </node>
  <node index="3">
    <host>hdc-data6</host>
    <port>2181</port>
  </node>
</zookeeper-servers>

<!-- 数据压缩算法  -->
<clickhouse_compression>
<case>
  <min_part_size>10000000000</min_part_size>
  <min_part_size_ratio>0.01</min_part_size_ratio>
  <method>lz4</method>
</case>
</clickhouse_compression>

</yandex>

补充说明：

标签名 描述 clickhouse_remote_servers 配置分布式表中使用的集群。 hdc_3s1r_cluster 自定义的标签名，也就是集群名称。群集名称不能包含“.”（点）符号。 shard 数据分片标签，一个shard标签组代表一个数据分片。 weight 定义数据分片的数据权重。比如有两个分片，第一个权重为9，第二个权重为10，则该批次的行将会有9/19的数据被发送到第一个分片，10/19的数据被发送到第二分片。 internal_replication 是否启用内部复制。true 代表写入数据时选择第一个健康的副本进行写入，其余副本以该表本身进行复制，保证复制表的一致性。false(默认) 代表将数据直接写入所有副本，因为没有检查复制表的一致性，而且随着时间的推移，它们将包含略微不同的数据。 replica 指定分片数据副本，可为每个服务器指定参数主机、端口和可选的用户、密码、安全、压缩等 host 数据分片远程服务地址（支持IPv6 ）。如果指定了域名（domain），服务器在启动时发出DNS请求并且只要服务器在运行就能一直保存结果。如果DNS请求失败，服务器不会启动。如果更改DNS记录，请重新启动服务器。 port TCP端口(对应config.xml的"tcp_port"，通常设置为9000)。不要将其与http_port混淆。 user 连接到远程服务器的用户的名称。默认值:“default”。此用户必须具有连接到指定服务器的权限。对应的用户名和密码是在user.xml定义。 password 连接到远程服务器的密码。默认值:空字符串。根据实际在user.xml中对应用户的密码明文。 secure 使用ssl进行连接，通常还应该定义端口= 9440。服务器应该监听9440并且有正确的证书，对应config.xml中“tcp_port_secure”。（示例中无） compression 开启数据压缩，默认True（示例中无） macros 宏定义。{layer} - ClickHouse集群的昵称，用于区分不同集群之间的数据。{shard} - 分片编号或符号引用。{replica} - 副本的名称（唯一），通常与主机名匹配macros为可选定义。配置文件中定义了在创建表时每台服务器就可以使用相同的建表DDL。否则要ReplicatedMergeTree指定zk路径和replica值。（后面复制表细讲） networks 监听网络，::/0代表监听所有ip zookeeper-servers ClickHouse在使用复制表时使用ZooKeeper存储复制元数据。和config.xml文件中对应 clickhouse_compression ClickHouse检查{min_part_size}和{min_part_size_ratio}，并处理与这些条件匹配的{case}块。如果不匹配，ClickHouse应用lz4压缩算法。{method}指压缩方法，可接受值:lz4或zstd(实验值)。 2.3.2 修改config.xml配置 在/etc/clickhouse-server/config.xml中放开远程主机监听

<listen_host>::1</listen_host>
<listen_host>0.0.0.0</listen_host>

遇到问题：

登录时报异常 Code: 210. DB::NetException: Connection refused (localhost:9000, ::1) 解决:需要开放<listen_host>::1</listen_host>。另外zookeeper集群没有正常启动也会造成建表时报此类错误。 默认路径为/etc/metrika.xml。若想改变路径或文件名则需要在/etc/clickhouse-server/config.xml中添加例如下配置：

<include_from>/etc/clickhouse-server/metrica.xml</include_from>

2.3.3 修改用户配置（user.xml） 出于安全性考虑，将明文密码配置改成加密配置<password_sha256_hex>。</password_sha256_hex> 加密密码生成方式1（随机生成）： PASSWORD= ( b a s e 64 < / d e v / u r a n d o m ∣ h e a d − c 8 ) ; e c h o " (base64 < /dev/urandom | head -c8); echo " (base64</dev/urandom∣head−c8);echo"PASSWORD"; echo -n “$PASSWORD” | sha256sum | tr -d ‘-’ 加密密码生成方式2（指定密码）： echo -n “your password” | sha256sum | tr -d ‘-’

在profiles标签下的readonly标签添加如下配置：

示例，添加一个只读用户

    <ckh_readonly>
        <password_sha256_hex>8545f4dc3fe83224980663ebc2540d6a68288c8afcbaf4da3b22e72212e256e1</password_sha256_hex>
        <networks incl="networks" replace="replace">
            <ip>::/0</ip>
        </networks>
        <profile>readonly</profile>
        <quota>default</quota>
        <allow_databases>
            <database>default</database>
        </allow_databases>
    </ckh_readonly>

2.3.4 重启集群及查看状态

2.3.5 登录验证 clickhouse-client -u ‘ckh_readonly’ --password ‘ck10086’ 更多参数帮助：clickhouse-client --help

改用默认用户登录，创建分布式表（建表语句需要在每台数据分片的服务器上都执行一遍,大规模集群通常通过脚本维护）。为何要这样创建，后面讲解。

CREATE TABLE ontime_local (FlightDate Date,Year UInt16) ENGINE = MergeTree(FlightDate, (Year, FlightDate), 8192); CREATE TABLE ontime_all AS ontime_local ENGINE = Distributed(hdc_3s1r_cluster, default, ontime_local, rand()); 插入测试数据

insert into ontime_all (FlightDate,Year)values(‘2001-10-12’,2001); insert into ontime_all (FlightDate,Year)values(‘2002-10-12’,2002); insert into ontime_all (FlightDate,Year)values(‘2002-10-12’,2003); 在任何一台服务器客户端查询结果：

三、表的基本理解和使用 在分布式集群中，我们通常需要先创建本地表（分片表/复制表）再创建分布式表。因为分布式表只是作为一个查询引擎，本身不存储任何数据，查询时将sql发送到所有集群分片，然后进行进行处理和聚合后将结果返回给客户端。创建什么样的表，需要根据实际的使用场景决定在创建表指定什么样的表引擎。

表引擎（即表的类型）决定了：

数据的存储方式和位置，写到哪里以及从哪里读取数据 支持哪些查询以及如何支持。 并发数据访问。 索引的使用（如果存在）。 是否可以执行多线程请求。 数据复制参数。 在读取时，引擎只需要输出所请求的列，但在某些情况下，引擎可以在响应请求时部分处理数据。 对于大多数正式的任务，应该使用MergeTree族中的引擎。

首先了解一下建表的几种方式

Way 1: 常规语法 CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], … ) ENGINE = engine

Way 2: 具有相同结构的表，同时可以对其指定不同的表引擎声明。 CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]

Way 3: 使用指定的引擎创建一个与SELECT子句的结果具有相同结构的表，并使用SELECT子句的结果填充。 CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine AS SELECT …

由上可知，ENGINE = ？需要我们对表引擎的了解才能做出选择。 根据个人的理解以分片表、复制表和分布式表来展开介绍部分表引擎。这里更多的是介绍引擎的特性以便更好地对相应的场景做出选择。更多可以参考官网。