• Hive之DQL操作

    Hive系列第六章
    （实际是第七篇，就不改目录序号了，大家知道就行，后续的篇章类推即可）

    第六章 DQL查询数据

    DDL： Data Definition Language     数据定义语言
DML： Data Manipulation Language   数据操纵语言
DQL ：Data Query Language 数据查询语言

    • 1
    • 2
    • 3

    官方文档： https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

    Hive 中的 SELECT 基础语法和标准 SQL 语法基本一致，支持 WHERE、DISTINCT、GROUP BY、 ORDER BY、HAVING、LIMIT、子查询等

    语法结构：

    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 [offset,] rows]

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    缩进问题：多行的时候不要用Tab，在linux里面不能用tab，因为tab是提示。可以用多个空格。

    6.1 查询数据准备

    以为企业当中非常非常常见的雇员表和部门表做演示！！！

    1、创建雇员表emp：

    emp雇员表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

    • 1
    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';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    2、创建部门表dept：

    dept部门表(deptno部门编号/dname部门名称/loc地点编号)

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

    • 1
    • 2
    • 3
    • 4
    • 5

    3、准备两个表数据

    dept表数据：dept.txt

    10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700
50	Admin	2000

    • 1
    • 2
    • 3
    • 4
    • 5
    注：ACCOUNTING：财务部/会计部。RESEARCH：研究。SALES：销售部。OPERATIONS：营业部。Admin：行政部

    • 1

    emp雇员表数据：emp.txt

    7369	ZHANGSAN	CLERK	7902	1980-12-17	800.00	0.00	20
7499	LISI	SALESMAN	7698	1981-2-20	1600.00	300.00	30
7521	WANGWU	SALESMAN	7698	1981-2-22	1250.00	500.00	30
7566	ZHAOLIU	MANAGER	7839	1981-4-2	2975.00	0.00	20
7654	SUNQI	SALESMAN	7698	1981-9-28	1250.00	1400.00	30
7698	ZHOUBA	MANAGER	7839	1981-5-1	2850.00	0.00	30
7782	WUJIU	MANAGER	7839	1981-6-9	2450.00	0.00	10
7788	ZHENGSHI	ANALYST	7566	1987-4-19	3000.00	0.00	20
7839	LIUYI	PRESIDENT	7566	1981-11-17	5000.00	0.00	10
7844	CHENGER	SALESMAN	7698	1981-9-8	1500.00	2000.00	30
7876	ZHANGSAN2	CLERK	7788	1987-5-23	1100.00	0.00	20
7900	LISI2	CLERK	7698	1981-12-3	950.00	0.00	30
7902	WANGWU2	ANALYST	7566	1981-12-3	3000.00	0.00	20
7934	ZHAOLIU2	CLERK	7782	1982-1-23	1300.00	0.00	10

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    emp雇员表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

    • 1

    4、导入表数据

    load data local inpath '/home/data/dept.txt' into table dept;
load data local inpath '/home/data/emp.txt' into table emp;

    • 1
    • 2

    6.2 一般查询

    6.2.1 全表查询

    hive (mydb)> select * from dept;
OK
dept.deptno	dept.dname	dept.loc
10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700
50	Admin	2000
Time taken: 0.07 seconds, Fetched: 5 row(s)
hive (mydb)> select deptno,dname,loc from dept;
OK
deptno	dname	loc
10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700
50	Admin	2000
Time taken: 0.082 seconds, Fetched: 5 row(s)
hive (mydb)> select * from emp;
OK
emp.empno	emp.ename	emp.job	emp.mgr	emp.hiredate	emp.sal	emp.comm	emp.deptno
7369	ZHANGSAN	CLERK	7902	1980-12-17	800.0	0.0	20
7499	LISI	SALESMAN	7698	1981-2-20	1600.0	300.0	30
7521	WANGWU	SALESMAN	7698	1981-2-22	1250.0	500.0	30
7566	ZHAOLIU	MANAGER	7839	1981-4-2	2975.0	0.0	20
7654	SUNQI	SALESMAN	7698	1981-9-28	1250.0	1400.0	30
7698	ZHOUBA	MANAGER	7839	1981-5-1	2850.0	0.0	30
7782	WUJIU	MANAGER	7839	1981-6-9	2450.0	0.0	10
7788	ZHENGSHI	ANALYST	7566	1987-4-19	3000.0	0.0	20
7839	LIUYI	PRESIDENT	7566	1981-11-17	5000.0	0.0	10
7844	CHENGER	SALESMAN	7698	1981-9-8	1500.0	2000.0	30
7876	ZHANGSAN2	CLERK	7788	1987-5-23	1100.0	0.0	20
7900	LISI2	CLERK	7698	1981-12-3	950.0	0.0	30
7902	WANGWU2	ANALYST	7566	1981-12-3	3000.0	0.0	20
7934	ZHAOLIU2	CLERK	7782	1982-1-23	1300.0	0.0	10
Time taken: 0.067 seconds, Fetched: 14 row(s)
hive (mydb)> 


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38

    在这里插入图片描述

    6.2.2 查询特定的列

    hive (mydb)> select deptno,dname from dept;
OK
deptno	dname
10	ACCOUNTING
20	RESEARCH
30	SALES
40	OPERATIONS
50	Admin
Time taken: 0.123 seconds, Fetched: 5 row(s)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    多说一句：

    1、HQL 语句大小写不敏感
2、关键字不能被缩写也不能分行
3、HQL 可以写在一行或者多行
4、各子句建议分行写，这样看着清晰写
5、建议多注意缩进，提高阅读性

    • 1
    • 2
    • 3
    • 4
    • 5

    6.2.3 算术运算符

    数学运算： 
	1. 加法操作: +
	2. 减法操作: –
	3. 乘法操作: *
	4. 除法操作: /
	5. 取余操作: %
	6. 位与操作: &
	7. 位或操作: |
	8. 位异或操作: ^
	9．位取反操作: ~

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    hive (mydb)> select sal -2 from emp;
hive (mydb)> select sal- 2 from emp;
hive (mydb)> select sal-2 from emp;
以上都ok

    • 1
    • 2
    • 3
    • 4

    6.2.4 给列起别名

    hive (mydb)> select empno,ename as name,hiredate from emp;
OK
empno	name	hiredate
7369	ZHANGSAN	1980-12-17
7499	LISI	1981-2-20
7521	WANGWU	1981-2-22
7566	ZHAOLIU	1981-4-2
7654	SUNQI	1981-9-28
7698	ZHOUBA	1981-5-1
7782	WUJIU	1981-6-9
7788	ZHENGSHI	1987-4-19
7839	LIUYI	1981-11-17
7844	CHENGER	1981-9-8
7876	ZHANGSAN2	1987-5-23
7900	LISI2	1981-12-3
7902	WANGWU2	1981-12-3
7934	ZHAOLIU2	1982-1-23
Time taken: 0.07 seconds, Fetched: 14 row(s)
hive (mydb)> select empno,ename,hiredate as date from emp;
NoViableAltException(86@[87:7: ( ( ( KW_AS )? identifier ) | ( KW_AS LPAREN identifier ( COMMA identifier )* RPAREN ) )?])
	at org.antlr.runtime.DFA.noViableAlt(DFA.java:158)
	at org.antlr.runtime.DFA.predict(DFA.java:116)
	at org.apache.hadoop.hive.ql.parse.HiveParser_SelectClauseParser.selectItem(HiveParser_SelectClauseParser.java:1726)
	at org.apache.hadoop.hive.ql.parse.HiveParser_SelectClauseParser.selectList(HiveParser_SelectClauseParser.java:1234)
	at org.apache.hadoop.hive.ql.parse.HiveParser_SelectClauseParser.selectClause(HiveParser_SelectClauseParser.java:1004)
	at org.apache.hadoop.hive.ql.parse.HiveParser.selectClause(HiveParser.java:45140)
	at org.apache.hadoop.hive.ql.parse.HiveParser.atomSelectStatement(HiveParser.java:39777)
	at org.apache.hadoop.hive.ql.parse.HiveParser.selectStatement(HiveParser.java:40044)
	at org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:39690)
	at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:38900)
	at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:38788)
	at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:2396)
	at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1420)
	at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:220)
	at org.apache.hadoop.hive.ql.parse.ParseUtils.parse(ParseUtils.java:74)
	at org.apache.hadoop.hive.ql.parse.ParseUtils.parse(ParseUtils.java:67)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:616)
	at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1826)
	at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1773)
	at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1768)
	at org.apache.hadoop.hive.ql.reexec.ReExecDriver.compileAndRespond(ReExecDriver.java:126)
	at org.apache.hadoop.hive.ql.reexec.ReExecDriver.run(ReExecDriver.java:214)
	at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:239)
	at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:188)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:402)
	at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:821)
	at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:759)
	at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:683)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.util.RunJar.run(RunJar.java:323)
	at org.apache.hadoop.util.RunJar.main(RunJar.java:236)
FAILED: ParseException line 1:31 cannot recognize input near 'as' 'date' 'from' in selection target
hive (mydb)> select empno,ename,hiredate as date1 from emp;
OK
empno	ename	date1
7369	ZHANGSAN	1980-12-17
7499	LISI	1981-2-20
7521	WANGWU	1981-2-22
7566	ZHAOLIU	1981-4-2
7654	SUNQI	1981-9-28
7698	ZHOUBA	1981-5-1
7782	WUJIU	1981-6-9
7788	ZHENGSHI	1987-4-19
7839	LIUYI	1981-11-17
7844	CHENGER	1981-9-8
7876	ZHANGSAN2	1987-5-23
7900	LISI2	1981-12-3
7902	WANGWU2	1981-12-3
7934	ZHAOLIU2	1982-1-23
Time taken: 0.08 seconds, Fetched: 14 row(s)
hive (mydb)> 
 
注意：起名的时候不能是关键字，比如上面的中间报错，就是因为date是关键字。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76

    6.2.5 基本聚合函数count/max/min/avg/sum

    hive (mydb)> select count(*) as count_res from emp;

hive (mydb)> select max(sal) sal_max from emp;

hive (mydb)> select min(sal) sal_min from emp;

hive (mydb)> select sum(sal) sal_sum from emp;

hive (mydb)> select avg(sal) sal_avg from emp;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    操作版本：

    select count(*) as count_res from emp;
select max(sal) sal_max from emp;
select min(sal) sal_min from emp;
select sum(sal) sal_sum from emp;
select avg(sal) sal_avg from emp;

    • 1
    • 2
    • 3
    • 4
    • 5

    6.2.5 Limit 和 Where

    limit用来限制返回的结果数量

    hive (mydb)> select * from emp limit 3;

    • 1

    Where条件过滤。注意Where语句紧紧跟着From语句后面，另外需要注意Where子语句中不能使用别名！！！

    hive (mydb)> select * from emp where sal >1200;

hive (mydb)> select * from emp where sal >1200 limit 3;

    • 1
    • 2
    • 3

    6.2.6 比较运算符Between/In/Is Null

    操作符支持的数据类型描述
    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基本数据类型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 中的正则表达式接口 实现的， 因为正则也依据其中的规则。例如，正则表达式必须和

    示例：

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

between两边都包含
hive (mydb)> select * from emp where sal between 1100 and 1500;
hive (mydb)> select ename,sal from emp where sal between 1100 and 1500;
OK
ename	sal
WANGWU	1250.0
SUNQI	1250.0
CHENGER	1500.0
ZHANGSAN2	1100.0
ZHAOLIU2	1300.0
Time taken: 0.1 seconds, Fetched: 5 row(s)

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

IN两边都包含
hive (mydb)> select * from emp where sal IN (1500, 5000);
OK
emp.empno	emp.ename	emp.job	emp.mgr	emp.hiredate	emp.sal	emp.comm	emp.deptno
7839	LIUYI	PRESIDENT	7566	1981-11-17	5000.0	0.0	10
7844	CHENGER	SALESMAN	7698	1981-9-8	1500.0	2000.0	30
Time taken: 0.097 seconds, Fetched: 2 row(s)
hive (mydb)> select * from emp where sal IN (1100, 1500);
OK
emp.empno	emp.ename	emp.job	emp.mgr	emp.hiredate	emp.sal	emp.comm	emp.deptno
7844	CHENGER	SALESMAN	7698	1981-9-8	1500.0	2000.0	30
7876	ZHANGSAN2	CLERK	7788	1987-5-23	1100.0	0.0	20
Time taken: 0.097 seconds, Fetched: 2 row(s)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30

    6.2.6 LIKE 和 RLIKE

    理论：

    1、使用 LIKE 运算选择类似的值
2、选择条件可以包含字符或数字:
	% 代表零个或多个字符(任意个字符)。
	_ 代表一个字符。
3、RLIKE 子句
RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式来指定匹配条件。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    示例：

    1、查找名字以 L 开头的员工信息
hive (mydb)> select * from emp where ename LIKE 'L%';
2、查找名字中第二个字母为 I 的员工信息
hive (mydb)> select * from emp where ename LIKE '_I%';
3、查找名字中带有 L 的员工信息
hive (mydb)> select * from emp where ename RLIKE '[L]';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    6.2.7 与或非

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

    操作一把：

    1、查询薪水大于 1100，部门是 30
hive (mydb)> select * from emp where sal>1100 and deptno=30;
2、查询薪水大于 1100，或者部门是 30
hive (mydb)> select * from emp where sal>1100 or deptno=30;
3、查询除了 10 部门和 20 部门以外的员工信息
hive (mydb)> select * from emp where deptno not IN(10, 20);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    6.3 常见的分组操作

    6.3.1 Group by

    1、理论

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

    • 1

    2、实践

    1、计算雇员表 emp 每个部门的平均工资
select deptno, avg(sal) sal_avg from emp group by deptno;
起个别名方式：
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

2、计算雇员表 emp 每个部门中每个岗位的最高薪水
select deptno, job, max(sal) sal_max from emp
group by
deptno, job;

起个别名方式：
select e.deptno, e.job, max(e.sal) max_sal from emp e
group by
e.deptno, e.job;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    6.3.2 Having

    1、理论

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

    • 1
    • 2
    • 3

    2、实践

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

先看看每个部门的平均工资
select deptno, avg(sal) from emp group by deptno;

算出来每个部门的平均薪水大于 2100 的部门
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2100;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    6.4 Join语句常操作

    6.4.1 等值连接join

    SQL JOIN在Hive中基本上都可以

    根据员工表和部门表中的部门编号相等，查询相关的信息
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;
select e.* from emp e join dept d on e.deptno = d.deptno;

给表起个别名
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    6.4.2 内连接inner join

    内连接：连接两个表中都存在与连接条件相匹配的数据join

    hive (mydb)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;
等价于
hive (mydb)> select e.empno, e.ename, d.deptno, d.dname from emp e inner join dept d on e.deptno = d.deptno;

    • 1
    • 2
    • 3

    6.4.3 左外连接left join

    左外连接：JOIN 操作符左表中符合条件的所有记录将会被返回。

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

    • 1

    6.4.4 右外连接right join

    右外连接：JOIN 操作符右表中符合条件的所有记录将会被返回。

    hive (mydb)> select e.empno, e.ename, d.deptno, d.dname from emp e right join dept d on e.deptno = d.deptno;
Query ID = root_20210927183009_f815a87e-95de-48fb-9bca-032a149c5c20
Total jobs = 1
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1632726364438_0018, Tracking URL = http://hadoop10:8088/proxy/application_1632726364438_0018/
Kill Command = /software/hadoop/bin/mapred job  -kill job_1632726364438_0018
Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0
2021-09-27 18:30:28,275 Stage-3 map = 0%,  reduce = 0%
2021-09-27 18:30:35,441 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 1.21 sec
MapReduce Total cumulative CPU time: 1 seconds 210 msec
Ended Job = job_1632726364438_0018
MapReduce Jobs Launched: 
Stage-Stage-3: Map: 1   Cumulative CPU: 1.21 sec   HDFS Read: 8787 HDFS Write: 644 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 210 msec
OK
e.empno	e.ename	d.deptno	d.dname
7782	WUJIU	10	ACCOUNTING
7839	LIUYI	10	ACCOUNTING
7934	ZHAOLIU2	10	ACCOUNTING
7369	ZHANGSAN	20	RESEARCH
7566	ZHAOLIU	20	RESEARCH
7788	ZHENGSHI	20	RESEARCH
7876	ZHANGSAN2	20	RESEARCH
7902	WANGWU2	20	RESEARCH
7499	LISI	30	SALES
7521	WANGWU	30	SALES
7654	SUNQI	30	SALES
7698	ZHOUBA	30	SALES
7844	CHENGER	30	SALES
7900	LISI2	30	SALES
NULL	NULL	40	OPERATIONS
NULL	NULL	50	Admin
Time taken: 26.977 seconds, Fetched: 16 row(s)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37

    6.4.5 满外连接 full join

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

    hive (mydb)> select e.empno, e.ename, d.deptno, d.dname from emp e full join dept d on e.deptno = d.deptno;
Query ID = root_20210927183205_09dedd3c-44e1-40b4-b005-2550a1afad09
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=
In order to set a constant number of reducers:
  set mapreduce.job.reduces=
Starting Job = job_1632726364438_0019, Tracking URL = http://hadoop10:8088/proxy/application_1632726364438_0019/
Kill Command = /software/hadoop/bin/mapred job  -kill job_1632726364438_0019
Hadoop job information for Stage-1: number of mappers: 2; number of reducers: 1
2021-09-27 18:32:14,343 Stage-1 map = 0%,  reduce = 0%
2021-09-27 18:32:25,610 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.39 sec
2021-09-27 18:32:32,802 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 3.65 sec
MapReduce Total cumulative CPU time: 3 seconds 650 msec
Ended Job = job_1632726364438_0019
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 2  Reduce: 1   Cumulative CPU: 3.65 sec   HDFS Read: 17662 HDFS Write: 644 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 650 msec
OK
e.empno	e.ename	d.deptno	d.dname
7934	ZHAOLIU2	10	ACCOUNTING
7839	LIUYI	10	ACCOUNTING
7782	WUJIU	10	ACCOUNTING
7876	ZHANGSAN2	20	RESEARCH
7788	ZHENGSHI	20	RESEARCH
7369	ZHANGSAN	20	RESEARCH
7566	ZHAOLIU	20	RESEARCH
7902	WANGWU2	20	RESEARCH
7844	CHENGER	30	SALES
7499	LISI	30	SALES
7698	ZHOUBA	30	SALES
7654	SUNQI	30	SALES
7521	WANGWU	30	SALES
7900	LISI2	30	SALES
NULL	NULL	40	OPERATIONS
NULL	NULL	50	Admin
Time taken: 27.901 seconds, Fetched: 16 row(s)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41

    6.4.6 多表连接

    说在前面：连接 n 个表，至少需要 n-1 个连接条件。

    1、创建表
create table if not exists location(
loc int,
loc_name string)
row format delimited fields terminated by '\t';

2、造点数据 
vim location.txt
数据如下：
1700	beijing
1800	shanghai
1900	guangzhou
2000	shenzhen


3、导入数据
load data local inpath '/home/data/location.txt' into table location;

4、多表连接join查询
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;

操作如下:
hive (mydb)> 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;
No Stats for mydb@emp, Columns: ename, deptno
No Stats for mydb@dept, Columns: loc, dname, deptno
No Stats for mydb@location, Columns: loc, loc_name
Query ID = root_20210928152128_c4444f0e-971d-4d5a-97cc-999a4e74775c
Total jobs = 1
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1632812825234_0002, Tracking URL = http://hadoop10:8088/proxy/application_1632812825234_0002/
Kill Command = /software/hadoop/bin/mapred job  -kill job_1632812825234_0002
Hadoop job information for Stage-5: number of mappers: 1; number of reducers: 0
2021-09-28 15:21:54,894 Stage-5 map = 0%,  reduce = 0%
2021-09-28 15:22:03,113 Stage-5 map = 100%,  reduce = 0%, Cumulative CPU 1.54 sec
MapReduce Total cumulative CPU time: 1 seconds 540 msec
Ended Job = job_1632812825234_0002
MapReduce Jobs Launched: 
Stage-Stage-5: Map: 1   Cumulative CPU: 1.54 sec   HDFS Read: 12157 HDFS Write: 602 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 540 msec
OK
e.ename	d.dname	l.loc_name
ZHANGSAN	RESEARCH	shanghai
LISI	SALES	guangzhou
WANGWU	SALES	guangzhou
ZHAOLIU	RESEARCH	shanghai
SUNQI	SALES	guangzhou
ZHOUBA	SALES	guangzhou
WUJIU	ACCOUNTING	beijing
ZHENGSHI	RESEARCH	shanghai
LIUYI	ACCOUNTING	beijing
CHENGER	SALES	guangzhou
ZHANGSAN2	RESEARCH	shanghai
LISI2	SALES	guangzhou
WANGWU2	RESEARCH	shanghai
ZHAOLIU2	ACCOUNTING	beijing
Time taken: 36.375 seconds, Fetched: 14 row(s)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67

    ​ 一般情况下，Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。本例中会首先启动一个 MapReduce job 对表 e 和表 d 进行连接操作，然后会再启动一个 MapReduce job 将第一个 MapReduce job 的输出和表 l 进行连接操作。这是因为 Hive 总是按照从左到右的顺序执行的。
    ​ 但是请注意：当对 3 个或者更多表进行 join 连接时，如果每个 on 子句都使用相同的连接键的话，那么只会产生一个 MapReduce job。

    6.4.7 笛卡尔积

    1、概念

    笛卡尔集其实来自于数学上的概念。
笛卡尔集这词经常出现于数据库多表查询中。给定一组域D1，D2，…，Dn，这些域中可以有相同的域。 D1，D2，…，Dn的笛卡儿积为：
D1×D2×…×Dn ={（d1，d2，…，dn）|diÎDi，i=1，2，…，n}
其中每一个元素（d1，d2，…，dn）叫作一个n元组或简称元组。元组（d1，d2，…，dn）中的每一个值di叫作一个分量。
当A≠B时，A×B≠B×A
例 A={1，2}，B={0，1}
A×B={(1，0),(1，1)，(2，0)，(2，1)}，
B×A={(0，1)，(0，2)，(1，1)，(1，2)}，
显然，A×B≠B×A。
注：来自于百度百科

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    2、实践

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

    • 1

    6.5 排序那些事

    6.5.1 Order By 全局排序

    1、理论

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

    1、使用 ORDER BY 的时候的排序规则
ASC（ascend）: 升序（默认规则）
DESC（descend）: 降序
2、ORDER BY 子句在 SELECT 语句结尾

    • 1
    • 2
    • 3
    • 4

    2、实践

    1、按工资升序排列
select * from emp order by sal;
2、按工资降序排列
select * from emp order by sal desc;

    • 1
    • 2
    • 3
    • 4

    3、再实践一把

    1、按照别名排序
按照员工薪水的 2 倍排序
select ename, sal*2 sal2 from emp order by sal2;
2、多个列排序
按照部门和工资升序排序
select ename, deptno, sal from emp order by deptno, sal;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    6.5.2 Sort By每个Reduce内部排序

    1、理论

    Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，这个时候可以使用 Sort by。
Sort by 为每个 Reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说并不一定是排序。

    • 1
    • 2

    2、实践

    0、查看设置 reduce 个数
hive (mydb)> set mapreduce.job.reduces;
mapreduce.job.reduces=-1
默认值是-1，为系统自动匹配。匹配原则是按照block的个数来启动map和reduce。
1、设置 reduce 个数
hive (mydb)> set mapreduce.job.reduces=3;
2、查看设置 reduce 个数
hive (mydb)> set mapreduce.job.reduces;
3、根据部门编号降序查看员工信息
select * from emp sort by deptno asc;
4、将查询结果导入到文件中（按照部门编号升序排序）
insert overwrite local directory '/home/data/sortbyresult' select * from emp sort by deptno asc;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    去sortbyresult查看结果，结果发现里面有3个结果

    在这里插入图片描述

    6.5.3 Distribute By分区

    1、理论

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

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

    • 1
    • 2
    • 3

    2、实践

    先按照部门编号分区，再按照员工编号升序排序。
hive (mydb)> set mapreduce.job.reduces=3;
hive (mydb)> insert overwrite local directory '/home/data/distributebyres' select * from emp distribute by deptno sort by empno asc;

注意：
1、distribute by 的分区规则是根据分区字段的 hash 值与 reduce 的个数进行模除后，余数相同的分到一个区。
2、Hive 中需要将 DISTRIBUTE BY 语句写在 SORT BY 语句之前。

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    查看一下，看最后一列，里面都是同一个部门，然后每一个部门里面按照empno升序排列

    在这里插入图片描述

    6.5.4 Cluster By

    1、理论

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

    • 1
    • 2

    2、实践

    hive (mydb)> select * from emp cluster by deptno;
等价于
hive (mydb)> select * from emp distribute by deptno sort by deptno;

    • 1
    • 2
    • 3


    声明：
            文章中代码及相关语句为自己根据相应理解编写，文章中出现的相关图片为自己实践中的截图和相关技术对应的图片，若有相关异议，请联系删除。感谢。转载请注明出处，感谢。


    By luoyepiaoxue2014

    B站： https://space.bilibili.com/1523287361 点击打开链接
    微博地址： http://weibo.com/luoyepiaoxue2014 点击打开链接

  • 相关阅读:
    SAP AIF BTI750
    Pyhon-每日一练(1)
    html、css
    ElasticSearch7.3学习(二十九)----聚合实战之使用Java api实现电视案例
    工程类如安监、基建等项目投标流程规范建议
    Mybatis之动态sql和分页
    DataFrame插入多列PerformanceWarning: DataFrame is highly fragmented.
    设计模式-工厂方法模式
    【springMVC】了解springMVC
    Linux 命令（142）—— hexdump 命令
  • 原文地址：https://blog.csdn.net/luoyepiaoxue2014/article/details/128037278