【Hive学习之四】Hive 案例

环境
  虚拟机:VMware 10
  Linux版本:CentOS-6.5-x86_64
  客户端:Xshell4
  FTP:Xftp4
  jdk8
  hadoop-3.1.1
  apache-hive-3.1.1

一、需求:统计出掉线率最高的前10基站
  数据:
    record_time:通话时间
    imei:基站编号
    cell:手机编号
    drop_num:掉话的秒数
    duration:通话持续总秒数

【Hive学习之四】Hive 案例

1、建表

--数据表
create table cell_monitor(
record_time string,
imei string,
cell string,
ph_num string,
call_num string,
drop_num int,
duration int,
drop_rate DOUBLE,
net_type string,
erl string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE; --结果表
create table cell_drop_monitor(
imei string,
total_call_num int,
total_drop_num int,
d_rate DOUBLE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

2、导入数据

LOAD DATA LOCAL INPATH '/root/cdr_summ_imei_cell_info.csv' OVERWRITE INTO TABLE cell_monitor;
#展示前10条
hive> select * from cell_monitor limit 10;
OK
record_time imei cell ph_num call_num NULL NULL NULL net_type erl
-- ::+ - 0.0 G
-- ::+ - 0.0 G
-- ::+ - 0.0 G
-- ::+ - 0.0 G
-- ::+ - 0.0 G
-- ::+ - 0.0 G
-- ::+ - 0.0 G
-- ::+ - 0.0 G
-- ::+ - 0.0 G
Time taken: 0.132 seconds, Fetched: row(s)
hive>

出现NULL 是因为字段类型是非字符串类型,匹配不上 所以显示NULL

3、查询掉线率 倒序排列

from cell_monitor cm
insert overwrite table cell_drop_monitor
select cm.imei,sum(cm.drop_num),sum(cm.duration),sum(cm.drop_num)/sum(cm.duration) d_rate
group by cm.imei
sort by d_rate desc;

【Hive学习之四】Hive 案例

二、使用hive实现wordcount

1、建表

--数据表
create table docs(line string);
--结果表
create table wc(word string, totalword int);
hive> create table docs(line string);
OK
Time taken: 0.722 seconds
hive> create table wc(word string, totalword int);
OK
Time taken: 0.045 seconds

2、导入数据

/root/wc:

hadoop hello world
hello hadoop
hbase zookeeper
name name name

导入数据:

hive> load data local inpath '/root/wc' into table docs;
Loading data to table default.docs
OK
Time taken: 0.392 seconds
hive> select * from docs;
OK
hadoop hello world
hello hadoop
hbase zookeeper
name name name
Time taken: 1.728 seconds, Fetched: 4 row(s)

3、统计

hive> select explode(split(line, ' ')) as word from docs;
OK
hadoop
hello
world
hello
hadoop
hbase
zookeeper
name
name
name
Time taken: 0.377 seconds, Fetched: row(s)
hive>

下面统计语句会产生MR任务:

from (select explode(split(line, ' ')) as word from docs) w
insert into table wc
select word, count() as totalword
group by word
order by word;

【Hive学习之四】Hive 案例

4、查询结果

hive> select * from wc;
OK
hadoop 2
hbase 1
hello 2
name 3
world 1
zookeeper 1
Time taken: 0.121 seconds, Fetched: 6 row(s)
hive>

上一篇:团队作业4——beta冲刺


下一篇:洛谷 P2178 [NOI2015]品酒大会 解题报告