Flink SQL结合Kafka、Elasticsearch、Kibana实时分析电商用户行为

body { margin: 0 auto; font: 13px / 1 Helvetica, Arial, sans-serif; color: rgba(68, 68, 68, 1); padding: 5px }
h1, h2, h3, h4 { color: rgba(17, 17, 17, 1); font-weight: 400 }
h1, h2, h3, h4, h5, p { margin-bottom: 16px; padding: 0 }
h1 { font-size: 28px }
h2 { font-size: 22px; margin: 20px 0 6px }
h3 { font-size: 21px }
h4 { font-size: 18px }
h5 { font-size: 16px }
a { color: rgba(0, 153, 255, 1); margin: 0; padding: 0; vertical-align: baseline }
a:link, a:visited { text-decoration: none }
a:hover { text-decoration: underline }
ul, ol { padding: 0; margin: 0 }
li { line-height: 24px; margin-left: 30px }
li ul, li ul { margin-left: 24px }
ul, ol { font-size: 14px; line-height: 20px; max-width: 98% }
p { font-size: 14px; line-height: 20px; max-width: 98%; margin-top: 3px }
pre { padding: 0 4px; max-width: 98%; white-space: pre; word-wrap: normal; overflow: auto; font-family: Consolas, Monaco, Andale Mono, monospace; line-height: 1.5; font-size: 13px; border: 1px solid rgba(221, 221, 221, 1); background-color: rgba(247, 247, 247, 1); border-radius: 3px }
code { font-family: Consolas, Monaco, Andale Mono, monospace; line-height: 1.5; font-size: 13px; border: 1px solid rgba(221, 221, 221, 1); background-color: rgba(247, 247, 247, 1); border-radius: 3px }
code pref { color: rgba(255, 0, 0, 1) }
pre code { border: 0 }
aside { display: block; float: right; width: 390px }
blockquote { border-left: 0.5em solid rgba(64, 170, 83, 1); padding: 0 2em; margin-left: 0; max-width: 98% }
blockquote cite { font-size: 14px; line-height: 20px; color: rgba(191, 191, 191, 1) }
blockquote cite:before { content: "— " }
blockquote p { color: rgba(102, 102, 102, 1); max-width: 98% }
hr { height: 1px; border-top: 1px dashed rgba(0, 102, 204, 1); border-right: none; border-bottom: none; border-left: none }
button, input, select, textarea { font-size: 100%; margin: 0; vertical-align: baseline; *vertical-align: middle }
button, input { line-height: normal; *overflow: visible }
{ border: 0; padding: 0 }
button, input[type="button"], input[type="reset"], input[type="submit"] { cursor: pointer; -webkit-appearance: button }
input[type="checkbox"], input[type="radio"] { cursor: pointer }
input:not([type="image"]), textarea { -webkit-box-sizing: content-box; -moz-box-sizing: content-box; box-sizing: content-box }
input[type="search"] { -webkit-appearance: textfield; -webkit-box-sizing: content-box; -moz-box-sizing: content-box; box-sizing: content-box }
{ -webkit-appearance: none }
label, input, select, textarea { font-family: "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 13px; font-weight: normal; line-height: normal; margin-bottom: 18px }
input[type="checkbox"], input[type="radio"] { cursor: pointer; margin-bottom: 0 }
input[type="text"], input[type="password"], textarea, select { display: inline-block; width: 210px; padding: 4px; font-size: 13px; font-weight: normal; line-height: 18px; height: 18px; color: rgba(128, 128, 128, 1); border: 1px solid rgba(204, 204, 204, 1); -webkit-border-radius: 3px; -moz-border-radius: 3px; border-radius: 3px }
select, input[type="file"] { height: 27px; line-height: 27px }
textarea { height: auto }
{ color: rgba(191, 191, 191, 1) }
{ color: rgba(191, 191, 191, 1) }
input[type="text"], input[type="password"], select, textarea { -webkit-transition: border linear 0.2s, box-shadow linear 0.2s; -moz-transition: border linear 0.2s, box-shadow linear 0.2s; transition: border 0.2s linear, box-shadow 0.2s linear; -webkit-box-shadow: inset 0 1px 3px rgba(0, 0, 0, 0.1); -moz-box-shadow: inset 0 1px 3px rgba(0, 0, 0, 0.1); box-shadow: inset 0 1px 3px rgba(0, 0, 0, 0.1) }
input[type="text"]:focus, input[type="password"]:focus, textarea:focus { outline: none; border-color: rgba(82, 168, 236, 0.8); -webkit-box-shadow: inset 0 1px 3px rgba(0, 0, 0, 0.1), 0 0 8px rgba(82, 168, 236, 0.6); -moz-box-shadow: inset 0 1px 3px rgba(0, 0, 0, 0.1), 0 0 8px rgba(82, 168, 236, 0.6); box-shadow: inset 0 1px 3px rgba(0, 0, 0, 0.1), 0 0 8px rgba(82, 168, 236, 0.6) }
button { display: inline-block; padding: 4px 14px; font-family: "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 13px; line-height: 18px; -webkit-border-radius: 4px; -moz-border-radius: 4px; border-radius: 4px; -webkit-box-shadow: inset 0 1px 0 rgba(255, 255, 255, 0.2), 0 1px 2px rgba(0, 0, 0, 0.05); -moz-box-shadow: inset 0 1px 0 rgba(255, 255, 255, 0.2), 0 1px 2px rgba(0, 0, 0, 0.05); box-shadow: inset 0 1px rgba(255, 255, 255, 0.2), 0 1px 2px rgba(0, 0, 0, 0.05); background-color: rgba(0, 100, 205, 1); background-repeat: repeat-x; color: rgba(255, 255, 255, 1); text-shadow: 0 -1px rgba(0, 0, 0, 0.25); border-top: 1px solid rgba(0, 0, 0, 0.1); border-right: 1px solid rgba(0, 0, 0, 0.1); border-bottom: 1px solid rgba(0, 0, 0, 0.25); border-left: 1px solid rgba(0, 0, 0, 0.1); -webkit-transition: 0.1s linear all; -moz-transition: 0.1s linear all; transition: all 0.1s linear }
button:hover { color: rgba(255, 255, 255, 1); background-position: 0 -15px; text-decoration: none }
button:active { -webkit-box-shadow: inset 0 3px 7px rgba(0, 0, 0, 0.15), 0 1px 2px rgba(0, 0, 0, 0.05); -moz-box-shadow: inset 0 3px 7px rgba(0, 0, 0, 0.15), 0 1px 2px rgba(0, 0, 0, 0.05); box-shadow: inset 0 3px 7px rgba(0, 0, 0, 0.15), 0 1px 2px rgba(0, 0, 0, 0.05) }
{ padding: 0; border: 0 }
table { border-spacing: 0; border: 1px solid rgba(204, 204, 204, 1) }
td, th { border: 1px solid rgba(204, 204, 204, 1); padding: 5px }
pre .literal, pre .comment, pre .template_comment, pre .diff .header, pre .javadoc { color: rgba(0, 128, 0, 1) }
pre .keyword, pre .css .rule .keyword, pre .winutils, pre .javascript .title, pre .nginx .title, pre .subst, pre .request, pre .status { color: rgba(0, 0, 255, 1); font-weight: bold }
pre .number, pre .hexcolor, pre .python .decorator, pre .ruby .constant { color: rgba(0, 0, 255, 1) }
pre .string, pre .tag .value, pre .phpdoc, pre .tex .formula { color: rgba(221, 17, 68, 1) }
pre .title, pre .id { color: rgba(153, 0, 0, 1); font-weight: bold }
pre .javascript .title, pre .lisp .title, pre .clojure .title, pre .subst { font-weight: normal }
pre .class .title, pre .haskell .type, pre .vhdl .literal, pre .tex .command { color: rgba(68, 85, 136, 1); font-weight: bold }
pre .tag, pre .tag .title, pre .rules .property, pre .django .tag .keyword { color: rgba(0, 0, 128, 1); font-weight: normal }
pre .attribute, pre .variable, pre .lisp .body { color: rgba(0, 128, 128, 1) }
pre .regexp { color: rgba(0, 153, 38, 1) }
pre .class { color: rgba(68, 85, 136, 1); font-weight: bold }
pre .symbol, pre .ruby .symbol .string, pre .lisp .keyword, pre .tex .special, pre .prompt { color: rgba(153, 0, 115, 1) }
pre .built_in, pre .lisp .title, pre .clojure .built_in { color: rgba(0, 134, 179, 1) }
pre .preprocessor, pre .pi, pre .doctype, pre .shebang, pre .cdata { color: rgba(153, 153, 153, 1); font-weight: bold }
pre .deletion { background: rgba(255, 221, 221, 1) }
pre .addition { background: rgba(221, 255, 221, 1) }
pre .diff .change { background: rgba(0, 134, 179, 1) }
pre .chunk { color: rgba(170, 170, 170, 1) }
pre .markdown .header { color: rgba(136, 0, 0, 1); font-weight: bold }
pre .markdown .blockquote { color: rgba(136, 136, 136, 1) }
pre .markdown .link_label { color: rgba(136, 136, 255, 1) }
pre .markdown .strong { font-weight: bold }
pre .markdown .emphasis { font-style: italic }
pref { color: rgba(255, 0, 0, 1) }

使用Flink SQL结合Kafka、Elasticsearch、Kibana实时分析电商用户行为 (Use flink sql to combine kafka, elasticsearch and kibana, real-time analysis of e-commerce user behavior.)

Flink与其它实时计算工具区别之一是向用户提供了更多抽象易用的API，比如读写各类程序的connector接口、Table API和SQL，从数据加载、计算、一直到输出，所有操作都可以使用SQL完成，大大减少了开发量和维护成本，本文将通过实时分析电商用户行为数据介绍flink sql的使用，分析的内容如下：

1. 分析每10分钟累计在线用户数；
2. 分析每小时购买量；
3. 分析top浏览商品类目(浏览的商品归属于那个类目)；

1 最终实时分析kibana展现效果

2 流程和版本信息

• kafka --> flink --> es -->kibana

数据采集存储到kafka，通过flink消费kafka数据，实时计算，结果存储到es，最后通过kibana展现。

版本信息

flink 1.12.1、kafka_2.13-2.7.0、elasticsearch 7.10.1、kibana 7.10.1

3 数据结构

电商用户行为分析共涉及3个表，商品类目信息表、商品类目信息表、用户行为信息表，其中用户行为信息表共5个列：用户ID、商品ID、商品类目ID、行为类型、时间戳；

4 kafka数据

./kafka-console-consumer.sh --topic user_behavior --bootstrap-server kafka:9092 --from-beginning --max-messages 5

1,2268318,2520377,pv,1511544070
1,2333346,2520771,pv,1511561733

数据来源于淘宝开放的用户行为数据UserBehavior，数据格式为csv，以逗号分隔；

2 使用Flink SQL建表读取kafka数据

现在数据已经存储在kafka，进入flink sql client，

创建消费kafka数据表；

CREATE TABLE user_behavior (
    user_id BIGINT,
    item_id BIGINT,
    category_id BIGINT,
    behavior STRING,
    app_time BIGINT,
    ts AS TO_TIMESTAMP(FROM_UNIXTIME(app_time, 'yyyy-MM-dd HH:mm:ss')),
    proctime AS PROCTIME(),
    WATERMARK FOR ts AS ts - INTERVAL '5' SECOND
) WITH (
    'connector' = 'kafka',  --使用kafka connector
    'topic' = 'user_behavior',  --kafka topic
    'scan.startup.mode' = 'earliest-offset',  --从topic最开始处开始消费
    'properties.bootstrap.servers'='localhost:9092',  --kafka broker地址
    'properties.group.id' = 'test-group03',
    'format' = 'csv',  --存储在kafka的数据格式为csv
    'csv.field-delimiter'=',' --数据分隔符
);

• WATERMARK 定义处理混乱次序的事件时间属性，每5秒触发一次window
• PROCTIME 是内置函数，产生一个虚拟的Processing Time列，偶尔会用到
• WITH 里定义kafka连接信息和属性
• 由于事件时间格式为bigint,在sql中将其转为timestamp

3 分析场景

3.1 场景1：分析每10分钟累计在线用户数

最终的分析结果数据会写入es，首先创建es index和写入es的表；

CREATE TABLE cumulative_uv (
    date_str STRING,
    time_str STRING,
    uv BIGINT,
    PRIMARY KEY (date_str, time_str) NOT ENFORCED
) WITH (
    'connector' = 'elasticsearch-7',
    'hosts' = 'http://localhost:9200',
    'index' = 'cumulative_uv'
);

• WITH 里面定义es连接信息和属性

分析每10分钟在线用户数只需要知道日期(date_str)、时间(time_str)、数量(uv)即可；上面已经定义了消费kafka数据的表 user_behavior，现在查询该表，并将数据写入es；

INSERT INTO
    cumulative_uv
SELECT
    date_str, MAX(time_str), COUNT(DISTINCT user_id) as uv
FROM (
  SELECT
    DATE_FORMAT(ts, 'yyyy-MM-dd') as date_str,
    SUBSTR(DATE_FORMAT(ts, 'HH:mm'),1,4) || '0' as time_str,
    user_id
  FROM user_behavior)
GROUP BY
    date_str;

由于分析跨度为每10分钟，在sql 内层查询中使用 SUBSTR 截取事件小时和分钟字符，拼凑成每10分钟的数据，比如: 12:10，12:20。提交sql后，flink会将sql以流作业方式按照设定的WATERMARK和窗口提交到集群运行；

现在查询kibina可以看到数据已经实时写入.

3.2 场景2：分析每小时购买量

创建es index和写入es的表；

CREATE TABLE buy_cnt_per_hour (
    hour_of_day BIGINT,
    buy_cnt BIGINT
) WITH (
    'connector' = 'elasticsearch-7',
    'hosts' = 'http://localhost:9200',
    'index' = 'buy_cnt_per_hour'
);

查询 user_behavior 表，将数据写入es；

INSERT INTO
    buy_cnt_per_hour
SELECT
    HOUR(TUMBLE_START(ts, INTERVAL '1' HOUR)), COUNT(*)
FROM
    user_behavior
WHERE
    behavior='buy'
GROUP BY
    TUMBLE(ts, INTERVAL '1' HOUR);

• HOUR 为内置函数，从一个 TIMESTAMP 列中提取出一天中第几个小时的值
• TUMBLE 为窗口函数，按设定的时间切窗

首先通过(behavior='buy') 过滤出购买数据，再通过窗口函数(TUMBLE)按一小时切窗，统计出每小时共有多少"buy"的用户行为。

3.3 场景3：分析top浏览商品类目

由于kafka数据存储的是商品id，商品id对应的商品类目名称存储在mysql数据库，需先创建连接mysql的数据表；

CREATE TABLE category (
    category_id BIGINT,
    category_name STRING
) WITH (
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://localhost:3306/test',
    'table-name' = 'category',
    'username' = 'sywu',
    'password' = 'sywu',
    'lookup.cache.max-rows' = '5000',
    'lookup.cache.ttl' = '10min'
);

为了后续查询方便，创建kafka数据表和mysql数据表关联视图；

CREATE VIEW rich_user_behavior
AS
SELECT
    U.user_id, U.item_id, U.behavior, case when C.category_name is null then 'other' else C.category_name end as category_name
FROM
    user_behavior AS U LEFT JOIN category FOR SYSTEM_TIME AS OF U.proctime AS C
ON
    U.category_id = C.category_id;

现在 kafka 数据表和 mysql数据表通过视图表 rich_user_behavior 关联在一起；分析top浏览商品类目只需要知道商品类目名和浏览数即可，所以在此创建一张包含商品类目名和浏览数的表；

CREATE TABLE top_category (
    category_name STRING PRIMARY KEY NOT ENFORCED,
    buy_cnt BIGINT
) WITH (
    'connector' = 'elasticsearch-7',
    'hosts' = 'http://localhost:9200',
    'index' = 'top_category'
);

查询视图表 rich_user_behavior表，过滤分组统计数据；

INSERT INTO
    top_category
SELECT
    category_name, COUNT(*) buy_cnt
FROM
    rich_user_behavior
WHERE
    behavior='buy'
GROUP BY
    category_name;

到此3个分析需求实现，作业正常实时运行。

4 总结

通过Flink 提供的Table API和SQL，以及处理数据的窗口、读写各类程序的connector接口和函数，使用上面的SQL DML操作，flink即实现了用户行为数据的实时分析需求；从开发角度看，代码量和开发难度大大降低；从维护角度看，维护成本也大大降低。

参考文献

• https://ci.apache.org/projects/flink/flink-docs-release-1.12/dev/table/ - Table API & SQL
• https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/sql/queries.html#group-windows - flink group windows
• https://tianchi.aliyun.com/dataset/dataDetail?dataId=649 - taobao UserBehavior