0%

HiveDML数据操作之查询

发表于 分类于
HiveDML是Hive中对表中数据的查询、插入、删除和修改的语言。

一、常用函数

总行数

1
select count(*) from emp;

最大值

1
select max(id) from emp;

最小值

1
select min(id) from emp;

总和

1
select sum(id) from emp;

平均值

1
select avg(id) from emp;

二、案例实操

2.1 比较运算符

查出薪水大于1000的虽有员工emp

1
select * from emp where sal > 1000;

查出薪水等于1000的员工

1
select * from emp where sal = 1000;

查询工资在1000到5000的员工

1
select * from emp where sal between 1000 and 5000;

查询comm为空的所有员工

1
select * from emp where comm is null;

查询工资是1000或者5000的员工

1
select * from emp where sal in (1000, 5000);

2.2 Like RLike

查询薪资是以2开头的员工

1
select * from emp where sal like '2%';

查询薪水中第二个数值为2的员工信息

1
select * from emp where sal like '_2%';

查询薪水中含有2的员工信息 (rlike是Hive的一个扩展功能，可以通过正则表达式来指定匹配条件)

1
select * from emp where sal rlike '[2]';

2.3 Group By

计算各个部门的平均工资

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

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

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

2.4 Having

1.having 与 where 不同点
(1)where 针对表中的列发挥作用，查询数据;having 针对查询结果中的列发挥作用， 筛选数据。
(2)where 后面不能写聚合函数，而 having 后面可以使用聚合函数。 (3)having 只用于 group by 分组统计语句。
计算平均薪资大于2000的部门

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

2.5 JOIN

Join 连接谓词中不支持 or
左连接

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

右连接

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

内连接

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

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

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

笛卡尔积
1.笛卡尔集会在下面条件下产生
(1)省略连接条件 (2)连接条件无效 (3)所有表中的所有行互相连接

1
select empno, dname from emp, dept;

2.6 排序

  • Order By: 全局排序，一个Reducer
  • Sort By: 每个Reducer内部进行排序，对全局结果集来说不是排序。
  • Distribute By: 类似MR中partition，进行分区，结合sort by使用。
  • Cluster By: 当distribute by和sorts by字段相同时，可以使用cluster by方式。cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

2.7 分桶表

略

2.8 CASE WHEN

  1. 数据准备
name dept_id sex
悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女 
1
2
3
4
5
6
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;

2.9 其他常用的查询函数

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

1)date_format:格式化时间

1
2
3
4
hive(default)> select date_format('2019-06-29','yyyy-MM-dd');
OK
_c0
2019-06-29

2)date_add:时间跟天数相加

1
2
3
4
5
6
7
8
hive (default)> select date_add('2019-06-29',5);
OK
_c0
2019-07-04
hive (default)> select date_add('2019-06-29',-5);
OK
_c0
2019-06-24

3)date_sub:时间跟天数相减

1
2
3
4
5
6
7
8
9
10
11
12
hive (default)> select date_sub('2019-06-29',5);
OK
_c0
2019-06-24
hive (default)> select date_sub('2019-06-29 12:12:12',5);
OK
_c0
2019-06-24
hive (default)> select date_sub('2019-06-29',-5);
OK
_c0
2019-07-04

4)datediff:两个时间相减

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
hive (default)> select datediff('2019-06-29','2019-06-24');
OK
_c0
5
hive (default)> select datediff('2019-06-24','2019-06-29');
OK
_c0
-5
hive (default)> select datediff('2019-06-24 12:12:12','2019-06-29');
OK
_c0
-5
hive (default)> select datediff('2019-06-24 12:12:12','2019-06-29 13:13:13');
OK
_c0
-5