查询基本语法
[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available
starting with Hive 0.13.0)
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]
数据准备

create database test;
use test;

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

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

create table if not exists location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';

create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';
create table stu(id int, name string)
row format delimited fields terminated by '\t';

create table emp_sex(
name string, 
dept_id string, 
sex string) 
row format delimited fields terminated by "\t";

create table movie_info(
movie string, 
category array<string>) 
row format delimited fields terminated by "\t"
collection items terminated by ",";

create table business(
name string, 
orderdate string,
cost int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

create table score(
name string,
subject string, 
score int) 
row format delimited fields terminated by "\t";


load data local inpath '/opt/module/datas/dept.txt' into table test.dept;
load data local inpath '/opt/module/datas/emp.txt' into table test.emp;
load data local inpath '/opt/module/datas/location.txt' into table test.location;
load data local inpath '/opt/module/datas/student.txt' into table stu;
load data local inpath '/opt/module/datas/emp_sex.txt' into table emp_sex;
load data local inpath '/opt/module/datas/movie.txt' into table movie_info;
load data local inpath "/opt/module/datas/business.txt" into table business;

dept.txt内容如下

10  ACCOUNTING  1700
20  RESEARCH    1800
30  SALES   1900
40  OPERATIONS  1700

emp.txt内容如下

7369    SMITH   CLERK   7902    1980-12-17  800.00      20
7499    ALLEN   SALESMAN    7698    1981-2-20   1600.00 300.00  30
7521    WARD    SALESMAN    7698    1981-2-22   1250.00 500.00  30
7566    JONES   MANAGER 7839    1981-4-2    2975.00     20
7654    MARTIN  SALESMAN    7698    1981-9-28   1250.00 1400.00 30
7698    BLAKE   MANAGER 7839    1981-5-1    2850.00     30
7782    CLARK   MANAGER 7839    1981-6-9    2450.00     10
7788    SCOTT   ANALYST 7566    1987-4-19   3000.00     20
7839    KING    PRESIDENT       1981-11-17  5000.00     10
7844    TURNER  SALESMAN    7698    1981-9-8    1500.00 0.00    30
7876    ADAMS   CLERK   7788    1987-5-23   1100.00     20
7900    JAMES   CLERK   7698    1981-12-3   950.00      30
7902    FORD    ANALYST 7566    1981-12-3   3000.00     20
7934    MILLER  CLERK   7782    1982-1-23   1300.00     10

location.txt内容如下

1700    Beijing
1800    London
1900    Tokyo

student.txt内容如下

6       zhao`
7       jun
8       feng
9       xiang
10      bin

movie.txt内容如下

无双  剧情,动作,犯罪
找到你 剧情
影   剧情,动作,古装
嗝嗝老师 Hichki 剧情,喜剧
李茶的姑妈   喜剧
胖子行动队   喜剧,动作
西虹市首富   喜剧
黄金兄弟    动作,犯罪

business内容如下

jack    2018-01-01  10
tony    2018-01-02  15
jack    2018-02-03  23
tony    2018-01-04  29
jack    2018-01-05  46
jack    2018-04-06  42
tony    2018-01-07  50
jack    2018-01-08  55
mart    2018-04-08  62
mart    2018-04-09  68
neil    2018-05-10  12
mart    2018-04-11  75
neil    2018-06-12  80
mart    2018-04-13  94

基本查询

全表和特定列查询

-- 全表查询
select * from emp;
select * from dept;
-- 选择特定列查询
 select dptno, dname from dept;

注意

1. SQL 语言大小写不敏感。
2. SQL 可以写在一行或者多行
3. 关键字不能被缩写也不能分行
4. 各子句一般要分行写。
5. 使用缩进提高语句的可读性。

列别名

重命名一个列，便于计算，紧跟列名，也可以在列名和别名之间加入关键字‘AS’

select dptno as no, dname name from dept;

算术运算符

select sal, sal * 1.3 from emp;

常用函数

-- 求总行数（count）
select count(empno) from emp;
-- 求工资的最大值（max）
select max(sal) from emp;
-- 求工资的最小值（min）
select min(sal) from emp;
-- 求工资的总和（sum）
select sum(sal) from emp;
-- 求工资的平均值（avg）
select avg(sal) from emp;

limit语句

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

select * from emp limit 5;

Where语句

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

 select * from emp where sal > 2000;

比较运算符

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

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

Like和Rlike

使用LIKE运算选择类似的值，选择条件可以包含字符或数字，% 代表零个或多个字符(任意个字符)，_ 代表一个字符。
RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

-- 查找以2开头薪水的员工信息
select * from emp where sal like '2%';
-- 查找第二个数值为2的薪水的员工信息
select * from emp where sal like'_2%';
--查找薪水中含有2的员工信息

逻辑运算符

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

分组

Group By语句

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

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

Having语句

having与where不同点

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

-- 部门的平均薪水大于2000的部门
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

Join语句

等值join

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

-- 查询员工编号、员工名称、部门编号和部门名称
select e.empno, e.ename,d.deptno,d.dname from emp e join dept d on e.deptno = d.deptno;

表的别名

好处

1. 使用别名可以简化查询
2. 使用表名前缀可以提高执行效率

内连接

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

select e.empno, e.ename,d.deptno,d.dname from emp e join dept d on e.deptno = d.deptno;

左外连接

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

 select e.empno,e.ename,d.deptno,d.dname from emp e left join dept d on e.deptno = d.deptno;

右外连接

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

select e.empno,e.ename,d.deptno,d.dname from emp e right join dept d on e.deptno = d.deptno;

满外连接

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

多表链接

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

select e.empno,e.ename,d.deptno,d.dname,l.loc_name from emp e join dept d on e.deptno = d.deptno join location l on d.loc=l.loc;

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

笛卡尔积

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

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

链接谓词不支持or

-- 错误的sql
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
= d.deptno or e.ename=d.ename;

排序

全局排序order by

Order By：全局排序，一个MapReduce
ASC（ascend）: 升序（默认），DESC（descend）: 降序
ORDER BY 子句在SELECT语句的结尾

-- 查询员工信息按工资升序排列
select * from emp order by sal;
-- 查询员工信息按工资降序排列
select * from emp order by sal desc;
-- 按照员工薪水的2倍排序
select sal, sal * 2 doublesal from emp order by doublesal;
-- 按照部门和工资升序排序
select * from emp order by deptno,sal;

内部排序sort by

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

-- 设置reduce个数
set mapreduce.job.reduces=3;
-- 根据部门编号降序查看员工信息
select * from emp sort by deptno desc;
-- 将查询结果导入到文件中（按照部门编号降序排序）
insert overwrite local directory '/opt/module/datas/sort_result' row format delimited fields terminated by '\t' select * from emp sort by deptno desc;

分区排序distribute by

Distribute By,类似MR中partition，进行分区，结合sort by使用
注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前

-- 先按照部门编号分区，再按照员工编号降序排序
insert overwrite local directory '/opt/module/datas/distribute_result' row format delimited fields terminated by '\t' select * from emp distribute by deptno sort by empno desc;

Cluster By

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

insert overwrite local directory '/opt/module/datas/cluster_result' row format delimited fields terminated by '\t' select * from emp cluster by deptno;

分桶及抽样查询

分区针对的是数据的存储路径；分桶针对的是数据文件。
分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑
分桶是将数据集分解成更容易管理的若干部分的另一个技术

分桶表数据存储

-- 设置相关参数
set hive.enforce.bucketing=true;
set mapreduce.job.reduces=-1;
-- 通过查询的方式插入数据
insert into table stu_buck select * from stu;
-- 查询数据
select * from stu_buck;

分桶表数据存储.png

分桶表数据查询.png

分桶抽样查询

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

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

y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。
x表示从哪个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第4(x+y)个bucket的数据。
注意：x的值必须小于等于y的值，否则

分桶抽样查询x小于y.png

其他常用查询函数

空字段赋值

NVL：给值为NULL的数据赋值，它的格式是NVL( string1, replace_with)。它的功能是如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL

select nvl(comm, -1) from emp;
select nvl(comm, mgr) from emp;
select nvl(comm, ename) from emp;

CASE WHEN

求出不同部门男女各多少人。结果如下

A  2  2
B  4  2

select dept_id, sum(case sex when '男' then 1 else 0 end) male_count, sum(case sex when '女' then 1 else 0 end) female_count
from emp_sex
group by dept_id;

结果如下

case when then else end.png

行转列

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
需求，把部门和性别相同的人归类到一起，结果如下

 A,男  唐僧|孙悟空|猪八戒|沙悟净
B,女  玉兔精|蜘蛛精
B,男  金角大王|银角大王

select temp.id_sex,concat_ws('|',collect_set(temp.name)) from(
select name, dept_id, sex, concat(dept_id, ',', sex) as id_sex from emp_sex 
)temp
group by temp.id_sex;

列转行

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合
需求，将电影分类中的数组数据展开

select movie,cate
from movie_info
lateral view explode(category) tableAlias as cate;

窗口函数

相关函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点，UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n)：往前第n行数据
LEAD(col,n)：往后第n行数据
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型

需求

1. 查询在2018年4月份购买过的顾客及总人数
2. 查询顾客的购买明细及月购买总额
3. 上述的场景,要将cost按照日期进行累加
4. 查询顾客上次的购买时间
5. 查询前20%时间的订单信息

对应sql

-- 查询在2018年4月份购买过的顾客及总人数
select name,count(*) over()
from business
where orderdate like '2018-04-%'
group by name;
-- 查询顾客的购买明细及月购买总额
select name, orderdate, cost, sum(cost) over(distribute by month(orderdate))
from business;
-- 查询顾客的购买明细及月购买总额，将cost按照日期进行累加
select name, orderdate, cost, 
sum(cost) over() as sumcost1,--所有行相加
sum(cost) over(distribute by month(orderdate)) as sumcost2, --按月分组，组内数据相加
sum(cost) over(distribute by month(orderdate) sort by orderdate) as sumcost3, -- 按月分组，组内数据累加
sum(cost) over(distribute by month(orderdate) sort by orderdate rows between unbounded preceding and current row) as sumcost4, --和sumcost3一样,由起点到当前行的聚合
sum(cost) over(distribute by month(orderdate) sort by orderdate rows between 1 preceding and current row) as sumcost5, -- 当前行和前一行做集合
sum(cost) over(distribute by month(orderdate) sort by orderdate rows between 1 preceding and 1 following) as sumcost6, -- 当前行前面一行，当前行，当前行后面一行做聚合
sum(cost) over(distribute by month(orderdate) sort by orderdate rows between current row and unbounded following) as sumcost7 -- 当前行及后面所有行
from business;
-- 查询顾客上次购买时间
select name, orderdate, cost,
lag(orderdate,1) over(distribute by name sort by orderdate) prev_time
from business;
select name, orderdate, cost,
lag(orderdate,1,'1900-01-01') over(distribute by name sort by orderdate) prev_time
from business;
-- 查询前20%时间的订单信息
select * from
(select name, orderdate, cost, ntile(5) over(sort by orderdate) as sorted from business
)tmp
where tmp.sorted = 1;

Rank

相关函数说明

RANK() 排序相同时会重复，总数不会变
DENSE_RANK()排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

需求

计算每门学科成绩排名

对应sql

select name, subject, score,
rank() over(distribute by subject sort by score desc) as rk,
dense_rank() over(distribute by subject sort by score desc) as dr,
row_number() over(distribute by subject sort by score desc) as rn
from score;