基于doris实时数仓实时报表生成

基于doris实时数仓实时报表生成

1、流程图

2、自动触发查询

--创建存储过程
CREATE PROCEDURE insetAutoInsert10sProcedure()
BEGIN
INSERT INTO auto_insert_10s(id,create_time,update_time) VALUES (1,NOW(),NOW());
END
;

--创建事件定时器
CREATE EVENT IF NOT EXISTS eventAutoInsert10s
ON SCHEDULE EVERY 10 SECOND
ON COMPLETION PRESERVE
DO CALL insetAutoInsert10sProcedure();

--开始事件
alter event eventAutoInsert10s on completion preserve enable;


--定时删除表中数据
CREATE PROCEDURE deleteAutoInsert10sProcedure()
BEGIN
delete from auto_insert_10s where create_time <= now();
END
;

CREATE EVENT IF NOT EXISTS eventDeleteAutoInsert10s
ON SCHEDULE EVERY 3600 SECOND
ON COMPLETION PRESERVE
DO CALL deleteAutoInsert10sProcedure();

alter event eventDeleteAutoInsert10s on completion preserve enable;


--启动定时器
SET GLOBAL event_scheduler = 1; 

--查看存储状态
SHOW PROCEDURE STATUS LIKE '%deleteAutoInsert10sProcedure%'$$

--查看事件
SHOW EVENTS;

--删除事件
DROP EVENT eventAutoInsert10s;

--停止定时器
SET GLOBAL event_scheduler = 0; 

--删除存储过程
DROP PROCEDURE insetAutoInsert10sProcedure;

--关闭事件
alter event eventAutoInsert10s on completion preserve disable;

ps1.可根据数据查询频率指定mysql定时器频率
ps2.需要全局启动定时器，才可以创建定时任务

3、flink读取binlog自动触发查询

jdbc_StarRocks_agg flink udtf函数，传入查询sql

CREATE TEMPORARY TABLE ods_qts_recommend_auto_insert_10s (
    id varchar
  )
WITH (
    'connector' = 'kafka',
    'format' = 'json',
    'properties.bootstrap.servers' = '',
    'properties.group.id' = 'test',
    'topic' = 'ods_qts_recommend_auto_insert_10s'
  );

create TEMPORARY VIEW view_log_platform_channel_dau
as
select b[1] ds
,b[2] platform
,b[3] platform_name
,b[4] channel
,b[5] channel_name
,b[6] dau
,b[7] new_dau
,b[8] exp_dau
,b[9] active_user
from(SELECT regexp_replace(substr(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,10),'-','') process_date
FROM ods_qts_recommend_auto_insert_10s
) m ,lateral table (jdbc_StarRocks_agg('readJdbc',
      'select 
       ds,
       platform,
       platform_name,
       channel,
       channel_name,
       count(distinct dau) dau,
       count(distinct new_dau) new_dau,
       count(distinct exp_dau) exp_dau,
       count(distinct active_user) active_user  
       from log_platform_channel_dau
       where 1=1
 group by ds,
       platform,
       platform_name,
       channel,
       channel_name'
       ,'ds'
       ,process_date
       )) as T (b)
;

create TEMPORARY VIEW view_sign_platform_channel_total
as
select b[1] ds
,b[2] platform
,b[3] platform_name
,b[4] channel
,b[5] channel_name
,b[6] sign_pv
,b[7] sign_uv
,b[8] total
from(SELECT regexp_replace(substr(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,10),'-','') process_date
FROM ods_qts_recommend_auto_insert_10s
) m ,lateral table (jdbc_StarRocks_agg('readJdbc',
      'select 
       ds,
       platform,
       platform_name,
       channel,
       channel_name,
       sum(pv) sign_pv,
       count(distinct uv) sign_uv,
       sum(total) total
       from sign_platform_channel_total
       where 1=1
 group by ds,
       platform,
       platform_name,
       channel,
       channel_name'
       ,'ds'
       ,process_date
       )) as T (b)
;

4、写入mysql或maxcompute

create TEMPORARY TABLE auto_arpu (
ds string
,platform string
,platform_name string
,channel string
,channel_name string
,dau bigint
,new_dau bigint
,exp_dau bigint
,active_user  bigint
,sign_pv bigint
,sign_uv bigint
,total double
,arpu double
,process_time string
)WITH (
    'connector' = 'odps',
    'endpoint' = '',
    'tunnelEndpoint' = '',
    'project' = '',
    'tablename' = 'auto_arpu',
    'accessid' = '',
    'accesskey' = '',
    'partition' = 'ds'
  );


insert into auto_arpu
select  regexp_replace(m.ds,'-','') ds
,m.platform
,m.platform_name
,m.channel
,m.channel_name
,cast(m.dau as bigint)
,cast(m.new_dau as bigint)
,cast(m.exp_dau as bigint)
,cast(m.active_user as bigint)
,cast(coalesce(n.sign_pv,0) as bigint) sign_pv
,cast(coalesce(n.sign_uv,0) as bigint) sign_uv
,cast(coalesce(n.total,0.0) as double) total
,cast(round(coalesce(n.total,0.0) / m.dau,6) as double) arpu
,process_time
from(select  ds
,platform
,platform_name
,channel
,channel_name
,dau
,new_dau
,exp_dau
,active_user 
,FROM_UNIXTIME(UNIX_TIMESTAMP()) process_time
from view_log_platform_channel_dau
) m left 
join 
(select ds
,platform
,platform_name
,channel
,channel_name
,sign_pv
,sign_uv
,round(total,2) total
from view_sign_platform_channel_total
) n 
on m.ds = n.ds 
and m.platform = n.platform
and m.channel = n.channel

5、报表数据源展示