Hive之从理论到实战（二）

Hive之从理论到实战（二） 第五章 查询

了解一下语法先：

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]

5.1 基本查询（Select…From） 5.11 全表和特定列查询

数据准备

(0)原始数据

dept:

10	ACCOUNTING	1700

20	RESEARCH	1800

30	SALES	1900

40	OPERATIONS	1700

emp：

7369	SMITH	CLERK	7902	1980-12-17	800.00		20

7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30

7521	WARD	SALESMAN	7698	1981-2-22	1250.00	500.00	30

7566	JonES	MANAGER	7839	1981-4-2	2975.00		20

7654	MARTIN	SALESMAN	7698	1981-9-28	1250.00	1400.00	30

7698	BLAKE	MANAGER	7839	1981-5-1	2850.00		30

7782	CLARK	MANAGER	7839	1981-6-9	2450.00		10

7788	SCOTT	ANALYST	7566	1987-4-19	3000.00		20

7839	KING	PRESIDENT		1981-11-17	5000.00		10

7844	TURNER	SALESMAN	7698	1981-9-8	1500.00	0.00	30

7876	ADAMS	CLERK	7788	1987-5-23	1100.00		20

7900	JAMES	CLERK	7698	1981-12-3	950.00		30

7902	FORD	ANALYST	7566	1981-12-3	3000.00		20

7934	MILLER	CLERK	7782	1982-1-23	1300.00		10

（1）创建部门表

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

（2）创建员工表

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

（3）导入数据

load data local inpath '/opt/module/datas/dept.txt' into table

dept;

load data local inpath '/opt/module/datas/emp.txt' into table emp;

1) 全表查询

hive (default)> select * from emp;

hive (default)> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp ;

2） 选择特定列查询

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

注意：

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

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

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

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

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

5.1.2 列别名

意义：

1）重命名一个列

2）便于计算

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

4）案例实 ***

查询名称和部门

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

5.1.3 常用函数

count(),max(),min(),sum(),avg()

5.1.4 Limit语句

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

hive (default)> select * from emp limit 5;
##查询前五条记录

hive (default)> select * from emp limit 2,3;
##查询从第3条到第4条（limit计数从0开始）

5.1.5 Like和RLike

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

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

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

3）RLIKE子句

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

4）案例实 ***

（1）查找名字以A开头的员工信息

hive (default)> select * from emp where ename LIKE 'A%';

（2）查找名字中第二个字母为A的员工信息

hive (default)> select * from emp where ename LIKE '_A%';

（3）查找名字中带有A的员工信息

hive (default)> select * from emp where ename  RLIKE '[A]';

5.2 分组 5.2.1 Group By语句

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

1） 案例实 ***

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

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

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

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

 t.deptno, t.job;

**在Hive中对它的语法要求更加严格了，本来在mysql中还是可以分组然后查询非分组字段的，但是Hive中不允许这样的 *** 作。**我们观察上面的两个案例就可以看出查询的字段除了聚合函数就是分组的字段。

5.2.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;

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

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

 avg_sal > 2000;

5.2.3 Join语句

必要的原则：

连接可以理解为内连接和外连接，任何连接都在两者包含范围内。

内连接取主表和从表的交集，

外连接取主表的所有数据和 主表和从表的连接字段相等的数据

注意避免笛卡尔积，即避免犯以下三点错误：

（1）省略连接条件

（2）连接条件无效

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

5.3 排序 5.3.1 全局排序 （order by）

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

5.3.2 每个Reduce内部排序（Sort By）

Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。

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

1） 设置reduce个数

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

2） 查看设置reduce个数

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

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

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

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

hive (default)> insert overwrite local directory '/opt/module/hive/datas/sortby-result'

 select * from emp sort by deptno desc;

5.3.3 分区（Distribute By）

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

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

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

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

5.3.4 分桶 （Cluster by）

当distribute by和sort 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;

**注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。**因为分区规则是根据分区字段的hash码与reduce个数进行模除后，余数相同的分到一个区。

第六章 分区表和分桶表 6.1 分区表

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERe子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。毕竟咱们以后动辄几百上千万数据，妈耶想想就多。

6.1.1 分区表基本 *** 作

1）引入分区表（需要根据日期对日志进行管理, 通过部门信息模拟）

dept_20200401.log

dept_20200402.log

dept_20200403.log

……

2）创建分区表语法

hive (default)> create table dept_partition(

deptno int, dname string, loc string

)

partitioned by (day string)

row format delimited fields terminated by 't';

注意：分区字段(day)不能是表中已经存在的数据，可以将分区字段看作表的伪列。

3）加载数据到分区表中

（1） 数据准备

dept_20200401.log

10	ACCOUNTING	1700

20	RESEARCH	1800

dept_20200402.log

30	SALES	1900

40	OPERATIONS	1700

dept_20200403.log

50	TEST	2000

60	DEV	1900

（2） 加载数据

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200402.log' into table dept_partition partition(day='20200402');

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200403.log' into table dept_partition partition(day='20200403');

注意：分区表加载数据时，必须指定分区

3) 查询分区表的数据应该在where中筛选分区才能体现分区的意义。

4）多分区联合查询时用union连接查询语句。

5）增加分区

创建单个分区

hive (default)> alter table dept_partition add partition(day='20200404') ;

同时创建多个分区

hive (default)> alter table dept_partition add partition(day='20200405')  partition(day='20200406');

6）删除分区

删除单个分区

hive (default)> alter table dept_partition drop partition (day='20200406');

同时删除多个分区

hive (default)> alter table dept_partition drop partition (day='20200404'), partition(day='20200405');

7）查看分区表有多少分区

hive> show partitions dept_partition;

6.2 分桶表

分区提供一个隔离数据和优化查询的便利方式，不过并非所有的数据都可以形成合理的分区。对于一张表或者分区，Hive可以进一步组织成桶，也就是粒度更细的数据范围划分。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。

分区针对的是数据的存储路径，分桶针对的是数据文件。

1）先创建分桶表

（1）数据准备

1001	ss1

1002	ss2

1003	ss3

1004	ss4

1005	ss5

1006	ss6

1007	ss7

1008	ss8

1009	ss9

1010	ss10

1011	ss11

1012	ss12

1013	ss13

1014	ss14

1015	ss15

1016	ss16

（2）创建分桶表

create table stu_bucket(id int, name string)

clustered by(id) 

into 4 buckets

row format delimited fields terminated by 't';

（3）查看表结构

hive (default)> desc formatted stu_bucket;

Num Buckets: 4

（4）导入数据到分桶表中，load的方式

hive (default)> load data inpath  '/student.txt' into table stu_bucket;

（5）查看创建的分桶表中是否分成4个桶

（6）查询分桶的数据

hive(default)> select * from stu_buck;

（7）分桶规则：

根据结果可知：Hive的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方 式决定该条记录存放在哪个桶当中

2）分桶表 *** 作需要注意的事项:

（1）reduce的个数设置为-1,让Job自行决定需要用多少个reduce或者将reduce的个数设置为大于等于分桶表的桶数

（2）从hdfs中load数据到分桶表中，避免本地文件找不到问题

（3）不要使用本地模式

第7章 函数 7.1 系统内置函数

1）查看系统自带的函数

show functions;

2. 显示自带的函数的用法

desc function 函数名;

3. 详细显示自带的函数的用法

desc function extended upper;

7.2 常用内置函数 7.2.1 空字段赋值

NVL:给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。

7.2.2 行转列

1） 相关函数说明

CONCAt(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_Ws(separator, str1, str2,…)：它是一个特殊形式的 CONCAt()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

注意: CONCAT_WS must be "string or array

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

7.2.3 列转行（需要掌握）

1）函数说明

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和split, explode等UDTF（一个参数，多个返回值）一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

7.2.4 窗口函数 （重点）

1） 函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的改变而变化。

CURRENT ROW：当前行

n PRECEDING：往前n行数据 （指一个数据范围）

n FOLLOWING：往后n行数据

UNBOUNDED：起点，

​ UNBOUNDED PRECEDING 表示从前面的起点，

UNBOUNDED FOLLOWING表示到后面的终点

LAG(col,n,default_val)：往前第n行数据 （指某一个数据记录）

LEAD(col,n, default_val)：往后第n行数据

NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

7.2.5 Rank

1）函数说明

RANK() 排序相同时会重复，总数不会变

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

7.2.6 自定义函数

根据用户自定义函数类别分为以下三种：

（1）UDF（User-Defined-Function）

​ 一进一出

（2）UDAF（User-Defined Aggregation Function）

​ 聚集函数，多进一出

​ 类似于：count/max/min

（3）UDTF（User-Defined Table-Generating Functions）

​ 一进多出

​ 如lateral view explode()

第八章 企业级调优 8.1 执行计划（Explain）

1 ）基本语法

EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query

2）案例实 ***

（1）查看下面这条语句的执行计划

没有生成MR任务的

hive (default)> explain select * from emp;

Explain

STAGE DEPENDENCIES:

 Stage-0 is a root stage

 

STAGE PLANS:

 Stage: Stage-0

  Fetch Operator

   limit: -1

   Processor Tree:

​    TableScan

​     alias: emp

​     Statistics: Num rows: 1 Data size: 7020 Basic stats: COMPLETE Column stats: NONE

​     Select Operator

​      expressions: empno (type: int), ename (type: string), job (type: string), mgr (type: int), hiredate (type: string), sal (type: double), comm (type: double), deptno (type: int)

​      outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7

​      Statistics: Num rows: 1 Data size: 7020 Basic stats: COMPLETE Column stats: NONE

​      ListSink

有生成MR任务的

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

Explain

STAGE DEPENDENCIES:

 Stage-1 is a root stage

 Stage-0 depends on stages: Stage-1

 

STAGE PLANS:

 Stage: Stage-1

  Map Reduce

   Map Operator Tree:

​     TableScan

​      alias: emp

​      Statistics: Num rows: 1 Data size: 7020 Basic stats: COMPLETE Column stats: NONE

​      Select Operator

​       expressions: sal (type: double), deptno (type: int)

​       outputColumnNames: sal, deptno

​       Statistics: Num rows: 1 Data size: 7020 Basic stats: COMPLETE Column stats: NONE

​       Group By Operator

​        aggregations: sum(sal), count(sal)

​        keys: deptno (type: int)

​        mode: hash

​        outputColumnNames: _col0, _col1, _col2

​        Statistics: Num rows: 1 Data size: 7020 Basic stats: COMPLETE Column stats: NONE

​        Reduce Output Operator

​         key expressions: _col0 (type: int)

​         sort order: +

​         Map-reduce partition columns: _col0 (type: int)

​         Statistics: Num rows: 1 Data size: 7020 Basic stats: COMPLETE Column stats: NONE

​         value expressions: _col1 (type: double), _col2 (type: bigint)

   Execution mode: vectorized

   Reduce Operator Tree:

​    Group By Operator

​     aggregations: sum(VALUE._col0), count(VALUE._col1)

​     keys: KEY._col0 (type: int)

​     mode: mergepartial

​     outputColumnNames: _col0, _col1, _col2

​     Statistics: Num rows: 1 Data size: 7020 Basic stats: COMPLETE Column stats: NONE

​     Select Operator

​      expressions: _col0 (type: int), (_col1 / _col2) (type: double)

​      outputColumnNames: _col0, _col1

​      Statistics: Num rows: 1 Data size: 7020 Basic stats: COMPLETE Column stats: NONE

​      File Output Operator

​       compressed: false

​       Statistics: Num rows: 1 Data size: 7020 Basic stats: COMPLETE Column stats: NONE

​       table:

​         input format: org.apache.hadoop.mapred.SequenceFileInputFormat

​         output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

​         serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

 

 Stage: Stage-0

  Fetch Operator

   limit: -1

   Processor Tree:

​    ListSink

8.2 本地模式

大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过，有时Hive的输入数据量是非常小的，比如说我现在这种学习阶段。在这种情况下，为查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。

用户可以通过设置hive.exec.mode.local.auto的值为true，来让Hive在适当的时候自动启动这个优化。

set hive.exec.mode.local.auto=true;  //开启本地mr
//设置local mr的最大输入数据量，当输入数据量小于这个值时采用local  mr的方式，默认为134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
//设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4
set hive.exec.mode.local.auto.input.files.max=10;

8.3 表的优化 8.3.1 大表和小表join（MapJoin）

将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率；再进一步，可以使用map join让小的维度表（1000条以下的记录条数）先进内存。在map端完成join。

实际测试发现：新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有明显区别。并且Hive默认开启了MapJoin功能。

所以我们只需要设置大表和小表的阈值就好啦

大表小表的阈值设置（默认25M以下认为是小表）：

set hive.mapjoin.smalltable.filesize = 25000000;

8.3.2 大表和大表Join

1）空key过滤

有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。

2）空key转换

有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上。

8.3.3 Group by

默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。

并不是所有的聚合 *** 作都需要在Reduce端完成，很多聚合 *** 作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。

1） 开启map端聚合参数设置

（1）是否在Map端进行聚合，默认为True

set hive.map.aggr = true

（2）在Map端进行聚合 *** 作的条目数目

set hive.groupby.mapaggr.checkinterval = 100000

（3）有数据倾斜的时候进行负载均衡（默认是false）

set hive.groupby.skewindata = true

当选项设定为 true，生成的查询计划会有两个MR Job。第一个MR Job中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合 *** 作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合 *** 作。

8.3.4 Count（Diistinct） 去重统计

数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT *** 作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换,但是需要注意group by造成的数据倾斜问题。

8.3.5 行列过滤

列处理：*在SELECT中，只拿需要的列，如果有分区，尽量使用分区过滤，少用SELECT 。

行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤，最好能够通过子查询后再关联表，如下：

hive (default)> select b.id from bigtable b

join (select id from bigtable where id <= 10 ) o on b.id = o.id;

8.4 合理设置Map和Reduce数 合理设置Map数

1）通常情况下，作业会通过input的目录产生一个或者多个map任务。

主要的决定因素有：input的文件总个数，input的文件大小，集群设置的文件块大小。

2）是不是map数越多越好？

答案是否定的。如果一个任务有很多小文件（远远小于块大小128m），则每个小文件也会被当做一个块，用一个map任务来完成，而一个map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的map数是受限的。

3）是不是保证每个map处理接近128m的文件块，就高枕无忧了？

答案也是不一定。比如有一个127m的文件，正常会用一个map去完成，但这个文件只有一个或者两个小字段，却有几千万的记录，如果map处理的逻辑比较复杂，用一个map任务去做，肯定也比较耗时。

针对上面的问题2和3，我们需要采取两种方式来解决：即减少map数和增加map数；

当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。
增加map的方法为：根据computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M公式，调整maxSize最大值。让maxSize最大值低于blocksize就可以增加map的个数。

在map执行前合并小文件，减少map数：CombineHiveInputFormat具有对小文件进行合并的功能（系统默认的格式）。HiveInputFormat没有对小文件合并功能。

合理设置Reduce数

（1）过多的启动和初始化reduce也会消耗时间和资源；

（2）另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；

在设置reduce个数的时候也需要考虑这两个原则：处理大数据量利用合适的reduce数；使单个reduce任务处理数据量大小要合适；

8.5 严格模式

Hive可以通过设置防止一些危险 *** 作：

1） 将hive.strict.checks.no.partition.filter设置为true时，对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。

2）将hive.strict.checks.orderby.no.limit设置为true时，对于使用了order by语句的查询，要求必须使用limit语句。

3） 将hive.strict.checks.cartesian.product设置为true时，会限制笛卡尔积的查询。