Hive优化培训ppt课件
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
2）有数据倾斜时使用负载均衡
set hive.groupby.skewindata = true; -- 有数据倾斜的时候进行负载均衡（默认是false） 当选项设定为 true 时，生成的查询计划有两个 MapReduce 任务。在第一个 MapReduce 任务中，map 的输出结果会随机分布 到 reduce 中，每个 reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的group by key有可能分发到不同的 reduce 中，从而达到负载均衡的目的；第二个 MapReduce 任务再根据预处理的数据结果按照group by key分布到各个 reduce 中，最 后完成最终的聚合操作。
Join优化
1）优先过滤数据，最大限度减少参与join的数据量
2）小表join大表原则 join 操作的reduce阶段，位于join左边的表内容会被加载进内存
3）多表join使用相同多连接键
4）启用mapjoin mapjoin是将join双方比较小的表直接分发到各个map进程的内存中，在map进程中进行join操作，这样就 不用进行reduce步骤，从而提高了速度
启用压缩
1）Map输出压缩
set press=true; set press.codec=press.SnappyCodec;
2）中间数据压缩
set press.intermediate=true; set pression.codec=press.SnappyCodec; set pression.type=BLOCK;
-- 是否根据输入小表的大小，自动将reduce端的common join 转化为map join，将小表刷入内存中。 set hive.auto.convert.join = true; -- 刷入内存表的大小(字节) set hive.mapjoin.smalltable.filesize = 2500000; -- Map Join所处理的最大的行数。超过此行数，Map Join进程会异常退出 set hive.mapjoin.maxsize=1000000;
stored as textfile;
Lateral view outer explode：
原理解析
1. HQL到MapReduce
2. 分析过程
1）将SQL转为AST 2）语义分析(Semantics Analyze) 3）将QueryBlock转化为OperatorTree 4）优化OperatorTree 5）翻译为MapReduce任务 6）生成最终执行计划
控制reduce数量
参数1：hive.exec.reducers.bytes.per.reducer(默认1G) 参数2：hive.exec.reducers.max(默认为999)
reduce计算公式： N = min(参数2， 总输入数据量/参数1)
直接控制： set mapred.reduce.tasks=10;
Count Distinct优化
修改前：select count(distinct id) from tablename; 修改后：select count(1) from (select distinct id from tablename) tmp; 启动两个job，一个job负责子查询(可以有多个reduce)，另一个job负责count(1)
-- Map端输入，不合并 set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
2）MapReduce输出合并 -- 是否合并Map输出文件, 默认值为真 set hive.merge.mapfiles=true;
-- 是否合并Reduce 端输出文件,默认值为假 set hive.merge.mapredfiles=true;
大数据基础
Hive使用优化
部门： 姓名： 日期：
01 Hive基础
02 原理解析 03 程序优化
案例分析
Hive基础
1. 数据类型 2. 数据存储格式 3. DDL操作 4. DML操作
基础数据类型
数值类型： TINYINT 、SMALLINT、INT 、BIGINT、FLOAT、DOUBLE、DECIMAL 字符串类型： STRING、VARCHAR、CHAR 日期类型： TIMESTAMP、DATE、INTERVAL 其他类型： BOOLEAN、BINARY
手工控制Mapper的数量(必须大于default_mapper_num才生效)：
set mapred.map.tasks=10;
通过修改每个片的大小来控制mapper个数：
可以通过mapred.min.split.size设置每个任务处理的文件的大小，这个大小只有在大于dfs_block_size的时候才会生效 split_size=max(mapred.min.split.size, dfs_block_size) split_num=total_size/split_size compute_map_num = min(split_num, max(default_mapper_num, mapred.map.tasks))
读取需要的列只需要读取每个row group 的头部定义。 数据按行分块 每块按照列存储 读取全量数据的操作 性能可能比sequencefile没有明显的优势
数据按行分块 每块按照列存储
列存储 行存储
压缩快,快速列存取 ,效率比rcfile高,是rcfile的改良版本 相对于ORC，Parquet压缩比较低，查询效率较低，不支持update、 insert和ACID.但是Parquet支持Impala查询引擎
CSV文件形式：
create external table xy_dw.dw_idcard_province_map_dim ( id string comment '身份证前六位', city string comment '省市区县', province string comment '省份'
分区表：
CREATE TABLE user ( id string comment ‘ID’, name string comment ‘姓名’ ) PARTITIONED BY (pk_day string);
静态分区操作： Insert overwrite table user partition(pk_day=‘2019-07-29’) select id,name from user1 where pk_day = ‘2019-07-29’;
二进制文件格式
压缩格式
压缩格式 GZIP BZIP2 LZO SNAPPY
压缩后大小 中 小 大 大
压缩速度 中 慢 快 快
是否可分割 否 是 是 是
样本测试
100G text JSON文件
存储格式 数据压缩后大小 存储耗费时间 SQL查询响应速度
ORC
Sequencefile
1.8G
67.0G
535.7s 625.8s
19.63s 184.07s
Parquet
RCfile
Avro
11G
63.8G
66.7G
537.3s
543.48s 544.3s
24.22s
88.5s
281.65s
DDL操作
数据库： create / drop / alter / use
表操作： 临时表：create temporary table … 外部表：create external table …
动态分区操作：
Insert overwrite table user partition(pk_day) select id,name,pk_day from user1 where pk_day = ‘2019-07-29’;
Insert into table user partition(pk_day) select id,name,pk_day from user1 where pk_day = ‘2019-07-29’;
-- 合并文件的大小,默认值为 256000000 set hive.merge.size.per.task=25610001000;
控制map数量
Mapper个数默认计算方式：
输入文件总大小：total_size hdfs 设置的数据块大小：dfs_block_size default_mapper_num = total_size/dfs_block_size
) comment '身份证前六位和省份匹配表' row format delimited fields terminated by ',' lines terminated by '\n' stored as textfile;
注意规范： 1. 根据数仓规范创建表 2. 字段说明 3. 表说明 4. 最终结果表尽量使用外部表
ORC存储格式：
create table orc( … ) row format delimited fields terminated by ‘\t’ stored as orc;
Parquet存储格式：
create table parquet( … ) row format delimited fields terminated by ‘\t’ stored as parquet;
复杂数据类型
Array、Map、Struct、UnionType
存储格式
存储格式 TextFile SequenceFile
RCFile ORCFile Parquet AVRO
存储方式 行存储 行存储
特点 存储空间消耗比较大，并且压缩的text 无法分割和合并 查询的效率最 低,可以直接存储，加载数据的速度最高 存储空间消耗最大,压缩的文件可以分割和合并 查询效率高，需要通过 text文件转化来加载 存储空间最小， 查询的效率最高 ， 需要通过text文件转化来加载， 加载的速度最低。 压缩快 快速列存取。 读记录尽量涉及到的block最少
表创建：
create external table if not exists dw_stg.stu_score( student map<string,string> comment "学生信息", sub_score array<map<string,string>> comment '成绩表' ) comment "学生成绩表” ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe’
DML操作
例子：查询操作
{“student”: {“name”:”小明","age":11,"sex":"M"},"sub_score”:{} {“student”: {“name”:“小刘","age":11,"sex":"M"},"sub_score”:[{"subject":"语文","score":82},{"subject":"数学","score":80},{"subject":"英语","score":80}]}
3.整体流程
Hive优化
1. 表优化 2. 语法参数优化 3. 架构优化
1. 表优化
利用分区表 利用桶表 选择合适的存储格式 选择合适的压缩方式（压缩比例、压缩解压速度、是否支持切割）
2. 语法参数优化 列剪裁 分区剪裁
合并小文件
1）Map输入合并
-- Map端输入、合并文件之后按照block的大小分割（默认） set hive.input.format=bineHiveInputFormat;
5）尽量原子操作
避免一个SQL包含复杂的逻辑，可以使用中间表来完成复杂的逻辑
Group By 优化
1） Map端部分聚合
set hive.map.aggr=true; -- 开启Map端聚合参数设置 set hive.grouby.mapaggr.checkinterval=100000; -- 在Map端进行聚合操作的条目数目
set hive.groupby.skewindata = true; -- 有数据倾斜的时候进行负载均衡（默认是false） 当选项设定为 true 时，生成的查询计划有两个 MapReduce 任务。在第一个 MapReduce 任务中，map 的输出结果会随机分布 到 reduce 中，每个 reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的group by key有可能分发到不同的 reduce 中，从而达到负载均衡的目的；第二个 MapReduce 任务再根据预处理的数据结果按照group by key分布到各个 reduce 中，最 后完成最终的聚合操作。
Join优化
1）优先过滤数据，最大限度减少参与join的数据量
2）小表join大表原则 join 操作的reduce阶段，位于join左边的表内容会被加载进内存
3）多表join使用相同多连接键
4）启用mapjoin mapjoin是将join双方比较小的表直接分发到各个map进程的内存中，在map进程中进行join操作，这样就 不用进行reduce步骤，从而提高了速度
启用压缩
1）Map输出压缩
set press=true; set press.codec=press.SnappyCodec;
2）中间数据压缩
set press.intermediate=true; set pression.codec=press.SnappyCodec; set pression.type=BLOCK;
-- 是否根据输入小表的大小，自动将reduce端的common join 转化为map join，将小表刷入内存中。 set hive.auto.convert.join = true; -- 刷入内存表的大小(字节) set hive.mapjoin.smalltable.filesize = 2500000; -- Map Join所处理的最大的行数。超过此行数，Map Join进程会异常退出 set hive.mapjoin.maxsize=1000000;
stored as textfile;
Lateral view outer explode：
原理解析
1. HQL到MapReduce
2. 分析过程
1）将SQL转为AST 2）语义分析(Semantics Analyze) 3）将QueryBlock转化为OperatorTree 4）优化OperatorTree 5）翻译为MapReduce任务 6）生成最终执行计划
控制reduce数量
参数1：hive.exec.reducers.bytes.per.reducer(默认1G) 参数2：hive.exec.reducers.max(默认为999)
reduce计算公式： N = min(参数2， 总输入数据量/参数1)
直接控制： set mapred.reduce.tasks=10;
Count Distinct优化
修改前：select count(distinct id) from tablename; 修改后：select count(1) from (select distinct id from tablename) tmp; 启动两个job，一个job负责子查询(可以有多个reduce)，另一个job负责count(1)
-- Map端输入，不合并 set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
2）MapReduce输出合并 -- 是否合并Map输出文件, 默认值为真 set hive.merge.mapfiles=true;
-- 是否合并Reduce 端输出文件,默认值为假 set hive.merge.mapredfiles=true;
大数据基础
Hive使用优化
部门： 姓名： 日期：
01 Hive基础
02 原理解析 03 程序优化
案例分析
Hive基础
1. 数据类型 2. 数据存储格式 3. DDL操作 4. DML操作
基础数据类型
数值类型： TINYINT 、SMALLINT、INT 、BIGINT、FLOAT、DOUBLE、DECIMAL 字符串类型： STRING、VARCHAR、CHAR 日期类型： TIMESTAMP、DATE、INTERVAL 其他类型： BOOLEAN、BINARY
手工控制Mapper的数量(必须大于default_mapper_num才生效)：
set mapred.map.tasks=10;
通过修改每个片的大小来控制mapper个数：
可以通过mapred.min.split.size设置每个任务处理的文件的大小，这个大小只有在大于dfs_block_size的时候才会生效 split_size=max(mapred.min.split.size, dfs_block_size) split_num=total_size/split_size compute_map_num = min(split_num, max(default_mapper_num, mapred.map.tasks))
读取需要的列只需要读取每个row group 的头部定义。 数据按行分块 每块按照列存储 读取全量数据的操作 性能可能比sequencefile没有明显的优势
数据按行分块 每块按照列存储
列存储 行存储
压缩快,快速列存取 ,效率比rcfile高,是rcfile的改良版本 相对于ORC，Parquet压缩比较低，查询效率较低，不支持update、 insert和ACID.但是Parquet支持Impala查询引擎
CSV文件形式：
create external table xy_dw.dw_idcard_province_map_dim ( id string comment '身份证前六位', city string comment '省市区县', province string comment '省份'
分区表：
CREATE TABLE user ( id string comment ‘ID’, name string comment ‘姓名’ ) PARTITIONED BY (pk_day string);
静态分区操作： Insert overwrite table user partition(pk_day=‘2019-07-29’) select id,name from user1 where pk_day = ‘2019-07-29’;
二进制文件格式
压缩格式
压缩格式 GZIP BZIP2 LZO SNAPPY
压缩后大小 中 小 大 大
压缩速度 中 慢 快 快
是否可分割 否 是 是 是
样本测试
100G text JSON文件
存储格式 数据压缩后大小 存储耗费时间 SQL查询响应速度
ORC
Sequencefile
1.8G
67.0G
535.7s 625.8s
19.63s 184.07s
Parquet
RCfile
Avro
11G
63.8G
66.7G
537.3s
543.48s 544.3s
24.22s
88.5s
281.65s
DDL操作
数据库： create / drop / alter / use
表操作： 临时表：create temporary table … 外部表：create external table …
动态分区操作：
Insert overwrite table user partition(pk_day) select id,name,pk_day from user1 where pk_day = ‘2019-07-29’;
Insert into table user partition(pk_day) select id,name,pk_day from user1 where pk_day = ‘2019-07-29’;
-- 合并文件的大小,默认值为 256000000 set hive.merge.size.per.task=25610001000;
控制map数量
Mapper个数默认计算方式：
输入文件总大小：total_size hdfs 设置的数据块大小：dfs_block_size default_mapper_num = total_size/dfs_block_size
) comment '身份证前六位和省份匹配表' row format delimited fields terminated by ',' lines terminated by '\n' stored as textfile;
注意规范： 1. 根据数仓规范创建表 2. 字段说明 3. 表说明 4. 最终结果表尽量使用外部表
ORC存储格式：
create table orc( … ) row format delimited fields terminated by ‘\t’ stored as orc;
Parquet存储格式：
create table parquet( … ) row format delimited fields terminated by ‘\t’ stored as parquet;
复杂数据类型
Array、Map、Struct、UnionType
存储格式
存储格式 TextFile SequenceFile
RCFile ORCFile Parquet AVRO
存储方式 行存储 行存储
特点 存储空间消耗比较大，并且压缩的text 无法分割和合并 查询的效率最 低,可以直接存储，加载数据的速度最高 存储空间消耗最大,压缩的文件可以分割和合并 查询效率高，需要通过 text文件转化来加载 存储空间最小， 查询的效率最高 ， 需要通过text文件转化来加载， 加载的速度最低。 压缩快 快速列存取。 读记录尽量涉及到的block最少
表创建：
create external table if not exists dw_stg.stu_score( student map<string,string> comment "学生信息", sub_score array<map<string,string>> comment '成绩表' ) comment "学生成绩表” ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe’
DML操作
例子：查询操作
{“student”: {“name”:”小明","age":11,"sex":"M"},"sub_score”:{} {“student”: {“name”:“小刘","age":11,"sex":"M"},"sub_score”:[{"subject":"语文","score":82},{"subject":"数学","score":80},{"subject":"英语","score":80}]}
3.整体流程
Hive优化
1. 表优化 2. 语法参数优化 3. 架构优化
1. 表优化
利用分区表 利用桶表 选择合适的存储格式 选择合适的压缩方式（压缩比例、压缩解压速度、是否支持切割）
2. 语法参数优化 列剪裁 分区剪裁
合并小文件
1）Map输入合并
-- Map端输入、合并文件之后按照block的大小分割（默认） set hive.input.format=bineHiveInputFormat;
5）尽量原子操作
避免一个SQL包含复杂的逻辑，可以使用中间表来完成复杂的逻辑
Group By 优化
1） Map端部分聚合
set hive.map.aggr=true; -- 开启Map端聚合参数设置 set hive.grouby.mapaggr.checkinterval=100000; -- 在Map端进行聚合操作的条目数目