基础语法及执行顺序

查询语句语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list| [DISTRIBUTE BY col_list] [SORT BY col_list]]
[LIMIT number]

书写次序和执行次序

顺序	书写次序	书写次序说明	执行次序	执行次序说明
1	`select`	查询	`from`	先执行表与表直接的关系
2	`from`	先执行表与表直接的关系	`on`
3	`join on`		`join`
4	`where`		`where`	过滤
5	`group by`	分组	`group by`	分组
6	`having`	分组后再过滤	`having`	分组后再过滤
7	`distribute by` `cluster by`	4个by	`select`	查询
8	`sort by`		`distinct`	去重
9	`order by`		`distribute by` `cluster by`	4个by
10	`limit`	限制输出的行数	`sort by`
11	`union/union all`	合并	`order by`
12			`limit`	限制输出的行数
13			`union /union all`	合并

基本查询（Select…From）

全表和特定列查询

首先数据准备，在/opt/module/hive/datas目录下增加dept.txt和emp.txt文件。

dept.txt文件内容如下：

10	行政部	1700
20	财务部	1800
30	教学部	1900
40	销售部	1700

emp.txt文件内容如下：

7369	张三	研发	800.00	30
7499	李四	财务	1600.00	20
7521	王五	行政	1250.00	10
7566	赵六	销售	2975.00	40
7654	侯七	研发	1250.00	30
7698	马八	研发	2850.00	30
7782	金九	\N	2450.0	30
7788	银十	行政	3000.00	10
7839	小芳	销售	5000.00	40
7844	小明	销售	1500.00	40
7876	小李	行政	1100.00	10
7900	小元	讲师	950.00	30
7902	小海	行政	3000.00	10
7934	小红明	讲师	1300.00	30

然后创建表dept和emp，并加载数据如下所示：

create external table if not exists dept(
    deptno int,
    dname string,
    loc int
)
row format delimited
fields terminated by '\t';

load data local inpath '/opt/module/hive-3.1.2/datas/dept.txt' into table dept;

create external table if not exists emp(
    empno int,
    ename string,
    job string,
    sal double,
    deptno int
)
row format delimited
fields terminated by '\t';

load data local inpath '/opt/module/hive-3.1.2/datas/emp.txt' into table emp;

全表查询

1	select * from dept;

选择特定列查询

1	select dname, loc from dept;

注意：

SQL 语言大小写不敏感。

SQL可以写在一行或者多行

关键字不能被缩写也不能分行

各子句一般要分行写。

使用缩进提高语句的可读性。

列别名

列别名：为列指定一个别名

优点：便于计算

实现：紧跟列名，也可以在列名和别名之间加入关键字AS

查询税后收入（税后收入=总收入*80%）
1
select dname, loc * 0.8 from dept;
这个是直接查询的结果，结果中可以看到查询到的税后收入列明为_c1，这个列名没有什么实际意义，可以手动给其取一个别名，这样在打印的时候就会显示对应的别名。
1
select dname, loc * 0.8 as shuihou from dept;

这里的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`按位取反

案例演示见前一节给的案例，计算税后收入。

常用函数

函数名称	函数意义
`count`	求总行数
`max`	求最大值
`min`	求最小值
`sum`	求总和
`avg`	求平均值

求emp表的总行数
1
select count(1) from emp;
这个时候由于文件数不为1，但是行数确为1，此时需要经过MR才能计算总行数。
求emp表的工资最大值
1
select max(sal) from emp;
此时仍然需要经过MR才可以计算出结果。
为了让简单任务计算快一点，可以设置不让任务经过YARN集群，采用本地模式执行。

使用以下命令设置参数即可。
1
set hive.exec.mode.local.auto=true;
此时再执行相关代码就会再本地模式中运行MR，不需要走YARN使用集群计算。

但是要注意，这种情况下仅适用于数据量不大，并且计算难度小的任务。
求emp表工资最小值。
1
select min(sal) from emp;
设置完本地模式后，此时MR就不使用YARN去执行了，直接在本地执行，大大节省了等待的时间。
求emp表工资总和。
1
select sum(sal) from emp;
求emp表工资平均值。
1
select avg(sal) from emp;

Limit语句

一般的查询会返回多行数据，在成产环境中，通常使用LIMIT子句用于限制返回的行数。

返回指定的行数

1	select * from emp limit 5;

指定偏移量和显示的行数

1	select * from emp limit 3, 5;

where语句

使用WHERE子句，将不满足条件的行过滤掉。
WHERE子句紧随FROM子句。

查询出薪水大于2000的所有员工姓名。
1
select ename, sal from emp where sal > 2000;

注意：where子语句中不能使用字段别名。

因为where的运算次序比select的运算次序高，在执行where时别名还未生效。

比较运算符

下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。

操作符	支持的数据类型	描述
`A=B`	基本数据类型	如果`A`等于`B`则返回`TRUE`，反之返回`FALSE`
`A<=>B`	基本数据类型	如果`A`和`B`都为`NULL`，则返回`TRUE`，如果一边为`NULL`，返回`False`
`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`任一为`NUL`L，则结果为`NULL`。如果`A`的值大于等于`B`而且小于或等于`C`，则结果为`TRUE`，反之为`FALSE`。如果使用`NOT`关键字则可达到相反的效果。
`A IS NULL`	所有数据类型	如果`A`等于`NUL`L，则返回`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`	`STRING`类型	`B`是基于`java`的正则表达式，如果`A`与其匹配，则返回`TRUE`；反之返回`FALSE`。匹配使用的是`JDK`中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串`A`相匹配，而不是只需与其子字符串匹配。

案例实操：

查询出薪水等于5000的所有员工

1	select ename, job from emp where sal = 5000;

查询工资在500到1000的员工信息

1	select ename, job from emp where sal between 500 and 1000;

查询job为空的所有员工信息

1	select ename, job from emp where job is null;

查询工资是1500或5000的员工信息

1	select ename, job from emp where sal in (1500, 5000);

like和rlike

like关键字：使用LIKE运算选择类似的值
- 选择条件可以包含字符或数字
- % -->代表零个或多个字符(任意个字符)。

_ -->代表一个字符。

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

案例实操：

查找名字以“小”开头的员工信息

1	select ename, job from emp where ename like "小%";

1	select ename, job from emp where ename rlike "^小";

查找名字以“明”结尾的员工信息

1	select ename, job from emp where ename like "%明";

1	select ename, job from emp where ename rlike "明$";

查找名字中带有“明”的员工信息

1	select ename, job from emp where ename like "%明%";

1	select ename, job from emp where ename rlike "明+";

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

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

案例实操：

查询薪水大于1000，部门是30的员工信息。

1	select ename, job from emp where sal > 1000 and deptno = 30;

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

1	select ename, job, deptno, sal from emp where sal > 1000 or deptno = 30;

查询除了20部门和30部门以外的员工信息
1
select * from emp where deptno not in (20, 30);

分组查询

Group By语句

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

案例实操：

计算emp表每个部门的平均工资

1	select deptno, avg(sal) sal_avg from emp group by deptno;

计算emp每个部门中每个岗位的最高薪水

1	select deptno, job, max(sal) sal_max from emp group by deptno, job;

Having语句

having与where不同点：

where后面不能写聚合函数，而having后面可以使用聚合函数。
having只用于group by分组统计语句。

案例实操：

求每个部门的平均工资

1	select deptno, avg(sal) sal_avg from emp group by deptno;

求每个部门的平均薪水大于2000的部门
1
select deptno, avg(sal) sal_avg from emp group by deptno having sal_avg > 2000;
注意：在having中可以使用字段别名，因为where是先判断数据是否符合条件，符合条件的数据再入内存中然后生成别名，所以别名是where判断条件后才生成的，这也是where不能使用别名的原因。

having是先从磁盘中读取数据到内存中，然后才进行判断，这个时候别名已经生成了，所以having可以使用别名。

Join语句

常见的连接方式有6种，后面依次举例说明。

要演示该部分内容，首先需要对前面的数据进行修改，每个表格插入几条特有数据。

1	insert into table emp values(1001, '王麻子', '研发', 4000, 50);

1	insert into table dept values(60, '总经办', 1900);

内连接

select
    e.deptno,
    e.job,
    e.sal,
    d.dname,
    d.loc
from emp as e
join dept d on e.deptno = d.deptno;

结果如下所示，这里我们采用IDEA方式运行，结果更直观。

左连接

-- 左连接
select
    e.ename,
    e.deptno,
    e.job,
    e.sal,
    d.dname,
    d.loc
from emp as e
left join dept d on e.deptno = d.deptno;

可以看到王麻子此时也被包含到了结果中。

思考：如何只保存左表所特有的数据。

select
    e.ename,
    e.deptno,
    e.job,
    e.sal,
    d.dname,
    d.loc
from emp as e
left join dept d on e.deptno = d.deptno
where d.dname is null;

此时结果中只有王麻子。

右连接

-- 右连接
select
    e.ename,
    e.deptno,
    e.job,
    e.sal,
    d.dname,
    d.loc
from emp as e
right join dept d on e.deptno = d.deptno;

同样思考一下，如何只保存右表中所特有的数据。

-- 右连接,只保存右表中所特有的数据
select
    e.ename,
    e.deptno,
    e.job,
    e.sal,
    d.dname,
    d.loc
from emp as e
right join dept d 
on e.deptno = d.deptno
where e.ename is null;

此时结果中只有总经办

外连接

-- 外连接
select
    e.ename,
    e.deptno,
    e.job,
    e.sal,
    d.dname,
    d.loc
from emp as e
full outer join dept d on e.deptno = d.deptno;

思考：如何只保存两个表中所特有的数据。

-- 外连接, 只保存两个表中所特有的数据。
select
    e.ename,
    e.deptno,
    e.job,
    e.sal,
    d.dname,
    d.loc
from emp as e
full outer join dept d 
on e.deptno = d.deptno
where e.ename is null or d.dname is null;

多表连接

为了演示这个内容，还需要再创建一个表location.txt，这个表的内容是具体的地点编号对应的地点，内容如下：

1700	武汉市洪山区
1800	武汉市汉阳区
1900	武汉市江夏区
2000	武汉市汉口区

创建表如下所示：

-- 创建地点表
create external table if not exists location(
    loc int,
    loc_string string
)
row format delimited
fields terminated by '\t'
lines terminated by '\n';

-- 加载数据
load data local inpath '/opt/module/hive-3.1.2/datas/location.txt' into table location;

查询每个员工姓名、部门名称及部门所在地

-- 查询每个员工姓名、部门名称及部门所在地
select
    e.ename,
    d.dname,
    l.loc_name
from emp e
left join dept d on e.deptno = d.deptno
left join location l on d.loc = l.loc;

笛卡尔积

笛卡尔积会在下面条件下产生

省略连接条件
连接条件无效
所有表中的所有行互相连接

案例实操：

1 2	select empno, dname from emp, dept;

此时一共获取了75条数据。

排序

全局排序（Order By）

Order By：全局排序，只有一个Reducer。

顺序：① ASC（ascend）: 升序（默认）；② DESC（descend）: 降序

位置：ORDER BY子句在SELECT语句的结尾

案例实操：

查询员工信息按工资升序排列
1
select * from emp order by sal asc;
查询员工信息按工资降序排列
1
select * from emp order by sal desc;

按照员工税后薪水排序

1	select ename, sal*0.8 shuihou from emp order by shuihou;

按照部门升序和工资降序排序

1	select deptno, sal from emp order by deptno asc, sal desc;

注意：使用order by对数据排序时只有一个reduce，面对海量数据排序时，一个reduce效率太低，并且大多数场景下，并不需要全局排序，一般都是筛选出top5、top10等等。

例如，我们手动把reduces数设置为3
1
2
>-- 设置reduce为3
>set mapreduce.job.reduces=3;
注意：由于前面我们设置过本地模式运行，所以这里需要将其设置回来使用YARN去执行。可以手动执行命令set hive.exec.mode.local.auto=false;切换，也可以简单点直接重启Hive客户端。

再次执行排序操作
1
>select * from emp order by sal desc;
可以看到，即使我们手动将reduce的值设置为3，最终在执行排序任务时，仍然时只有一个reducer。

每个Reduce内部排序（Sort By）

Sort by:在每个Reducer内部进行排序，对全局结果集来说不是有序。sort by为每个reducer产生一个排序文件，每个Reducer内部进行排序，对全局结果来说不是排序。

-- 设置reducer个数为3
set mapreduce.job.reduces=3;

-- 排序
select * from emp sort by sal desc;

可以看到此时排序的结果并不是完全有序的，将结果导入文件中。

insert overwrite local directory "/opt/module/hive-3.1.2/datas/export"
row format delimited
fields terminated by '\t'
lines terminated by '\n'
select * from emp sort by sal desc;

此时在export中就会生成三个文件，这三个文件分别对应于3个reducer。

依次查看每个文件内容：

可以看到这三个文件内容是排序的结果。

通过观察结果我们发现，进入reduce里面的数据是随机的，如何才能控制相应字段进入到同一个reduce中呢？

分区（Distribute By）

在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常时为了进行后续的聚集操作。

distribute by可以实现。distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。

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

set mapreduce.job.reduces=3;
insert overwrite local directory "/opt/module/hive-3.1.2/datas/export"
row format delimited
fields terminated by '\t'
lines terminated by '\n'
select * from emp
distribute by deptno
sort by sal desc;

此时再次查看export目录下的文件内容。

可以看到此时已经按照部门进行分区了。

注意：

distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一起。

第一个表：30%3=0

第二个表：40%3=1;10%3=1

第三个表：50%3=2;20%3=2

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

测试完毕后，记得将mapreduce.job.reduces的值恢复到-1，否则下面的分区和分桶测试就会报错。

Cluster By

当distribute by和sort by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。
但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

查询emp表中的员工姓名、员工编号、部门编号、薪资，并按照部门编号分区排序。

select ename,empno,deptno,sal 
from emp 
distribute by deptno 
sort by deptno;

上面写法与以下写法等价。

1	select ename,empno,deptno,sal from emp cluster by deptno;

Ming-Log's Blog

Hadoop-18-Hive3查询操作

基础语法及执行顺序

基本查询（Select…From）

全表和特定列查询

列别名

算数运算符

常用函数

Limit语句

where语句

比较运算符

like和rlike

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

分组查询

Group By语句

Having语句

Join语句

内连接

左连接

右连接

外连接

多表连接

笛卡尔积

排序

全局排序（Order By）

每个Reduce内部排序（Sort By）

分区（Distribute By）

Cluster By