hive-1 hive-1 - 锐客网

##后台nginx日志mpi-web报表生成 use bi; ##set ($suffix = "${env.YYYY}${env.MM}${env.DD}"); ##今日临时表创建 drop table if exists dpstg_mpi_nginx_log_today_${env.YYYY}${env.MM}${env.DD}; create table if not exists dpstg_mpi_nginx_log_today_${env.YYYY}${env.MM}${env.DD} ( server_ip string, url string, times int, avg_response_today int, avg_body_size int, response_line95_today int, count_50x int, count_40x int, count_30x int, count_20x int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\005' COLLECTION ITEMS TERMINATED BY '\002' MAP KEYS TERMINATED BY '\003' LINES TERMINATED BY '\n' STORED AS ORC; ##昨日临时表创建 drop table if exists dpstg_mpi_nginx_log_yesterday_${env.YYYY}${env.MM}${env.DD}; create table if not exists dpstg_mpi_nginx_log_yesterday_${env.YYYY}${env.MM}${env.DD} ( server_ip string, url string, times int, avg_response_today int, avg_body_size int, response_line95_today int, count_50x int, count_40x int, count_30x int, count_20x int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\005' COLLECTION ITEMS TERMINATED BY '\002' MAP KEYS TERMINATED BY '\003' LINES TERMINATED BY '\n' STORED AS ORC; ##今日报表生成 insert into table dpstg_mpi_nginx_log_today_${env.YYYY}${env.MM}${env.DD} select server_ip, concat(parse_url(url,'HOST'), regexp_replace(parse_url(url, 'PATH'),'[0-9|\+|\*]','')) as url_s, count(url) as times, round(avg(response_time)*1000) as avg_response_today, round(avg(body_bytes_sent)) as avg_body_size, round(percentile(round(response_time*1000),0.95)) as response_line95_today, sum(if(status like '50%' ,1 ,0)) as count_50x, sum(if(status like '40%' ,1 ,0)) as count_40x, sum(if(status like '30%' ,1 ,0)) as count_30x, sum(if(status like '20%' ,1 ,0)) as count_20x from dpdw_nginx_log_all where application = 'mpi-web' and hp_stat_date='${env.YYYYMMDD}' and parse_url(url, 'PATH')<>'/index.jsp' and parse_url(url, 'PATH')<>'/heartbeat.html' and parse_url(url, 'HOST')<>'dp' and parse_url(url, 'HOST')<>'www.dianping.com' group by concat(parse_url(url,'HOST'), regexp_replace(parse_url(url, 'PATH'),'[0-9|\+|\*]','')),server_ip,application order by times desc ; ##昨日报表生成 insert into table dpstg_mpi_nginx_log_yesterday_${env.YYYY}${env.MM}${env.DD} select server_ip as server_ip, concat(parse_url(url,'HOST'), regexp_replace(parse_url(url, 'PATH'),'[0-9|\+|\*]','')) as url, count(url) as times, round(avg(response_time)*1000) as avg_response_today, round(avg(body_bytes_sent)) as avg_body_size, round(percentile(round(response_time*1000),0.95)) as response_line95_today, sum(if(status like '50%' ,1 ,0)) as count_50x, sum(if(status like '40%' ,1 ,0)) as count_40x, sum(if(status like '30%' ,1 ,0)) as count_30x, sum(if(status like '20%' ,1 ,0)) as count_20x from dpdw_nginx_log_all where application = 'mpi-web' and hp_stat_date='${env.YYYYMMDD_P1D}' and parse_url(url, 'PATH')<>'/index.jsp' and parse_url(url, 'PATH')<>'/heartbeat.html' and parse_url(url, 'HOST')<>'dp' and parse_url(url, 'HOST')<>'www.dianping.com' group by concat(parse_url(url,'HOST'), regexp_replace(parse_url(url, 'PATH'),'[0-9|\+|\*]','')),server_ip,application order by times desc ; insert overwrite table dprpt_mpi_nginx_log_daily partition(hp_cal_dt = '${env.YYYYMMDD}') select t.server_ip as server_ip, t.url as url, t.times as times, coalesce(y.times,0) as times_cmp, t.avg_response_today as avg_response_today, coalesce(y.avg_response_today,0) as avg_response_cmp, t.response_line95_today as response_line95_today, coalesce(y.response_line95_today,0) as response_line95_today_cmp, t.avg_body_size as avg_body_size, t.count_20x as count_20x, coalesce(y.count_20x,0) as count_20x_cmp, t.count_30x as count_30x, coalesce(y.count_30x,0) as count_30x_cmp, t.count_40x as count_40x, coalesce(y.count_40x,0) as count_40x_cmp, t.count_50x as count_50x, coalesce(y.count_50x,0) as count_50x_cmp from dpstg_mpi_nginx_log_today_${env.YYYY}${env.MM}${env.DD} t left outer join dpstg_mpi_nginx_log_yesterday_${env.YYYY}${env.MM}${env.DD} y on t.url=y.url and t.server_ip=y.server_ip order by times desc; drop table if exists dpstg_mpi_nginx_log_today_${env.YYYY}${env.MM}${env.DD}; drop table if exists dpstg_mpi_nginx_log_yesterday_${env.YYYY}${env.MM}${env.DD};

hive-1

推荐阅读

农业电商企业有哪些成功的农业电商企业是什么样子，成功的农业电商企业是什么样子的

大金中央空调面板显示扳手怎么办,这些问题会马上呈现

妈妈的弟弟的女儿叫什么

投资干股是什么意思什么是干股，公司原始股如何分配

你的中医入门了吗？医案分析简单易学

香椿怎么吃

春幡春胜是什么意思

scp秘密实验室怎么调中文

市场上最便宜的淡水鱼，最便宜的淡水鱼是什么鱼

怎样切洋葱可以避免泪流满面

羽绒服是白鹅绒好还是白鸭绒好

2018-03-22|2018-03-22 今天关键词（聪明思考，愚笨做事晨间小记第435天）

如何辨别洗脸巾是否有荧光剂

牡丹花养护管理方法牡丹花养殖技巧

开发小孩智力的动画片有哪些

要起球的呢大衣是什么面料

科目二什么时候开始计时

如何注册品牌名称和商标注册品牌需要什么手续，怎么申请品牌,注册商标

etc注销不了怎么办找谁 etc注销不了怎么办

如何进行云服务器授权？云服务器怎么授权