Hive笔记第三篇：Hive基础查询

wsl850
247 阅读9分钟

1.查询

语法

ELECT [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.1 基本查询

1.1.1 全表和特定列查询

1. 全表查询

hive (default)> select * from dept;
hive (default)> select deptno,dname,loc from dept;

2. 特定列查询

hive (default)> select deptno from dept;

1.1.2 列别名

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

hive (default)> select ename AS name, deptno dn from emp;

1.1.3 算术运算符

屏幕截图 2021-12-31 104025.png

hive (default)> select sal +1 from emp;

1.1.4 常用函数

1. 求总行数count()

hive (default)> select count(*) cnt  from emp;

2. 求最大值 max()

hive (default)> select max(sal) max_sal from emp;

3. 求最小值 min()

hive (default)> select min(sal) min_sal from emp;

4. 求平均值 avg()

hive (default)> select avg(sal) avg_sal from emp;

1.1.5 limit 语句

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

语法

select * from tableName limit i,n
# tableName：表名
# i：为查询结果的索引值(默认从0开始)，当i=0时可省略i
# n：为查询结果返回的数量
# i与n之间使用英文逗号","隔开 
# limit n 等同于 limit 0,n

案例

hive (default)> select * from emp limit 5;
OK
emp.empno	emp.ename	emp.job	emp.mgr	emp.hiredate	emp.sal	emp.comm	emp.deptno
7369	SMITH	CLERK	7902	1980-12-17	800.0	NULL	20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.0	300.0	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.0	500.0	30
7566	JONES	MANAGER	7839	1981-4-2	2975.0	NULL	20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.0	1400.0	30

hive (default)> select * from emp limit 2,5;
OK
emp.empno	emp.ename	emp.job	emp.mgr	emp.hiredate	emp.sal	emp.comm	emp.deptno
7521	WARD	SALESMAN	7698	1981-2-22	1250.0	500.0	30
7566	JONES	MANAGER	7839	1981-4-2	2975.0	NULL	20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.0	1400.0	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.0	NULL	30
7782	CLARK	MANAGER	7839	1981-6-9	2450.0	NULL	10

1.1.6 Where语句

hive (default)> select * from emp where sal>2000;
OK
emp.empno	emp.ename	emp.job	emp.mgr	emp.hiredate	emp.sal	emp.comm	emp.deptno
7566	JONES	MANAGER	7839	1981-4-2	2975.0	NULL	20
7698	BLAKE	MANAGER	7839	1981-5-1	2850.0	NULL	30
7782	CLARK	MANAGER	7839	1981-6-9	2450.0	NULL	10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.0	NULL	20
7902	FORD	ANALYST	7566	1981-12-3	3000.0	NULL	20

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

1.1.7 计较运算符

操作符支持的数据类型描述
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 BSTRING 类型使用B 是一个 SQL 下的简单正则表达式，也叫通配符模式，如果 A 与其匹配的话，则返回 TRUE；反之返回 FALSE。B 的表达式。说明如下：‘x%’表示 A 必须以字母‘x’开头，‘%x’表示 A必须以字母’x’结尾，而‘%x%’表示 A 包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用 NOT 关键字则可达到相反的效果
A RLIKE B, A REGEXP BSTRING 类型B 是基于 java 的正则表达式，如果 A 与其匹配，则返回TRUE；反之返回 FALSE。匹配使用的是 JDK 中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串 A 相匹配，而不是只需与其字符串匹配

案例

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

1.1.8 Like和RLike

选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。

RLIKE 子句

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

案例

（1）查找名字以 A 开头的员工信息

hive (default)> select * from emp where ename like 'A%';

（2）查找名字中第二个字母为 A 的员工信息

hive (default)> select * from emp where ename like '_A%';

（3）查找名字中带有 A 的员工信息

hive (default)> select * from emp where ename RLIKE '[A]';

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

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

案例

（1）查询薪水大于 1000，部门是 30

hive (default)> select * from emp where sal>1000 and deptno=30;

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

hive (default)> select * from emp where sal>1000 or deptno=30;

（3）查询除了 20 部门和 30 部门以外的员工信息

hive (default)> select * from emp where deptno not in (20,30);

1.2 分组

1.2.1 Group By 语句

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

案例

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

hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno;

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

hive (default)> select deptno, job,max(sal) from emp group by deptno,job;

1.2.2 Having 语句

1. having 与 where 不同点

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

2. 案例

（1）求每个部门的平均薪水大于 2000 的部门

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

1.3 Join

1.3.1 等值join

1. 根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称

hive (default)> select e.empno,e.ename,d.deptno,d.dname from emp e join dept d on e.deptno=d.deptno;

1.3.2 内连接

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

hive (default)> select e.empno,e.ename,d.deptno,d.dname from emp e join dept d on e.deptno=d.deptno;

1.3.3 左外连接

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

hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

1.3.4 右外连接

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

hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

1.3.5 满外连接

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

hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

1.3.6 多表连接

数准备

1700    Beijing
1800    London
1900    Tokyo

建表

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

导入数据

hive (default)> load data local inpath '/opt/data/location.txt' into table location;

多表连接查询

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

1.4 排序

1.4.1 全局排序（OrderBy）

Order By：全局排序，只有一个Reducer 使用Order By字句排序

ASC:升序 （默认）
DESC:降序

案例

1.查询员工信息按工资升序排列

hive (default)> select * from emp order by sal;

2.查询员工信息按工资降序排列

hive (default)> select * from emp order by sal desc;

3.按照别名排序

hive (default)> select ename, sal*2 twosal from emp order by twosal;

4.多个列排序

hive (default)> select ename,deptno,sal from emp order by deptno,sal;

1.4.2 每个Reduce内部排序(Sort By)

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

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

1. 设置reduce个数

hive (default)> set mapreduce.job.reduce=3;

2. 查看reduce个数

hive (default)> set mapreduce.job.reduce;

3. 根据部门编号降序查看员工信息

hive (default)> select * from emp sort by deptno desc;

4. 将查询结果导入到文件中(按部门编号降序)

insert overwrite local directory '/opt/data/export/sort-by' select * from emp order by deptno desc;

1.4.3 分区排序(Distribute By)

Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition（自定义分区），进行分区，结合 sort by 使用。

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

先按照部门编号分区，再按照员工编号降序排序

insert overwrite local directory '/opt/data/export/distribute-by'  select * from emp distribute by deptno sort by empno desc;

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

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

1.4.4 Cluster By

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

以下两种写法等价

hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
avatar
文章
阅读
粉丝