hive的三个基本小案例的实现

淩亂°似流年 2021-11-02 10:28 177阅读 0赞

建表的基本语法：create table tablename(col,type)   
例一：  
create table sxtTest(  
id int,  
name string,  
likes array<string>,  
address map<string,string>  
)  
row format delimited  
fields terminated by ','  
collection items terminated by '-'  
map keys terminated by ':'  
lines terminated by '\\n';

\#加载数据的方式

load data local inpath '/home/data/sxt\_hive.txt' into table sxtTest;

模拟的数据：

1,penglinag,lol-book-movie,shanghai:pudong  
2,peng,lol-book,shanghai:pudong  
3,linagliang,lol-book,shanghai:yanyin  
4,liang,lol-book-movie,shanghai:yu

例二：外部表  
create table sxtTest(  
id int,  
name string,  
likes array<string>,  
address map<string,string>  
)  
row format delimited  
fields terminated by ','  
collection items terminated by '-'  
map keys terminated by ':'  
lines terminated by '\\n'  
location /usr;  
注意加上，上面的这句就成为外部表了

分区字段：注意分区字段中的属性不能出现在分区里面  
create table a(  
id int,  
name string,  
likes array<string>,  
address map<string,string>  
)  
partitioned by (age int,sex string)  
row format delimited  
fields terminated by ','  
collection items terminated by '-'  
map keys terminated by ':'  
lines terminated by '\\n';  
   
当有几个分区字段时候，加载数据就要给分区指定几个属性  
load data local inpath '/home/data/sxt\_hive.txt' into table a partition(age=10,sex='boy');  
   
alter table a add partition(age=10,sex='girl');  
结论：添加分区的时候，必须在现有的分区基础之上  
      删除分区的时候，会将所有的存在的分区全部删除

###    hive案例实践： ###

1、实现struct例子

create table student(  
id int,  
info struct<name:string,age:int>  
)  
row format delimited  
fields terminated by ','  
collection items terminated by ':';

数据：  
2017113828,彭亮:22  
2017113822,小明:20  
2017113820,流星:22

2、基站掉话率：找出掉线率最高的前10基站

结果表的字段：  
record\_time：通话时间  
imei：基站编号  
cell：手机编号  
drop\_num：掉话的秒数  
duration：通话持续总秒数

结果表：  
create table call\_result(  
imei string,  
drop\_num int,  
duration int,  
drop\_rate double  
)row format delimited  
fields terminated by ','

hive的语句：  
*from call\_monitor cm   
insert into call\_result  
select cm.imei,sum(cm.drop\_num) sdrop,sum(cm.duration) sdura,sum(cm.drop\_num)/sum(cm.duration) s\_rate group by cm.imei order by s\_rate desc*

原始数据表：  
create table call\_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 ',';

3、使用hive实现wordcount

create table WC(word string);

实现语句

方法一：  
select t1.word,count(t1.word) from (select explode(split(word,' '))word from WC)t1 group by t1.word;

方法二：  
用一个结果表来存放查出的单词和次数，然后进行统计：

create table WC\_result(  
word string，  
ct int  
);

from (select explode(split(word,' ')) word from WC)t1  
insert into WC\_result  
select t1.word count(t1.word) group by t1.word;