hive join有几种 hive in joinsource

转载

风之谷启航 2023-07-12 18:45:24

文章标签 hive join有几种大数据 hive 数据库数据 文章分类 Hive 大数据

Hive性能调优（三）

第7章 Hive数据处理模式

过滤模式
聚合模式
连接模式

第8章 YARN日志

ResourceManager Web UI 界面
JobHistory Web UI 界面

第9章数据存储

数据格式
数据归档

@第10章发现并优化问题

@监控普通表存储的文件的平均大小
监控大表不分区的表
@监控分区数据不均匀的表
@查询表字段的空值率
监控当前集群状态

@定位性能瓶颈

HiveServer2 WebUI 排除大数据组件的问题
Job OverView 排查长时间等待调度
Map任务读取小任务
数据倾斜
缓慢的Shuffle

特殊语句

第7章 Hive数据处理模式

过滤模式

普通的where的在map端过滤，map端的任务在执行时尽可能将计算逻辑发送到数据所在的机器中执行，多机同时过滤。对一个作业应尽量将其放在前面进行数据过滤
having子句过滤发生在数据聚合后，MR引擎中在Reduce阶段过滤，在Reduce 和Group by操作之后
distinct 在Reduce阶段，和用group by的执行计划类似，分组聚合的方式不是Hive去重的唯一方式，有时还会用到Hash表
multi-group-by-insert语法，表在读取一次后，可以被多个查询语句使用

from student_tb_txt
insert into table student_stat partition(tp)
select s_age,max(s_birth) stat, 'max' tp
group by s_age
insert into table student_stat partition(tp)
select s_age,min(s_birth) stat, 'min' tp
group by s_age

分区列筛选，能够在Map之前进行数据过滤，得益于分区表存储格式——分区表|分区列(part = ?)
分桶列筛选，能够对数据重新组织，快速过滤不需要的文件，分区是对目录的过滤**，分桶是对文件的过滤**，记录所存储的桶 = mod(hash(分桶列的值)，16) 把所有文件按照hash值分到16个桶
在列过滤中，ORC/Parquet格式中存储了文件定义的Schema，可以直接读取表所在的列，比一般的格式先取整行数据，再通过列的偏移量得到对应的列值更快。

聚合模式

distinct模式，在map端开启聚合，会在Map中进行数据局部聚合，再在Reduce中开启全局聚合
**COUNT(*)和COUNT(1)会得到包含NULL的数据行数，在数据统计时，不会读取表中的数据，只是使用到HDFS文件中每一行的行偏移量（**HDFS添加的），
而COUNT(<列名>)会得到NULL之外的数据行数。针对列的计数，涉及字段的筛选和数据序列化和反序列化
在ORC文件中，这三者相差不多
可计算中间结果的聚合模式，sum、max、min、avg等在本地处理阶段被聚合成少量数据，之后再做计算可以减少网络和下游节点处理的数据量
不可计算中间结果的聚合模式，collect_list collect_set，和前面的类似，不同的是Map阶段Reduce Output Operator 中 value输出的是个集合数组

连接模式

Repartition连接，发生在shuffle和Reduce阶段，Map读取两个表的数据，将按照连接条件发往相同的Reduce，在Reduce中计算合并
Replication连接（map join），发生在Map阶段，减少从HDFS中读取表的次数，在操作时将一个表的数据复制到各个Map任务所在的节点并存储在缓存中，适用于有小表的情况
map join

先启动一个作业读取小表的数据，在内存中构建哈希表，把哈希表写入本地磁盘，然后把哈希表上传到HDFS并添加到分布式缓存中。
再启动一个任务读取B表的数据，在连接时map会获取缓存中的数据并存入哈希表中，B表会和哈希表的数据匹配
时间复杂度是O(1)
开启hint命令 set hive.ignore.mapjoin.hint = false /*+mapjoin(b)*/ hint操作
推荐：

hive.auto.convert.join = true 是否根据文件大小把repartition 转为 replication；
hive.smalltable.filesize = 25000000 byte 小表数据小与阈值时，将转换和上面的一起用

使用桶的Map连接要保证连接的两张表的分桶数之间是倍数关系
倾斜连接

set hive.optimize.skewjoin = true 是否优化有倾斜键的表连接
set hive.skewjoin.key = 100000 相同键的行数多于指定值认为该键是倾斜连接键

-- 可以通过创建表时制定数据倾斜键，将制定的数据键分割成单独的数据文件或目录
create table a(s_no string,s_score bigint)
skewed by (s_score) on (99,97)
stored as directories

表连接与基于成本的优化器

一开始 Hive使用的是基于规则的优化器 Rule Based Optimizer
后来使用Calcite 是一个开源的基于成本优化器（CBO），根据Hive收集到表、列的统计信息，估算成本
CBO优点

表连接的顺序优化，不需要特别制定大小表的顺序，会根据收到的情况，自动算出
Bushy Tee连接的执行，可以估算出每个表连接的组合，两两结合生成中间结果，未引入的时候所有表连接都被表示成左深树，内部节点都至少有一个叶子作为子节点
简化表的连接，多表连接时，识别并抽取相同的连接谓词，构造一个隐式的连接谓词作为替换

第8章 YARN日志

ResourceManager Web UI 界面

一般来说端口8088
Scheduler 中比较重要的 Application Queues
主要的性能瓶颈定位

JobHistory Web UI 界面

一般来说端口 19888

查看集群状态

Cluster中的About | metrics | nodes 链接

第9章数据存储

数据格式

Text File format : 默认格式，数据不做压缩，磁盘开销大，数据解析开销大。在反序列化过程中，必须逐个字符判断是不是分隔符和行结束符，因此反序列化开销会比SequenceFile高几十倍
Sequence File format

SequenceFile 是 Hadoop API 提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点
SequenceFile 支持三种压缩选择：NONE, RECORD, BLOCK。 Record 压缩率低，一般建议使用 BLOCK 压缩。
面向行：在一起存储的同一行数据是连续存储

ORCFile : 对RCFile做了一些优化，支持各种复杂的数据类型性能比较好

将数据先按行进行切分，一个行组内包含若干行，每一行组再按列进行存储，ORC 将行的集合存储在一个文件中，并且集合内的行数据将以列式存储。采用列式格式，压缩非常容易，从而降低了大量的存储成本。
ORC 会创建索引（文件级、条带级、行组级（在stripe中每10000行构成一个行组）），当查询的时候会很快。当查询时，会查询特定列而不是查询整行，因为记录是以列式存储的。
由三部分构成：

条带stripe：ORC文件存储数据的地方
文件脚注：包含文件stripe的列表包括行数，数类型，最大值等
postscript：压缩参数和压缩大小

ORC文件也是以二进制方式存储的，所以是不可以直接读取

RCfile format : RCFILE 是一种行列存储相结合的存储方式，数据按行分块，每块按列存储。首先，其将数据按行分块，保证同一个 record 在一个块上，避免读一个记录需要读取多个 block。其次，块数据列式存储，有利于数据压缩和快速的列存取。RCFile 目前没有性能优势，只有存储上能省 10% 的空间。
Parquet :

列式数据存储。查询比较快
Parquet支持嵌套的数据模型，每一个数据模型的schema包含多个字段，每一个字段有三个属性：重复次数、数据类型和字段名
二进制方式存储的，是不可以直接读取和修改的

AVRO : avro Schema 数据序列化。

数据归档

启动数据归档
set hive.archive.enable = true; set hive.archive.har.parentdir.settable=true;
归档后的最大文件大小
set har.partfile.size = 109951162776
对分区执行归档的命令
alter table tablename archive partition(partition_col = partition_val)
还原成普通分区
alter table tablename unarchive partition(partition_col = partition_val)

大量的小文件可以通过 Hadoop归档 （Hadoop archive）把文件归并成几个较大的文件
归并后的分区会先创建一个data.har 目录，包含索引( _index 和 _masterindex )和数据(part-*)
归档后不会对数据进行压缩

@第10章发现并优化问题

收集表的元数据： analyze table 表名 compute statistics (noncan)
收集字段的元数据： analyze table 表名 compute statistics for columns
收集分区的元数据： analyze table 表名 partition (分区列 = 分区值) compute statistics
收集所有分区的列的元数据： analyze table 表名 partition(分区列) compute statistics for columns

@监控普通表存储的文件的平均大小

--整体逻辑通过 DBS找到对应库下面的表TBLS
--再通过TBLS找到每个表对应的表属性，取得totalSize和numFiles两个属性
select tbl_ name, avgfilesize 'fileSize (MB)'
from (
	select tp.totalSize/(1024*1024)/numFiles avgfilesize, TBL_NAME
	from DBS d
	inner join TBLS t on d.DB_ID = t.DB_ID -- DBS 的主键DB_ID*
	left join 
  (
		select TBL_ID,
		max (case PARAM_KEY when 'numFiles'then PARAM_VALUE else 0 end) numFiles, --每个表存储的文件个数
		max (case PARAM_KEY when 'totalSize'then PARAM_VALUE else 0 end) totalSize --文件存储的大小
		from TABLE_PARAMS --TABLE PARAMS 记录的表属性
    GROUP BY TBL_ID
  )tp
  on t.TBL_ID = tp.TBL_ID
  where d.NAME = '数据库名' and tp.numFiles is not null and tp.numFiles > 0
 )a
 where avgfilesize > hdfs的文件块大小*2

监控大表不分区的表

/*监控大表不分区的表*/
select t.TBL_NAME '表名',d.'NAME' '库名',totalsize /1024/1024/1024 '文件大小(GB)'
from DBS d
inner join TBLS t on d.DB ID= t.DB ID
inner join(
  select TBL_ID,
  max (case PARAM_KEY when 'totalSize' then PARAM_VALUE else 0 end) totalSize
  from TABLE_PARAMS
	GROUP BY TBL_ID
)tp 
on t.TBL_ID  = tp.TBL_ID
left join(
	select distinct TBL_ID from PARTITIONS -- 分区数据
)part
on t.TBL_ID = part.TBL_ID
where d.NAME = '数据库名' and part.TBL_ID is null and totalSize/1024/1024/1024 > 30

@监控分区数据不均匀的表

select TBL_NAME, max (totalSize) , min(totalSize) , avg (totalSize)
from
(
  select pp.totalSize, TBL_NAME, part.PART_NAME
  from DBS d
  inner join TBLS t on d.DB_ID=t.DB_ID
  inner join PARTITIONS part on t.TBL_ID=part.TBL_ID
  inner join 
  (
    select PART_ID,
    max (case PARAM_KEY when 'totalSize ' then PARAM_VALUE else 0 end)/1024/1024 totalSize
    from PARTITION_PARAMS -- 分区属性
    GROUP BY PART_ID
  ) pp on part.PART ID=pp.PART ID
  where d.NAME= 'default'
  and pp.totalSize is not null and pp. totalSize > 0
)
a group by TBL_NAME
having max (totalSize) > avg (totalSize)*5

@查询表字段的空值率

-- 表字段的空值率，以及重复字段所占的比例
select t.TBL_NAME '表名', d.NAME '库名', tcs.COLUMN_NAME '字段名',
				NUM_NULLS*1.0/tp.numRows '空值率',
				1-NUM_DISTINCTS*1.0/t.numRows '重复字段占比'
from DBS d
inner join TBLS t on d.DB_ ID = t.DB_ID
inner join TAB_COL_STATS tcs on t.TBL_ID=tcs.TBL_ID
left join
(
	select TBL_ID,
  max (case PARAM_KEY when 'numRows' then PARAM_VALUE else 0 end) numsRows
  from TABLE_PARAMS
	GROUP BY TBL_ID
)tp
on t.TBL_ID = tp.TBL_ID
where d.NAME = '数据库名'  and t.TBL_NAME = '表名'

监控当前集群状态

获取集群的状态信息
get http:// /ws/v1/cluster/info
获取集群任务的状态信息
get http:// /ws/v1/cluster/metrics
获取提交到集群所有任务的运行信息
get http:// /ws/v1/cluster/apps
get http:// /ws/v1/cluster/apps?user=hue
可使用参数特定查询：states(处于特定状态)、finalStatus(处于final状态)、user(用户)、 queue(指定队列) startedTimeBegin(从指定时间开始运行的任务)、 startedTimeEnd 、 finishedTime、任务ID号
获取当前资源调度的分配信息
get http:// /ws/v1/cluster/scheduler

# 用python获取
import requests
import json
clustr_status_url = 'http://<rm http address:port> /ws/v1/cluster'
res = requests.get(clustr_status_url)
print res.content
ct = json.loads(res.content)
print ct.get('clusterInfo').get('state')

@定位性能瓶颈

HiveServer2 WebUI 排除大数据组件的问题

提供的作业分析工具 http://主机名:10002/hiveserver2.jsp
端口默认：10002
Drill down 中 Performance Logging 中

获取元数据所用的时间
任务提交到集群后运行的消耗

Job OverView 排查长时间等待调度

可以查看作业是否存在长时等待
submitted 和 started 启动间隔，间隔长需要查看scheduler的信息 ，队列资源是否过载

Map任务读取小任务

如果一个作业读取的文件多数是小文件，没有配置合并读取小文件，意味着每读取一个文件都要启动一个Java进程
追踪过程：FINISHED的作业 --> Tracking URL : History --> Map --> name --> status
xxxxxxxx/part-001-xxxxxx:0+214149 表示读取了214149bytes的文件内容

每个Map最小输入大小(这个值决定了合并后文件的数量)
set mapred.min.split.size=256000000;

数据倾斜

开启Map端聚合参数设置
set hive.map.aggr=true
进行负载均衡
set hive.groupby.skewindata = true
调整reduce所执行的内存大小，使用
set mapreduce.reduce.memory.mb

单个节点任务所处理的数据量远大于同类型任务，可能的原因
任务读取大文件

读取压缩的不可分割的大文件

任务需要处理大量相同键的数据

数据含有大量无意义的数据，空值NULL等
还有倾斜数据在进行聚合计算时无法聚合中间结果，大量数据要经过shuffle处理，引起数据倾斜，
数据在计算时做多维数据集合，导致维度膨胀引起的数据倾斜，产出的数据膨胀，导致内存溢出
两表进行Join，含有大量相同的倾斜数据键

不可拆分大文件引起的数据倾斜

对文件使用GZIP压缩等不支持文件分割操作的压缩方式，压缩文件只会被一个任务所读取。
解决方法：数据压缩时采用bzip2和Zip等支持文件分割的压缩算法

聚合计算时无法聚合中间结果

如使用了collect_list聚合函数，开启hive.groupby.skewindata也没用（第一个作业均分map数据，预聚合；）
collect_list这类要求全量操作所有数据的中间结果来说，起不到作用
解决方法：调整reduce所执行的内存大小，使用mapreduce.reduce.memory.mb

两表进行Join，含有大量相同的倾斜数据键

解决方法：第一个作业处理没有倾斜的数据；第二个作业将倾斜的数据存到分布式缓存中，分发到各个Map任务所在节点。在Map阶段完成join，也就是Mapjoin

完成的任务 --> Reduce任务列 --> Task --> 框架计数器
可以查看耗时短、耗时长、平均耗时可判断是否存在数据倾斜

缓慢的Shuffle

涉及磁盘的读写和网络传输，容易产生性能瓶颈
JobHistory --> JobID --> Reduce --> Elapsed Time Shuffle

特殊语句

左半连接：left semi join
mapjoin 适用小表和大表： map-side join
两个都是桶表且存在桶数倍数关系： bucketed map join
两个都是桶表且存在桶数倍数关系且基于有序数据合并: bucketed sorted merge join
倾斜连接: skew join
获取表大部分信息： show table extended like 表名

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。