1. 查询
先创建部门表和员工表
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可以省略
2. 算术运算符
| 运算符 | 描述 |
|---|---|
| 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;
3. 常用函数
3.1. UDF函数
给定一个输入 返回一个输出
select substring(name,1,1) from emp;
3.2. 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; -- 平均数
3.3. UDTF函数
表生成函数 给定一个输入 返回多个输出
4. Limit
select * from emp limit 5; -- 取前n行
5. 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相匹配，而不是只需与其字符串匹配。 |
5.1. Like和Rlike
Like是通配符匹配
select * from emp where ename like "A%"; -- 通配符 %_ %零个或多个字符 _单个字符
Rlike是正则表达式匹配
select * from emp where ename rlike "^A"; -- 正则匹配
5.2. 逻辑运算符（And/Or/Not）
| 操作符 | 含义 |
|---|---|
| AND | 逻辑并 |
| OR | 逻辑或 |
| NOT | 逻辑否 |
select * from emp where sal>1000 and deptno=30;
6. 分组
6.1. Group By语句
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno; -- 按deptno分组
6.2. Having语句
where后面不能写分组函数，而having后面可以使用分组函数。
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
6.3. mysql执行顺序:
- from
- where
- group by
- select
- having
- order by
- limit
7. Join
Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接
7.1. 内连接
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
只有查询表和join表同时存在才保存
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
7.2. 左外连接
左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
就算了查询表在join没有符合的数据 也会返回查询结果
select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
7.3. 右外连接
右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
如果join表中有查询表没有的数据 也会被保存下来
select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
7.4. 满外连接
满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
左右表其中一方没有的都会被保存下 使用null代替
select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;
7.5. 多表连接查询
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;
7.6. 笛卡尔积
（1）省略连接条件 (即左表每个数据都会连接右表的每条数据 左表*右边)
（2）连接条件无效
（3）所有表中的所有行互相连接
select empno, dname from emp, dept;
8. 排序
8.1. 全局排序（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工作量
8.2. 内部排序(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;
8.3. 分区排序(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语句之前
8.4. 桶排序(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;
9. 分桶表和抽样查询
创建分桶表
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;
- 分桶可以使 无法分区的表进行分桶 (分区无法使用主键分区 因为主键是唯一)
- 每个桶 对应则一个文件
- 数据存放位置会根据 列值 + 桶数 来存放
9.1. 抽样查询
对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。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的数据。
若有收获，就点个赞吧
0 人点赞
