用4c8g单机ClickHouse完美解决亿级数据量企业画像查询毫秒级响应

【背景】

项目上要做一个企业画像的功能，企业有4000万+家，像企业对外投资、企业变更、企业图谱数据都突破了一亿条，也就是单表超过了一亿。我们本来在应用层面是个Spring Boot + Mysql + Nginx的架构，服务器配置也就是4核8G，这么大的数据量远远超出了我们的处理能力，这意味着我们整个架构要全部调整；可是项目已经快到验收阶段，主要的功能都已经实现，就是企业画线模块数据量达不到合同要求，于是我们引入了ClickHouse，在应用架构不调整的情况下，仅仅是把企业画线模块数据源换成了ClickHouse，就解决了这个问题。

最终的结果是99%概率在200ms内响应。

 

服务器

单机，CentOS，单机，4C8G，磁盘500G

ClickHouse简介

ClickHouse是一个开源免费的，面向列的MPP架构数据分析数据库(大规模并行处理)，由俄罗斯Yandex为OLAP和大数据用例创建。

 

ClickHouse特点

• 列式数据存储：与一般关系型数据库的行式数据存储不同，它采用的是和Hbase一样的列式存储。
• 数据压缩：除了在磁盘空间和CPU消耗之间进行不同权衡的高效通用压缩编解码器之外，ClickHouse还提供针对特定类型数据的专用编解码器；
• 多核心并行处理：ClickHouse会使用服务器上一切可用的资源，从而以最自然的方式并行处理大型查询。
• 多服务器分布式处理：数据可以保存在不同的shard上，每一个shard都由一组用于容错的replica组成；
• 支持SQL：支持一种基于SQL的声明式查询语言，它在许多情况下与ANSI SQL标准相同。
• 向量引擎：为了高效的使用CPU，数据不仅仅按列存储，同时还按向量(列的一部分)进行处理，这样可以更加高效地使用CPU。
• 索引：按照主键对数据进行排序，这将帮助ClickHouse在几十毫秒以内完成对数据特定值或范围的查找。

数据架构

• 数据层：用来做数据清洗和计算，主要是Hadoop集群，使用Hive来做清洗和计算；
• 服务层：把数据层清洗后的数据包装成API接口，暴露给应用层使用；
• 应用层：部署在客户私有云上的分析应用系统；

服务层和数仓层都在公司内网，初始化时可以采用导出数据文件的方式，后面的更新数据使用服务层的API接口暴露给应用使用，进行数据更新。

ClickHouse建表

1.企业基本信息表(主表）

CREATE TABLE dwd_company_info
(
    enterprise_id String,
    enterprise_name String,
    credite_code String,
    reg_status  Int8,
    reg_date  Date,
    reg_province String,
    ...
    update_time DateTime
) ENGINE = MergeTree()
PRIMARY KEY (credite_code, enterprise_name)
ORDER BY (update_time)

主要是满足企业名称或者信用代码的查询，update_time是为了让查询结果有序；

2.企业对外投资信息表

CREATE TABLE dwd_company_invest_info
(
    id Int64,
    enterprise_id String,
    enterprise_name String,
    invest_enterprise_id String,
    invest_enterprise_name String,
    invest_date  Date,
    invest_mount String,
    ...
    update_time DateTime
) ENGINE = MergeTree()
PRIMARY KEY (enterprise_id)
ORDER BY (update_time)

注意，ClickHouse中的主键不存在唯一约束。

这里把enterprise_id做为主键，主要是因为先查询的是企业基本信息，有了基本信息后就拿到了enterprise_id，再查看企业画线的其它数据都是通过企业id去查询的。

ClickHouse更新数据

最开始打算用这种以下模式更新，以主表dwd_company_info为例：

1. 每周六凌晨1点开始，拉最新数据到表ods_company_info_yyyymmdd；
2. 然后把中间表跟正式表union到一起后按主键取最新数据，最后插入到dwd_company_info_yyyymmdd
3. 把dwd_company_info备份成dwd_company_info_bak_yyyymmdd，再把dwd_company_info_yyyymmdd改名成dwd_company_info；

CREATE TABLE ods_company_info_yyyymmdd AS dwd_company_info;
#拉取完最新数据成功后
CREATE TABLE dwd_company_info_yyyymmdd AS dwd_company_info;
INSERT INTO dwd_company_info_yyyymmdd SELECT * FROM ......;
#替换正式表
RENAME TABLE dwd_company_info TO dwd_company_info_bak_yyyymmd;
RENAME TABLE dwd_company_info_yyyymmdd TO dwd_company_info;

但实际使用发现一直内存溢出，最后放弃。。。；

最后使用的是ReplacingMergeTree表引擎，这个表引擎会把排序键相同的行进行合并。

CREATE TABLE dwd_company_info_yyyymmdd
(
    enterprise_id String,
    enterprise_name String,
    credite_code String,
    reg_status  Int8,
    reg_date  Date,
    reg_province String,
    ...
    update_time DateTime
) ENGINE = ReplacingMergeTree(update_time)
ORDER BY (enterprise_id)

唯一的问题是数据合并不确定啥时候触发，啥时候结束。我们采取手动触发数据合并+推迟3天替换dwd_company_info的做法。

手动触发数据合并的语句如下：

OPTIMIZE  table dwd_company_info_yyyymmdd final;

还好企业数据更新频率不大，有的数据都是一个月才更新；

初始化数据

公司内数据导出：

clickhouse-client --format_csv_delimiter="|" --query "select * from  dwd_company_info FORMAT TSV"  > dwd_company_info.csv

客户环境数据导入：

clickhouse-client --format_csv_delimiter="|" --query "INSERT INTO dwd_company_info FORMAT CSV"  < dwd_company_info.csv

总结

打开页面第一次查询，会慢一些，大概在2s以内，其它的查询基本上在200ms以内，虽然首次查询慢了一些，但满足合同需求，问题不大。

用下来整体感觉还是很惊艳的，因为单机扛住了亿级数据量的查询。在整个过程中，最费时间最消耗脑力的还是数据更新，因为一直内存溢出，曾经一度要靠考虑放弃ClickHouse，ClickHouse最大的问题就是不支持update语法，如果能支持数据更新，就更完美了。ClickHouse第二大的问题就是表关联查询，关联查询很容易就内存溢出了；官网上说的对关联查询支持的很好，但我自己体验很差，这个大概率是我的服务器配置太差，就不把锅甩在ClickHouse身上了~。