Hive 查询
鸡汤: 为之所想，行之所动
1. 基本¶
1.1 基本查询¶
- 全表查询
1: jdbc:hive2://master:10000> select * from student3; +--------------+----------------+--+ | student3.id | student3.name | +--------------+----------------+--+ +--------------+----------------+--+ No rows selected (0.068 seconds) 1: jdbc:hive2://master:10000> select * from student; +-------------+---------------+--+ | student.id | student.name | +-------------+---------------+--+ | 1 | 张三 | | 2 | 李四 | | 3 | 王五 | | 4 | 赵六 | +-------------+---------------+--+ 4 rows selected (0.118 seconds)
1: jdbc:hive2://master:10000> select name from student; +-------+--+ | name | +-------+--+ | 张三 | | 李四 | | 王五 | | 赵六 | +-------+--+ 4 rows selected (0.11 seconds)
注意
1. SQL 语言大小写不敏感。 2. SQL 可以写在一行或者多行 3. 关键字不能被缩写也不能分行 4. 各子句一般要分行写。 5. 使用缩进提高语句的可读性。
1.2 别名¶
select id as sid,name as sname from student; 或者 select id sid,name sname from student;
别名作用: 就是便于引用，在列名和别名之间加入关键字as或者也可不加
1: jdbc:hive2://master:10000> select id,name from student; +-----+-------+--+ | id | name | +-----+-------+--+ | 1 | 张三 | | 2 | 李四 | | 3 | 王五 | | 4 | 赵六 | +-----+-------+--+ 4 rows selected (0.077 seconds) 1: jdbc:hive2://master:10000> select id as sid,name as sname from student; +------+--------+--+ | sid | sname | +------+--------+--+ | 1 | 张三 | | 2 | 李四 | | 3 | 王五 | | 4 | 赵六 | +------+--------+--+ 4 rows selected (0.082 seconds) 省略写法 1: jdbc:hive2://master:10000> select id sid,name sname from student; +------+--------+--+ | sid | sname | +------+--------+--+ | 1 | 张三 | | 2 | 李四 | | 3 | 王五 | | 4 | 赵六 | +------+--------+--+ 4 rows selected (0.078 seconds)
1.3 运算符¶
| 运算符 | 描述 |
|---|---|
| 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按位取反 |
1: jdbc:hive2://master:10000> select id,name from student; +-----+-------+--+ | id | name | +-----+-------+--+ | 1 | 张三 | | 2 | 李四 | | 3 | 王五 | | 4 | 赵六 | +-----+-------+--+ 4 rows selected (0.066 seconds) 1: jdbc:hive2://master:10000> select id*10 from student; +------+--+ | _c0 | +------+--+ | 10 | | 20 | | 30 | | 40 | +------+--+ 4 rows selected (0.112 seconds)
1.4 常用函数¶
1．求总行数（count） select count(*) cnt from emp; 2．求工资的最大值（max） select max(sal) max_sal from emp; 3．求工资的最小值（min） select min(sal) min_sal from emp; 4．求工资的总和（sum） select sum(sal) sum_sal from emp; 5．求工资的平均值（avg） select avg(sal) avg_sal from emp;
1.5 limit¶
典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
select * from emp limit 2;
2. where¶
使用WHERE子句，将不满足条件的行过滤掉,WHERE子句紧随FROM子句
查询出年龄大于20的所有员工
select * from emp where age >20;
2.1 比较运算符¶
下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。
| 操作符 | 支持的数据类型 | 描述 |
|---|---|---|
| A=B | 基本数据类型 | 如果A等于B则返回TRUE，反之返回FALSE |
| A<=>B | 基本数据类型 | 如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL |
| 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是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。 |
- 案例
1: jdbc:hive2://master:10000> select * from emp; +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+ | emp.empno | emp.ename | emp.job | emp.mgr | emp.hiredate | emp.sal | emp.comm | emp.deptno | +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+ | 1 | 张三 | it | 1 | 2017-07-22 | 10000.0 | 32.0 | 1 | | 2 | 李四 | it | 2 | 2018-03-18 | 3000.0 | 29.0 | 2 | | 3 | 王五 | it | 3 | 2019-05-05 | 9800.0 | 33.0 | 2 | | 4 | 赵六 | it | 4 | 2020-06-08 | 19000.0 | 45.0 | 3 | | 5 | 陈七 | it | 5 | 2010-06-08 | 9000.0 | NULL | 1 | +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+
查询出薪水等于3000的所有员工 hive (default)> select * from emp where sal =3000; 查询工资在4000到10000的员工信息 hive (default)> select * from emp where sal between 4000 and 10000; 查询comm为的所有员工信息 hive (default)> select * from emp where comm is null; 查询工资是4000或10000的员工信息 hive (default)> select * from emp where sal IN (1500, 5000)
详细操作
1: jdbc:hive2://master:10000> select * from emp where sal=3000; +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+ | emp.empno | emp.ename | emp.job | emp.mgr | emp.hiredate | emp.sal | emp.comm | emp.deptno | +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+ | 2 | 李四 | it | 2 | 2018-03-18 | 3000.0 | 29.0 | 2 | +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+ 1: jdbc:hive2://master:10000> select * from emp where sal between 4000 and 10000; +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+ | emp.empno | emp.ename | emp.job | emp.mgr | emp.hiredate | emp.sal | emp.comm | emp.deptno | +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+ | 1 | 张三 | it | 1 | 2017-07-22 | 10000.0 | 32.0 | 1 | | 3 | 王五 | it | 3 | 2019-05-05 | 9800.0 | 33.0 | 2 | | 5 | 陈七 | it | 5 | 2010-06-08 | 9000.0 | NULL | 1 | +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+ 2 rows selected (0.18 seconds) 1: jdbc:hive2://master:10000> select * from emp where comm is null; +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+ | emp.empno | emp.ename | emp.job | emp.mgr | emp.hiredate | emp.sal | emp.comm | emp.deptno | +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+ | 5 | 陈七 | it | 5 | 2010-06-08 | 9000.0 | NULL | 1 | +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+ 1: jdbc:hive2://master:10000> select * from emp where sal in (4000 ,10000); +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+ | emp.empno | emp.ename | emp.job | emp.mgr | emp.hiredate | emp.sal | emp.comm | emp.deptno | +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+ | 1 | 张三 | it | 1 | 2017-07-22 | 10000.0 | 32.0 | 1 | +------------+------------+----------+----------+---------------+----------+-----------+-------------+--+
2.2 Like¶
- 使用LIKE运算选择类似的值
- 选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。 _ 代表一个字符。
- RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
案例
查找以2开头薪水的员工信息 hive (default)> select * from emp where sal LIKE '2%'; 查找第二个数值为2的薪水的员工信息 hive (default)> select * from emp where sal LIKE '_2%'; 查找薪水中含有2的员工信息 hive (default)> select * from emp where sal RLIKE '[2]';
2.3 逻辑运算符¶
| 操作符 | 含义 |
|---|---|
| AND | 逻辑并 |
| OR | 逻辑或 |
| NOT | 逻辑否 |
案例
查询薪水大于1000，部门是30 hive (default)> select * from emp where sal>1000 and deptno=30; 查询薪水大于1000，或者部门是30 hive (default)> select * from emp where sal>1000 or deptno=30; 查询除了20部门和30部门以外的员工信息 hive (default)> select * from emp where deptno not IN(30, 20);
3. 分组¶
3.1 group by¶
GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
create table emp
计算emp表每个部门的平均工资 hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno; 计算emp每个部门中每个岗位的最高薪水 hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;
3.2 having¶
having与where不同点
- where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
- where后面不能写分组函数，而having后面可以使用分组函数。
- having只用于group by分组统计语句。
案例
求每个部门的平均薪水大于2000的部门 求每个部门的平均工资 hive (default)> select deptno, avg(sal) from emp group by deptno; 求每个部门的平均薪水大于2000的部门 hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;