clickhouse学习笔记

Kevin_du
189 阅读17分钟

clickhouse的特点

ClickHouse 将数据划分为多个 partition(一般按照天来进行分区)，每个 partition 再进一步划分为多个 index granularity(索引粒度)，然后通过多个 CPU 核心分别处理其中的一部分来实现并行数据处理。单条 Query 就能利用整机所有 CPU。极致的并行处理能力，极大的降低了查询延时。但是有一个弊端就是不适合高并发查询，官网建议qps在100以内，用druid可以进行控制(最下面demo会讲)。

为什么这么快：
向量化执行引擎

ClickHouse 可以通过指令并行处理多列数据，充分发挥CPU多核和向量计算的优势。在进行算术运算、过滤和聚合操作时，ClickHouse会以批量的方式处理数据，而不是逐行处理，从而提高计算效率；

并行计算

ClickHouse 会将查询分解成多个任务，这些任务可以并行执行，在不同的 CPU 核心上运行。同时，在分布式环境下，ClickHouse 会将数据分散到多个节点上， 并行执行查询 。这种水平扩展能力使得 ClickHouse 能够处理 PB 级的数据集；

列式存储

此块内容可以参考数据密集型应用系统设计第三章：存储与检索

列式存储查询只涉及需要的列，而不是读取整个行，并且列式存储模式使得相关数据能够聚集在一起，减少了磁盘读取的随机性，提高了缓存命中率。

clickhouse优点：

  • 支持完备的SQL操作（clickhouse.com/docs/zh/sql…）
  • 列式存储与数据压缩（见：数据压缩格式）
  • 向量化执行引擎
  • 关系型模型(与传统数据库类似)
  • 丰富的表引擎（见下文）
  • 并行处理
  • 在线查询
  • 数据分片(partition)

clickhouse缺点：

  • 不支持事务。
  • 不擅长根据主键按行粒度进行查询（虽然支持），故不应该把ClickHouse当作Key-Value数据库使用。
  • 不擅长按行删除数据（虽然支持）。
  • 不支持高并发QPS或TPS操作，因此需要需要限制，建议使用Druid来进行资源池限制：maxActive

查询存储和表信息：

--查询表的信息
SELECT 
    `database` AS `库名`,
    `table` AS `表名`,
    sum(`rows`) AS `总行数`,
    formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
    round((1-sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`
FROM system.parts
GROUP BY `database`,`table`
order by sum(data_compressed_bytes) desc;

结果集：

明细表建表信息：

当ttl_only_drop_parts启用时（=1），当ClickHouse服务器中的所有行都到期时，它将丢弃整个部分。

同理：TTL toDate (response_time) + toIntervalDay (15) SETTINGS ttl_only_drop_parts = 1，表示当日期超过15天时，ClickHouse会删除所有对应的行。

GRANULARITY表示索引颗粒度， 一般不会更改。

/******************************************/
/*   DatabaseName = bangdao_rta_advertising   */
/*   TableName = response_rta_ad_log   */
/******************************************/
CREATE TABLE
  bangdao_rta_advertising.response_rta_ad_log (
    `request_id` String COMMENT '请求编号',
    `device_type` String COMMENT '设备类型',
    `device_id` String COMMENT '设备号',
    `rta_id` String COMMENT 'rta_id',
    `ad_id` String COMMENT 'ad_id',
    `is_new` Bool COMMENT '是否是新用户',
    `is_pass` Bool COMMENT '是否通过参竞',
    `response_time` DateTime COMMENT '请求时间',
    INDEX idx_response_time response_time TYPE minmax GRANULARITY 100,
    INDEX idx_rta_id_ad_id (rta_id, ad_id) TYPE bloom_filter GRANULARITY 8192,
    INDEX index_rta_id rta_id TYPE bloom_filter GRANULARITY 100,
    INDEX index_ad_id ad_id TYPE bloom_filter GRANULARITY 100
  ) ENGINE = MergeTree
PARTITION BY
  toYYYYMMDD (response_time) PRIMARY KEY (request_id, rta_id, ad_id)
ORDER BY
  (request_id, rta_id, ad_id) TTL toDate (response_time) + toIntervalDay (15) SETTINGS ttl_only_drop_parts = 1,
  index_granularity = 8192;

聚合表建表信息：

/******************************************/
/*   DatabaseName = bangdao_rta_advertising   */
/*   TableName = response_rta_ad_log_aggregate   */
/******************************************/
CREATE TABLE
  bangdao_rta_advertising.response_rta_ad_log_aggregate (
    `rta_id` String COMMENT 'rta_id',
    `ad_id` String COMMENT 'ad_id',
    `response_time` DateTime COMMENT '请求时间',
    `request_pv` AggregateFunction (uniq, String) COMMENT '请求PV',
    `device_id` AggregateFunction (uniq, String) COMMENT '请求uv',
    `is_new` Bool COMMENT '是否是新用户',
    `is_pass` Bool COMMENT '是否通过参竞'
  ) ENGINE = AggregatingMergeTree
PARTITION BY
  toYYYYMMDD (response_time) PRIMARY KEY (rta_id, ad_id)
ORDER BY
  (rta_id, ad_id, is_new, is_pass) TTL toDate (response_time) + toIntervalDay (15) SETTINGS ttl_only_drop_parts = 1,
  index_granularity = 8192;

查询聚合表语句

select rta_id,ad_id,toDate(response_time) AS report_date,
  -- 计算总PV（假设request_pv是唯一的，如果不是，则应该使用其他方法）
  -- 如果request_pv确实唯一，则可以直接使用count或sum，但这里使用uniqMerge作为示例
  toInt64(uniqMerge(request_pv)) AS request_pv,
  -- 计算总UV
  toInt64(uniqMerge(device_id)) AS request_uv_total,
  -- 计算新用户的UV
  toInt64(uniqMergeIf(device_id, is_new)) AS request_uv_new,
  -- 计算老用户的UV
  toInt64(uniqMergeIf(device_id, not is_new)) AS request_uv_old,
  -- 计算通过的总UV
  toInt64(uniqMergeIf(device_id, is_pass)) AS pass_uv_total,
  -- 计算通过的新用户UV
  toInt64(uniqMergeIf(device_id, is_pass AND is_new)) AS pass_uv_new,
  -- 计算通过的老用户UV
  toInt64(uniqMergeIf(device_id, is_pass AND not is_new)) AS pass_uv_old
  from `response_rta_ad_log_aggregate`
  where toYYYYMMDD(response_time) = '20241017'
group by rta_id,ad_id,toDate(response_time)

数据类型(多查，没必要全写) clickhouse.com/docs/zh/sql…

整数类型： Int8—UInt64

浮点数类型：Float32—Float64

字符串类型

日期和时间类型

布尔类型

枚举类型

集合类型

字典类型

高精度类型

Geo数据类型

JSON类型

多余类型以及使用可以查字典

表引擎

MergeTree系列引擎

MergeTree引擎适用于olap场景查询，它能够有效地处理大量的写入和查询负载，并支持数据压缩和分区等功能。

MergeTree

基本的引擎，用于存储有序数据，支持数据分区、索引、排序。支持主键( PRIMARY KEY)和分区( PARTITION BY)功能，以优化查询性能。支持数据压缩和副本功能。

ReplacingMergeTree

支持在多个相同主键的记录中进行替换，在写入数据时，如果主键相同，较新的记录会替换较旧的记录。它能够在合并分区时删除重复的数据。值得注意的是，ReplacingMergeTree 只是在一定程度上解决了数据重复问题，但是并不能完全保障数据不重复。

SummingMergeTree

该引擎继承了 MergeTree 引擎，用于聚合相同主键的数据，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值，即如果存在重复的数据，会对对这些重复的数据进行合并成一条数据，类似于 group by 的效果。

AggregatingMergeTree

用于存储和查询预计算的聚合数据。适合高效地存储大规模的聚合结果，一般用于针对明细表的聚合表。

VersionedCollapsingMergeTree

用于存储“有版本”的数据，并根据一个版本标识来合并或压缩记录，适用于具有“状态变更”或“事务回滚”需求的场景。

CollapsingMergeTree

与VersionedCollapsingMergeTree类似，但不需要版本号，基于SIGN字段来进行记录的合并（例如：用于记录撤销/撤回操作）。

Distributed 引擎

用于在集群中将数据查询和写入分发到多个节点上，实现分布式数据查询。Distributed 引擎本身不直接存储数据，而是作为一个中介层，负责协调不同节点的数据访问。它通常与 MergeTree 等其他存储引擎结合使用，来实现数据的分布式存储和查询。Distributed一般会有两个组件

SQL操作

Explain查看执行计划

  • AST（默认）：展示解析后的抽象语法树（Abstract Syntax Tree）。
  • SYNTAX：展示语法分析的结果（更接近 SQL）。
  • PLAN：展示查询的逻辑执行计划，包含表扫描、过滤、聚合等操作。
  • PIPELINE：展示物理执行计划，包括并发管道信息，适合分析执行时的并发优化。

EXPLAIN 的基本语法如下：

EXPLAIN PLAN select `rta_id`,'default','2024-11-16', uniqState(`request_id`) as `request_pv`,uniqState(`device_id`) as `device_id`,`is_new` ,`is_pass` 
  from bangdao_rta_advertising.`response_rta_ad_log` prewhere toYYYYMMDD(response_time) = '20241121'
        and response_time >= '2024-11-21 00:00:00' and response_time <'2024-11-21 00:01:00'
  group by `rta_id`,`is_new` ,`is_pass` ;
结果

clickhouse多表联合查询

使用小表作为右表，因为ClickHouse 会将右表加载到内存中，因此右表的数据量必须控制在内存容量内；

clickhouse物化视图

ClickHouse的物化视图是一种查询结果的持久化，普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据，可以将普通视图理解为是个子查询。物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中，对数据重新进行了组织，你可以理解物化视图是完全的一张新表。

它能够在底层数据更新后，自动更新自己的数据。数据更新包括两个方面的变化：基础表的数据修改和基础表的数据新增。所以一张表如果写入量特别大就会消耗很多机器的资源，比如数据带宽占满、存储一下子增加了很多。比如rta就扛不住。

语法：
#以下SQL来自于chatGpt资料
create table order_detail 
(
   id String,
   sku_id  String,
   pay_number Int32,
   pay_amount Int32, 
   order_date Date 
)
ENGINE = MergeTree()
partition by toYYYYMMDD(order_date)
order by (id,sku_id);

创建物化视图的限制

1.必须指定物化视图的engine 用于数据存储

2.TO [db].[table]语法的时候，不得使用POPULATE。

3.查询语句(select）可以包含下面的子句： DISTINCT, GROUP BY, ORDER BY, LIMIT…

4.物化视图的alter操作有些限制，操作起来不大方便。

5.若物化视图的定义使用了TO [db.]name 子语句，则可以将目标表的视图 卸载 DETACH 再装载 ATTACH

ClickHouse语法优化规则

TODO，存储优化，排序优化、查询语句优化、

ClickHouse常用函数（提供部分，多余的自查）

uniq

UniqState 计算唯一值的状态，用于分布式查询中的增量计算；

UniqMerge 合并UniqState状态，计算唯一值的数量；

prewhere

PREWHERE 是用来在 读取数据之前 进行筛选的。它与 WHERE 子句的主要区别是：PREWHERE 会在读取数据时就过滤掉不符合条件的行，而 WHERE 子句是在数据读取后对已经加载的数据进行过滤。PREWHERE 通常适用于那些有 低选择性（即过滤条件能显著减少数据量）的列。通过提前筛选，这些数据不会被加载到内存中，进而减少 I/O 开销。

toYYYYMMDD

toYYYYMMDD 是 ClickHouse 中的一个日期和时间转换函数，用于将日期类型数据转换为 YYYYMMDD 格式的函数，同理还有toYYYYMMDDHH等。

uniqMergeIf

uniqMergeIf 是 ClickHouse 中的一个聚合函数，通常用于合并来自多个节点的数据时执行唯一计数（去重计数）操作，同时允许基于给定的条件( expression )进行过滤。它的主要用途是支持在分布式查询中进行去重计数的合并操作；

uniqMergeIf(expression, condition)

#例子：如果is_pass为true并且is_new为false，则计算device_id的总数
toInt64(uniqMergeIf(device_id, is_pass AND not is_new)) AS pass_uv_old

uniqExactMerge

见名知意(Exact)，与 uniqMerge 不同，uniqExactMerge 提供精准的去重计数，而非基于近似算法的估算。当数据分布在多个分布式节点上，需要对分布式节点上的部分聚合结果合并并进行精准去重统计时，使用 uniqExactMerge。

    SELECT rta_id,report_date,
        uniqExactMerge(request_pv) request_pv,
        uniqExactMerge(request_uv_total) request_uv_total,
        uniqExactMerge(request_uv_new) request_uv_new,
        uniqExactMerge(request_uv_old) request_uv_old,
        uniqExactMerge(pass_uv_total) pass_uv_total,
        uniqExactMerge(pass_uv_new) pass_uv_new,
        uniqExactMerge(pass_uv_old) pass_uv_old    
        from rta_pv_uv_rta_report_daily    
    <where>
        <if test="reportDate != null">
            AND  report_date = #{reportDate,jdbcType=DATE}       
        </if>
        <if test="rtaId != null and rtaId !=''">
            AND  rta_id = #{rtaId}       
        </if>
    </where>
    group by rta_id,report_date

groupArray

toDate(日期函数)

toDate(expression)

返回 Date 类型的数据，仅保留日期部分，格式为 YYYY-MM-DD，更多日期函数参考：clickhouse.com/docs/zh/sql…

ClickHouse压缩

如果没有显式指定，ClickHouse将默认使用LZ4。，压缩比也更多取决于数据列的排序，由于RTA属于高度重复的数据，并且属于极度频繁的写入，使用ZSTD或LZ4等高效压缩算法，压缩比和速度都能达到一个较好的平衡。压缩比越高，存储空间的占用越小。

同时，排序字段 (ORDER BY) 决定了数据在磁盘上的存储顺序。相似值存储在一起可以大幅提高压缩效率。将高基数字段（重复率较低的字段）放在排序字段的后面，低基数字段优先排序。以rta为例：一个请求（requestId）可以包含一个rtaId，以及成百上千个创意(创意Id都不同)，而这些请求数据全部要根据创意维度写入clickhouse，所以应该将重复率最高的requestID放在最前面，后面跟着rtaId和创意Id：

{
  "userId": "2088722041433835",
  "reqId": "af7f0886ce5e4d58bf0926ce11257864",
  "rtaReqInfo": [
    {
      "rtaId": "1694584128",
      "adId": "171609025",
      "orderId": "0",
      "enableBidInfo": true,
      "chargeType": "CPC",
      "extInfo": {
        "target_url": "https://s.click.taobao.com/oIcf0Yt",
        "principal_pid": "2088141990697691"
      }
    },
    {
      "rtaId": "1694584128",
      "adId": "171609148",
      "orderId": "0",
      "enableBidInfo": true,
      "chargeType": "CPC",
      "extInfo": {
        "target_url": "https://s.click.taobao.com/oIcf0Yt",
        "principal_pid": "2088141990697691"
      }
    },
    {
      "rtaId": "1694584128",
      "adId": "171609406",
      "orderId": "0",
      "enableBidInfo": true,
      "chargeType": "CPC",
      "extInfo": {
        "target_url": "https://s.click.taobao.com/oIcf0Yt",
        "principal_pid": "2088141990697691"
      }
    }
  ],
  "mediaInfo": [
    {

    }
  ],
  "deviceInfo": {
    "osType": "ANDROID",
    "geo": {
      "cityCode": "370600"
    },
    "alipayClientVersion": "10.6.33.8000",
    "deviceId": [
      {
        "type": "OAID_HASH",
        "value": "8b4ed2c5f09ecddc44982e15e7de406f"
      }
    ]
  },
  "requestTime": "1725257453160"
}

ORDER BY (request_id, rta_id, ad_id)

clickhouse副本与分片

副本（Replica） 是指同一数据的多个拷贝，通常分布在集群中的不同节点上。副本的主要目的是实现 高可用性 和 容错性，确保即使某个节点发生故障，数据仍然可以通过其他副本提供，避免服务中断。

分片（Sharding）是一种数据存储和管理技术，也叫做分区（partition），Hbase中称之为区域（Region），通常用于大型分布式数据库或系统中，以提高系统的可扩展性和性能。通过将数据分割成多个“片”（shard），并将每个片存储在不同的数据库服务器上，可以减轻单个服务器的压力，提高系统的响应速度和处理能力。

ClickHouse 的副本机制是通过表引擎中的 ReplicatedMergeTree 类型来实现的，通常与 分区（Partition）和 分片（Shard）结合使用，从而提高数据的可用性和查询的负载均衡能力。

当一个副本的节点不可用时，ClickHouse 会自动选择其他副本进行查询和写入操作，确保系统的高可用性；通过 ZooKeeper 协调，ReplicatedMergeTree 表保证了副本之间的数据一致性。每个副本都有相同的数据，当一个副本接收到新的数据时，会通过 ZooKeeper 通知其他副本进行同步。

clikhouse性能为何如此卓越

列式存储： 使得数据可以按列而非按行存储，提高压缩比，同时列式存储只读取查询所需的列，而不是整个行，能显著提升查询速度。

数据分区： 通过分区策略（如按日期）将数据切分成多个物理块，查询时可以快速跳过不相关的分区，减少I/O操作。

数据排序： MergeTree引擎使用排序来组织数据,并创建基于主键(ORDER BY)的索引。这种方式使得数据检索时能够进行范围查询, 利用二分快速定位数据,从而提高查询速度。

增量合并： ClickHouse在后台通过合并操作（Merge）优化存储，合并过程中删除重复数据、压缩存储（LZ4、ZSTD等），提高查询和存储效率。

clickhouse索引与压缩比例

clickhouse不需要显示创建索引，索引是基于ORDER BY定义的列的最小值和最大值(整个数据块)，查询时，ClickHouse可以通过检查每个数据块的最小值和最大值来决定是否扫描该数据块（跳过那些没有匹配项的块），这就叫稀疏索引（跳过不相关的数据库）；

二级索引：

除了主键索引，ClickHouse的MergeTree引擎还支持通过WHERE条件使用其他列进行查询时自动使用**二级索引，这些索引是在查询时进行推断的，但并不像主键索引那样显式创建。

ClickHouse 优缺点及性能情况

「优点」

  1. 为了高效的使用CPU，数据不仅仅按列存储，同时还按向量进行处理；
  2. 数据压缩空间大，减少IO；处理单查询高吞吐量每台服务器每秒最多数十亿行；
  3. 索引非B树结构，不需要满足最左原则；只要过滤条件在索引列中包含即可；即使在使用的数据不在索引中，由于各种并行处理机制 ClickHouse 全表扫描的速度也很快；
  4. 写入速度非常快，50-200M/s，对于大量的数据更新非常适用。

「缺点」

  1. 不支持事务，不支持真正的删除/更新；
  2. 不支持高并发，官方建议qps为100，可以通过修改配置文件增加连接数，但是在服务器足够好的情况下；
  3. SQL满足日常使用80%以上的语法，join写法比较特殊；最新版已支持类似SQL的join，但性能不好；
  4. 尽量做1000条以上批量的写入，避免逐行insert或小批量的insert，update，delete操作，因为ClickHouse底层会不断的做异步的数据合并，会影响查询性能，这个在做实时数据写入的时候要尽量避开；
  5. Clickhouse快是因为采用了并行处理机制，即使一个查询，也会用服务器一半的CPU去执行，所以ClickHouse不能支持高并发的使用场景，默认单查询使用CPU核数为服务器核数的一半，安装时会自动识别服务器核数，可以通过配置文件修改该参数。

clickhouse + Kafka集成jdbc

clickhouse jdbc Maven依赖：

<!--动态数据源依赖包-->
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
  <version>2.5.4</version>
</dependency>
<!--clickhouse驱动支持 -->
<dependency>
  <groupId>ru.yandex.clickhouse</groupId>
  <artifactId>clickhouse-jdbc</artifactId>
  <version>0.2.4</version>
</dependency>
yaml配置：

配置采用Druid配置，CK官网不建议QPS超过100，当连接数超过一定值时会出现tcp连接错误，故使用maxActive来限制最大连接数

  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    dynamic:
      primary: master #设置默认的数据源或者数据源组,默认值即为master
      strict: true #设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候会抛出异常,不启动则使用默认数据源.
      datasource:
        master:
          url: jdbc:clickhouse://cc-2zecn9iuq1z1hybnb.clickhouse.ads.aliyuncs.com:8123/bangdao_rta_advertising?queryTimeout=600000&socket_timeout=600000
          username: xxxxxxxxxxxx
          password: xxxxxxxxxxxx
          driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
          druid:
            # 初始连接数
            initialSize: 5
            # 最小连接池数量
            minIdle: 10
            # 最大连接池数量
            maxActive: 15
            # 配置获取连接等待超时的时间
            maxWait: 60000
            # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
            timeBetweenEvictionRunsMillis: 60000
            # 配置一个连接在池中最小生存的时间，单位是毫秒
            minEvictableIdleTimeMillis: 300000
            # 配置一个连接在池中最大生存的时间，单位是毫秒
            maxEvictableIdleTimeMillis: 900000
            # 配置检测连接是否有效
            validationQuery: SELECT 1
            testWhileIdle: true
            testOnBorrow: false
            testOnReturn: false
            filters: stat,slf4j

当代码拉取kafka（当消息过多时，使用线程池方式进行拉取）消息内容时，封装结果集列表，同时使用并行流（parallelStream 会将数据分割到多个线程进行处理）的方式


private final String sql = "INSERT INTO response_rta_ad_log(`request_id`,`device_type`,`device_id`,`rta_id`,`ad_id`,`is_new`,`is_pass`,`response_time`,`user_id`)  " +
"VALUES (?,?,?,?,?,?,?,?,?)";


    @Override
    public void insertBatchJDBC(List<ResponseRtaAdLog> sources) {
        Connection connection = getConnection();
        PreparedStatement prepareStatement = null;
        try {
            prepareStatement = connection.prepareStatement(sql);
            connection.setAutoCommit(false);

            for (ResponseRtaAdLog log : sources) {
                prepareStatement.setObject(1, log.getRequestId());
                prepareStatement.setObject(2, log.getDeviceType());
                prepareStatement.setObject(3, log.getDeviceId());
                prepareStatement.setObject(4, log.getRtaId());
                prepareStatement.setObject(5, log.getAdId());
                prepareStatement.setObject(6, log.getIsNew());
                prepareStatement.setObject(7, log.getIsPass());
                prepareStatement.setObject(8, log.getResponseTime());
                prepareStatement.setObject(9, log.getUserId());
                prepareStatement.addBatch();
            }
            prepareStatement.executeBatch();
            connection.commit();
        } catch (Exception e) {
            throw new ServiceException(e);
        } finally {
            try {
                if (ObjectUtils.isNotEmpty(connection)) {
                    connection.close();
                }
                if (ObjectUtils.isNotEmpty(prepareStatement)) {
                    prepareStatement.close();
                }
            } catch (SQLException e) {
                log.error("close resource exception:{}", e.getMessage());
            }
        }
    }

    /**
     * 获取clickhouse连接
     *
     * @return 连接实例
         */
    public Connection getConnection() {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            log.info("===This Connection isClosed ? " + conn.isClosed());
        } catch (Exception e) {
            log.error("get connect exception:{}", e.getMessage());
        }
        return conn;
    }
avatar
文章
阅读
粉丝