Hive sql基本实操

比眉伴天荒 2021-12-18 05:27 359阅读 0赞

### \#**启动hdfs** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70][]

### **\#启动mysql** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 1][]

### **\#启动hive** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 2][]

### **\#操作基本和sql一样（这是在服务端操作）** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 3][]

### **\#在server01上启动客户端** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 4][]

### \#在server02上用客户端连接server01上的客户端 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 5][]

###  \#创建，t\_a,t\_b表 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 6][]

### \#在server01上/root/dataDir创建文件a.txt、b.txt;内容如下 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 7][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 8][]

### \#导入数据 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 9][]

### \#表里已经有数据了 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 10][]

### \#join操作 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 11][]

### \#sql语句其实是转换为map,reduce ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 12][]

### \#左半连接，只显示左表数据 ###

![20190702185916805.png][]

### \#分区表，以dt(日期分区) ###

一

有如下数据  
access.log.0804

192.168.33.3,http://www.edu360.cn/stu,2017-08-04 15:30:20  
192.168.33.3,http://www.edu360.cn/teach,2017-08-04 15:35:20  
192.168.33.4,http://www.edu360.cn/stu,2017-08-04 15:30:20  
192.168.33.4,http://www.edu360.cn/job,2017-08-04 16:30:20  
192.168.33.5,http://www.edu360.cn/job,2017-08-04 15:40:20

access.log.0805

192.168.33.3,http://www.edu360.cn/stu,2017-08-05 15:30:20  
192.168.44.3,http://www.edu360.cn/teach,2017-08-05 15:35:20  
192.168.33.44,http://www.edu360.cn/stu,2017-08-05 15:30:20  
192.168.33.46,http://www.edu360.cn/job,2017-08-05 16:30:20  
192.168.33.55,http://www.edu360.cn/job,2017-08-05 15:40:20

access.log.0806

192.168.133.3,http://www.edu360.cn/register,2017-08-06 15:30:20  
192.168.111.3,http://www.edu360.cn/register,2017-08-06 15:35:20  
192.168.34.44,http://www.edu360.cn/pay,2017-08-06 15:30:20  
192.168.33.46,http://www.edu360.cn/excersize,2017-08-06 16:30:20  
192.168.33.55,http://www.edu360.cn/job,2017-08-06 15:40:20  
192.168.33.46,http://www.edu360.cn/excersize,2017-08-06 16:30:20  
192.168.33.25,http://www.edu360.cn/job,2017-08-06 15:40:20  
192.168.33.36,http://www.edu360.cn/excersize,2017-08-06 16:30:20  
192.168.33.55,http://www.edu360.cn/job,2017-08-06 15:40:20

二，

建表映射上述数据  
create table t\_access(ip string,url string,access\_time string)  
partitioned by (dt string)  
row format delimited fields terminated by ',';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 13][]

三，导入数据  
load data local inpath '/root/hivetest/access.log.0804' into table t\_access partition(dt='2017-08-04');  
load data local inpath '/root/hivetest/access.log.0805' into table t\_access partition(dt='2017-08-05');  
load data local inpath '/root/hivetest/access.log.0806' into table t\_access partition(dt='2017-08-06');

四，sql练习

\-- 查看表的分区  
show partitions t\_access;

\--删除分区  
alter table t\_access drop partition (dt='2017-08-04');

\--删除表数据

truncate table t\_access;

\-- 求8月4号以后，每天http://www.edu360.cn/job的总访问次数，及访问者中ip地址中最大的  
select dt,'http://www.edu360.cn/job',count(1),max(ip)  
from t\_access  
where url='http://www.edu360.cn/job'  
group by dt having dt>'2017-08-04';

select dt,max(url),count(1),max(ip)  
from t\_access  
where url='http://www.edu360.cn/job'  
group by dt having dt>'2017-08-04';

select dt,url,count(1),max(ip)  
from t\_access  
where url='http://www.edu360.cn/job'  
group by dt,url having dt>'2017-08-04';

select dt,url,count(1),max(ip)  
from t\_access  
where url='http://www.edu360.cn/job' and dt>'2017-08-04'  
group by dt,url;

\-- 求8月4号以后，每天每个页面的总访问次数，及访问者中ip地址中最大的

select dt,url,count(1),max(ip)  
from t\_access  
where dt>'2017-08-04'  
group by dt,url;

\-- 求8月4号以后，每天每个页面的总访问次数，及访问者中ip地址中最大的，且，只查询出总访问次数>2 的记录  
\-- 方式1：  
select dt,url,count(1) as cnts,max(ip)  
from t\_access  
where dt>'2017-08-04'  
group by dt,url having cnts>2;

\-- 方式2：用子查询  
select dt,url,cnts,max\_ip  
from  
(select dt,url,count(1) as cnts,max(ip) as max\_ip  
from t\_access  
where dt>'2017-08-04'  
group by dt,url) tmp  
where cnts>2;

### \#HIVE 中的复合数据类型 ###

### 数组 ###

有如下数据：  
zhanlang2,wujing:wugang:longmu,2017-08-16  
sanshengsanshi,liuyifei:yangyang,2017-08-20

建表映射：  
create table t\_movie(movie\_name string,actors array<string>,first\_show date)  
row format delimited fields terminated by ','  
collection items terminated by ':';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 14][]

导入数据  
load data local inpath '/root/dataDir/movie-actor.txt' into table t\_movie;（汉字乱码，暂时没解决）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 15][]

查看导入结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 16][]

select movie\_name,**actors\[0\]**,first\_show from t\_movie;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 17][]

select movie\_name,actors,first\_show  
from t\_movie where array\_contains(actors,'yangyang');

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 18][]

select movie\_name  
,size(actors) as actor\_number  
,first\_show  
from t\_movie;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 19][]

### MAP类型 ###

有如下数据：fm.dat  
1,zhangsan,father:xiaoming\#mother:xiaohuang\#brother:xiaoxu,28  
2,lisi,father:mayun\#mother:huangyi\#brother:guanyu,22  
3,wangwu,father:wangjianlin\#mother:ruhua\#sister:jingtian,29  
4,mayun,father:mayongzhen\#mother:angelababy,26

建表映射上述数据

create table t\_family(id int,name string,family\_members map<string,string>,age int)  
row format delimited fields terminated by ','  
collection items terminated by '\#'  
map keys terminated by ':';

导入数据  
load data local inpath '/root/hivetest/fm.dat' into table t\_family;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 20][]

导入结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 21][]

查出每个人的 爸爸、姐妹  
select id,name,family\_members\["father"\] as father,family\_members\["sister"\] as sister,age  
from t\_family;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 22][]

以下自己练习：

\-- 查出每个人有哪些亲属关系  
select id,name,map\_keys(family\_members) as relations,age  
from  t\_family;

\-- 查出每个人的亲人名字  
select id,name,map\_values(family\_members) as relations,age  
from  t\_family;

\-- 查出每个人的亲人数量  
select id,name,size(family\_members) as relations,age  
from  t\_family;

\-- 查出所有拥有兄弟的人及他的兄弟是谁  
\-- 方案1：一句话写完  
select id,name,age,family\_members\['brother'\]  
from t\_family  where array\_contains(map\_keys(family\_members),'brother');

\-- 方案2：子查询  
select id,name,age,family\_members\['brother'\]  
from  
(select id,name,age,map\_keys(family\_members) as relations,family\_members   
from t\_family) tmp   
where array\_contains(relations,'brother');

### struct类型 ###

假如有以下数据：  
1,zhangsan,18:male:深圳  
2,lisi,28:female:北京  
3,wangwu,38:male:广州  
4,赵六,26:female:上海  
5,钱琪,35:male:杭州  
6,王八,48:female:南京

\-- 建表映射上述数据

drop table if exists t\_user;  
create table t\_user(id int,name string,info struct<age:int,sex:string,addr:string>)  
row format delimited fields terminated by ','  
collection items terminated by ':';

\-- 导入数据  
load data local inpath '/root/hivetest/user.dat' into table t\_user;

\-- 查询每个人的id name和地址  
select id,name,info.addr  
from t\_user;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70]: /images/20211218/1d1c418275b546f996a60a8ae973d8a7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 1]: /images/20211218/88a57e924db043228b3c858ae514964f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 2]: /images/20211218/31c5596280734d24809b942c030b76b3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 3]: /images/20211218/dd85a0e555f1444e98cd1eefd74db5a1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 4]: /images/20211218/31623ca74e934163bfcea3a2b08808e0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 5]: /images/20211218/6b2e833b5efc47dd8630d52e47b355be.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 6]: /images/20211218/df470ca6de2846d0a8379e9cf11fba68.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 7]: /images/20211218/01a7c093758b45ceac6f2a29d8cbaa84.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 8]: /images/20211218/8e20177acbab4674a44b01a675dbf5e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 9]: /images/20211218/79fe84f45aa14f39a5848313776ef490.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 10]: /images/20211218/16ffff853a2b41dd924a0e6d8b7bf161.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 11]: /images/20211218/bd3745d54be9446dbf17fa2673e49e5c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 12]: /images/20211218/addeae966a924cd7ae49e5a57df3fed7.png
[20190702185916805.png]: /images/20211218/d9cd82adb13749ac8f4bba8faf60dde1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 13]: /images/20211218/0e4bb125c88c4d1eb0c10f161eb70476.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 14]: /images/20211218/4c6232a808e64c6ca3c3bb3723b0d870.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 15]: /images/20211218/2db8abd0ec1148ed8b665ff68178bb67.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 16]: /images/20211218/c510328367794c519e9ff0fc417c9b51.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 17]: /images/20211218/c387714b1a654fa0950a2182f4c4a44d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 18]: /images/20211218/d57fe48048904315ae484e3126405fac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 19]: /images/20211218/2637cb8cf29d45a0929eec3eb70b54fd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 20]: /images/20211218/ad39f0612b2d431cb4c1a93a64e1110f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 21]: /images/20211218/a7cc74fdf15544cda8a6ad353414a513.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTEyNjg0Mg_size_16_color_FFFFFF_t_70 22]: /images/20211218/85026733492e4fdd916d805db70f4043.png