Flink实时数仓：MySQL 关联 HBase维表数据到写入ClickHouse

本文链接：https://blog.csdn.net/qq_22473611/article/details/119575041

一、介绍

本文介绍了结合 MySQL 数据库、Flink、HBase 以及clickhouse来构建实时数仓，并通过流计算 Flink读取 MySQL 数据、关联 HBase 中的维表，最终将数据存入clickhouse进行指标分析，实现完整实时数仓的全流程操作指导。

1.2 需求梳理

当然实际需求还会有更多，这里主要以为可视化大屏为目的进行实时计算的处理

二 DWS 层的定位是什么

➢ 轻度聚合，因为 DWS 层要应对很多实时查询，如果是完全的明细那么查询的压力是非常大的。
➢ 将更多的实时数据以主题的方式组合起来便于管理，同时也能减少维度查询的次数。

2 DWS 层-访客主题宽表的计算

设计一张 DWS 层的表其实就两件事：维度和度量(事实数据)

➢ 度量包括 PV、UV、跳出次数、进入页面数(session_count)、连续访问时长
➢ 维度包括在分析中比较重要的几个字段：渠道、地区、版本、新老用户进行聚合

2.1 需求分析与思路

➢ 接收各个明细数据，变为数据流
➢ 把数据流合并在一起，成为一个相同格式对象的数据流
➢ 对合并的流进行聚合，聚合的时间窗口决定了数据的时效性
➢ 把聚合结果写在数据库中

2.2 功能实现

3 DWS 层-商品主题宽表的计算

3.1 需求分析与思路

➢ 从 Kafka 主题中获得数据流
➢ 把 Json 字符串数据流转换为统一数据对象的数据流
➢ 把统一的数据结构流合并为一个流
➢ 设定事件时间与水位线
➢ 分组、开窗、聚合
➢ 写入 ClickHouse

4 DWS 层-地区主题表(FlinkSQL)

地区主题主要是反映各个地区的销售情况。从业务逻辑上地区主题比起商品更加简单，
业务逻辑也没有什么特别的就是做一次轻度聚合然后保存，所以在这里我们体验一下使用
flinkSQL，来完成该业务。

4.1 需求分析与思路

➢ 定义 Table 流环境
➢ 把数据源定义为动态表
➢ 通过 SQL 查询出结果表
➢ 把结果表转换为数据流
➢ 把数据流写入目标数据库
如果是Flink官方支持的数据库，也可以直接把目标数据表定义为动态表，用insert into
写入。由于ClickHouse目前官方没有支持的jdbc连接器（目前支持Mysql、 PostgreSQL、
Derby）。也可以制作自定义 sink，实现官方不支持的连接器。但是比较繁琐。

三写入 OLAP 数据库

为何要写入 ClickHouse 数据库，ClickHouse 数据库作为专门解决大量数据统计分析
的数据库，在保证了海量数据存储的能力，同时又兼顾了响应速度。而且还支持标准 SQL，
即灵活又易上手

1) ClickHouse 数据表准备

create table visitor_stats_2021 (
	stt DateTime,
	edt DateTime,
	vc String,
	ch String ,
	ar String ,
	is_new String ,
	uv_ct UInt64,
	pv_ct UInt64,
	sv_ct UInt64,
	uj_ct UInt64,
	dur_sum UInt64,
	ts UInt64
) engine =ReplacingMergeTree( ts)
partition by toYYYYMMDD(stt)
order by ( stt,edt,is_new,vc,ch,ar);

实现：使用 MySQL 关联 HBase 维表数据到 ClickHouse

一数据准备

1、mysql数据准备

-- 创建数据库
CREATE DATABASE mysqltestdb;

-- 在新建的数据库上新建表 student
CREATE TABLE `student` (
  `id`          int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name`        varchar(10) COLLATE utf8mb4_bin DEFAULT '' COMMENT '名字',
  `age`         int(11) DEFAULT NULL COMMENT '年龄',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '数据创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT COMMENT='学生表'

-- student 表中插入数据
 INSERT INTO mysqltestdb.student(id,name,age) VALUES(1,"xiaomin",20);

Hbase 表。

# 进入HBase命令
root@172~# hbase shell

-- 建表语句
  create 'dim_hbase', 'cf'

-- 插入数据
  put 'dim_hbase','1','cf:school_name','MingDeSchool'

ClickHouse

-- 登录客户端示例如下，请将 xx.xx.xx.xx 替换为用户 CK 的 IP 地址
clickhouse-client -hxx.xx.xx.xx --port 9000 -user xx -password xx

-- 新建数据库：
CREATE DATABASE testdb ON cluster default_cluster;

-- 新建表：
CREATE TABLE testdb.student_school ON cluster default_cluster (
`stu_id`          Int32,
`stu_name`        Nullable(String),
`school_name`     Nullable(String),
`Sign`            Int8
) ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/{layer}-{shard}/testdb/student_school', '{replica}', Sign) ORDER BY stu_id;

二、数据清洗和运算加工、创建 Flink SQL 作业

数据准备

按照上面的操作创建表，并向 MySQL 和 HBase 表中插入数据。

创建 Flink SQL 作业

在流计算 Oceanus 控制台创建 SQL 作业。

Source 端

MySQL-CDC Source：

--学生信息作为cdc源表
CREATE TABLE `student` (
  `id`     INT NOT NULL,
  `name`   VARCHAR,
  `age`    INT,
  proc_time AS PROCTIME(),
  PRIMARY KEY (`id`) NOT ENFORCED
) WITH (
  'connector' = 'mysql-cdc',
  'hostname' = 'YoursIp',
  'port' = '3306',
  'username' = '用户名',
  'password' = 'YoursPassword',
  'database-name' = 'mysqltestdb',
  'table-name' = 'student'
);

HBase 维表：

--示例使用school学校信息作为维表
CREATE TABLE dim_hbase (
  rowkey  STRING,
  cf      ROW <school_name STRING>,  -- 如果有多个列簇，写法 cf Row<age INT,name String>
  PRIMARY KEY (rowkey) NOT ENFORCED
) WITH (
  'connector' = 'hbase-1.4',
  'table-name' = 'dim_hbase',
  'zookeeper.quorum' = '用户自己的hbase服务器zookeeper地址，多个用逗号隔开'
);

Sink 端

创建到 ClickHouse 的创建表语句。

--关联后存入clickhouse表
CREATE TABLE `student_school` (
  stu_id       INT,
  stu_name     STRING,
  school_name  STRING,
  PRIMARY KEY (`stu_id`) NOT ENFORCED
) WITH (
  'connector' = 'clickhouse',
  'url' = 'clickhouse://yourIP:8123',
  -- 如果ClickHouse集群未配置账号密码可以不指定
  --'username' = 'root',
  --'password' = 'root',
  'database-name' = 'testdb',
  'table-name' = 'student_school',
  'table.collapsing.field' = 'Sign'
);

进行逻辑运算

此示例中，只进行了简单的 Join 没有进行复杂的运算。详细运算逻辑可参见 Oceanus 运算符和内置函数或者 Flink 官网 Flink SQL 开发。

INSERT INTO
  student_school
SELECT
  student.id   AS stu_id,
  student.name AS stu_name,
  dim_hbase.cf.school_name
FROM
  student
  JOIN dim_hbase for SYSTEM_TIME AS OF student.proc_time
    ON CAST(student.id AS STRING) = dim_hbase.rowkey;