Hive案例
2019-01-23 本文已影响0人
上杉丶零
一、基站掉话率统计
- 需求分析
统计基站掉话率，找出掉话率最高的前10个基站 - 输入样例
record_time,imei,cell,ph_num,call_num,drop_num,duration,drop_rate,net_type,erl
2011-07-13 00:00:00+08,356966,29448-37062,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,352024,29448-51331,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,353736,29448-51331,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,353736,29448-51333,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,351545,29448-51333,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,353736,29448-51343,1,0,0,8,0,G,0
2011-07-13 00:00:00+08,359681,29448-51462,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,354707,29448-51462,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,356137,29448-51470,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,352739,29448-51971,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,354154,29448-51971,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,127580,29448-51971,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,354264,29448-51973,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,354733,29448-51973,1,0,0,36,0,G,0
2011-07-13 00:00:00+08,356807,29448-51973,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,125470,29448-51973,1,0,0,13,0,G,0
2011-07-13 00:00:00+08,353530,29448-52061,1,0,0,46,0,G,0
2011-07-13 00:00:00+08,352417,29448-5231,1,0,0,2,0,G,0
2011-07-13 00:00:00+08,353419,29448-5231,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,306416,29448-5231,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,356208,29448-5233,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,357238,29448-5233,1,0,0,21,0,G,0
2011-07-13 00:00:00+08,354154,29448-52541,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,358662,29448-53050,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,357470,29448-53523,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,354555,29448-53523,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,864301,29448-53871,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,357727,29448-53871,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,356049,29448-53871,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,356569,29448-54853,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,353257,29448-54874,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,355287,29448-55671,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,358675,29448-55672,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,358212,29448-55672,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,358086,29448-55672,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,125470,29448-55672,1,0,0,27,0,G,0
2011-07-13 00:00:00+08,865524,29448-55813,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,863149,29448-55823,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,860139,29448-55823,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,352983,29448-55823,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,355383,29448-5613,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,865653,29448-5642,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,358306,29448-5642,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,356918,29448-5642,1,0,0,26,0,G,0
2011-07-13 00:00:00+08,355682,29448-5642,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,863056,29448-5642,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,356847,29448-5642,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,359367,29448-5642,1,0,0,144,0,G,0
2011-07-13 00:00:00+08,354826,29448-57671,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,358713,29448-57672,1,0,0,164,0,G,0
2011-07-13 00:00:00+08,355352,29448-57681,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,863864,29448-57681,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,351523,29448-57681,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,860139,29448-57682,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,353477,29448-57682,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,356752,29448-57682,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,357236,29448-57682,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,354599,29448-57952,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,351761,29464-10001,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,356988,29464-10003,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,358407,29464-10043,1,2,2,4977,100,G,1
2011-07-13 00:00:00+08,352559,29464-10051,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,352012,29464-10051,1,0,0,15,0,G,0
2011-07-13 00:00:00+08,356977,29464-10051,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,121530,29464-10051,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,354890,29464-10051,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,355944,29464-10051,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,353709,29464-10051,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,122910,29464-10051,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,351761,29464-10051,1,0,0,128,0,G,0
2011-07-13 00:00:00+08,355075,29464-10051,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,356858,29464-10051,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,351662,29464-10051,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,353199,29464-10051,1,0,0,16,0,G,0
2011-07-13 00:00:00+08,354330,29464-10052,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,355398,29464-10052,1,0,0,1,0,G,0
2011-07-13 00:00:00+08,358086,29464-10053,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,864770,29464-10053,1,0,0,56,0,G,0
2011-07-13 00:00:00+08,122700,29464-10053,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,120360,29464-10053,1,0,0,15,0,G,0
2011-07-13 00:00:00+08,359440,29464-10071,1,0,0,14,0,G,0
2011-07-13 00:00:00+08,356807,29464-10071,1,0,0,15,0,G,0
2011-07-13 00:00:00+08,125500,29464-10072,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,354568,29464-10072,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,353100,29464-10082,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,357232,29464-10162,1,0,0,19,0,G,0
2011-07-13 00:00:00+08,354200,29464-10163,1,0,0,530,0,G,0
2011-07-13 00:00:00+08,357507,29464-10232,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,356226,29464-10232,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,125330,29464-10232,1,0,0,49,0,G,0
2011-07-13 00:00:00+08,125400,29464-10232,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,357386,29464-10232,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,352438,29464-10232,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,355053,29464-10232,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,866810,29464-10232,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,125500,29464-10232,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,355177,29464-10232,1,0,0,19,0,G,0
2011-07-13 00:00:00+08,862880,29464-10232,1,0,0,24,0,G,0
2011-07-13 00:00:00+08,354851,29464-10232,0,0,0,0,0,G,0
2011-07-13 00:00:00+08,357664,29464-10232,0,0,0,0,0,G,0
- 输出样例
+-------------------------+-----------------------------------+-----------------------------------+---------------------------+
| cell_drop_monitor.imei | cell_drop_monitor.total_drop_num | cell_drop_monitor.total_duration | cell_drop_monitor.d_rate |
+-------------------------+-----------------------------------+-----------------------------------+---------------------------+
| 639876 | 1 | 734 | 0.0013623978201634877 |
| 356436 | 1 | 1028 | 9.727626459143969E-4 |
| 351760 | 1 | 1232 | 8.116883116883117E-4 |
| 368883 | 1 | 1448 | 6.906077348066298E-4 |
| 358849 | 1 | 1469 | 6.807351940095302E-4 |
| 358231 | 1 | 1613 | 6.199628022318661E-4 |
| 863738 | 2 | 3343 | 5.982650314089142E-4 |
| 865011 | 1 | 1864 | 5.36480686695279E-4 |
| 862242 | 1 | 1913 | 5.227391531625719E-4 |
| 350301 | 2 | 3998 | 5.002501250625312E-4 |
+-------------------------+-----------------------------------+-----------------------------------+---------------------------+
- 示例代码
CREATE TABLE cell_monitor(
record_time string,
imei string,
cell string,
ph_num int,
call_num int,
drop_num int,
duration int,
drop_rate double,
net_type string,
erl int
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
CREATE TABLE cell_drop_monitor(
imei string,
total_drop_num int,
total_duration int,
d_rate double
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';
load data local inpath '/opt/hive/data/cdr_summ_imei_cell_info.csv' into table cell_monitor;
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;
二、单词统计
- 需求分析
统计每个单词出现的次数 - 输入样例
kk,123,weiwei,123
hlooe,hadoop,hello,ok
h,kk,123,weiwei,ok
ok,h
- 输出样例
+-------------------------+--------------------------+
| word_count_result.word | word_count_result.count |
+-------------------------+--------------------------+
| 123 | 3 |
| h | 2 |
| hadoop | 1 |
| hello | 1 |
| hlooe | 1 |
| kk | 2 |
| ok | 3 |
| weiwei | 2 |
+-------------------------+--------------------------+
- 示例代码
CREATE TABLE word_count(
line string
);
CREATE TABLE word_count_result(
word string,
count int
);
load data local inpath '/opt/hive/data/wordcount.txt' into table word_count;
FROM (SELECT explode(split(line, ',')) word FROM word_count) wc
INSERT OVERWRITE TABLE word_count_result
SELECT wc.word, count(wc.word) GROUP BY wc.word;
