用户标签数据在ClickHouse的存储与使用

基本思路

• 离线部分：Hdfs的离线数据导入ClickHouse
• 实时部分：直接写入ClickHouse，使用AggregatingMergeTree表引擎按排序键合并最新数据
• 查询：使用final关键字，只查询最新合并后的数据
• 定时执行optimize，对全表数据按排序键合并
• 数据下发：对用户提交的条件拆分，分别从不同的表中把数据插入下发明细表(AggregatingMergeTree)，最终将数据按user_id去重后下发
• 群组人数：对用户提交的条件拆分，分别从不同表中把user_id的bitmap查询出来，将多组bitmap合并后返回群组人数

表结构

宽表字段数量800+

CREATE TABLE cust_label_all_hdfs (    
  user_id String COMMENT '客户账号',
  ……  
) ENGINE = HDFS('hdfs://xx.xx.xx.xx:8020/hive/cust_label.db/cust_label_all/stat_dt=2021-09-09/*','Parquet')

子表A字段19，数据量3000W，子表B字段13，数据量5000W

CREATE TABLE cust_label_table_a (
  user_id String COMMENT '客户账号',
  ……
) ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/tables/{layer}-{shard}/cust_label/cust_label_d_member','{replica}')
ORDER BY user_id

---

数据下发

对用户提交的条件拆分，分别从不同的表中把数据插入下发明细表(AggregatingMergeTree)，最终将数据按user_id去重后下发

标签库CH.jpg

---

人数统计

对用户提交的条件拆分，分别从不同表中把user_id的Bitmap查询出来，将多组Bitmap合并后返回群组人数

select
  bitmapOrCardinality(id_bitmap_a, id_bitmap_b) as user_num
from
  (
    select
      1 as join_id,
      groupBitmapState(user_id) as id_bitmap_a
    from
      表A
    where
      条件
  ) T1
  inner join (
    select
      1 as join_id,
      groupBitmapState(user_id) as id_bitmap_b
    from
      表B
    where
      条件
  ) T2 on T1.join_id = T2.join_id

---

数据导入

子表导入宽表(@Deprecated)

源表使用HDFS表引擎直接读取hdfs中的子表，目标表建立大宽表使用AggregatingMergeTree表引擎实现子表导入的数据按排序键合并
使用insert into语句执行数据导入
使用optimize语句进行合并，合并前执行SET optimize_throw_if_noop = 1可以看到合并时的异常信息

这里由于Hive中的离线数据本身子表合并大宽表已经非常耗时了，用ClickHouse存储时不必要再浪费时间从离线大宽表

子表A导入结果

Elapsed: 1839.742 sec. Processed 29.87 million rows, 8.33 GB (16.23 thousand rows/s., 4.53 MB/s.)

子表B导入结果

Elapsed: 3731.763 sec. Processed 52.14 million rows, 13.51 GB (13.97 thousand rows/s., 3.62 MB/s.)

执行合并结果

OPTIMIZE TABLE cust_label.cust_label_d_all FINAL
Query id: 08a04d4d-93cc-4386-8cf9-5012072ff984
Ok.
0 rows in set. Elapsed: 0.002 sec.

这里看到只执行了0.002秒，说明并没有真正去合并宽表数据，也没有任何的异常，这个问题导致了我们使用这种子表导入宽表合并数据的方案不可用

子表直接导入

源表使用HDFS表引擎直接读取hdfs中的子表，目标表建立与hdfs表字段相同的子表
实时标签的表使用AggregatingMergeTree表引擎实现新增的数据按排序键合并

子表A导入结果

Elapsed: 172.970 sec. Processed 29.87 million rows, 8.33 GB (172.67 thousand rows/s., 48.18 MB/s.)

子表B导入结果

Elapsed: 306.156 sec. Processed 52.14 million rows, 13.51 GB (170.29 thousand rows/s., 44.12 MB/s.)

执行合并结果

子表A Elapsed: 51.150 sec.
子表B Elapsed: 83.499 sec.