Hive案例

作者: 上杉丶零 | 来源:发表于2019-01-23 15:12 被阅读0次

Hive案例
Hive案例
Hive练习案例
尚硅谷大数据技术之Flume
sendmail使用案例1
Hive SQL练习之影评案例
hive日志分析案例
hive日志分析案例
Hive导入文件案例
hive日志分析案例

一、基站掉话率统计

需求分析
统计基站掉话率，找出掉话率最高的前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;