Hive查询操作（一）

查询

        数据仓库是典型的查多写少，这个是根据HDFS本身的特点来的。HDFS本身不支持随机修改，只支持追加。所以Hive读多改少，写就写一遍，重点在查询。

        查询的用处在方方面面都有，比如insert等，都需要查询。

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

查询语句语法：

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available  starting with Hive 0.13.0) 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. 全表和特定列查询

创建部门表

hive (default)> create table if not exists dept( deptno int, dname string, loc int ) row format delimited fields terminated by '\t';

创建员工表

hive (default)> 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';

导入数据

hive (default)> load data local inpath '/export/servers/datas/dept.txt' into table dept;

hive (default)> load data local inpath '/export/servers/datas/emp.txt' into table emp;

1）．全表查询

hive (default)> select * from emp;

2）．选择特定列查询

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

注意：

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

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

（3）关键字不能被缩写也不能分行

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

（5）使用缩进提高语句的可读性。

2. 列别名

（1）．重命名一个列

（2）．便于计算

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

（4）．案例实操

查询名称和部门

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

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显示。

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

4. 常用函数

这些函数都是聚合函数，多输入1输出。（可以通过 show functions 命令查看所有函数）。另外还有1输入1输出的函数（比如upper、lower等）

（1）．求总行数（count）

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

这里要进行mapreduce操作。

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

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

这里要进行mapreduce操作。

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

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

这里要进行mapreduce操作。

（4）．求工资的总和（sum）

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

这里要进行mapreduce操作。

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

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

这里要进行mapreduce操作。

5. Limit语句

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

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

hive (default)> select * from emp limit 5, 5;

hive (default)> select * from emp limit 10, 5;

将来在做大查询的时候一定要加limit，比如做一个简表排序。

2、Where语句

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

（2）．WHERE子句紧随FROM子句

（3）．案例实操

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

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

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

1. 比较运算符（Between/In/ Is Null）

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是基于java的正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

2）案例实操

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

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

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

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

或

hive (default)> select * from emp where sal > 500 and sal < 1000;

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

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

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

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

hive (default)> select * from emp where deptno IN(10, 30);

2. Like和RLike

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

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

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

_ 代表一个字符。

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

4）案例实操

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

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

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

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

（3）查找薪水中含有2的员工信息

hive (default)> select * from emp where sal RLIKE '[2]';

出错？可参考 Hive问题汇总_TriumPhSk的博客-CSDN博客 

貌似2.1.0版本后rlike只能传入string类型的字段，而这里的sal字段类型为double（之前的版本可以自动转换为string类型）。可以进行如下：

查找名字以K开头的员工

hive (default)> select * from emp where ename RLIKE '^K.*';

3. 逻辑运算符（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(30, 20);

注意MySQL的时候一般不要这样写，尤其是当这个是主键的时候。因为这样没法做索引，它会做全表扫描。但是在Hive中本身就是全表扫描，所以要求没那么严格。但是有一个例外，就是分区表的时候千万不要这样写NOT IN，这样会引起全表扫描。分区只能写“分区=xxx”。

​​​​​​​3、分组

1. Group By语句

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

案例实操：

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

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

这里要进行mapreduce操作。运行时间较长。

注意Group By分组的操作是在查询之前完成的。（先分组，再查询）

注意Hive中规定：select中出现的内容（deptno）一定要在group by中出现。（MySQL没有这样的规定）

比如下面这样，select中多了ename：

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

这样写在MySQL中是可以运行的（尽管没有什么意义）。但是Hive这样写根本编译不通过。

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

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

这里要进行mapreduce操作。

2. Having语句

1）．having与where不同点

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

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

2）．案例实操

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

求每个部门的平均工资

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

这里要进行mapreduce操作。

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

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

这里要进行mapreduce操作。

注意having是发生在group by之后的，不能使用where。

4、Join语句

1. 等值Join

Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。

案例实操

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

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

这里要进行mapreduce操作。

2. 表的别名

1）．好处

（1）使用别名可以简化查询。

（2）使用表名前缀可以提高执行效率。

2）．案例实操

合并员工表和部门表

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

这里要进行mapreduce操作。

3. 内连接

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

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

和上面的一样。

4. 左外连接

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

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

这里要进行mapreduce操作。

5. 右外连接

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

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

这里要进行mapreduce操作。

6. 满外连接

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

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

这里要进行mapreduce操作。

7. 多表连接

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

数据准备

1）．创建位置表

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

2）．导入数据

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

3）．多表连接查询

hive (default)> 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;

这里要进行mapreduce操作。

        大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。

        注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。

        优化：当对3个或者更多表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。

        如果是几张大表，每join一次就会多一个mapreduce。但是现在是几个小表，所以总共只会用一个mapper（连reduce都没有）。

8. 笛卡尔积

1）．笛卡尔集会在下面条件下产生

（1）省略连接条件

（2）连接条件无效

（3）所有表中的所有行互相连接

2）．案例实操

hive (default)> select empno, dname from emp, dept;

结果很长。

这里总共14员工*4部门=56个数据。

在Hive中，由于Hive很多都是很大的表。所以Hive是严禁笛卡尔积的。

9. 连接谓词中不支持or

hive join目前不支持在on子句中使用谓词or

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

因为不支持非等值连接，所以也不支持or。

5、排序

1. 全局排序（Order By）

Order By：全局排序，只有一个Reducer

1）．使用 ORDER BY 子句排序

ASC（ascend）: 升序（默认）

DESC（descend）: 降序

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

3）．案例实操

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

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

这里要进行mapreduce操作。

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

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

这里要进行mapreduce操作。

这里的是全局排序。要实现全局排序，就不能分区。因为分区只能区内有序。区与区之间就无序了。所以Order By全局排序就只能有一个Reducer。如果数据量大，则一定是很慢的。所以Order By一定要慎用。可以查其中一部分数据，然后Order By，数据一般不要超过几百万条（就是跟MySQL数据量差不多）。

另一个不能随便乱用的是DISTINCT，这个称为全局去重。原因跟Order By是一样的。如果要全局去重，也需要把所有数据输入到一个Reducer中。

2. 按照别名排序

按照员工薪水的2倍排序

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

这里要进行mapreduce操作。

3. 多个列排序

按照部门和工资升序排序

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

这里要进行mapreduce操作。

4. 每个MapReduce内部排序（Sort By）

Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序（我们输出的文件大致上有序就行了），此时可以使用sort by。

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

1．设置reduce个数

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

因为目前数据量太少，如果不设置reduces=3，都会以1个reducer来执行。

2．查看设置reduce个数

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

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

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

这里要进行mapreduce操作。

注意reducer的输出中显示了3个。然后留意输出的结果，是按降序排列的。第1个reducer从7844到7782，第2个reducer从7521到7934，第3个reducer是后面两个（注意可能每次运行的结果不一样）。尽管不是全局有序，但是大体上是有序的。

注意这里分区是没有什么依据的。（系统随机分区？）

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

hive (default)> insert overwrite local directory '/export/servers/datas/sortby-result' select * from emp sort by deptno desc;

此时，在/export/servers/datas/sortby-result/ 目录下生成了000000_0 文件

5. 分区排序（Distribute By）

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

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

案例实操：

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

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

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

留意运行的结果。部门no为30、10、50、20的各一组。

将查询结果导入到文件。

hive (default)> insert overwrite local directory '/export/servers/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

此时，在/export/servers/datas/distribute-result/ 目录下生成了如下文件：

注意：

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

2）．Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

另外，Distributed By的分区跟分区表的分区不是同一个概念。Distributed By的分区指的是mapreduce的分区。

6. Cluster By

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

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

1）以下两种写法等价

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

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

这里要进行mapreduce操作。

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

另外关于DISTINCT也是不能随便用。假如表需要进行全局去重，可以考虑使用Group By，分区去重（Group By也可以达到去重的效果）。