Hive的查询

基本查询

全表和特定列查询

1.全表查询
	select * from emp;
2.选择特定列查询
	select empno,ename from emp;
注意：
	1.SQL语言大小写不敏感
	2.SQL可以写在一行或者多行
	3.关键字不能被缩写也不能分行

列别名

主要作用：
	重命名一个列
	便于计算
使用AS关键字为列指定别名
select ename as name from emp;
	

算术运算符

运算符	描述
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 按位取反

select sal + 1 as sal from emp;

常用函数

1.求总行数（count）

select count(*)  count from emp;

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

select max(sal) maxSal from emp;

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

select min(sal) minSal from emp;

4.求工资的总和（sum）

select sum(sal) sumSal from emp;

5.求工资的平均值

select avg(sal) avgSal from emp;

limit语句

limit用于限制返回的行数

select * from emp limit 5;

Where语句

where用于过滤不满足条件的数据

查询薪水大于1000的员工

select * from emp where sal > 1000;

比较运算符（Between/in/is null）

操作符	支持的数据类型	描述
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 相匹配，而不是只需与其字符串匹配。

代码示例：

（1）查询出薪水等于5000的所有员工
	select * from emp where sal = 5000;
（2）查询工资在500到1000的员工
	select * from emp where sal between 500 and 1000;
（3）查询comm为空的所有员工信息
	select * from emp where comm is null;
（4）查询工资是1500和5000的员工信息
	select * from emp where sal in (1500,5000);

Like和Likes

1.使用LIKE运算选择类似的值
2.选择条件可以包含字符或者数字

%:代表零个或多个字符（任意字符）
_:代表一个字符

3.RLIKE 子句是Hive中的一个扩展，其可以使用Java的正则表达式来指定匹配条件

案例操作

1.查询以2开头薪水的员工信息
	select * from emp where sal LIKE '2%';
2.查找第二个数字为2的薪水员工信息
	select * from emp where sal LIKE '_2%';
3.查找薪水中含有2的员工信息
	select * from emp where sal RLIKE '[2]';

逻辑运算符（And/Or/Not）

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

实例操作：

1.查询薪水大于1000，部门是30的员工
	select * from emp where sal > 1000 and deptno = 30
2.查询薪水大于1000，或者部门是30的员工
	select * from emp where sal > 1000 or deptno = 30
3.查询除了20部门和30部门以外的员工信息
	select * from emp where deptno not in (20,30)

分组

Group By语句

GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

实例操作：

1.计算emp表每个部门的平均工资
	select  deptno,avg(sal) from emp group by deptno;
2.计算emp每个部门中每个岗位的最高薪水
	select deptno,job,max(sal) from emp group by deptno,job;

Having语句

1.having与where的不同点
	a.where针对表中的列发挥作用，查询数据：having针对查询结果中的列发挥作用，筛选数据。
	b.where后面不能写分组函数，而having可以使用分组函数
	c.having只用于group by分组统计语句

实例操作：

求每个部门的平均薪水大于2000的部门
	select deptno,avg(sal) avgSal from emp  group by deptno having avgSal > 2000 

Join语句

等值Join

Hive 支持通常的 SQL JOIN 语句，但是只支持等值连接，不支持非等值连接

根据员工表和部门表中的部门编号相等，查询员工信息
select e.* from emp e jion dept d on e.deptno = d.deptno

表的别名

1）好处
（1）使用别名可以简化查询。
（2）使用表名前缀可以提高执行效率。
2）案例实操
合并员工表和部门表
	hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno =d.deptno;

内连接

内连接：只有进行连续的两个表中都存在与连接条件相匹配的数据才会被保留下来。

select e.* from emp e join dept d on e.deptno = d.deptno

左外连接

左外连接：JOIN操作符左边表符合WHERE子句的所有记录将会被返回。

select * from emp e left join dept d on e.deptno = d.deptno

右外连接

右外连接：JOIN操作符右边表符合WHERE子句的所有记录将会被返回

select * from emp e right join dept d on e.deptno = d.deptno

满外连接

满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果一表字段没有符合条件的值的话，那么就使用NULL值替代。

select * from emp e full join dept d on  e.deptno = d.deptno

多表连接

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

select * from emp e join dept d on e.deptno = d.deptno
join location l on d.loc = l.loc

大多数情况下，HIVE会对每对Join连接对象启动一个MapReduce任务。本例中会首先启动MapReduceJOB对e表和d表进行连接操作，然后在启动一个MapReduceJOB将第一个MapReduceJOB的输出和表L进行连接
为什么不是表d和表l先进行连接操作呢？因为Hive总是按照从左往右的顺序执行的

笛卡尔积

1）笛卡尔集会在下面条件下产生:
	（1）省略连接条件
	（2）连接条件无效
	（3）所有表中的所有行互相连接
2）案例实操
	hive (default)> select empno, deptno from emp, dept;
	FAILED: SemanticException Column deptno Found in more than One Tables/Subqueries

连接谓词中不支持or

在Join……on 中不支持使用 or  可以在join……on的后面紧跟where语句，然后使用or条件

排序

全局排序（order BY）

全局排序，执行一个MapReduce

使用ORDER BY子句排序
	ASC：升序（默认）
	DESC：降序
ORDER BY 位于SELECT语句的结尾

查询员工的信息，按薪水降序
select * from emp order sal desc
查询员工信息，按薪水升序
select * from emp order sal asc

按照别名排序

按照员工薪水的 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:每个MapReduce内部进行排序，对全局结果集来说不是排序

1.设置reduce个数
	set mapreduce.job.reduces=3;
2.查看设置的reduce个数
	set mapreduce.job.reduces;
3.根据部门编号降序查看员工信息
	select * from emp sort by empno desc;
	
上述查询语句会执行3个Reduce，其中每个Reduce中的数据是按照指定条件排序的，但是最终的结果是无序的

4.将结果导入到文件中（按照部门编号降序排序）
	insert overwrite local directory '/sortby' select * from emp sort by deptno desc;

sort by:执行3个Reduce导出的文件为，每个文件中的数据是有序的。3个Reduce就生成了三个文件

分区排序（Distribute by）

Distribute By：类似MR中partition，进行分区，结合sort by使用
注意：Hive要求Distribute By 语句写在Sort By之前。可以联想Map方法输出到环形缓冲区的中，环形缓冲区对数据就是先分区在排序的。保证每个分区中的数据是有序的。

对于Distribute By 的测试，一定要多分配reduce进行处理，1个是不可以的。否则无法看到Distribute by的效果

先按照部门编号分区，再按照员工编号降序排序。
set mapreduce.job.reduces =3
insert overwrite local direcotry '/opt/dist' select * from emp distribute by deptno sort by empno desc

Cluster By

如果Distribute By 的字段和Sort by的字段相同时候，可以使用Cluster by关键字。（即为当分区依据和分区内排序依据相同时，可以使用Cluster by）
Cluster By除了具有Distribute By的功能外还兼具Sort By的功能。但是排序只能倒序，不能指定ASC，DESC

下面两种写法意义相同：
select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno ;

注意：按照部门编号分区，不一定就是固定死的数值，可以是 20 号和 30 号部门分到一个分区里面去。

分桶和抽样查询

分桶表数据存储

分区针对的是数据的存储路径，分桶针对的是数据文件。

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可以形成合理的分区，特别是之前所提到过的药确定合适的划分大小这个疑虑。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。

创建分桶表：
	create table stu_buck(id int, name string)
	clustered by(id)
	into 4 buckets
	row format delimited fields terminated by '\t';

查看表结构
	desc formatted stu_buck;

导入数据到分桶表中
	load data local inpath '/opt/module/datas/student.txt' into table stu_buck;

查看HDFS文件上的分桶表文件是否分成了4个桶、
	我们发现文件并没有分成4个桶。

通过子查询的方式导入数据：
	创建普通表
		create table stu(id int,name string) 
		row format delimited fields terminated by '\t';
导入数据到普通表中
	load data local inpath '\opt\data\stu.txt' into table stu;
清空stu_buck表
	truncate table stu_buck;
	select * from stu_buck;
导入数据，通过子查询的方式
	insert into stu_buck select id,name from stu cluster by (id);
再次查看，发现还是一个桶。
		

分桶为什么没有成功？

需要修改一个属性：
set hive.enforce.bucketing=true
set mapreduce.job.reduces=-1;

insert into table stu_buck 
	select id,name from stu cluster by (id)

再次查看HDFS就形成了分桶文件

查询分桶数据

select * from stu_buck

分桶抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。
Hive 可以通过对表进行抽样来满足这个需求。

查询表 stu_buck 中的数据。

hive (default)> select * from stu_buck       tablesample(bucket 1 out of 4 on id);

注：tablesample 是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。y 必须是 table 总 bucket 数的倍数或者因子。
hive 根据 y 的大小，决定抽样的比例。
例如，table 总共分了 4 份，当 y=2 时，抽取(4/2=)2 个 bucket 的数据，
当 y=8 时，抽取(4/8=)1/2个 bucket 的数据

x 表示从哪个 bucket 开始抽取。
例如，table 总 bucket 数为 4，tablesample(bucket 4 out of4)，表示总共抽取（4/4=）1 个 bucket 的数据，
抽取第 4 个 bucket 的数据。

注意：
x 的值必须小于等于 y 的值，否则
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than
denominator in sample clause for table stu_buck

数据块抽样

Hive 提供了另外一种按照百分比进行抽样的方式，这种是基于行数的，按照输入路径下的数据块百分比进行的抽样。

hive (default)> select * from stu tablesample(0.1 percent) ;

提示：这种抽样方式不一定适用于所有的文件格式。另外，这种抽样的最小抽样单元是一个 HDFS 数据块。因此，如果表的数据大小小于普通的块大小 128M 的话，那么将会返回所有行。

本博客仅为博主学习总结，感谢各大网络平台的资料。蟹蟹!!