hive归纳总结

1. Hive的架构

Hive元数据默认存储在derby数据库，不支持多客户端访问，所以将元数据存储在MySQl，支持多客户端访问。

HiveServer2可以支持多客户端并发和身份认证。旨在为开放API客户端（如JDBC和ODBC）提供更好的支持

详见：https://zhuanlan.zhihu.com/p/68194396

2. Hive和数据库比较

Hive 和数据库除了拥有类似的查询语言，再无类似之处。
1）数据存储位置
Hive 存储在 HDFS 。数据库将数据保存在块设备或者本地文件系统中。
2）数据更新
Hive中不建议对数据的改写。而数据库中的数据通常是需要经常进行修改的，
3）执行延迟
Hive 执行延迟较高。数据库的执行延迟较低。当然，这个是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出优势。
4）数据规模
Hive支持很大规模的数据计算；数据库可以支持的数据规模较小。

3. 内部表和外部表

元数据、原始数据
1）删除数据时：
内部表：元数据、原始数据，全删除
外部表：元数据 只删除
2）在公司生产环境下，什么时候创建内部表，什么时候创建外部表？
在公司中绝大多数场景都是外部表。
自己使用的临时表，才会创建内部表；

补充
未被external修饰的是内部表（managed table）， 被external修饰的为外部表（external table）；
区别：
a.内部表数据由Hive自身管理，外部表数据由HDFS管理；
b.内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定；
c.删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除，数据还在HDFS里面；
d.对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要 修复（MSCK REPAIR TABLE table_name;）主要体现在load与drop（是否同时删除元数据与数据）的操作上
e.Hive创建内部表时，会将数据移动到数据仓库指向的路径，hive管理数据的生命周期。创建外部表时，仅仅记录数据所在的路径，并不会对数据做任何处理。
选择：
内部表与外部表没有太大区别，如果所有的 数据都用Hive处理，则创建内部表；如果数据的处理需要 hive和其他工具一起处理，则创建外部表。

4. 4个By区别

1）Order By：全局排序，只有一个Reducer；
2）Sort By：分区内有序；
3）Distribute By：类似MR中Partition，进行分区，结合sort by使用。
4） Cluster By：当Distribute by和Sorts by字段相同时，可以使用Cluster by方式。Cluster by除了具有Distribute by的功能外还兼具Sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
在生产环境中Order By用的比较少，容易导致OOM。
在生产环境中Sort By + Distribute By用的多。

补充：
order by：hive中的order by 和传统sql中的order by 一样，对数据做全局排序，加上排序，会新启动一个job进行排序，会把所有数据放到同一个reduce中进行处理，不管数据多少，不管文件多少，都启用一个reduce进行处理。如果指定了hive.mapred.mode=strict（默认值是nonstrict）,这时就必须指定limit来限制输出条数，原因是：所有的数据都会在同一个reducer端进行，数据量大的情况下可能不能出结果，那么在这样的严格模式下，必须指定输出的条数。

sort by：sort by 是局部排序，会在每个reduce端做排序，每个reduce端是排序的，也就是每个reduce出来的数据是有序的，但是全部不一定有序，除非一个reduce，一般情况下可以先进行局部排序完成后，再进行全局排序，会提高不少效率。

distribute by：distribute by 是控制map端在reduce上是如何区分的，distribute by 会把指定的值发到同一个reduce中，比如 用上面数据distribute by id 它就会把id相同的值放到一个reduce中执行，不是一个值一个reduce，而是相同的值进入到一个reduce，例如用上面数据可以进入到2个reduce，一般情况下可以sort by 结合使用，先进行分组reduce，再进行排序（相当于mapreduce中的分区函数）。

PS：Order by 能够预期产生完全排序的结果，但是它是通过只用一个reduce来做到这点的。所以对于大规模的数据集它的效率非常低。在很多情况下，并不需要全局排序，此时可以换成Hive的非标准扩展sort by。
Sort by为每个reducer产生一个排序文件。在有些情况下，你需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。
Hive的distribute by 子句可以做这件事

cluster by（只能是使用默认的升序排序，不能使用ACS和DESC）：
这个其实就是distribute by 和sort by 结合使用的结果（前提是同一个字段）。　　
例如：select id,money,name from t cluster by id;等价于：select id,money,name from t distribute by id sort by id

distribute by和group by的区别：都是按key值划分数据 都使用reduce操作 **唯一不同的是，distribute by只是单纯的分散数据，distribute by col – 按照col列把数据分散到不同的reduce。而group by把相同key的数据聚集到一起，后续必须是聚合操作。

order by和sort by的区别：order by是全局排序 sort by只是确保每个reduce上面输出的数据有序。如果只有一个reduce时，和order by作用一样。

5. 系统函数

1）date_add、date_sub函数（加减日期）
2）next_day函数（周指标相关）
3）date_format函数（根据格式整理日期）
4）last_day函数（求当月最后一天日期）
5）collect_set函数
6）get_json_object解析json函数，用来解析json字符串的一个字段
7）json_tuple解析json函数，用来解析json字符串中的一个或多个字段
8）NVL（表达式1，表达式2）
如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。

附注：
1.get_json_object的使用示例：

> select get_json_object('{"age":18, "name": "lili", "gender": "female"}', "$.age");
+------+--+
| _c0  |
+------+--+
| 18   |
+------+--+

> select get_json_object('[{"age":18, "name": "lili", "gender": "female"}, {"age":19, "name": "lucy", "gender": "female"}, {"age":15, "name": "mike", "gender": "male"}]', "$.[0,1,2].age") as age;
+-------------+--+
|     age     |
+-------------+--+
| [18,19,15]  |
+-------------+--+

> select get_json_object('[{"age":18, "name": "lili", "gender": "female"}, {"age":19, "name": "lucy", "gender": "female"}, {"age":15, "name": "mike", "gender": "male"}]', "$.[0].age");
+------+--+
| _c0  |
+------+--+
| 18   |
+------+--+

2.json_tuple的使用示例：

> select json_tuple('{"age":18, "name": "lili", "gender": "female"}', "age");
+-----+--+
| c0  |
+-----+--+
| 18  |
+-----+--+

> select json_tuple('{"age":18, "name": "lili", "gender": "female"}', "age", "name", "gender");
+-----+-------+---------+--+
| c0  |  c1   |   c2    |
+-----+-------+---------+--+
| 18  | lili  | female  |
+-----+-------+---------+--+

6. 自定义UDF、UDTF函数

1）在项目中是否自定义过UDF、UDTF函数，以及用他们处理了什么问题，及自定义步骤？
（1）用UDF函数解析公共字段；用UDTF函数解析事件字段。
（2）自定义UDF：继承UDF，重写evaluate方法
（3）自定义UDTF：继承自GenericUDTF，重写3个方法：initialize(自定义输出的列名和类型)，process（将结果返回forward(result)），close
2）为什么要自定义UDF/UDTF？
因为自定义函数，可以自己埋点Log打印日志，出错或者数据异常，方便调试。

补充
自定义udf编写
编写java业务处理类，导出jar并上传至linux服务器

import java.util.Map;

import org.apache.commons.collections.map.HashedMap;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class DemoUDF extends UDF {
    
    public static Map<String,String> nationMap = new HashedMap();
    static{
        nationMap.put("china", "中国");
        nationMap.put("us", "美国");
        nationMap.put("uk", "英国");
    }
    Text t = new Text();
    public Text evaluate(Text nation){
        String nation_e = nation.toString();
        String name = nationMap.get(nation_e);
        if(name == null ){
            name = "非中美英";
        }
        t.set(name);
        return t;
    }
}

hive有三种方法使用自定义的UDF函数

1. 临时添加UDF，如下：

hive> select * from test;     
OK  
Hello  
wORLD  
ZXM  
ljz  
Time taken: 13.76 seconds  
hive> add jar /home/work/udf.jar;                                
Added /home/work/udf.jar to class path  
Added resource: /home/work/udf.jar  
hive> create temporary function mytest as 'test.udf.ToLowerCase';  
OK  
Time taken: 0.103 seconds  
hive> show functions;  
......  
mytest  
......  
hive> select mytest(test.name) from test;  
......  
OK  
hello  
world  
zxm  
ljz  
Time taken: 38.218 seconds 

这种方式在会话结束后，函数自动销毁，因此每次打开新的会话，都需要重新add jar并且create temporary function

2. 进入会话前自动创建，使用hive -i参数在进入hive时自动初始化：

$ cat hive_init   
add jar /home/work/udf.jar;  
create temporary function mytest as 'test.udf.ToLowerCase';  
$ hive -i hive_init   
Logging initialized using configuration in file:/home/work/hive/hive-0.8.1/conf/hive-log4j.properties  
Hive history file=/tmp/work/hive_job_log_work_201209200147_1951517527.txt  
hive> show functions;  
......  
mytest  
......  
hive> select mytest(test.name) from test;  
......  
OK  
hello  
world  
zxm  
ljz 

3. 自定义UDF注册为hive内置函数
   上传jar包到
   hdfs:hdfs dfs -put /opt/testudf3.jar /apps/hive/functions
   在hive命令行中create function 函数名 as ‘方法的全类名’ using jar ‘jar包的全路径’
   创建函数例如:
   create function triangle2 as ‘cn.udf.Triangle’ using jar ‘hdfs://192.168.233.133:9000/apps/hive/functions/testudf3.jar’;
   使用函数:select triangle2(6);
   
   ***附注：***临时函数只在当前会话中有用，且能够跨库使用；永久函数不能跨库使用，只能在创建的库中使用

参考：Hive（四）–hive内置函数、自定义函数UDF https://blog.csdn.net/jiandanbuguo/article/details/108776343

7. 窗口函数

1）Rank
（1）RANK() 排序相同时会重复，总数不会变
（2）DENSE_RANK() 排序相同时会重复，总数会减少
（3）ROW_NUMBER() 会根据顺序计算
2） OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
（1）CURRENT ROW：当前行 current row
（2）n PRECEDING：往前n行数据 precending
（3） n FOLLOWING：往后n行数据following
（4）UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点 unbounded
（5） LAG(col,n)：往前第n行数据lag
（6）LEAD(col,n)：往后第n行数据lead
（7） NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。ntile
3）手写TopN

8. Hive优化

1）MapJoin
如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即：在Reduce阶段完成join。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join，避免reducer处理。
2）行列过滤
列处理：在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。
行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤。
3）列式存储
4）采用分区技术
5）合理设置Map数
mapred.min.split.size: 指的是数据的最小分割单元大小；min的默认值是1B
mapred.max.split.size: 指的是数据的最大分割单元大小；max的默认值是256MB
通过调整max可以起到调整map数的作用，减小max可以增加map数，增大max可以减少map数。
需要提醒的是，直接调整mapred.map.tasks这个参数是没有效果的。
https://www.cnblogs.com/swordfall/p/11037539.html
6）合理设置Reduce数
Reduce个数并不是越多越好
（1）过多的启动和初始化Reduce也会消耗时间和资源；
（2）另外，有多少个Reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；
在设置Reduce个数的时候也需要考虑这两个原则：处理大数据量利用合适的Reduce数；使单个Reduce任务处理数据量大小要合适；
7）小文件如何产生的？
（1）动态分区插入数据，产生大量的小文件，从而导致map数量剧增；
（2）reduce数量越多，小文件也越多（reduce的个数和输出文件是对应的）；
（3）数据源本身就包含大量的小文件。
8）小文件解决方案
（1）在Map执行前合并小文件，减少Map数：CombineHiveInputFormat具有对小文件进行合并的功能（系统默认的格式），或者使用Apache archive进行文件合并。HiveInputFormat没有对小文件合并功能。
（2）merge
// 输出合并小文件
SET hive.merge.mapfiles = true; – 默认true，在map-only任务结束时合并小文件
SET hive.merge.mapredfiles = true; – 默认false，在map-reduce任务结束时合并小文件
SET hive.merge.size.per.task = 268435456; – 默认256M
SET hive.merge.smallfiles.avgsize = 16777216; – 当输出文件的平均大小小于16m该值时，启动一个独立的map-reduce任务进行文件merge
（3）开启JVM重用
set mapreduce.job.jvm.numtasks=10
9）开启map端combiner（不影响最终业务逻辑）
set hive.map.aggr=true；
10）压缩（选择快的）
设置map端输出、中间结果压缩。（不完全是解决数据倾斜的问题，但是减少了IO读写和网络传输，能提高很多效率）
set hive.exec.compress.intermediate=true --启用中间数据压缩
set mapreduce.map.output.compress=true --启用最终数据压缩
set mapreduce.map.outout.compress.codec=…; --设置压缩方式
11）采用tez引擎或者spark引擎

9. Hive解决数据倾斜方法

1）数据倾斜长啥样？

2）怎么产生的数据倾斜？
（1）不同数据类型关联产生数据倾斜
情形：比如用户表中user_id字段为int，log表中user_id字段string类型。当按照user_id进行两个表的Join操作时。
解决方式：把数字类型转换成字符串类型
select * from users a
left outer join logs b
on a.usr_id = cast(b.user_id as string)
bug记录：https://www.jianshu.com/p/2181e00d74dc
（2）控制空值分布
在生产环境经常会用大量空值数据进入到一个reduce中去，导致数据倾斜。
解决办法：
自定义分区，将为空的key转变为字符串加随机数或纯随机数，将因空值而造成倾斜的数据分不到多个Reducer。
注意：对于异常值如果不需要的话，最好是提前在where条件里过滤掉，这样可以使计算量大大减少
3）解决数据倾斜的方法？
（1）group by
注：group by 优于distinct group
解决方式：采用sum() group by的方式来替换count(distinct)完成计算。
（2）mapjoin
（3）开启数据倾斜时负载均衡
set hive.groupby.skewindata=true;
思想：就是先随机分发并处理，再按照key group by来分发处理。
操作：当选项设定为true，生成的查询计划会有两个MRJob。
第一个MRJob中，Map的输出结果集合会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的GroupBy Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；
第二个MRJob再根据预处理的数据结果按照GroupBy Key分布到Reduce中（这个过程可以保证相同的原始GroupBy Key被分布到同一个Reduce中），最后完成最终的聚合操作。
点评：它使计算变成了两个mapreduce，先在第一个中在shuffle过程partition时随机给 key打标记，使每个key随机均匀分布到各个reduce上计算，但是这样只能完成部分计算，因为相同key没有分配到相同reduce上。
所以需要第二次的mapreduce，这次就回归正常shuffle，但是数据分布不均匀的问题在第一次mapreduce已经有了很大的改善，因此基本解决数据倾斜。因为大量计算已经在第一次mr中随机分布到各个节点完成。
（4）设置多个reduce个数

10. Hive里边字段的分隔符用的什么？为什么用\t？有遇到过字段里边有\t的情况吗，怎么处理的？

hive 默认的字段分隔符为ascii码的控制符\001（^A）,建表的时候用fields terminated by ‘\001’。注意：如果采用\t或者\001等为分隔符，需要要求前端埋点和javaEE后台传递过来的数据必须不能出现该分隔符，通过代码规范约束。一旦传输过来的数据含有分隔符，需要在前一级数据中转义或者替换（ETL）。
可以设置参数（导入HDFS同样有效）：
–hive-drop-import-delims 导入到hive时删除 \n, \r, \001
–hive-delims-replacement 导入到hive时用自定义的字符替换掉 \n, \r, \001
字段包含分隔符存在的问题：

添加参数的效果：

在Hive表里的体现：

11. Tez引擎优点？

Tez可以将多个有依赖的作业转换为一个作业，这样只需写一次HDFS，且中间节点较少，从而大大提升作业的计算性能。
Mr/tez/spark区别：
Mr引擎：多job串联，基于磁盘，落盘的地方比较多。虽然慢，但一定能跑出结果。一般处理，周、月、年指标。
Spark引擎：虽然在Shuffle过程中也落盘，但是并不是所有算子都需要Shuffle，尤其是多算子过程，中间过程不落盘 DAG有向无环图。 兼顾了可靠性和效率。一般处理天指标。
Tez引擎：完全基于内存。 注意：如果数据量特别大，慎重使用。容易OOM。一般用于快速出结果，数据量比较小的场景。

12. MySQL元数据备份

1）MySQL之元数据备份（项目中遇到的问题）
元数据备份（重点，如数据损坏，可能整个集群无法运行，至少要保证每日零点之后备份到其它服务器两个复本）
Keepalived或者用mycat
2）MySQL utf8超过字节数问题
MySQL的utf8编码最多存储3个字节，当数据中存在表情号、特色符号时会占用超过3个字节数的字节，那么会出现错误 Incorrect string value: ‘\xF0\x9F\x91\x91\xE5\xB0…’
解决办法：将utf8修改为utf8mb4
首先修改库的基字符集和数据库排序规则

再使用 SHOW VARIABLES LIKE ‘%char%’; 命令查看参数

确保这几个参数的value值为utf8mb4 如果不是使用set命令修改
如：set character_set_server = utf8mb4;

13. Union与Union all区别

1）union会将联合的结果集去重，效率较union all差
2）union all不会对结果集去重，所以效率高

14. 深入理解Hive分区与分桶

分区：我们可以按照日期对数据表进行分区，不同日期的数据存放在不同的分区，在查询时只要指定分区字段的值就可以直接从该分区查找。

静态分区：
CREATE TABLE p_table2(id int, name string) PARTITIONED BY(date_day string,emp_no string) stored as orc;

动态分区：
动态分区与静态分区建表语句一样，不同在：动态分区插入数据时需要开启动态数据支持：
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nostrict;
插入数据（覆盖）insert overwrite table p_table2 partition(date_day，emp_no) select 2 as id,‘lily’ as name,‘2019-07-14’ as date_day, ‘a’ as emp_no;
分区并没有写死，而是根据查询到的值动态创建的两级分区。

分桶：它指定分桶表的某一列，让该列数据按照哈希取模的方式随机、均匀地分发到各个桶文件中。因为分桶操作需要根据某一列具体数据来进行哈希取模操作，故指定的分桶列必须基于表中的某一列（字段）
create table b_table1(id int,name string) clustered by (id) sorted by(id) into 4 buckets stored as textfile;
使用CLUSTERED BY 子句来指定划分桶所用的列和要划分的桶的个数，当表分区时，每个分区下都会有4个桶。

区别：
1、分区使用的是表外字段，需要指定字段类型；分桶使用的是表内字段，已经知道字段类型，不需要再指定。
2、分区通过关键字partitioned by(partition_name string)声明，分桶表通过关键字clustered by(column_name) into 3 buckets声明。
3、分区划分粒度较粗，分桶是更细粒度的划分、管理数据，可以对表进行先分区再分桶的划分策略。
4、分区是个伪列，只对应着文件存储路径上的一个层级。