HIVE使用

1.创建表

--txt

create table olt_loid(AREA_ID string,CITY string,LOID string,OLT_IP string,PON_ID string,ONU_ID string)

row format delimited fields terminated by ','

stored as textfile;

create table loid_account(AREA_ID string,CITY string,LINE_ID string,LOID string)

row format delimited fields terminated by ','

stored as textfile;

create table port_flow_20180803(IP string,PORT_CLASS int,IF_POSITION string,IF_SPEED string,IF_IN_OCTETS string,COLLECT_TIME timestamp,qujianflow string)

row format delimited fields terminated by ','

stored as textfile;

create table date201808031(ID string,DEV_ID string,PORT_ID string,IP string,AREA_ID string,BUREAU_ID string,DEV_TYPE_ID string,DEV_SUB_TYPE_ID string,SLOT_NO string,PORT_CLASS int,IF_INDEX string,IF_POSITION string,IF_TYPE string,IF_NAME string,IF_SPEED bigint,IF_PHYS_ADDRESS string,IF_ADMIN_STATUS string,IF_OPER_STATUS string,IF_IN_OCTETS bigint,IF_IN_UCASTPKTS string,IF_IN_NUCASTPKTS string,IF_IN_DISCARDS string,IF_IN_ERRORS string,IF_IN_MULTICASTPKTS string,IF_IN_BROADCASTPKTS string,IF_IN_UNKOWNPROTOS string,IF_OUT_OCTETS bigint,IF_OUT_UCASTPKTS string,IF_OUT_NUCASTPKTS string,IF_OUT_DISCARDS string,IF_OUT_ERRORS string,IF_OUT_MULTICASTPKTS string,IF_OUT_BROADCASTPKTS string,PERCENT_IN_DISCARDS string,PERCENT_IN_ERRORS string,IN_SPEED string,IN_BAND_WIDTH string,PERCENT_IN_BAND_WIDTH string,PERCENT_OUT_DISCARDS string,PERCENT_OUT_ERRORS string,OUT_SPEED string,OUT_BAND_WIDTH string,PERCENT_OUT_BAND_WIDTH string,TOTAL_THUOUGHPUT string,TOTAL_BAND_WIDTH string,PERCENT_TOTAL_BAND_WIDTH string,COLLECT_TIME timestamp,EXECUTE_FLAG int,DISPATCH_TIME timestamp,EMS_ID string,DEV_NAME string,INSERT_TIME timestamp)

row format delimited fields terminated by ','

stored as textfile;

--csv

create table AAA_act_user(date1 string,LINE_ID string,online string,offcount int)

row format serde

'org.apache.hadoop.hive.serde2.OpenCSVSerde'

with

SERDEPROPERTIES

("separatorChar"=",")

STORED AS TEXTFILE;

2.加载数据

load data local inpath '/mnt/data/portflow.txt' into table port_flow_20180803;

load data local inpath '/mnt/data/basedata/olt_loid.txt' into table olt_loid;

load data local inpath '/mnt/data/basedata/loid_account.txt' into table loid_account;

load data local inpath '/mnt/data/basedata/AAA_act_user.csv' into table AAA_act_user;

load data local inpath '/mnt/data/01portflow/20180803.txt' into table date20180803;

load data local inpath '/mnt/data/01portflow/20180803.txt.utf' into table date201808031;

3.

--olt带宽利用率

select max(qujianflow*8/(15*60)/IF_SPEED),IP,IF_POSITION from port_flow_20180803 group by IP,IF_POSITION;

--OLT带宽利用率 取两位小数

select round(max(qujianflow*8/(15*60)/IF_SPEED),2),IP,IF_POSITION from port_flow_20180803 group by IP,IF_POSITION;

--OLT 端口活跃用户数

select count(*),b.OLT_IP,b.PON_ID from (select a.date1,a.LINE_ID,a.online,a.offcount,a.CITY,a.LOID,olt_loid.OLT_IP,olt_loid.PON_ID,olt_loid.ONU_ID from (select AAA_act_user.date1,AAA_act_user.LINE_ID,AAA_act_user.online,AAA_act_user.offcount,loid_account.CITY,loid_account.LOID from AAA_act_user join loid_account on AAA_act_user.LINE_ID=loid_account.LINE_ID ) as a join olt_loid on a.LOID=olt_loid.LOID) as b where b.online='是' group by b.OLT_IP,b.PON_ID;

--OLT带宽利用率、活跃用户数

select f.maxflow,f.IP,u.act_users from (select round(max(qujianflow*8/(15*60)/IF_SPEED),2) maxflow,IP from port_flow_20180803 group by IP) as f join (select count(*) act_users,b.OLT_IP from (select a.date1,a.LINE_ID,a.online,a.offcount,a.CITY,a.LOID,olt_loid.OLT_IP,olt_loid.PON_ID,olt_loid.ONU_ID from (select AAA_act_user.date1,AAA_act_user.LINE_ID,AAA_act_user.online,AAA_act_user.offcount,loid_account.CITY,loid_account.LOID from AAA_act_user join loid_account on AAA_act_user.LINE_ID=loid_account.LINE_ID ) as a join olt_loid on a.LOID=olt_loid.LOID) as b where b.online='是' group by b.OLT_IP) as u on f.IP=U.OLT_IP;

--OLT丢包数

select d.IP,sum(d.discards) from(select IP,IF_POSITION,max(bigint(IF_IN_DISCARDS))-min(bigint(IF_IN_DISCARDS)) discards from date20180803 group by IP,IF_POSITION) as d group by d.IP;







create table result_oltip_devname as select distinct(ip),dev_name from date201808031





select userinfo.corre_point_id,userinfo.loid,userinfo.corre_port_id,obd.name,obd.splitting_ratio,obd.splitter_level,obd.superior_point from userinfo join obd on userinfo.corre_point_id=obd.point_id where splitting_ratio=64





select a.point_id,a.count_loid,obd.name,obd.splitting_ratio,obd.splitter_level,superior_point from(select corre_point_id point_id,count(loid) count_loid from userinfo group by corre_point_id) as a join obd on obd.point_id=a.point_id limit 20;