Hive 自定函数和优化策略

七、函数

7.1 系统内置函数

1. 查看系统自带的函数

   show functions;
   

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

   desc function extended upper;
   

7.2 自定义函数

1. 函数分类

   UDF（User-Defined-Function） 一进一出型 lower()

   UDAF（User-Defined Aggregation Function） 多进一出型 count/max/min

   UDTF（User-Defined Table-Generating Functions） 一进多出型 lateral view explore()

2. 自定义一个 UDF 函数

   步骤：

   （1）继承 org.apache.hadoop.hive.ql.UDF

   （2）需要实现evaluate函数；evaluate函数支持重载；

   （3）在hive的命令行窗口创建函数

   ​ a）添加 jar

   add jar linux_jar_path;
   

   ​ b）创建 function

   create [temporary] function [dbname.]function_name AS class_name;
   

   （4）在 hive 的命令行窗口删除函数

   Drop [temporary] function [if exists] [dbname.]function_name;
   

   注意：UDF 必须要有返回类型，可以返回 null，但是返回类型不能为 void；

八、压缩和存储

8.1 开启 Map 输出阶段压缩

开启 map 输出阶段压缩可以减少 job 中 map 和 Reduce task 间数据传输量。

开启 hive 中间传输数据压缩功能

set hive.exec.compress.intermediate=true;

开启 mapreduce 中 map 输出压缩功能

set mapreduce.map.output.compress=true;

设置 mapreduce 中 map 输出数据的压缩方式

set mapreduce.map.output.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;

执行查询语句

select count(ename) name from emp;

8.2 开启 Reduce 输出阶段压缩

当 Hive 将输出写入到表中时，输出内容同样可以进行压缩。属性 hive.exec.compress.output 控制着这个功能。

开启 hive 最终输出数据压缩功能

set hive.exec.compress.output=true;

开启 mapreduce 最终输出数据压缩

set mapreduce.output.fileoutputformat.compress=true;

设置 mapreduce 最终数据输出压缩方式

set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;

设置 mapreduce 最终数据输出压缩为块压缩

set mapreduce.output.fileoutputformat.compress.type=BLOCK;

测试一下输出结果是否是压缩文件

insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

8.3 文件存储格式

Hive 支持的存储数的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET

TEXTFILE 和 SEQUENCEFILE 的存储格式都是基于行存储的。

ORC 和 PARQUET 是基于列式存储的

TEXTFILE 格式：默认格式，数据不做压缩，磁盘开销大，数据解析开销大。

Orc 格式：Index Data（默认是每隔1W行做一个索引）、Row Data、Stripe Footer

Parquet 格式：Parquet 格式文件是自解析的。

8.4 存储和压缩结合

1. 修改 Hadoop 集群具有 Snappy 压缩方式

   查看 hadoop checknative 命令使用

   hadoop checknative
   

   查看 hadoop 支持的压缩方式

   INFO zlib.ZlibFactory: Successfully loaded & initialized native-zlib library
   Native library checking:
   hadoop:  true /opt/module/hadoop-2.7.2/lib/native/libhadoop.so
   zlib:    true /lib64/libz.so.1
   snappy:  false 
   lz4:     true revision:99
   bzip2:   false
   

   将编译好的支持 Snappy 压缩的 hadoop-2.7.2.tar.gz 包导入到 hadoop102 的 /opt/software 中，解压 hadoop-2.7.2.tar.gz 到当前路径

   tar -zxvf hadoop-2.7.2.tar.gz
   

   进入到 /opt/software/hadoop-2.7.2/lib/native 路径可以看到支持 Snappy 压缩的动态链接库

   /opt/software/hadoop-2.7.2/lib/native
   

   

   拷贝 /opt/software/hadoop-2.7.2/lib/native 里面的所有内容到开发集群的 /opt/module/hadoop-2.7.2/lib/native 路径上

   cp ../native/* /opt/module/hadoop-2.7.2/lib/native/
   

   分发集群

   xsync native/
   

   再次查看 hadoop 支持的压缩类型

   hadoop checknative
   INFO zlib.ZlibFactory: Successfully loaded & initialized native-zlib library
   Native library checking:
   hadoop:  true /opt/module/hadoop-2.7.2/lib/native/libhadoop.so
   zlib:    true /lib64/libz.so.1
   snappy:  true /opt/module/hadoop-2.7.2/lib/native/libsnappy.so.1
   lz4:     true revision:99
   bzip2:   false
   

   重新启动 hadoop 集群和 hive

2. 测试存储和压缩

   创建一个非压缩的的 ORC 存储方式的表

   create table log_orc_none(
   track_time string,
   url string,
   session_id string,
   referer string,
   ip string,
   end_user_id string,
   city_id string
   )
   row format delimited fields terminated by '\t'
   stored as orc tblproperties ("orc.compress"="NONE");
   

   插入数据

   insert into table log_orc_none select * from log_text ;
   

   查看插入后数据

   dfs -du-h /user/hive/warehouse/log_orc_none/ ;
   

   

   创建一个 SNAPPY 压缩的 ORC 存储方式的表

   create table log_orc_snappy(
   track_time string,
   url string,
   session_id string,
   referer string,
   ip string,
   end_user_id string,
   city_id string
   )
   row format delimited fields terminated by '\t'
   stored as orc tblproperties ("orc.compress"="SNAPPY");
   

   插入数据

   insert into table log_orc_snappy select * from log_text ;
   

   查看插入后数据

   dfs -du -h /user/hive/warehouse/log_orc_snappy/ ;
   

   
   orc存储文件默认采用ZLIB压缩。比snappy压缩的小。2.8 M

在实际的项目开发当中，hive表的数据存储格式一般选择：orc或parquet。压缩方式一般选择snappy，lzo。

九、调优

9.1 Fetch 抓取

​ Fetch 抓取是指，Hive 中对某些情况的查询可以不必使用MapReduce计算。例如：SELECT *
FROM employees; 在这种情况下，Hive 可以简单地读取 employee 对应的存储目录下的文件，然后输出查询结果到控制台。

​ 在 hive-default.xml.template 文件中 hive.fetch.task.conversion 默认是 more，老版本 hive 默认是 minimal，该属性修改为 more 以后，在全局查找、字段查找、limit 查找等都不走 mapreduce。

<property>
    <name>hive.fetch.task.conversion</name>
    <value>more</value>
    <description>
      Expects one of [none, minimal, more].
      Some select queries can be converted to single FETCH task minimizing latency.
      Currently the query should be single sourced not having any subquery and should not have
      any aggregations or distincts (which incurs RS), lateral views and joins.
      0. none : disable hive.fetch.task.conversion
      1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only
      2. more  : SELECT, FILTER, LIMIT only (support TABLESAMPLE and virtual columns)
    </description>
 </property>

9.2 本地模式

​ 有时 Hive 的输入数据量非常小，在该情况下，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;

9.3 表的优化

1. 小表、大表 Join

   将 key 相对分散，并且数据量小的表放在 join 的左边，这样可以有效减少内存溢出错误发生的几率;

   使用 map join 让小的维度表（1000条以下的记录条数）先进内存。在 map 端完成 reduce。

2. 大表 Join 大表

   空 Key 过滤

   有时 join 超时是因为某些 key 对应的数据太多，而相同 key 对应的数据都会发送到相同的 reducer 上，key对应的字段为空，从而导致内存不够。

   -- 过滤空 Key
   insert overwrite table jointable 
   select n.* from (select * from nullidtable where id is not null ) n  left join ori o on n.id = o.id;
   

   空 key 转换

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

   -- 空 Key 转换成 'hive+随机数'
   insert overwrite table jointable
   select n.* from nullidtable n full join ori o on 
   case when n.id is null then concat('hive', rand()) else n.id 
   

3. MapJoin

   如果不指定 MapJoin 或者不符合 MapJoin 的条件，那么 Hive 解析器会将 Join 操作转换成 Common Join，即：在 Reduce 阶段完成 join。容易发生数据倾斜。可以用 MapJoin 把小表全部加载到内存在 map 端进行 join，避免 reducer 处理。

   开启 MapJoin 参数设置

   -- 设置自动选择 Mapjoin
   set hive.auto.convert.join = true; 默认为true
   -- 大表小表的阈值设置（默认 25M 以下认为是小表）
   set hive.mapjoin.smalltable.filesize=25000000;
   

4. Group By

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

   开启 Map 端聚合参数设置

   -- 是否在Map端进行聚合，默认为True
   set hive.map.aggr = true
   -- 在 Map 端进行聚合操作的条目数目
   set hive.groupby.mapaggr.checkinterval = 100000
   -- 有数据倾斜的时候进行负载均衡（默认是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 中），最后完成最终的聚合操作。

5. Count(Distinct) 去重统计

   数据量小的时候无所谓，数据量大的情况下，由于 COUNT DISTINCT 操作需要用一个 Reduce Task 来完成，这一个 Reduce 需要处理的数据量太大，就会导致整个 Job 很难完成，一般 COUNT DISTINCT 使用先 GROUP BY 再 COUNT 的方式替换。

   -- 设置5个reduce个数
   set mapreduce.job.reduces = 5;
   -- 直接使用 DISTINCT 关键字
   select count(distinct id) from bigtable;
   -- 采用GROUP by去重id
   select count(id) from (select id from bigtable group by id) a;
   

   虽然会多用一个 Job 来完成，但在数据量大的情况下，这个绝对是值得的。

6. 笛卡尔积问题

   尽量避免笛卡尔积，join 的时候不加 on 条件，或者无效的 on 条件，Hive 只能使用1个 reducer 来完成笛卡尔积。

7. 行列过滤

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

   行处理：在分区剪裁中，当使用外关联时，先使用 Where 条件过滤，在进行表关联，这样可以避免全表关联。

8. 动态分区调整

   进行相应的配置后，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中。

   开启动态分区参数设置

   -- 开启动态分区功能（默认true，开启）
   set hive.exec.dynamic.partition=true;
   -- 设置为非严格分区模式
   set hive.exec.dynamic.partition.mode=nonstrict;
   -- 在所有执行MR的节点上，最大一共可以创建多少个动态分区
   set hive.exec.max.dynamic.partitions=1000;
   -- 在每个执行MR的节点上，最大可以创建多少个动态分区。
   set hive.exec.max.dynamic.partitions.pernode=100;
   -- 整个MR Job中，最大可以创建多少个HDFS文件。
   set hive.exec.max.created.files=100000
   -- 当有空分区生成时，是否抛出异常。一般不需要设置。
   set hive.error.on.empty.partition=false
   
   create table nba (name string, number int, team string)
   row format delimited fields terminated by '\t';
   
   insert overwrite table nba_part partition(team)
   select name,number,team from nba;
   

9. 分桶

10. 分区

9.4 数据倾斜

1. 合理设置 Map 数

2. 小文件进行合并

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

   set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
   

3. 复杂文件增加 Map 数

   当 input 的文件都很大，任务逻辑复杂，map 执行非常慢的时候，可以考虑增加 map 数，来使得每个 map 处理的数据量减少，从而提高任务的执行效率。

   -- 根据公式 computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize))) 调整 maxSize 最大值
   -- 让 maxSize 最大值低于 blocksize 就可以增加 map 的个数。
   set mapreduce.input.fileinputformat.split.maxsize;
   

4. 合理设置 Reduce 数

   -- 调整reduce个数方法一
   -- 每个Reduce处理的数据量默认是256MB
   hive.exec.reducers.bytes.per.reducer=256000000
   -- 每个任务最大的reduce数，默认为1009
   hive.exec.reducers.max=1009
   -- 计算reducer数的公式
   N=min(参数2，总输入数据量/参数1)
   
   -- 调整reduce个数方法二
   -- 在hadoop的mapred-default.xml文件中修改，设置每个job的Reduce个数
   set mapreduce.job.reduces = 15;
   

   reduce 个数并不是越多越好，要遵循两个原则：处理大数据量利用合适的 reduce 数；使单个 reduce 任务处理数据量大小要合适。

9.5 并行执行

​ Hive 会将一个查询转化成一个或者多个阶段，这些阶段可能并非完全互相依赖的，有些阶段是可以并行执行的。

​ 通过设置参数 hive.exec.parallel 值为 true，就可以开启并发执行。如果job中并行阶段增多，那么集群利用率就会增加。

​ 在系统资源比较空闲的时候才有优势，否则，没资源，并行也起不来。

set hive.exec.parallel=true;              //打开任务并行执行
set hive.exec.parallel.thread.number=16;  //同一个sql允许最大并行度，默认为8。

9.6 严格模式

​ Hive 提供了一个严格模式，可以防止用户执行那些可能意想不到的不好的影响的查询。

​ 通过设置属性 hive.mapred.mode 值为默认是非严格模式 nonstrict 。

<property>
    <name>hive.mapred.mode</name>
    <value>strict</value>
    <description>
      The mode in which the Hive operations are being performed. 
      In strict mode, some risky queries are not allowed to run. They include:
        Cartesian Product.
        No partition being picked up for a query.
        Comparing bigints and strings.
        Comparing bigints and doubles.
        Orderby without limit.
</description>
</property>

​ 除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。

​ 使用了order by语句的查询，要求必须使用limit语句。

​ 限制笛卡尔积的查询。

9.7 JVM 重用

​ JVM 重用是 Hadoop 调优参数的内容，其对 Hive 的性能具有非常大的影响，特别是对于很难避免小文件的场景或 task 特别多的场景，这类场景大多数执行时间都很短。JVM 重用可以使得 JVM 实例在同一个 job 中重新使用 N 次。

<property>
  <name>mapreduce.job.jvm.numtasks</name>
  <value>10</value>
  <description>How many tasks to run per jvm. If set to -1, there is
  no limit. 
  </description>
</property>

​ 如果某个“不平衡的”job中有某几个 reduce task 执行的时间要比其他 Reduce task 消耗的时间多的多的话，那么保留的插槽就会一直空闲着却无法被其他的 job 使用，直到所有的 task 都结束了才会释放。

9.8 推测执行

​ 推测执行（Speculative Execution）机制，它根据一定的法则推测出“拖后腿”的任务，并为这样的任务启动一个备份任务，让该任务与原始任务同时处理同一份数据，并最终选用最先成功运行完成任务的计算结果作为最终结果。

​ 设置开启推测执行参数：Hadoop的mapred-site.xml文件中进行配置

<property>
  <name>mapreduce.map.speculative</name>
  <value>true</value>
  <description>If true, then multiple instances of some map tasks 
               may be executed in parallel.</description>
</property>

<property>
  <name>mapreduce.reduce.speculative</name>
  <value>true</value>
  <description>If true, then multiple instances of some reduce tasks 
               may be executed in parallel.</description>
</property>

​ hive 本身也提供了配置项来控制 reduce-side 的推测执行

<property>
    <name>hive.mapred.reduce.tasks.speculative.execution</name>
    <value>true</value>
    <description>Whether speculative execution for reducers should be turned on. </description>
</property>

9.9 压缩

9.10 执行计划（Explain）

​ 同 SQL 的 explain 类似，可以查看语句的，详细执行环节。

​ 基本语法：

EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query