Hive的基础到调优大全

一.hive基本概念

1.什么是hive
基于hadoop的数据仓库的工具,将结构化的数据映射成一张表,提供类sql的查询功能.
hive运行的本质就mapreduce.
2.hive的特点
1.可扩展
Hive可以自由的扩展集群的规模,一般情况下不需要重启服务.
2.延展性
Hive支持用户自定义函数,用户可以根据自己的需求来实现自己的函数.
3.容错性
良好的容错性,节点出现问题SQL仍可完成执行.
3.hive的架构
(1)用户接口:提供写sql的客户端
(2)解析器:主要包括三部分：
1)编译器:将sql编译成mapreduce
2)优化器:对执行的sql,mr进行优化
3)执行器:执行mr作业
(3)元数据存储:通常是存储在关系数据库如mysql/derby中.Hive 将元数据存储在数据库中.Hive 中的元数据包括表的名字,表的列和分区及其属性,表的属性(是否为外部表等),表的数据所在目录等.

4.hive存储
1)数据存储:hdfs
2)元数据库:默认 derby
3)hive数据存储:
Text SequenceFile,ParquetFile orc
5.启动命令
hive访问方式
(1) hive cli： bin/hive
(2) beeline:
前台启动: bin/hive --service hiveserver2
后台启动:nohup bin/hive --service hiveserver2 &
进入beeline：bin/beeline
连接hive :!connect jdbc:hive2://node02:10000
(3)hive命令：
hive -e 执行的sql
hive -f 执行的sql脚本

二.数据仓库

1.概念
Data Warehouse dw 或dwh
仓库:用户储存物品,粮仓:储存粮食. 数仓:数据仓库
数据仓库,不能创造数据,也不能消耗数据(删除),构建一个集成化的数据环境,往往用于企业数据分析和决策使用.
2.主要特征
1)面向主题:面向分析的内容进行数据的组织
2)集成性:多个数据源的数据集成到一起使用
3)非易失性:数据进行集合后不会轻易的删除
4)时变性:数据随时间变化,数据要更新.
3.数据仓库和数据库的区别
OLAP:联机分析处理（On-LineAnalytical Processing）：对某一主题的历史数据进行分析，支持决策所用，对于数据仓库，往往进行查询分析所使用。
OLTP:联机事物处理（On-Line Transaction Processing）：对于数据的增删改的事物操作
4.数据仓库的架构
1)源数据:ods,贴源层
2)数据仓库层:聚合数据,多维模型建立
3)数据应用层:做一个报表或图表综合展现
4)ETL(抽取Extra,转化Transfer, 装载Load)./configure --prefix=/usr/local/openresty --with-luajit && make && make install
5)元数据:数据仓库中数据模型的定义

三.Hive的基本操作

1.CURD数据库
create database if not exists myhive;
use myhive;
​ 创建指定目录的数据库:create database myhive2 location ‘/myhive2’;
​ 修改数据库:alter database myhive2 set dbproperties(‘key’=‘value’);
​ 查看数据库信息:desc database [extended] myhive2;
​ 删除数据库:drop database myhive2 [casecade];
2.创建表
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name //创建一张表 ，表名是table_name
[(col_name data_type [COMMENT col_comment], …)] //创建指定字段
[COMMENT table_comment] //给当前表添加注释，注释中不能用中文
[PARTITIONED BY (col_name data_type [COMMENT col_comment], …)] //分区，将数据分文件夹进行存储
[CLUSTERED BY (col_name, col_name, …) //分桶，相当于mr中的分区
[SORTED BY (col_name [ASC|DESC], …)] INTO num_buckets BUCKETS] //将数据分到几个桶当中
[ROW FORMAT row_format] //行的格式化
[STORED AS file_format] //数据的存储类型
[LOCATION hdfs_path] //指定存储的路径
3.管理表
1.内部表
创建表的时候不使用external创建表，称之为内部表。当删除表的时候，表的数据也随之删除。
1)create table stu(id int,name string);
2)创建表并指定分隔符
create table if not exists stu2(id int ,name string) row format delimited fields terminated by ‘\t’ stored as textfile location ‘/user/stu2’;
3)根据查询结果创建一张表，会将当前的表的数据和结构全部复制过来
create table stu3 as select * from stu2;
4)根据已存在的结构创建表，只会复制表结构，不会复制表数据
create table stu4 like stu2;
5)查询表的类型
desc formatted stu2；
2.外部表
external表示外部表 外部表认为并不是当前用户独占数据，删除的时候，只删除表结构，不删除表数据。
创建老师表：
create external table techer (t_id string,t_name string) row format delimited fields terminated by ‘\t’;
创建学生表：
create external table student (s_id string,s_name string,s_birth string , s_sex string ) row format delimited fields terminated by ‘\t’;
4.加载数据
1)本地加载数据
load data local inpath ‘local path’ [overwrite] into table student;
​2)从hdfs加载数据
load data inpath ‘hdfspath’ [overwrite] into table student;
5.分区表
在表的目录结构中,增加一级目录,核心思想:分而治之。
创建分区表语法
create table score(s_id string,c_id string, s_score int) partitioned by (month string) row format delimited fields terminated by ‘\t’;
加载数据到分区表中
load data local inpath ‘/export/servers/hivedatas/score.csv’ into table score partition (month=‘201806’);
6.分桶表
将数据按照指定的字段分成多个桶，类似于mr中的分区
开启hive的桶表功能
set hive.enforce.bucketing=true;
设置reduce的个数
set mapreduce.job.reduces=3;
创建通表
create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by ‘\t’;
加载数据不能使用load的方式，因为表已经分桶，要使用insert overwrite
7.hive中加载数据
1)直接向分区表中插入数据（非常不建议使用）
create table score3 like score;
insert into table score3 partition(month =‘201807’) values (‘001’,‘002’,‘100’);
2)通过查询插入数据
通过load方式加载数据
load data local inpath ‘/export/servers/hivedatas/score.csv’ overwrite into table score partition(month=‘201806’);
读时模式 ：无论数据对或错，先加载进来，然后读的在验证有效性。
写时模式：关系型数据库，在数据加载前完成一个数据的校验。
3)通过查询方式加载数据
create table score4 like score;
insert overwrite table score4 partition(month = ‘201806’) select s_id,c_id,s_score from score;
8.排序
1)order by：全局排序，只有一个reduce，会导致数据处理时间较长。
2)sort by:局部排序，只针对每一个reduce内的数据进行排序
3)distribute by :分桶使用，根据指定的字段将数据发送到不同的reduce中
4)Cluster by ：除了有分桶的作用，还可对数据排序。
因此，如果分桶和sort by字段是同一个时，此时，cluster by = distribute by + sort by

四.Hive的shell操作

sql语句…
load data inpath ‘/root/inner_table.dat’ into table t1; 移动hdfs中数据到t1表中
load data local inpath ‘/root/inner_table.dat’ into table t1; 上传本地数据到hdfs中

五.Hive的函数 udf用户自定义函数

1.内置函数
1)查看系统自带的函数
hive> show functions;
2)显示自带的函数的用法
hive> desc function upper;
3)详细显示自带的函数的用法
hive> desc function extended upper;
2.自定义函数
1)Hive 自带了一些函数,比如:max/min等,但是数量有限,自己可以通过自定义UDF来方便的扩展。
2)当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数(UDF：user-defined function).
3)根据用户自定义函数类别分为以下三种：
(1)UDF(User-Defined-Function)
一进一出
(2)UDAF(User-Defined Aggregation Function)
聚集函数，多进一出
类似于:count/max/min
(3)UDTF(User-Defined Table-Generating Functions)
一进多出
如lateral view explore()
4)官方文档地址
https://cwiki.apache.org/confluence/display/Hive/HivePlugins
5)编程步骤：
(1)继承org.apache.hadoop.hive.ql.UDF
(2)需要实现evaluate函数；evaluate函数支持重载；
6)注意事项
(1)UDF必须要有返回类型，可以返回null，但是返回类型不能为void；
(2)UDF中常用Text/LongWritable等类型，不推荐使用java类型；
3.UDF开发实例
第一步：创建maven java 工程，导入jar包 hive-exec
第二步：开发java类继承UDF，并重载evaluate 方法
第三步：将我们的项目打包，并上传到hive的lib目录下
第四步：添加我们的jar包到hive的客户端 add jar /export/servers/hive-1.1.0-cdh5.14.0/lib/udf.jar;
第五步：设置函数与我们的自定义函数关联 create temporary function tolowercase as ‘cn.itcast.udf.ItcastUDF’;
第六步：使用自定义函数 select tolowercase(‘abc’);

六.Hive的数据压缩

1.Snappy
2.开启Reduce输出阶段压缩
当Hive将输出写入到表中时，输出内容同样可以进行压缩。属性hive.exec.compress.output控制着这个功能。用户可能需要保持默认设置文件中的默认值false，这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行脚本中设置这个值为true，来开启输出结果压缩功能。
案例实操：
1）开启hive最终输出数据压缩功能
hive (default)>set hive.exec.compress.output=true;
2）开启mapreduce最终输出数据压缩
hive (default)>set mapreduce.output.fileoutputformat.compress=true;
3）设置mapreduce最终数据输出压缩方式
hive (default)> set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
4）设置mapreduce最终数据输出压缩为块压缩
hive (default)>set mapreduce.output.fileoutputformat.compress.type=BLOCK;
5）测试一下输出结果是否是压缩文件
insert overwrite local directory ‘/export/servers/snappy’ select * from score distribute by s_id sort by s_id desc;

七.hive的储存格式

1.列式存储和行式存储

行存储的特点： 查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。

列存储的特点： 因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。

2.开启Map输出阶段压缩
开启map输出阶段压缩可以减少job中map和Reduce task间数据传输量。具体配置如下：
案例实操：
1）开启hive中间传输数据压缩功能
hive (default)>set hive.exec.compress.intermediate=true;
2）开启mapreduce中map输出压缩功能
hive (default)>set mapreduce.map.output.compress=true;
3）设置mapreduce中map输出数据的压缩方式
hive (default)>set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;
4）执行查询语句
select count(1) from score;
3.四种格式
(1)TEXTFILE–textfile格式 行式存储
默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。
(2) ORC–org格式 列式存储
Orc (Optimized Row Columnar)是hive 0.11版里引入的新的存储格式。
可以看到每个Orc文件由1个或多个stripe组成，每个stripe250MB大小，这个Stripe实际相当于RowGroup概念，不过大小由4MB->250MB，这样能提升顺序读的吞吐率。每个Stripe里有三部分组成，分别是Index Data,Row Data,Stripe Footer：
(3)PARQUET–parquet格式 列式存储
Parquet是面向分析型业务的列式存储格式，由Twitter和Cloudera合作开发，2015年5月从Apache的孵化器里毕业成为Apache顶级项目。
Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。
通常情况下，在存储Parquet数据的时候会按照Block大小设置行组的大小，由于一般情况下每一个Mapper任务处理数据的最小单位是一个Block，这样可以把每一个行组由一个Mapper任务处理，增大任务执行并行度。
(4)SEQUENCEFILE 行式存储

八.数据压缩和储存模式的结合

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

九.Hive调优

1.fetch
(1)Fetch抓取(能不用mr尽量不拥)
能不走mr程序 尽量不走mr 程序 :全表扫描 列查询 limit查询
hive.fetch.task.conversion 默认是more 如果设置为none 意味着所谓的sql都走mr
(2)hive中mr程序智能本地 集群模式切换
set hive.exec.mode.local.auto =true;
hive会根据数据量以及map reduce个数自行评估 满足标准 local hadoop 不满足 yarn hadoop
控制条件：
整体输入的数据量 128M
整个maptask 4
整个reducetask 0 or 1
2.表的优化
(1)join
1.不管大小 还是小大 hive默认开启了map join 25M 小表
2.大大join null key处理
1).空key的过滤 —where id is not null;
2).空key的转换 case when
3).空key 的打散 rand()
2)group by(能在map端完成的 尽量不在reduce完成)
1.开启map聚合
set hive.groupby.mapaggr.checkinterval = 100000;
hive.map.aggr.hash.min.reduction=0.5
聚合的数据量 100000 > 0.5
2.开启reduce聚合数据倾斜的自动负载均衡
开启数据倾斜的条件
set hive.groupby.skewindata = true;
当选项设定为 true，生成的查询计划会有两个MR Job。
第一个会把结果随机分布到不同的reduce中进行局部聚合 避免数据倾斜
第二个会把第一个结果再根据group by 的条件 进行最终聚合
3)count(distinct) 先分组再统计 去重统计

数据量小 直接使用
如果数据量大 因为去重需要一个reducetask执行 就会使得reduce阶段及其漫长
优化方向：先分组 再统计 利用分组进行去重
(4)其他优化
1.笛卡尔积：
尽量避免笛卡尔积，即避免join的时候不加on条件，或者无效的on条件，Hive只能使用1个reducer来完成笛卡尔积。
2. 使用分区剪裁、列剪裁
在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。
在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤
3.动态分区调整：
默认是关闭的需要自己开启
set hive.exec.dynamic.partition=true;
关系型数据库中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用Hive的动态分区，需要进行相应的配置。
说白了就是以第一个表的分区规则，来对应第二个表的分区规则，将第一个表的所有分区，全部拷贝到第二个表中来，第二个表在加载数据的时候，不需要指定分区了，直接用第一个表的分区即可
4.分桶
分桶的好处在于减少join的时候笛卡尔积结果 优化查询结果
优化秘密：把join两边的表按照join的字段进行分桶操作 从而在底层优化join查询
3.数据倾斜问题 本质：maptask reducetask个数问题
maptask并行度决定因素
文件的个数
文件的大小
split size = block size = 128M
reducetask并行度决定因素
默认是一个 job.setNumReduceTasks(N) 手动决定的

maptask个数调整
文件都是大文件 maptask过多
/1.txt 10T
2.txt 5T
增大文件hdfs block size
直接修改hadoop 中dfs.blocksize 2G
也可以通过hive 中命令设置
(1)Map数
(2)小文件进行合并
在map执行前合并小文件，减少map数
小文件上传hdfs之前合并 java IO
hadopp fs -appenToFile
(3)如何适当的增加map数
当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。
(4)reduce数
1)调整reduce个数方法一
(1)每个Reduce处理的数据量默认是256MB
hive.exec.reducers.bytes.per.reducer=256123456
(2)每个任务最大的reduce数，默认为1009
hive.exec.reducers.max=1009
(3)计算reducer数的公式
N=min(参数2，总输入数据量/参数1)

2)调整reduce个数方法二
在hadoop的mapred-default.xml文件中修改
设置每个job的Reduce个数
set mapreduce.job.reduces = 15;
3)reduce个数并不是越多越好
(1)过多的启动和初始化reduce也会消耗时间和资源；
(2)另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；
在设置reduce个数的时候也需要考虑这两个原则：处理大数据量利用合适的reduce数；使单个reduce任务处理数据量大小要合适；

方式三: 佛性调优 人为设定 10
(5)面试中数据倾斜问题
什么是数据倾斜？
数据分配的不均匀 导致某个task迟迟不结束 影响最终job执行

map 100%  reduce90%

map 100%  reduce90%

map 100%  reduce90%

map 100%  reduce90%

数据倾斜怎么来的 ？怎么发现的？
数据本身问题
业务执行问题 sql逻辑问题

怎么解决数据倾斜问题？
分而治之 把大的数据倾斜的数据 进行打散分散 甚至不惜通过多步mr程序 先打散再处理
4.执行计划
用于查看sql语句执行的每个阶段情况 有哪些阶段 什么操作 是否具有依赖关系
explan 加sql语句
5.并行执行
如果一个job中存在于多个阶段 并且没有依赖关系 就可以开启并行执行
默认情况下是没有开启的 主要避免同时间消耗的资源过大。

set hive.exec.parallel=true; //打开任务并行执行
set hive.exec.parallel.thread.number=16 并行度
6.严格模式
控制一些意想不到的sql对性能产生的影响
默认是不开启的 nonstrict
如果开启 将会限制3中查询

如果表是分区表 若不使用分区字段查询 不允许
如果是group by语句 若不使用limit 不允许
如果join 语句 若不使用on表达式形成所谓的笛卡尔积 不允许
7.jvm重用
默认是一个jvm运行一个task 可以通过设置改变重用的次数
8.推测执行机制
mr中默认map reduce阶段都是开启的
通常在hive的使用中 都是关闭推测执行机制
9.数据压缩
压缩（压缩时间 压缩比 snappy） 数据存储格式：ORC PARQUET
10.hive的语句综合练习
注意:必练sql
https://blog.csdn.net/mrbcy/article/details/68965271