Hive查询语句,有点细

查询语句的顺序

select        (4 开始操作,拿取需要的值

from          (1 先到要读取的文件

tb_name

where       (2 写出判断条件

group by   (3 分组

having      (5 对结果进行筛选过滤

order by   (6 全局排序

limit          (7 limit用于限制返回的行数

 

基本查询

• 全表查询

0: jdbc:hive2://linux01:10000> select * from tb_log;
INFO  : Compiling command(queryId=root_20201201205632_7620de88-56b4-4703-8fe8-50cab6ea5e96): select * from tb_log
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Semantic Analysis Completed (retrial = false)
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:tb_log.log_id, type:string, comment:null), FieldSchema(name:tb_log.url, type:string, comment:null), FieldSchema(name:tb_log.ct, type:string, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=root_20201201205632_7620de88-56b4-4703-8fe8-50cab6ea5e96); Time taken: 0.484 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=root_20201201205632_7620de88-56b4-4703-8fe8-50cab6ea5e96): select * from tb_log
INFO  : Completed executing command(queryId=root_20201201205632_7620de88-56b4-4703-8fe8-50cab6ea5e96); Time taken: 0.0 seconds
INFO  : OK
INFO  : Concurrency mode is disabled, not creating a lock manager
+----------------+-------------+------------+
| tb_log.log_id  | tb_log.url  | tb_log.ct  |
+----------------+-------------+------------+
+----------------+-------------+------------+
No rows selected (0.504 seconds)

• 选择特定列查询

0: jdbc:hive2://linux01:10000> select name from tb_a;
INFO  : Compiling command(queryId=root_20201201210009_46adbe29-de81-446b-a52f-09fb9a684e62): select name from tb_a
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Semantic Analysis Completed (retrial = false)
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:name, type:string, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=root_20201201210009_46adbe29-de81-446b-a52f-09fb9a684e62); Time taken: 0.084 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=root_20201201210009_46adbe29-de81-446b-a52f-09fb9a684e62): select name from tb_a
INFO  : Completed executing command(queryId=root_20201201210009_46adbe29-de81-446b-a52f-09fb9a684e62); Time taken: 0.001 seconds
INFO  : OK
INFO  : Concurrency mode is disabled, not creating a lock manager
+-------+
| name  |
+-------+
| a     |
| b     |
| c     |
+-------+
3 rows selected (0.128 seconds)

• 列别名

0: jdbc:hive2://linux01:10000> select name a from tb_a;
INFO  : Compiling command(queryId=root_20201201210131_e49be02a-850b-4929-a632-6e6a05a6080b): select name a from tb_a
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Semantic Analysis Completed (retrial = false)
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:a, type:string, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=root_20201201210131_e49be02a-850b-4929-a632-6e6a05a6080b); Time taken: 0.104 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=root_20201201210131_e49be02a-850b-4929-a632-6e6a05a6080b): select name a from tb_a
INFO  : Completed executing command(queryId=root_20201201210131_e49be02a-850b-4929-a632-6e6a05a6080b); Time taken: 0.0 seconds
INFO  : OK
INFO  : Concurrency mode is disabled, not creating a lock manager
+-----+
|  a  |
+-----+
| a   |
| b   |
| c   |
+-----+
3 rows selected (0.132 seconds)

• 重命名一个列

• 便于计算

• 紧跟列名     可以在列名和别名之间添加as,也可省略

• 算数运算符

运算符	描述
A+B	A和B 相加
A-B	A减去B
A*B	A和B 相乘
A/B	A除以B
A%B	A对B取余
A&B	A和B按位取与
A|B	A和B按位取或
A^B	A和B按位取异或
~A	A按位取反

 

hive (default)> select sal + 1 from emp;

• 常用函数

1.   总行数（count）

hive (default)> select count(*) cnt from emp;

2．求工资的最大值（max）

hive (default)> select max(sal) max_sal from emp;

3．求工资的最小值（min）

hive (default)> select min(sal) min_sal from emp;

4．求工资的总和（sum）

hive (default)> select sum(sal) sum_sal from emp;

5．求工资的平均值（avg）

hive (default)> select avg(sal) avg_sal from emp;

• Limit语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

0: jdbc:hive2://linux01:10000> select * from tb_movie  limit 5;

显示五行

0: jdbc:hive2://linux01:10000> select * from tb_movie  limit 2,5;

从结果的第二行开始,返回五行

Where语句

比较运算符

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回TRUE，反之返回FALSE
A<=>B	基本数据类型	如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL
A<>B, A!=B	基本数据类型	A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE
A<B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE
A<=B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE
A>B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE
A>=B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE
A [NOT] BETWEEN B AND C	基本数据类型	如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL	所有数据类型	如果A等于NULL，则返回TRUE，反之返回FALSE
A IS NOT NULL	所有数据类型	如果A不等于NULL，则返回TRUE，反之返回FALSE
IN(数值1, 数值2)	所有数据类型	使用 IN运算显示列表中的值
A [NOT] LIKE B	STRING 类型	B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP B	STRING 类型	B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

Like和RLike

Like

1）使用LIKE运算选择类似的值

2）选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符)。

_ 代表一个字符。

（1）查找以2开头薪水的员工信息

hive (default)> select * from emp where sal LIKE '2%';

（2）查找第二个数值为2的薪水的员工信息

hive (default)> select * from emp where sal LIKE '_2%';

 RLike

3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

查找薪水中含有2的员工信息

hive (default)> select * from emp where sal RLIKE '[2]';

逻辑运算符

 

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

（1）查询薪水大于1000，部门是30

hive (default)> select * from emp where sal>1000 and deptno=30;

（2）查询薪水大于1000，或者部门是30

hive (default)> select * from emp where sal>1000 or deptno=30;

（3）查询除了20部门和30部门以外的员工信息

hive (default)> select * from emp where deptno not IN(30, 20);

 

分组 

Group By

分一次或者多次分组(多次分组是在上次分组的基础上再次进行分组)

Having

having针对查询结果中的列发挥作用，筛选数据

having只用于group by分组统计语句

求每个部门的平均薪水大于2000的部门

hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having

 avg_sal > 2000;

join语句

新版本的join已经支持不等值连接 

1. join   join 后面加 on 添加连接条件避免笛卡尔积的出现(常用的连接查询方法)
2. union 放在两个查询语句中间,把两个语句的到的值进行去重合并    
3. union all 放在两个查询语句中间,把两个语句的到的值进行不去重合并    
4. inner join(也就是join)
5. left join  左连接
6. right join  右连接
7. left semi join   (假设有a,b两表且可以连接,查询a的那个可以与b连接的那一列在b中出现谁(a与b连接的那一列不尽然完全相同),然后带着从b那里查询到的值带到自己表中,取出与之对应的数据)
8. full join 全连接,将两表数据全部连接,对应没有值的空位,用null来填补

排序

全局排序

Order By 全局排序,默认升序排序ASC,倒序排序DESC,用于SWELECT语句结尾.

（1）查询员工信息按工资升序排列

hive (default)> select * from emp order by sal;

（2）查询员工信息按工资降序排列

hive (default)> select * from emp order by sal desc;

 按照别名排序

按照员工薪水的2倍排序

hive (default)> select ename, sal*2 twosal from emp order by twosal

多个列排序

按照部门和工资升序排序

hive (default)> select ename, deptno, sal from emp order by deptno, sal ;

 MapReduce内部排序（Sort By）

Sort By：每个Reducer内部进行排序，对全局结果集来说不是排序。

将查询结果导入到文件中（按照部门编号降序排序）

hive (default)> insert overwrite local directory '/hive/sb'

 select * from emp sort by deptno desc;

 分区排序

Distribute By 分区字段

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

查看设置reduce个数

hive (default)> set mapreduce.job.reduces;

设置reduce的个数

set mapreduce.job.reduces=3;

Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

先按照部门编号分区，再按照员工编号降序排序。

hive (default)> set mapreduce.job.reduces=3;

hive (default)> insert overwrite local directory '/root/data/distribute-result' select * from emp distribute by deptno sort by empno desc; 

Cluster By

当分区字段distribute by 与 排序字段 sort by 相同,且是升序的时候可以使用Cluster By替换它两.因为它的功能与这两相同.

select * from emp cluster by deptno;

按照 deptno 分区,且 按照其升序排序

分桶抽样及抽样查询

分桶

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.创建一个分桶表

create table buck_stu(
id int, 
name string)
clustered by(id) 
into 3 buckets;

3.开启分桶功能

set hive.enforce.bucketing=true;     -- 开启分桶
set mapreduce.job.reduces=-1;

 4.将普通表的数据读出写入到分桶表中

insert into table buck_stu
select id, name from tb_stu;

 表已经分完.分桶是分hfile,分区是分文件夹.

Permission	Owner	Group	Size	Last Modified	Replication	Block Size	Name
	-rw-r--r--	root	supergroup	47 B	Dec 02 23:31	3	128 MB	000000_0
	-rw-r--r--	root	supergroup	47 B	Dec 02 23:31	3	128 MB	000001_0
	-rw-r--r--	root	supergroup	57 B	Dec 02 23:31	3	128 MB	000002_0

 抽样

一共三份取一份(大小随机) 分区字段是id

普通表也能使用

select * from  buck_stu tablesample(bucket 1 out of 3 on id);

其他常用查询函数

collect_list 将收集的多行数据聚集成一个数组集合

collect_set   ---去重     这两都是聚合函数

concet 拼接字符串,下面的更方便

concat_ws 拼接字符串   select concat_ws(","  ,  'a  ,  'b'); 第一个参数是拼接符,第二个是可变字符串,也可以放数组.select concat_ws(","  , array( 'a  ,  'b'));

 以上三个便能搞定列转行

列转行

原数据

+------------------+----------------+----------------+
| tb_teacher.name  | tb_teacher.xz  | tb_teacher.xx  |
+------------------+----------------+----------------+
| xx              | 处女座            | B              |
| xx               | 射手座            | A              |
| xx               | 处女座            | B              |
| xx           | 白羊座            | A              |
| xx            | 射手座            | A              |
+------------------+----------------+----------------+
 

select
concat(xz ,"," , xx) ,
collect_list(name)
from
tb_teacher 
group by  xz, xx  ;

星座和血型拼接到一起 名字变成数组 

+--------+-----------------+
|  _c0   |       _c1       |
+--------+-----------------+
| 处女座,B  | ["xx","xx"] |
| 射手座,A  | ["xx","xx"] |
| 白羊座,A  | ["xx"]      |
+--------+-----------------+

将数组拆分就完成

select
concat(xz ,"," , xx) as xax,
concat_ws("|",collect_list(name)) as names
from
tb_teacher 
group by  xz, xx  ;

+--------+-----------------+
|  _c0   |       _c1       |
+--------+-----------------+
| 处女座,B  | xx|xx    |
| 射手座,A  | xx|xx |
| 白羊座,A  | xx        |
+--------+-----------------+

行转列

原数据

《八佰》    战争
《八佰》    动作
《八佰》    抗日
《八佰》    剧情
《姜子牙》    动画
《姜子牙》    神话
《姜子牙》    科幻
《姜子牙》    动作
《战狼2》    战争
《战狼2》    动作
《战狼2》    灾难

select
name ,
concat_ws(",",collect_list(typ)) as  categorys
from
tb_movie 
group by  name ;

+--------+-----------------+
|  name  |    categorys    |
+--------+-----------------+
| 《八佰》   | 战争,动作,抗日,剧情     |
| 《姜子牙》  | 动画,神话,科幻,动作  |
| 《战狼2》  | 战争,动作,灾难        |
+--------+-----------------+

将上面的值转化成原数据,字符串切割成数组

select
name ,
split(categorys,",")  -- 将字符串切割成数组
from
tb_movie2 ;

+--------+-----------------------------+
|  name  |             _c1             |
+--------+-----------------------------+
| 《八佰》   | ["战争","动作","抗日","剧情"]       |
| 《姜子牙》  | ["动画","神话","科幻","动作",]  |
| 《战狼2》  | ["战争","动作","灾难"]            |
+--------+-----------------------------+

explode  将数组或集合转化为多行内容(将数组炸开)

select
explode(split(categorys,","))  -- 将字符串切割成数组
from
tb_movie2 ;

+------+
| col  |
+------+
| 战争   |
| 动作   |
| 抗日   |
| 剧情   |
| 动画   |
| 神话   |
| 科幻   |
| 动作   |
| 战争   |
| 动作   |
| 灾难   |
+------+

没有彻底转化,name不能直接转换,用测窗口函数

侧窗口函数

类似隐式join

lateral view

select
name  ,
tp
from
tb_movie2
lateral view 
explode(split(categorys , ',')) t  as  tp  ;
t  表名 
tp  虚拟表的字段名 

+--------+-----+
|  name  | tp  |
+--------+-----+
| 《八佰》   | 战争  |
| 《八佰》   | 动作  |
| 《八佰》   | 抗日  |
| 《八佰》   | 剧情  |
| 《姜子牙》  | 动画  |
| 《姜子牙》  | 神话  |
| 《姜子牙》  | 科幻  |
| 《姜子牙》  | 动作  |
| 《战狼2》  | 战争  |
| 《战狼2》  | 动作  |
| 《战狼2》  | 灾难  |
+--------+-----+

窗口函数

在进行分组聚合以后 ,想操作聚合以前的数据 使用到窗口函数

 

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化

CURRENT ROW：当前行  current row

n PRECEDING：往前n行数据  n  preceding

n FOLLOWING：往后n行数据  n following

UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点  unbound preceding  unbound following

LAG(col,n)：往前第n行数据  lag  参数一 字段  n

LEAD(col,n)：往后第n行数据 lead

NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号 

select
* ,
count(1) over(partition by name)  , -- 指定窗口大小是一个人
sum(money) over(partition by name)
from
tb_orders ;
尽量少划分窗口

起始行到当前行

select
* , 
sum(money)  over(partition by name  order by ctime  rows  between unbounded  preceding  and  current row ) 
from
tb_orders ;

这个太长,如果有了排序,就可以不用当前行什么的了

select
* , 
sum(money)  over(partition by name  order by ctime)  -- order by ctime  起始行和当前行
from
tb_orders ;

上一行和当前行

select
* , 
sum(money)  over(partition by name  order by ctime rows  between   1  preceding   and  current row )  -- order by ctime  起始行和当前行
from
tb_orders ;

+-----------------+------------------+------------------+---------------+
| tb_orders.name  | tb_orders.ctime  | tb_orders.money  | sum_window_0  |
+-----------------+------------------+------------------+---------------+
| jack            | 2020-01-01       | 10.0             | 10.0          |
| jack            | 2020-01-05       | 46.0             | 56.0          |
| jack            | 2020-01-08       | 55.0             | 111.0         |
| jack            | 2020-02-03       | 23.0             | 134.0         |
| jack            | 2020-04-06       | 42.0             | 176.0         |
| mart            | 2020-04-08       | 62.0             | 62.0          |
| mart            | 2020-04-09       | 68.0             | 130.0         |
| mart            | 2020-04-11       | 75.0             | 205.0         |
| mart            | 2020-04-13       | 94.0             | 299.0         |
| neil            | 2020-05-10       | 12.0             | 12.0          |
| neil            | 2020-06-12       | 80.0             | 92.0          |
| tony            | 2020-01-02       | 15.0             | 15.0          |
| tony            | 2020-01-04       | 29.0             | 44.0          |
| tony            | 2020-01-07       | 50.0             | 94.0          |
+-----------------+------------------+------------------+---------------+ 

上一行 当前行  和下一行

select
* , 
sum(money)  over(partition by name  order by ctime rows  between   1  preceding   and  1  following )  -- order by ctime  起始行和当前行
from
tb_orders ;

向前n行的数据  

向后n行的数据

select
* ,
lag(ctime ,1)  over(partition by name   order by ctime) 
from
tb_orders  ;

+-----------------+------------------+------------------+---------------+
| tb_orders.name  | tb_orders.ctime  | tb_orders.money  | lag_window_0  |
+-----------------+------------------+------------------+---------------+
| jack            | 2020-01-01       | 10.0             | NULL          |
| jack            | 2020-01-05       | 46.0             | 2020-01-01    |
| jack            | 2020-01-08       | 55.0             | 2020-01-05    |
| jack            | 2020-02-03       | 23.0             | 2020-01-08    |
| jack            | 2020-04-06       | 42.0             | 2020-02-03    |
| mart            | 2020-04-08       | 62.0             | NULL          |
| mart            | 2020-04-09       | 68.0             | 2020-04-08    |
| mart            | 2020-04-11       | 75.0             | 2020-04-09    |
| mart            | 2020-04-13       | 94.0             | 2020-04-11    |
| neil            | 2020-05-10       | 12.0             | NULL          |
| neil            | 2020-06-12       | 80.0             | 2020-05-10    |
| tony            | 2020-01-02       | 15.0             | NULL          |
| tony            | 2020-01-04       | 29.0             | 2020-01-02    |
| tony            | 2020-01-07       | 50.0             | 2020-01-04    |
+-----------------+------------------+------------------+---------------+