查询

先创建部门表和员工表

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';

导入数据

load data local inpath '/opt/module/datas/dept.txt' into table dept;
load data local inpath '/opt/module/datas/emp.txt' into table emp;

查询

select * from emp; -- 全表查询
select empno, ename from emp; -- 特定列查询
select empno as pno, ename as name from emp; -- 起列别名 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 按位取反

select sal +1 from emp;

常用函数

UDF 函数

给定一个输入 返回一个输出

select substring(name,1,1) from emp;

UDAF 函数

给定多个输入 返回一个输出 聚合函数

select count(*) from emp; -- 统计个数
select max(sal) max_sal from emp; -- 此列最大值
select min(sal) min_sal from emp; -- 此列最小值
select sum(sal) sum_sal from emp;  -- 此列总和
select avg(sal) avg_sal from emp; -- 平均数

UDTF 函数

表生成函数 给定一个输入 返回多个输出

Limit

select * from emp limit 5; -- 取前n行

Where

条件语句

select * from emp where sal > 1000;

操作符	支持的数据类型	描述
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 任一为 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 是基于 java 的正则表达式，如果 A 与其匹配，则返回 TRUE；反之返回 FALSE。匹配使用的是 JDK 中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串 A 相匹配，而不是只需与其字符串匹配。

Like 和 Rlike

Like 是通配符匹配

select * from emp where ename like "A%"; -- 通配符 %_   %零个或多个字符 _单个字符 

Rlike 是正则表达式匹配

select * from emp where ename rlike "^A"; -- 正则匹配 

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

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

select * from emp where sal>1000 and deptno=30;

分组

Group By 语句

select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno; -- 按deptno分组

Having 语句

where 后面不能写分组函数，而 having 后面可以使用分组函数。

select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

mysql 执行顺序:

1. from
2. where
3. group by
4. select
5. having
6. order by
7. limit

Join

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

内连接

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

只有查询表和 join 表同时存在才保存

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

左外连接

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

就算了查询表在 join 没有符合的数据 也会返回查询结果

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

右外连接

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

如果 join 表中有查询表没有的数据 也会被保存下来

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

满外连接

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

左右表其中一方没有的都会被保存下 使用 null 代替

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

多表连接查询

SELECT e.ename, d.dname, l.loc_name
FROM   emp e 
JOIN   dept d
ON     d.deptno = e.deptno 
JOIN   location l
ON     d.loc = l.loc;

笛卡尔积

（1）省略连接条件 (即左表每个数据都会连接右表的每条数据 左表 * 右边)

（2）连接条件无效

（3）所有表中的所有行互相连接

select empno, dname from emp, dept;

排序

全局排序（Order By）

全局排序，只有一个 Reducer

select *
from emp 
order by sal desc;

select *  -- 多条件排序
from emp 
order by 
deptno asc,
sal desc;

• ASC（ascend）: 升序（默认）
• DESC（descend）: 降序
• 不推荐对所有数据排序 通常通过 limit 求前 n 个 减少 MapReduce 工作量

内部排序 (Sort By)

对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用 sort by。

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

set mapreduce.job.reduces=3; -- 设置reduce个数 默认为-1 即一个reduce
set mapreduce.job.reduces; -- 查询reduce个数

select * from emp sort by deptno desc;

分区排序 (Distribute By)

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

select * from emp
distribute by empno
sort by sal desc; -- 按empno分区排序 再按 sal内部排序

• distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后，余数相同的分到一个区。
• Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前

桶排序 (Cluster By)

当 distribute by 和 sorts 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;

分桶表和抽样查询

创建分桶表

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

hive3 中支持从本地上传 reduce 会自动帮我们分桶

load data local inpath "/opt/module/datas/student.txt" into table stu_buck;

如果导入卡住 尝试临时换成 mr 引擎

set hive.execution.engine = mr;

• 分桶可以使 无法分区的表进行分桶 (分区无法使用主键分区 因为主键是唯一)
• 每个桶 对应则一个文件
• 数据存放位置会根据 列值 + 桶数 来存放

抽样查询

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

select * from stu_buck tablesample(bucket 1 out of 4 on id); -- 根据查询结果 按id分成4份 从中取出第一份出来

• TABLESAMPLE (BUCKET x OUT OF y) x 的值必须小于等于 y 的值，否则报错
• 分桶表可以加快抽样效率

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) 个和第 3 (x+y) 个 bucket 的数据。