Hive查询操作汇总

查询

LanguageManual Select - Apache Hive - Apache Software Foundation

SELECT [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 基本查询（SELECT…FROM）

1.1 全表和特定字段查询

1）全表查询（2.4.2章节表已经创建）

hive (soft863db)> select * from emp;

2）选择特定列查询

hive (soft863db)> select empno, ename from emp;

注意：

（1）SQL 语言大小写不敏感。

（2）SQL 可以写在一行或者多行

（3）各子句一般要分行写。

1.2 列别名

1）重命名一个列。

2）便于计算。

3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’

4）案例实操

         （1）查询名称和部门

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

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和B按位取异或
~A	A按位取反

1.4 常用函数

1）求总行数（count）（RM执行）

hive (soft863db)> select count(*) cnt from emp;[WU1] 

2）求工资的最大值（max）

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

3）求工资的最小值（min）

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

4）求工资的总和（sum）

hive (soft863db)> select sum(sal) sum_sal from emp;

5）求工资的平均值（avg）

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

1.5 LIMIT语句

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

hive (soft863db)> select * from emp limit 5;

2 WHERE语句

1）使用WHERE 子句，将不满足条件的行过滤掉。

2）WHERE 子句紧随 FROM 子句。

3）案例实操

查询出薪水大于1000的所有员工

hive (soft863db)> select * from emp where sal >1000;

2.1 比较运算符（BETWEEN/IN/ IS NULL）

1）下面表中描述了谓词操作符，这些操作符同样可以用于HAVING语句中。

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回TRUE，反之返回FALSE
A!=B	基本数据类型	如果A不等于B，则返回TRUE，反之返回FALSE
A<B	基本数据类型	如果A小于B，则返回TRUE，反之返回FALSE
A<=B	基本数据类型	如果A小于等于B，则返回TRUE，反之返回FALSE
A>B	基本数据类型	如果A大于B，则返回TRUE，反之返回FALSE
A>=B	基本数据类型	如果A大于等于B，则返回TRUE，反之返回FALSE
A [NOT] BETWEEN B AND C	基本数据类型	如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL	所有数据类型	如果A等于NULL，则返回TRUE，反之返回FALSE
A IS NOT NULL	所有数据类型	如果A不等于NULL，则返回TRUE，反之返回FALSE
IN(数值1, 数值2)	所有数据类型	等于数值1、数值2，返回TRUE
A [NOT] LIKE B	STRING 类型	B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。

2）案例实操

（1）查询出薪水等于5000的所有员工

hive (soft863db)> select * from emp where sal =5000;

         （2）查询工资在500到1000的员工信息

hive (soft863db)> select * from emp where sal between 500 and 1000;

         （3）查询comm为空的所有员工信息

hive (soft863db)> select * from emp where comm is null;

         （4）查询工资是1500和5000的员工信息

hive (soft863db)> select * from emp where sal IN (1500, 5000);

2.2 LIKE

1）使用LIKE运算选择类似的值

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

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

_ 代表一个字符。

3）案例实操

         （1）查找以2开头薪水的员工信息

       hive (soft863db)> select * from emp where sal LIKE '2%';

         （2）查找第二个数值为2的薪水的员工信息

hive (soft863db)> select * from emp where sal LIKE '_2%';

2.3 逻辑运算符（AND/OR/NOT）

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

案例实操

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

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

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

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

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

hive (soft863db)> select * from emp where deptno not IN(30, 20);

3 分组

3.1 GROUP BY语句[WU2] 

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

案例实操：

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

hive (soft863db)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

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

hive (soft863db)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;

注意事项：

（1）group by后不能用别名，因为执行到group by时，还没执行到select中的别名，所以别名还没生效。

错误示例：select deptno dept, avg(sal) avg_sal from emp group by dept;

1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、使用order by对结果集进行排序。
8、select 集合输出。

（2）select中的字段，如果不出现在 group by 中，它就必须出现在 select 的聚合函数中

         错误示例：select deptno, sal from emp group by deptno;

3.2 HAVING语句

1）having与where不同点

（1）where针对表中的列发挥作用，查询数据；having针对group by后的查询结果中的列发挥作用，筛选数据。

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

（3）having只用于group by分组统计语句。

错误使用：

       hive> select * from student having st_age < 20；

2）案例实操：

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

         求每个部门的平均工资（MR任务）

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

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

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

4 排序

4.1 全局排序（Order By）(MR任务)

Order By：全局排序，一个Reducer, 多个reducer无法保证全局有序

会对查询的结果做一次全局排序。所以说，所的数据都会到同一个reducer进行处理。

对于大量数据这将会消耗很长的时间去执行。

1）使用 ORDER BY 子句排序

ASC（ascend）: 升序（默认）

DESC（descend）: 降序

2）ORDER BY 子句在SELECT语句的结尾。

3）案例实操

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

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

 

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

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

4.2 按照别名排序

按照员工薪水的2倍排序

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

4.3 多个列排序

按照部门和工资升序排序

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

4.4 每个Reducer内部排序（Sort By）

Sort By：每个Reducer内部进行排序，对全局结果集来说不是排序。

         1）设置reduce个数

hive (soft863db)> set mapreduce.job.reduces=3;

2）查看设置reduce个数

hive (soft863db)> set mapreduce.job.reduces;

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

hive (soft863db)> select ename, deptno, sal from emp sort by deptno desc;

         4）将查询结果导入到文件中（按照部门编号降序排序）

hive (soft863db)> insert overwrite local directory '/usr/local/data/sortby-result'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

select ename, deptno, sal from emp sort by deptno desc;

                  执行之后，会产生三个文件，每个文件的内容为：

4.5 分区排序（DISTRIBUTE BY）

destribute by deptno ：10 20 30 40 50 60   reduce个数是3

HashPartitioner：key.hashCode % reduceTaskNum

10 % 3 == 1

20 % 3 == 2

30 % 3 == 0

40 % 3 == 1

50 % 3 == 2

60 % 3 == 0

Distribute By：类似MR中partition，进行分区，结合sort by使用。

         注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

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

案例实操：

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

hive (soft863db)> set mapreduce.job.reduces=3;

       hive (soft863db)> insert overwrite local directory '/usr/local/data/distribute-result'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

select ename, deptno, sal, empno from emp distribute by deptno sort by empno desc;

4.6 CLUSTER BY

当distribute by和sort by字段相同时，可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是正序排序，不能指定排序规则为ASC或者DESC。

1）以下两种写法等价

select * from emp cluster by deptno;

select * from emp distribute by deptno sort by deptno;

注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。