阿里云案例_保单金额实时统计_Flink+Datahub+Hologres

最新推荐文章于 2024-04-15 03:58:22 发布

vthinkwen

最新推荐文章于 2024-04-15 03:58:22 发布

阅读量1k

点赞数 1

文章标签：阿里云 flink kafka

原文链接：https://workbench.data.aliyun.com/experience_new.htm#/detail/6

版权

**情景:**某公司为保险业务构建金融类平台，该公司在平台中提供保险产品售卖与保单管理工具。平台中使用Flink实现续保客户全方位精细化管理，全流程周期跟踪。
使用

消息队列 Kafka 版
实时计算 Flink 版
交互式分析 Hologres

架构: 在这里插入图片描述
数据结构:
renew_track：续保任务跟踪表
call_log ：通话记录表

整个流程在阿里云上完成
Kafka中配置数据源表

登录Kafka控制台，选择对应region下的kafka实例
在topic管理页面创建topic：renew_track，call_logs
在consumer group页面新建【fin_group】的消费者组

Hologres中配置结果表

进入Hologres控制台，点击Hologres实例，在DB管理中新增数据库mydb
登陆Hologres数据库，在SQL编辑器页面新建SQL查询
Hologres中创建结果表 SQL语句

  CREATE TABLE renew_track_cnt(
     task_id BIGINT,
     nature_day VARCHAR ,
     task_owner VARCHAR , 
     is_quote BIGINT,
     is_message BIGINT,
     is_continue BIGINT,
     is_success BIGINT,
     is_failed BIGINT,
     is_called BIGINT,
     PRIMARY KEY(task_id,task_owner,nature_day)
  );

注册Schema

首先登陆flink控制台，选择集群所在region，点击开发控制台，进入开发界面
在SQL编辑器中选择新建文本，开始写flinkSQL语句，首先需要创建DDL语句，对数据上下游存储做一个引用，一个DDL语句创建完需要点击右上角的运行，生成schema

在这里插入图片描述

注册source的schema

--创建续保跟踪表
create table renew_track (
    track_id bigint COMMENT '跟踪ID',
    task_id bigint COMMENT '任务ID',
    create_time STRING COMMENT '创建时间',
    task_owner STRING COMMENT '任务持有人',
    track_action STRING COMMENT '跟踪动作',
    track_result STRING COMMENT '跟踪结果'
  ) with (
  'connector' = 'kafka',
    'topic' = 'renew_track',
    'properties.bootstrap.servers' = '<ip1:port1,ip2:port2,ip3:port3>',
    'properties.group.id' = 'fin_group',
    'scan.startup.mode' = 'earliest-offset',
    'format' = 'json'
  );

--通话记录表
create table call_log (
    call_id bigint COMMENT '通话ID',
    business_no bigint COMMENT '业务号',
    create_time STRING COMMENT '创建时间',
    caller_number STRING COMMENT '主叫号码',
    called_nimber STRING COMMENT '被叫号码',
    status STRING COMMENT '通话状态',
    call_duration double COMMENT '通话时长（s）'
  ) with (
  'connector' = 'kafka',
    'topic' = 'call_logs',
    'properties.bootstrap.servers' = '<ip1:port1,ip2:port2,ip3:port3>',
    'properties.group.id' = 'fin_group',
    'scan.startup.mode' = 'earliest-offset',
    'format' = 'json'
  );

注册Sink的schema

--创建结果表
create table renew_track_cnt (
    task_id BIGINT COMMENT '任务ID',
    nature_day STRING COMMENT '自然日',
    task_owner STRING COMMENT '任务持有人',
    is_quote BIGINT COMMENT '报价数',
    is_message BIGINT COMMENT '短信量',
    is_continue BIGINT COMMENT '任务跟进数',
    is_success BIGINT COMMENT '成功任务数',
    is_failed BIGINT COMMENT '失败任务数',
    is_called BIGINT COMMENT '通话量'
  ) with (
    'connector' = 'hologres',
    'dbname' = 'mydb',
    'endpoint' = '<（VPC地址）xxxxxxx:80>',
    'tablename' = 'renew_track_cnt',
    'password' = '<AccessKey Secret>',
    'username' = '<AccessKey ID>',
    'mutateType' = 'insertorupdate'
  );

说明
当mutateType设置为insertorupdate或insertorreplace时，系统根据主键更新数据。
Flink全托管定义的结果表中的数据列数不一定要和Hologres物理表的列数一致，您需要保证缺失的列没有非空约束，即列值可以为Null，否则会报错。

创建Flink作业
本场景用于计算每天跟踪专员跟进续保任务通话量、短信量以及成功任务量等。
用户投保会生成一份保单，在保险即将过期之前会生成一张续保任务单，任务单内容包括任务ID，跟踪专员ID，跟进动作，跟进结果等。Flink实时读取任务单信息，用where过滤出最新跟进结果（数据过滤），然后根据任务ID和日期做分组获取每个专员每天的任务跟进情况，最后join上从第三方获取的电话通话记录表，按照任务创建时间聚合统计每天专员跟进任务量。

在SQL编辑器中创建如下Flink SQL作业

--先创建临时视图，计算续保专员每天报价任务和发短信的数量
create TEMPORARY view tmp_track_1 as
      select task_id, task_owner, substring(create_time,1,10) as nature_day, 
      sum (case when track_action = 'QUOTE' then 1 else 0 end ) as is_quote, -- 报价
      sum (case when track_action = 'MESSAGE' then 1 else 0 end) as is_message -- 短信
      from renew_track
     group by task_id, task_owner, substring(create_time,1,10)
 ;
--创建临时视图，计算每天每个任务的最新进展   
create TEMPORARY view tmp_track_2 as  
      select task_id, task_owner, nature_day, 
      case when track_result = 'TRACKING' then 1 else 0 end as is_continue, -- 继续跟进     跟进结果
      case when track_result = 'SUCCESS' then 1 else 0 end as is_success, -- 成功
      case when track_result = 'FAILED' then 1 else 0  end as is_failed -- 战败
      from (
          select track_id, task_id, task_owner, substring(create_time,1,10) as nature_day, track_result, row_number () over (
              partition by task_id,substring(create_time,1,10)
              order by create_time desc
            ) rn
            from renew_track
        ) tt
        where tt.rn = 1
;
--创建临时视图，对通话记录进行过滤，计算每天每个任务的成功通话量
create TEMPORARY view tmp_track_3 as
select task_id,nature_day,count(is_called) as is_called
from
(
  select business_no as task_id, substring(create_time,1,10) as nature_day, 1 as is_called
            from call_log
            where status = 'SUCCESS' and call_duration > 0 and business_no is not null
)
group by task_id,nature_day
;
--最后通过join计算每个跟踪专员每天的任务量
insert into renew_track_cnt
select task_id,nature_day,task_owner,
sum (is_quote) as quote_task_cnt, --报价车辆数(任务数)
sum (is_continue) as continue_task_cnt, --继续跟进(任务数)
sum (is_success) as success_task_cnt, --成功(任务数)
sum (is_failed) as failed_task_cnt, --战败(任务数)
sum (is_called) as called_task_cnt, --通话量(任务数)
sum (is_message) as message_task_cnt --短信量(任务数)
from(
 select t1.task_id, t1.task_owner, t1.nature_day,t1.is_quote,t1.is_message,t2.is_continue,t2.is_success,t2.is_failed,t3.is_called
 from tmp_track_1 as t1
 left join
 tmp_track_2 as t2 on  t1.task_id=t2.task_id and t1.nature_day=t2.nature_day
 left join
 tmp_track_3 as t3 on t1.task_id = t3.task_id and t1.nature_day = t3.nature_day
)tt
group by task_id,nature_day,task_owner
 ;

点击运行，会提示是否创建SQL作业，点击确定
定义作业名称，点击右下角的创建作业完成创建
在作业列表中找到刚刚生成的作业，点开作业，选择右上角的启动，等待作业运行，当作业标识变为running，作业运行成功。

测试数据
返回Kafka控制台，【topic管理】中向kafka topic中逐条发送数据
renew_track:

{"track_id":1003,"task_id":101,"create_time":"2020-11-10 02:08:33","task_owner":"Aliy","track_action":"MESSAGE","track_result":"TRACKING"}
{"track_id":1006,"task_id":101,"create_time":"2020-11-10 05:08:33","task_owner":"Aliy","track_action":"QUOTE","track_result":"SUCCESS"}
{"track_id":1007,"task_id":102,"create_time":"2020-11-10 06:08:33","task_owner":"Aliy","track_action":"MESSAGE","track_result":"TRACKING"}
{"track_id":1006,"task_id":101,"create_time":"2020-11-10 05:08:33","task_owner":"Aliy","track_action":"QUOTE","track_result":"SUCCESS"}

call_logs:

{"call_id":1001,"business_no":101,"create_time":"2020-11-10 01:08:33","caller_number":"18347890123","called_number":"12435567554","status":"SUCCESS","call_duration":12.3}
{"call_id":1002,"business_no":102,"create_time":"2020-11-10 02:08:33","caller_number":"18347890123","called_number":"12435567554","status":"SUCCESS","call_duration":12.3}
{"call_id":1005,"business_no":102,"create_time":"2020-11-10 05:08:33","caller_number":"18347890123","called_number":"12435567554","status":"SUCCESS","call_duration":12.3}
{"call_id":1006,"business_no":101,"create_time":"2020-11-10 06:08:33","caller_number":"18347890123","called_number":"12435567554","status":"SUCCESS","call_duration":12.3}

登陆Hologres数据库，在连接管理中找到我的数据库mydb，选择想要查询的表renew_track_test，点击数据预览即可查看每个跟踪专员每天的工作业绩。
在这里插入图片描述

vthinkwen

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
阿里云案例_保单金额实时统计_Flink+Datahub+Hologres

**情景:**某公司为保险业务构建金融类平台，该公司在平台中提供保险产品售卖与保单管理工具。平台中使用Flink实现续保客户全方位精细化管理，全流程周期跟踪。使用消息队列 Kafka 版实时计算 Flink 版交互式分析 Hologres架构:数据结构:renew_track：续保任务跟踪表call_log ：通话记录表整个流程在阿里云上完成Kafka中配置数据源表登录Kafka控制台，选择对应region下的kafka实例在topic管理页面创建topic：renew_tr
复制链接

扫一扫