hive3.1.2 hql 语句知识点及练习（一）case when join 排序 分桶表

case when 和聚合函数

表格内容如下：

zs	A	男
ls	A	男
ss	B	男
fj	A	女
rb	B	女
hh	B	女

需求 结果如下；

+--------+----+----+
| dname  | m  | f  |
+--------+----+----+
| A      | 2  | 1  |
| B      | 1  | 2  |
+--------+----+----+

--创建表
create table tb_emp(
name string ,
dname string ,
gender string 
)
row format delimited fields terminated by "\t" ;
--导入数据
load data local inpath "/data/emp/" into table tb_emp ;

select
dname ,
sum(case gender when '男' then 1 else 0 end)  M ,
sum(case gender when '女' then 1 else 0 end)  F
from
tb_emp 
group by dname ;

或者

select
dname ,
sum(if(gender='男',1,0))  M ,
sum(if(gender='女',1,0))  F
from
tb_emp 
group by dname ;

关联查询 join

两个表
字段为 id 和 name

表一
1,  a
2,  b
3,  c
表二
1,zss
2,lss
3,www
1,xxx
2,yyy
4,zzz

笛卡尔积

select *
from  tb_a
join tb_b ;

select
*
from 
tb_a
inner join
tb_b ; 

select
*
from 
tb_a
,
tb_b ; 

以上三种方法都会产生笛卡尔积（所有表中的所有行互相连接），尽量避免产生笛卡尔积

left join

select
*
from 
tb_a
join
tb_b 
on tb_a.id = tb_b.id ;
--结果展示
+----------+------------+----------+------------+
| tb_a.id  | tb_a.name  | tb_b.id  | tb_b.name  |
+----------+------------+----------+------------+
| 1        | a          | 1        | zss        |
| 2        | b          | 2        | lss        |
| 3        | c          | 3        | www        |
| 1        | a          | 1        | xxx        |
| 2        | b          | 2        | yyy        |
+----------+------------+----------+------------+

right join

select
*
from
tb_a
right join 
tb_b 
on tb_a.id = tb_b.id ;
--结果展示
+----------+------------+----------+------------+
| tb_a.id  | tb_a.name  | tb_b.id  | tb_b.name  |
+----------+------------+----------+------------+
| 1        | a          | 1        | zss        |
| 2        | b          | 2        | lss        |
| 3        | c          | 3        | www        |
| 1        | a          | 1        | xxx        |
| 2        | b          | 2        | yyy        |
| NULL     | NULL       | 4        | zzz        |
+----------+------------+----------+------------+

left semi join

--查看a表中的id在b表中出现的id，保留a表的结果
select
*
from
tb_a
left semi join 
tb_b 
on tb_a.id = tb_b.id ;
--结果展示
+----------+------------+
| tb_a.id  | tb_a.name  |
+----------+------------+
| 1        | a          |
| 2        | b          |
| 3        | c          |
+----------+------------+
select 
*
from 
tb_a 
where id in (select id from tb_b)
--这种方法和上面的left semi join逻辑一样

--查看b表中的id在a表中出现的id，保留b表的结果
select
*
from
tb_b
left semi join 
tb_a
on tb_a.id = tb_b.id ;
--结果展示
+----------+------------+
| tb_b.id  | tb_b.name  |
+----------+------------+
| 1        | zss        |
| 2        | lss        |
| 3        | www        |
| 1        | xxx        |
| 2        | yyy        |
+----------+------------+
select 
*
from 
tb_b
where id in (select id from tb_a)

union

--连接两个查询的结果集  要求字段个数和数据类型一致  union all不会去重
select
*
from
tb_a
union all
select
*
from
tb_b ;

select
*
from
tb_a
union 
select
*
from
tb_b ; 
--结果展示
+---------+-----------+
| _u1.id  | _u1.name  |
+---------+-----------+
| 1       | a         |
| 2       | b         |
| 3       | c         |
+---------+-----------+

full join

+----------+------------+----------+------------+
| tb_a.id  | tb_a.name  | tb_b.id  | tb_b.name  |
+----------+------------+----------+------------+
| 1        | a          | 1        | xxx        |
| 1        | a          | 1        | zss        |
| 2        | b          | 2        | yyy        |
| 2        | b          | 2        | lss        |
| 3        | c          | 3        | www        |
| NULL     | NULL       | 4        | zzz        |
| 5        | e          | NULL     | NULL       |
+----------+------------+----------+------------+

多表连接

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件

a
join
b
join
c
join
d
on  a.id=b.id and b.id=c.id and  c.id=d.id ;

排序

在执行SQL的时候默认是一个reducetesk
set mapreduce.job.reduces=n; – 配置reduce的个数
set mapreduce.job.reduces; – 查看配置结果

order by  -- 全局最终结果排序 
distribute by 分区字段  --分区排序
sort by 排序字段  --区内数据排序 默认升序
cluster by   --当分区字段和排序字段相同 并且是升序的时候可使用cluster by 替换 distribute by ..sort by ..

表内容
1,a
2,a
3,a
4,b
5,b
6,c
7,c
8,e
create  table  tb_x(
id int ,
name string 
)
row format delimited fields  terminated by ',' ;
load data local inpath '/root/distribute/' into table tb_x ;

select *  from tb_x  distribute by  name  sort by name  desc;
或者
select *  from tb_x  cluster by  name ;
--结果：
+----------+------------+
| tb_x.id  | tb_x.name  |
+----------+------------+
| 8        | e          |
| 7        | c          |
| 6        | c          |
| 5        | b          |
| 4        | b          |
| 3        | a          |
| 2        | a          |
| 1        | a          |
+----------+------------+

对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
set mapreduce.job.reduces=3

-- 数据的导出 到本地的文件夹中
insert overwrite local directory "/root/distribute/x_res" 
select * from tb_x distribute by name；

--查看结果
[root@linux01 x_res]# cat 000000_0 
7c
6c
[root@linux01 x_res]# cat 000001_0 
3a
2a
1a
[root@linux01 x_res]# cat 000002_0 
8e
5b
4b

函数

1）
array(ele1 , ele2...)  --创建数组 数组是hive中的一种集合数据类型  和 java中的数组一样
select array(1,2,3,4) ;
select array(1,2,3,4)[index] ;
2）数组是否包含某个元素
array_contains(arr , element) ;
select array_contains(array('a','ab','abc','abcd'),'aa') ;
---------------------------------------------------------------------------------
大小写
upper
lower 
select lower('ABC') ;
select upper('ABC') ;
---------------------------------------------------------------------------------
切割
split(str , seq)  返回数组
select split("hello_tom_jim","_")[0] ;
---------------------------------------------------------------------------------
去除首尾空格
trim(str)   
select trim("  hello  ") ;
---------------------------------------------------------------------------------
产生一串随机数字
select uuid() ;
229e4f73-614d-4940-8043-00ac454588df 
---------------------------------------------------------------------------------
replace(字符串 , 要替换的子串 , 替换的新str) 替换字符串
select replace(uuid() ,'-','') ;
229e4f73614d4940804300ac454588df 
---------------------------------------------------------------------------------
substr(str ,起始位置1从开始 [,长度]) 
select substr('hello',2,3); --> ell             
substring  和substr函数一样         
substring_index 
select substring_index("a-b-c","-",2) ;  --   a-b

分桶表

对join查询的优化 将数据按照指定的字段的规则分文件
使用步骤
1 创建普通表 导入数据
2 创建分桶表
3 开启分桶功能
4 使用insert into的方式导入数据 到 分桶表中

表内容
1001	ss1
1002	ss2
1003	ss3
1004	ss4
1005	ss5
1006	ss6
1007	ss7
1008	ss8
1009	ss9
1010	ss10
1011	ss11
1012	ss12
1013	ss13
1014	ss14
1015	ss15
1016	ss16

--1 创建普通表  导入数据 
create table tb_stu(
id int, 
name string)
row format delimited fields terminated by '\t';
load data local inpath "/data/stu/" into  table tb_stu ;
--2 创建分桶表  关键字clustered by 
create table buck_stu(
id int, 
name string)
clustered by(id) 
into 3 buckets   --分3个桶
row format delimited fields terminated by '\t';
--3 开启分桶功能 
set hive.enforce.bucketing=true;     -- 开启分桶
set mapreduce.job.reduces=-1;  
--4  使用insert  into的方式导入数据 到 分桶表中
insert into table buck_stu
select id, name from tb_stu;

--结果展示
+--------------+----------------+
| buck_stu.id  | buck_stu.name  |
+--------------+----------------+
| 1005         | ss5            |
| 1002         | ss2            |
| 1011         | ss11           |
| 1014         | ss14           |
| 1008         | ss8            |
-----------------------------------
| 1006         | ss6            |
| 1012         | ss12           |
| 1003         | ss3            |
| 1009         | ss9            |
| 1015         | ss15           |
-----------------------------------
| 1016         | ss16           |
| 1013         | ss13           |
| 1010         | ss10           |
| 1007         | ss7            |
| 1004         | ss4            |
| 1001         | ss1            |
+--------------+----------------+
HDFS中的文件  一个表中的内容被分在了三个文件中
Permission	Owner	Group	Size	Last Modified	Replication	Block Size	Name	
-rw-r--r--	root	supergroup	47 B	Dec 01 14:49	3	128 MB	000000_0	
-rw-r--r--	root	supergroup	47 B	Dec 01 14:49	3	128 MB	000001_0	
-rw-r--r--	root	supergroup	57 B	Dec 01 14:49	3	128 MB	000002_0	

抽样查询

抽样查询一般不用

--接着上面的分桶案例
--分桶表中的第一桶
 select * from  buck_stu tablesample(bucket 1 out of 3 on id);
 +--------------+----------------+
| buck_stu.id  | buck_stu.name  |
+--------------+----------------+
| 1002         | ss2            |
| 1011         | ss11           |
| 1014         | ss14           |
| 1008         | ss8            |
| 1015         | ss15           |
| 1013         | ss13           |
| 1004         | ss4            |
+--------------+----------------+
 select * from  buck_stu tablesample(bucket 2 out of 3 on id);
 +--------------+----------------+
| buck_stu.id  | buck_stu.name  |
+--------------+----------------+
| 1012         | ss12           |
| 1010         | ss10           |
+--------------+----------------+
 select * from  buck_stu tablesample(bucket 3 out of 3 on id);
 +--------------+----------------+
| buck_stu.id  | buck_stu.name  |
+--------------+----------------+
| 1005         | ss5            |
| 1006         | ss6            |
| 1003         | ss3            |
| 1009         | ss9            |
| 1016         | ss16           |
| 1007         | ss7            |
| 1001         | ss1            |
+--------------+----------------+

时间

select unix_timestamp();
-- 
  +-------------+
  |     _c0     |
  +-------------+
  | 1610788628  |
  +-------------+

提取小时
select hour(from_unixtime(1610788628));