第一步拼接 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