1.hive 自定义udf或hive自带函数,解析xml。
udf
insert overwrite table shenxinfu2 partition(dt='20170711') select xmlpath(s.shenxinfu_result) as url,s.area from shenxfdb.action as s where s.dt='20170711';
自带
insert overwrite table shenxinfu2 partition(dt='20170711') select xpath_string(s.shenxinfu_result) as url,s.area from shenxfdb.action as s where s.dt='20170711';
insert into table applog.union_mac select
s.dt,lower(concat_ws('.',substr(regexp_replace(s.user_mac,':','' ) ,0,4) ,substr(regexp_replace(s.user_mac,':','' ) ,5,4)
,substr(regexp_replace(s.user_mac,':','' ) ,9,4))), s.area from applog.trapsource as s where s.user_mac <> 'blank' and s.dt = '20170714';
insert into table applog.union_mac select substr(s.riqi , 1 , 8),s.sta_mac, s.area from applog.syslog as s
where s.sta_mac!='blank' and substr(s.riqi , 1 , 8)='20170714' and s.subtype='2' or s.sta_mac!='blank' and substr(s.riqi , 1 , 8)='20170714' and s.subtype='3';
select area,count(distinct mac) from applog.union_mac where time='20170714' group by area;