Clickhouse-file:基于Clickhouse-local实现Clickhouse高效写入数据

前言

ClickHouse是一个用于OLAP(在线分析处理)的列式数据库管理系统,它专门为大数据量和高并发场景而设计。ClickHouse具有快速的扫描和聚合海量数据的能力,并且支持高并发查询,使其在处理数据分析任务方面比传统的行存储数据库更具优势。ClickHouse是一个开源项目,可以在较少的硬件资源上运行,并且可以轻松地水平扩展,以适应不断增长的数据量和流量。在快速增大的数据量的情况下,使用传统的数据写入模式,如:JDBC、CSV,会增大Clickhouse服务的数据解析压力。那是否有方法一方面能够提升数据写入量,另一方面减少Clickhouse的服务数据解析、写入压力呢?

1. 常用clickhouse flink connector

  • 基于Clickhouse-jdbc

使用官方提供的Clickhouse-jdbc驱动,通过SQL的形式将数据提交到Clickhouse服务器。优势是灵活性最高,但是在大数据量写入的情况下该方法会触发频繁的后台文件合并,甚至会出现“too many parts”的错误,同时大量的SQL解析对Clickhouse服务器有影响。

  • 官方插件(基于CSV)

将数据源流转化为CSV格式数据,并通过Http请求批量发送到Clickhouse。优势在于不需要任何驱动依赖,格式简单。缺点是稳定性差,对网络稳定性要求高

项目地址:GitHub - ivi-ru/flink-clickhouse-sink: Flink sink for Clickhouse

  • 基于clickhouse-file插件

Clickhouse-file插件,其主要实现方式是,将数据写入到CSV文件,然后通过Clickhouse-local将CSV数据文件转换为Clickhouse-file数据文件,然后将CLickhouse-file上传到远程Clickhouse服务器,用户通过Clickhouse查询数据。其优势是处理大数据量速度快,并且由于在本地生成数据文件,对Clickhouse服务器压力相对较小。缺点是配置复杂度高,通用性较差。其相对于JDBC模式的显著优势是,处理海量数据对服务器造成的压力较小。现在已开源Clickhouse-file的产品有:ClickhouseFile | Apache SeaTunnel,项目仓库地址:seatunnel/seatunnel-connectors-v2/connector-clickhouse · GitHub

2. Clickhouse-file模式

基于Clickhouse-file 模式的,其主要实现方式是,将数据写入到CSV文件,然后通过Clickhouse-local将CSV数据文件转换为Clickhouse-file数据文件,然后将CLickhouse-file上传到远程Clickhouse服务器,用户通过Clickhouse查询数据。现在已开源的积压Clickhouse-file的项目有:ClickhouseFile | Apache SeaTunnel

2.1 基于Clickhouse file模式处理流程

  1. Clickhouse Sink接收到源端数据源数据流

  2. 根据本地缓存的集群的数据分片策略,将数据分别写入到不同的本地缓存CSV文件

  3. 在同步执行数据快照时或缓存已满,使用Clickhouse Local将加载缓存的CSV数据文件,并生成本地Clickhouse文件

  4. 将Clickhouse本地文件,通过文件传输工具rsync传输到远程Clickhouse数据目录

  5. 数据文件Attach到Clickhouse Server,用户可以从Clickhouse中查询数据

图1

2.2 关键技术组件

  • clickhouse-local

clickhouse local,开发人员可以直接从命令行使用SQL命令(使用clickhouse SQL方言),提供了一种简单高效的方式来访问clickhouse功能,而无需完整安装clickhouse。用户可以使用clickhouse在不连接clickhouse服务器的情况下,生成clickhouse文件数据,并且支持大多数clickhouse engine。根据这个特性,我们可以在本地将数据通过clickhouse local工具,在本地生成clickhouse数据文件,此文件传输到远程clickhouse数据目录,用户即可查询新的数据。

​ 参考资料:clickhouse-local | ClickHouse Docs

  • clickhouse 临时文件传输到clickhouse服务器

    支持两种文件传输方式:rsyncscp

    • 相同点:

      • 均是基于ssh协议的安全传输工具。
      • 都可以通过远程主机的IP地址或域名进行连接,并且需要用户名和密码进行身份验证。
      • 传输数据时都会使用加密技术来保证数据的安全性。
    • 不同点:

      • scp只支持文件传输,而rsync除了可以传输文件,还可以传输目录、链接、设备文件等。
      • rsync比scp更加灵活,能够自动检测并同步源和目标之间的差异,只传输发生变化的部分,从而提高传输效率。
      • rsync复制时可以指定多个源目录,将它们合并到一个目的目录中,而scp只能传输单个文件或目录。
      • rsync还支持增量备份,并且可以在网络传输中断后恢复传输,而scp则不支持这些特性。
  1. Linux rsync

Linux rsync是一种用于文件同步和备份的命令行工具。它能够快速、可靠地将本地或远程计算机之间的文件进行同步,同时可以压缩数据传输并保证数据完整性。rsync的主要优点包括增量备份、支持复制硬链接和符号链接、支持过滤器以排除或包含特定文件和目录等功能。使用rsync可以帮助用户快速而有效地管理和备份文件,特别是在需要将文件从一个位置复制到另一个位置的情况下,比如在服务器之间同步数据。

Rsync可用于镜像数据、增量备份、在系统之间复制文件,并可替代scp、sftp和cp命令。

  • 本地文件传输到远程:

    rsync -a /opt/media/ remote_user@remote_host_or_ip:/opt/media/
    
  • 远程文件传输到本地:

    rsync -a remote_user@remote_host_or_ip:/opt/media/ /opt/media/
    

参考资料:Rsync Command in Linux with Examples | Linuxize

  1. scp

基于SSH协议的安全文件传输命令,它可以在本地与远程系统之间复制文件和目录。

  • 本地文件传输到远程:

    scp /opt/media/ remote_user@remote_host_or_ip:/opt/media/
    
  • 远程文件传输到本地:

    scp remote_user@remote_host_or_ip:/opt/media//opt/media/
    

3. 实战

场景:将本地生成的CSV文件,通过Clickhouse-file导入到远程Clickhouse服务器

  1. 在clickhouse存在表my_first_table

    CREATE TABLE my_first_table
    (
        user_id UInt32,
        message String,
        timestamp DateTime
    )
    ENGINE = ReplacingMergeTree
    PARTITION BY toYYYYMMDD(timestamp)
    PRIMARY KEY user_id
    
  2. 本地新增CSV文件 test_tmp.csv,并写入数据

echo -e "1,wang,2023-05-11 10:00:00\n2,zhang,2023-05-12 11:00:00\n3,sun,2023-05-13 12:00:00" > /Users/vhicool/data/test_tmp.csv
  1. 通过clickhouse local将CSV文件转换为Clickhouse-file
./clickhouse local --file "/Users/vhicool/data/test_tmp.csv"  -S "user_id UInt32,message String,timestamp DateTime" -N "test_tmp_table" \
-q "CREATE TABLE IF NOT EXISTS my_first_table
(user_id UInt32,message String,timestamp DateTime) 
ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMMDD(timestamp)
PRIMARY KEY user_id; 
INSERT INTO my_first_table select  user_id,message,timestamp from test_tmp_table"   \
--path "/Users/vhicool/data/test_tmp_file1"

此时我们到`/Users/vhicool/data/test_tmp_file1/目录下,可以查看当前生成的Clickhouse-file。

data/_local/
└── my_first_table
    ├── 20230511_1_1_0
    ├── 20230512_2_2_0
    ├── 20230513_3_3_0
    ├── detached
    └── format_version.tx
  1. 将Clickhouse-file文件复制到Clickhouse服务器数据目录
cp -r /Users/vhicool/data/test_tmp_file1/data/_local/my_first_table/*_*_*_* data/default/my_first_table/detached/
  1. 将数据文件ATTACH到Clickhouse数据表中

    local :) ALTER TABLE my_first_table ATTACH PART '20230511_1_1_0'
    local :) ALTER TABLE my_first_table ATTACH PART '20230512_2_2_0'
    local :) ALTER TABLE my_first_table ATTACH PART '20230513_3_3_0'
    

    查询导入的数据

    local :)  SELECT * FROM my_first_table
    
    ┌─user_id─┬─message─┬───────────timestamp─┐
    │       2 │ zhang   │ 2023-05-12 11:00:00 │
    └─────────┴─────────┴─────────────────────┘
    ┌─user_id─┬─message─┬───────────timestamp─┐
    │       1 │ wang    │ 2023-05-11 10:00:00 │
    └─────────┴─────────┴─────────────────────┘
    ┌─user_id─┬─message─┬───────────timestamp─┐
    │       3 │ sun     │ 2023-05-13 12:00:00 │
    └─────────┴─────────┴─────────────────────┘
    
    3 rows in set. Elapsed: 0.002 sec.
    
    

    查询数据分片

    
    local :)  SELECT
        partition,
        name,
        rows
    FROM system.parts
    WHERE table = 'my_first_table'
    
    ┌─partition─┬─name───────────┬─rows─┐
    │ 2023051120230511_1_1_0 │    1 │
    │ 2023051220230512_2_2_0 │    1 │
    │ 2023051320230513_3_3_0 │    1 │
    └───────────┴────────────────┴──────┘
    
    3 rows in set. Elapsed: 0.003 sec.
    

参考资料

flink官方写入数据支持的格式
clickhouse之数据写入问题_clickhouse insert/jdbc
百亿级数据同步,如何基于 SeaTunnel 的 ClickHouse 实现? | Apache SeaTunnel