Hive 知识点总结

hive是什么？

Hive是基于 Hadoop 的一个数据仓库工具：
1.hive本身不提供数据存储功能，使用HDFS做数据存储；
hive的元数据存储：通常是存储在关系数据库如 mysql(推荐) , derby（内嵌数据库）中
2.hive也不分布式计算框架，hive的核心工作就是把sql语句翻译成MR程序；
3.hive也不提供资源调度系统，也是默认由Hadoop当中YARN集群来调度；
4.可以将结构化的数据映射为一张数据库表，并提供 HQL(Hive SQL)查询功能。

hive的架构原理

hive由Driver组件，MetaStore组件，用户接口，hadoop四部分组成。
Hive通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的Driver，结合元数据(MetaStore)，将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。
1、用户接口:Client CLI(hive shell 命令行),JDBC/ODBC(java访问hive),WEBUI(浏览器访问hive)
2、元数据:Metastore:元数据包括:表名,表所属数据库(默认是default),表的拥有者,列/分区字段,表的类型(是否是外部表),表的数据所在目录等
默认存储在自带的derby数据库中,推荐使用MySQL存储Metastore
3、hive 使用HDFS进行存储,使用MapReduce进行计算
4、驱动器:Driver
(1)解析器(SQL Parser):将SQL字符转换成抽象语法树AST,这一步一般使用都是第三方工具库完成,比如antlr,对AST进行语法分析,比如表是否存在,字段是否存在,SQL语句是否有误
(2)编译器(Physical Plan):将AST编译生成逻辑执行计划
(3)优化器(Query Optimizer):对逻辑执行计划进行优化
(4)执行器(Execution):把逻辑执行计划转换成可以运行的物理计划,对于Hive来说,就是MR/Spark

星型模型 雪花模型 应用场景

星型模型的设计方式主要带来的好处是能够提升查询效率，因为生成的事实表已经经过预处理，主要的数据都在事实表里面，所以只要扫描事实表就能够进行大量的查询，而不必进行大量的join，其次维表数据一般比较少，在join可直接放入内存进行join以提升效率，除此之外，星型模型的事实表可读性比较好，不用关联多个表就能获取大部分核心信息，设计维护相对比较简答。
雪花模型的设计方式是比较符合数据库范式的理念，设计方式比较正规，数据冗余少，但在查询的时候可能需要join多张表从而导致查询效率下降，此外规范化操作在后期维护比较复杂。

hive 解析 json格式的数据

自带解析函数：json_tuple
create table name as select json_tuple(json字段的字段名，‘json内容的key值1’，‘json内容的key值2’…) from tableName

列转行 侧视图

select xx,xxx from xxx lateral view explode(xxx) 侧视图name as ;

hive和数据库的区别

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

hive数据的存储

1.Hive中所有的数据都存储在HDFS中，没有专门的数据存储格式（可支持Text，SequenceFile，ParquetFile，RCFILE、ORC等）
text：行存储，默认不压缩，序列化、反序列化开销大
sequence：行存储，二进制，压缩率底
RCfile：行分块，列式存储，解压效率差，读取稍慢
Parquet：列式存储，压缩比率高，但比ORC差，存取速度快
ORC：行分块，列式存储，压缩快，存取快，压缩率最高，RCfile升级版
2.只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据。
3.Hive 中包含以下数据模型：DB、Table，External Table，Partition，Bucket。
db：在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹
table：在hdfs中表现所属db目录下一个文件夹
external table：与table类似，不过其数据存放位置可以在任意指定路径
partition：在hdfs中表现为table目录下的子目录
bucket：在hdfs中表现为同一个表目录下根据hash散列之后的多个文件

数据仓库的四层结构

ODS:临时存储层 近源层 一般都是建的外部表。 --> 防止用户修改了原始数据
PDW：数据仓库层 明细层 就是加工近源层的数据。 --> 实现宽表
MID: 数据集市层 服务层 一般使用内部表。 -->大部分都是宽表 提取特征值 不在乎冗余 在乎业务
APP：应用层

数据质量的校验： 错误值、重复值、数据不一致、缺失值

准确性：数据是否与其对应的客观实体的特征相一致；
有效性：数据是否满足用户定义的条件或一定的域值范围内；取值范围、字段长度、正则匹配规则。（手机号码：/^1[0-9]{10}$/）
唯一性：数据是否存在重复记录
完整性：数据是否存在缺失记录或者缺失字段

hive自定义函数

udf 一进一出（敏感字段进行脱敏处理 mid5加密） udaf 多进一出 udtf 一进多出（公共字符串解析）

public class LinkStr extends UDAF {
private static String result="";
public static class MyLink implements UDAFEvaluator{

    @Override
    public void init() {
    }
    //map的一部分功能  写业务逻辑
    public boolean iterate(String name){
        result = result.concat(name);
        return true;
    }
    //大部分是combiner功能 分组 分割
    public String terminatePartial(){
        return result;
    }
    //partition 负责处理
    public boolean merge(String name){
        return iterate(name);
    }
    //reduce的一部分功能
    public String terminate(){
        return result;
    }
}
}

public class InitialUpper extends UDF {
    public String evaluate(final String txt){
        return txt.substring(0,1).toUpperCase()+txt.substring(1); //首字母大写
    }
}

自定义UDTF(extends Genertic UDTF->实现三个方法init(指定返回值的名称和类型)、process(处理字段一进多出)、close方法) -> 更加灵活以及方便定义bug

数据倾斜的优化

原因:

1、key分部不均 2、数据本身就存在数据倾斜 3、某些sql语句本身就有数据倾斜 4、建表时考虑不周

参数调节

hive.map.aggr=true
在map端部分聚合，相当于 mr中的 combiner

hive.groupby.skewindata=true
有数据倾斜的时候进行负载均衡，生成的查询计划会有两个MR Job
第一个MR Job中 Map的输出结果会随机分布到reduce中，每个Reduce做部分聚合操作，并输出结果。这样处理的结果是相同的Group By Key 有可能被分发到不同的Reduce中，从而达到负载均衡的目的。
第二个MR Job再根据预处理的数据结果按照Group By Key 分布到Reduce中（这个过程可以保证相同的Group By Key 被分布到同一个Reduce中），最后完成最终的聚合操作。

小文件过多:
需要合并小文件 ，可以通过set hive.merge.mapfiles=true来解决。

sql语句的调优

大小表join :
将小表(关联键记录少的表)依次放到前面，这样可以触发reduce端更少的操作次数，减少运行时间。 同时可以使用Map Join让小的维度表缓存到内存。

大表与大表进行join，如日志中部分的 userid丢失，如果取其中的 user_id 和 用户表中的user_id 关联，会碰到数据倾斜的问题。
解决方法：把空值的key变成一个字符串加随机数（rand()），将倾斜的数据分到不同reduce上，从而达到负载均衡的目的。又由于null值在join的时候关联不上，所以处理后并不会影响最终的结果。
case when userid is null then concat(‘hive’,rand()) else userid end
延申出—》遗漏数据的处理，我们一般计算这个属性值的平均值，并用此值补该属性遗漏的值。

经常遇到 count(distinct)操作，这样会导致最终只有一个 Reduce 任务。采用group by去重，再count()的方式来代替count(distinct)完成计算。
比如计算按用户名去重后的总用户量：select count(1) from (select name from user group by name) tmp;

使用union all+group by 替代 union

不同数据类型关联产生数据倾斜:
像userid 有int 有的表是string 两表进行join的时候 默认的hash操作会按照int类型的id进行分配，导致所有的string类型的id被分配到同一个reduce中
解决； cast（useid as strin）

（–count distinct 大量相同的特殊值:
将值为空的情况单独处理 ，如果只是计算count distinct ，可以不用处理，直接过滤，在最后的结果加1。
如果还要进行其他的计算，如需要groupby ，那就先将值为空的单独处理，再和其他计算结果进行union all
group by维度过小，某值的数量过多
采用group by去重 再count() 的方式来代替count(distinct) 完成计算）

hive性能优化

一、存储优化，选用ORC

text：行存储，默认不压缩，序列化、反序列化开销大
sequence：行存储，二进制，压缩率底
RCfile：行分块，列式存储，解压效率差，读取稍慢
Parquet：列式存储，压缩比率高，但比ORC差，存取速度快
ORC：行分块，列式存储，压缩快，存取快，压缩率最高，RCfile升级版

二、表设计优化

1、创建分区表 （分区提供一个隔离数据和优化查询的便利方式）
动态分区（hive.exec.dynamic.partition=true hive.exec.max.dynamic.partition.mode=nonstrict）
静态分区要指定分区的列
2、创建桶表 （分区针对的是数据的存储路径；分桶针对的是数据文件）
3、拆分不同的表存储

三、sql参数优化

1、作业有多个可并行的job时，设置任务并行及并行个数：
// 开启任务并行执行
set hive.exec.parallel=true;
// 同一个sql允许并行任务的最大线程数
set hive.exec.parallel.thread.number=8;
2、修改reduce、map个数，
(一般情况下不会修改此参数，可能会造成很多小文件，或者如果reduce资源不够会一直等待，除非比较特殊的任务，前提是排除sql逻辑及数据问题)
设置每个reduce处理的数据量
set hive.exec.reducers.bytes.per.reducer=104857600;
设置reduce个数
set mapred.reduce.tasks=1;
调整最大限制个数
set hive.exec.reducers.max;
3、使用mapjoin
set hive.auto.convert.join = true;
set hive.mapjoin.smalltable.filesize=50000000; – 默认是25M
set hive.auto.convert.join.noconditionaltask = true; – 默认值
set hive.auto.convert.join.noconditionaltask.size = 10000000; --默认值
4、使用TEZ查询引擎
set hive.execution.engine=tez;
5、合并输入输出小文件
–输出合并小文件
set hive.merge.mapredfiles=true;
set hive.merge.smallfiles.avgsize=512000000;
set hive.merge.size.per.task=1024000000; --默认值 256M
– 输入合并小文件，也用于调节map数量
set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; --默认值
set mapred.max.split.size=512000000;
set mapred.min.split.size.per.node=256000000;
set mapred.min.split.size.per.rack=256000000;
set mapred.min.split.size=256000000;
6、数据倾斜
set hive.map.aggr=true；–在map中会做部分聚集操作，效率更高但需要更多的内存。
set hive.groupby.skewindata=true;
如果以上不管用，可以对倾斜的数据进行单独的sql处理。

四、sql优化

1、使用union all+group by 替代 union
2、大小表使用mapjoin
3、用in 替代join查询
4、从逻辑上看有无优化的点
5、避免笛卡尔积

五、开启本地模式

hive.exec.mode.local.auto=true
hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显缩短。

几个排序的区别

order by 全局排序
sort by 每个reducer内部排序
distinct by 类似MR中的partition 进行分区，结合sort by 使用。放在 distinct by后面。
cluster by 当distinct by 和sort by 字段相同时，可以使用cluster by 但是只能升序排序。

窗口函数

rank() 排序相同时会重复，总数不会变
dense_rank() 排序相同时会重复，总数会变
row_number() 会根据顺序排序
over()子句：
聚合函数COUNT、SUM、MIN、MAX、AVG
over(partition by xx order by xxx)
LAG 和 LEAD 函数 可以返回上下数据行的数据。
window子句；

• PRECEDING：往前
• FOLLOWING：往后
• CURRENT ROW：当前行
• UNBOUNDED：起点

时间函数：

data_add()时间加day
data_sub()时间减day

数据缺失值处理办法；

删除样本或删除字段
用中位数、平均值、众数等填充
插补：同类均值插补、多重插补、极大似然估计

用其它字段构建模型，预测该字段的值，从而填充缺失值（注意：如果该字段也是用于预测模型中作为特征，那么用其它字段建模填充缺失值的方式，并没有给最终的预测模型引入新信息）

hash join

oracle,postgresql它们都是支持hash-join的，mysql并不支持
hash join是一种数据库在进行多表连接时的处理算法，对于多表连接还有两种比较常用的方式：sort merge-join 和 nested loop。

hash-join就算讲两表中的小表（称S）作为hash表，然后去扫描另一个表(称M)的每一行数据，用得出来的行数据根据连接条件去映射建立的hash表，hash表是放在内存中的，这样可以很快的得到对应的S表与M表相匹配的行。

hash-join只适用于等值连接，对于>, <, <=, >=这样的查询连接还是需要nested loop这种通用的连接算法来处理。如果连接key本来就是有序的或者需要排序，那么可能用merge-join的代价会比hash-join更小，此时merge-join会更有优势。

对于结果集很大的情况，merge-join需要对其排序效率并不会很高，而nested loop join是一种嵌套循环的查询方式无疑更不适合大数据集的连接，而hash-join正是为处理这种棘手的查询方式而生，尤其是对于一个大表和一个小表的情况，基本上只需要将大小表扫描一遍就可以得出最终的结果集。