StrutsStreaming实时cube：5个维度

 

第一步拼接 all#字段  
select 
  to_utc_timestamp(exec_time, 'PDT') as ftime, 
  substr(exec_time, 0, 10) as Fexec_date, 
  substr(exec_time, 12, 5) as Fexec_hhmm, 
  concat('ALL', '#', headers_host) as Fserver_ip, 
  concat('ALL', '#', Finterface) as Finterface, 
  concat('ALL', '#', Fmethod_name) as Fmethod_name, 
  concat(
    'ALL', 
    '#', 
    concat(Fset1, "->", Fset2)
  ) as Fset, 
  concat('ALL', '#', Fenv) as Fenv, 
  Fcall_num, 
  Fcall_num * Fcall_time as Fcall_time, 
  Fmax_call_time, 
  cast(
    case Ferr_code when "0" then cast(0 as double) else cast(Fcall_num as double) end as double
  ) as Ferr_num 
from 
  t_root_consumer 
where 
  headers_host != "" 
  and tail1 is null 
  and tail2 is null
  
  第二步 将数据进行拆分 1行边2行
  select 
  ftime, 
  Fexec_date, 
  Fexec_hhmm, 
  tp_Fserver_ip, 
  tp_Finterface, 
  tp_Fmethod_name, 
  tp_Fset, 
  tp_Fenv, 
  Fcall_num, 
  Fcall_time, 
  cast (Fmax_call_time as bigint) as Fmax_call_time, 
  Ferr_num 
from 
  t_aggregate_consumer lateral view explode(
    split(Fserver_ip, '#')
  ) tmptip as tp_Fserver_ip lateral view explode(
    split(Finterface, '#')
  ) tmptif as tp_Finterface lateral view explode(
    split(Fmethod_name, '#')
  ) tmptmethod as tp_Fmethod_name lateral view explode(
    split(Fset, '#')
  ) tmptfset as tp_Fset lateral view explode(
    split(Fenv, '#')
  ) tmptfset as tp_Fenv
  
  第三步：将数据进行分组
  select 
  Fexec_date, 
  Fexec_hhmm, 
  tp_Fserver_ip, 
  tp_Finterface, 
  tp_Fmethod_name, 
  tp_Fset, 
  tp_Fenv, 
  sum(Fcall_num) as Fcall_num, 
  sum(Fcall_time)/ sum(Fcall_num) as Fcall_time, 
  max(Fmax_call_time) as Fmax_call_time, 
  sum(Ferr_num) as Ferr_num 
from 
  t_cube 
group by 
  Fexec_date, 
  Fexec_hhmm, 
  tp_Fserver_ip, 
  tp_Finterface, 
  tp_Fmethod_name, 
  tp_Fset, 
  tp_Fenv
  
  第四步，将数据转换如phoenix
  select 
  Fexec_date AS FEXEC_DATE, 
  Fexec_hhmm AS FEXEC_HHMM, 
  tp_Fserver_ip AS FSERVER_IP, 
  tp_Finterface AS FINTERFACE, 
  tp_Fmethod_name AS FMETHOD_NAME, 
  tp_Fset AS FSET, 
  tp_Fenv AS FENV, 
  Fcall_num AS FCALL_NUM, 
  Fcall_time AS FCALL_TIME, 
  Fmax_call_time AS FMAX_CALL_TIME, 
  Ferr_num AS FERR_NUM 
from 
  t_bf
  最后一步：将数据入库phoenix
  
  求所有数据的总和：
  第一步转换成all
  select 
  to_utc_timestamp(exec_time, 'PDT') as ftime, 
  substr(exec_time, 0, 10) as Fexec_date, 
  substr(exec_time, 12, 5) as Fexec_hhmm, 
  'ALL' as Fserver_ip, 
  'ALL' as Finterface, 
  'ALL' as Fmethod_name, 
  'ALL' as Fset, 
  'ALL' as Fenv, 
  Fcall_num, 
  Fcall_num * Fcall_time as Fcall_time, 
  cast (Fmax_call_time as bigint) as Fmax_call_time, 
  cast(
    case Ferr_code when "0" then cast(0 as double) else cast(Fcall_num as double) end as double
  ) as Ferr_num 
from 
  t_root_consumer 
where 
  headers_host != "" 
  and tail1 is null 
  and tail2 is null
  第二步 将数据进行分组
  select 
  Fexec_date, 
  Fexec_hhmm, 
  Fserver_ip, 
  Finterface, 
  Fmethod_name, 
  Fset, 
  Fenv, 
  sum(Fcall_num) as Fcall_num, 
  sum(Fcall_time)/ sum(Fcall_num) as Fcall_time, 
  max(Fmax_call_time) as Fmax_call_time, 
  sum(Ferr_num) as Ferr_num 
from 
  t_aggregate_consumer 
group by 
  Fexec_date, 
  Fexec_hhmm, 
  Fserver_ip, 
  Finterface, 
  Fmethod_name, 
  Fset, 
  Fenv
  第三步，将数据转换如mysql
  
  select 
  Fexec_date as FEXEC_DATE, 
  Fexec_hhmm as FEXEC_HHMM, 
  Fcall_num as FCALL_NUM, 
  Fcall_time as FCALL_TIME, 
  Fmax_call_time as FMAX_CALL_TIME, 
  Ferr_num as FERR_NUM, 
  now() as Fmodify_time, 
  1 as Fversion 
from 
  t_bf