Flink SQL結合Kafka、Elasticsearch、Kibana實時分析電商使用者行為

堅持╅信念★發表於2021-01-16
SQLKafkaElasticsearch

使用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即實現了使用者行為資料的實時分析需求；從開發角度看，程式碼量和開發難度大大降低；從維護角度看，維護成本也大大降低。

參考文獻

相關文章