Hive性能优化之表设计优化2

简介: Hive性能优化之表设计优化2

2 分桶表

2.1 Hive中Join的问题

表的Join是数据分析处理过程中必不可少的操作,Hive同样支持Join的语法,Hive Join的底层还是通过MapReduce来实现的,但是Hive实现Join时面临一个问题:如果有两张非常大的表要进行

Join,两张表的数据量都很大,Hive底层通过MapReduce实现时,无法使用MapJoin提高Join的性

能,只能走默认的ReduceJoin,而ReduceJoin必须经过Shuffle过程,相对性能比较差,而且容易

产生数据倾斜,如何解决这个问题?

2.2 分桶表设计思想

针对以上的问题,Hive中提供了另外一种表的结构——分桶表结构。分桶表的设计有别于分区

表的设计,分区表是将数据划分不同的目录进行存储,而分桶表是将数据划分不同的文件进行存储。

分桶表的设计是按照一定的规则【通过MapReduce中的多个Reduce来实现】将数据划分到不同的

文件中进行存储,构建分桶表。

如果有两张表按照相同的划分规则【按照Join的关联字段】将各自的数据进行划分,在Join时,

就可以实现Bucket与Bucket的Join,避免不必要的比较。

例如:当前有两张表,订单表有1000万条,用户表有10万条,两张表的关联字段是userid,现

在要实现两张表的Join。

我们将订单表按照userid划分为3个桶,1000万条数据按照userid的hash取余存储在对应的

Bucket中。

同理,我们再将用户表按照相同的规则,存储在3个桶中。

在Join时,只需要将两张表的Bucket0与Bucket0进行Join,Bucket1与Bucket1进行Join,Bucket2

与Bucket2进行Join即可,不用让所有的数据挨个比较,降低了比较次数,提高了Join的性能。

2.3 分桶表测试

当前有两份较大的数据文件,emp员工数据和dept部门数据,现在要基于Hive实现两张表的

Join,我们可以通过分桶实现分桶Join提高性能。

⚫ 构建普通emp表

create database if not exists db_emp;
use db_emp;
--创建普通表
create table tb_emp01(
empno string,
ename string,
job string,
managerid string,
hiredate string,
salary double,
jiangjin double,
deptno string
) row format delimited fields terminated by ' '\t' ';
--加载数据
load data local inpath '/export/data/emp01.txt' into table tb_emp01;

⚫ 构建分桶emp表

use db_emp;
-- 创建分桶表
create table tb_emp02(
empno string,
ename string,
job string,
managerid string,
hiredate string,
salary double,
jiangjin double,
deptno string
)
clustered by(deptno) sorted by (deptno asc) into 3 buckets
row format delimited fields terminated by ' '\t' ';
-- 写入分桶表
insert overwrite table tb_emp02
select * from tb_emp01;

⚫ 构建普通dept表

use db_emp;
-- 创建部门表
create table tb_dept01(
deptno string,
dname string,
loc string
)
row format delimited fields terminated by ',';
-- 加载数据
load data local inpath '/export/data/dept01.txt' into table tb_dept01;

⚫ 构建分桶dept表

use db_emp;
--创建分桶表
create table tb_dept02(
deptno string,
dname string,
loc string
)
clustered by(deptno) sorted by (deptno asc) into 3 buckets
row format delimited fields terminated by ',';
--写入分桶表
insert overwrite table tb_dept02
select * from tb_dept01;

⚫ 普通的Join执行计划

explain
select
a.empno,
a.ename,
a.salary,
b.deptno,
b.dname
from tb_emp01 a join tb_dept01 b on a.deptno = b.deptno;

⚫ 分桶的Join执行计划

--开启分桶 SMB join
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join= true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
--查看执行计划
explain
select
a.empno,
a.ename,
a.salary,
b.deptno,
b.dname
from tb_emp02 a join tb_dept02 b on a.deptno = b.deptno;

3 索引设计

3.1 Hive中的索引

在传统的关系型数据库例如MySQL、Oracle等数据库中,为了提高数据的查询效率,可以为表

中的字段单独构建索引,查询时,可以基于字段的索引快速的实现查询、过滤等操作。

Hive中也同样提供了索引的设计,允许用户为字段构建索引,提高数据的查询效率。但是Hive的索引与关系型数据库中的索引并不相同,比如,Hive不支持主键或者外键。Hive索引可以建立在

表中的某些列上,以提升一些操作的效率,例如减少MapReduce任务中需要读取的数据块的数量。

在可以预见到分区数据非常庞大的情况下,分桶和索引常常是优于分区的。而分桶由于SMBJoin对关联键要求严格,所以并不是总能生效。官方文档:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDD L-Create/Drop/AlterIndex

注意:官方明确表示,索引功能支持是从Hive0.7版本开始,到Hive3.0不再支持。

3.2 索引的原理及使用

Hive中索引的基本原理:当为某张表的某个字段创建索引时,Hive中会自动创建一张索引表,

该表记录了该字段的每个值与数据实际物理位置之间的关系,例如数据所在的HDFS文件地址,以

及所在文件中偏移量offset等信息。

Hive的索引目的是提高Hive表指定列的查询速度。没有索引时,类似’WHERE tab1.col1 = 10’ 的

查询,Hive会加载整张表或分区,然后处理所有的rows,但是如果在字段col1上面存在索引时,那

么只会加载和处理文件的一部分。

构建数据时,Hive会根据索引字段的值构建索引信息,将索引信息存储在索引表中

查询数据时,Hive会根据索引字段查询索引表,根据索引表的位置信息读取对应的文件数据。

下面我们来实现索引的构建,例如:

当前有一张分区表tb_login_part,默认查询数据时,是没有索引的,当查询登陆日期时可以通

过分区过滤来提高效率,但是如果想按照用户ID进行查询,就无法使用分区进行过滤,只能全表扫

描数据。

如果我们需要经常按照用户ID查询,那么性能就会相对较差,我们可以基于用户ID构建索引来

加快查询效率。

⚫ 可以使用Hive3.0以下版本测试

⚫ 创建索引

--为表中的 userid 构建索引
create index idx_user_id_login on table tb_login_part(userid)
--索引类型为 Compact,Hive 支持 Compact 和 Bitmap 类型,存储的索引内容不同
as 'COMPACT'
--延迟构建索引
with deferred rebuild;

⚫ 构建索引

alter index idx_user_id_login ON tb_login_part rebuild;

通过运行一个MapReduce程序来构建索引

⚫ 查看索引

desc default__tb_login_part_idx_user_id_login__;

select * from default__tb_login_part_idx_user_id_login__;

索引中记录了每个用户ID对应的文件以及在文件中的位置

⚫ 删除索引

DROP INDEX idx_user_id_login ON tb_login_part;

3.3 索引的问题与应用

⚫ 问题

Hive构建索引的过程是通过一个MapReduce程序来实现的,这就导致了Hive的一个

问题,每次Hive中原始数据表的数据发生更新时,索引表不会自动更新,必须手动执行

一个Alter index命令来实现通过MapReduce更新索引表,导致整体性能较差,维护相对繁琐。例如:

◼ 表中数据发生新增或者修改

◼ 索引表没有更新

◼ 手动更新索引表

alter index idx_user_id_login ON tb_login_part rebuild;

⚫ 应用

由于Hive的索引设计过于繁琐,所以从Hive3.0版本开始,取消了对Hive Index的支

持及使用,不过如果使用的是Hive1.x或者Hive2.x在特定的场景下依旧可以使用Hive

Index来提高性能。

实际工作场景中,一般不推荐使用Hive Index,推荐使用ORC文件格式中的索引来代替Hive Index提高查询性能。

目录
相关文章
|
5月前
|
SQL 缓存 关系型数据库
ClickHouse(19)ClickHouse集成Hive表引擎详细解析
Hive引擎允许对HDFS Hive表执行 `SELECT` 查询。目前它支持如下输入格式: -文本:只支持简单的标量列类型,除了 `Binary` - ORC:支持简单的标量列类型,除了`char`; 只支持 `array` 这样的复杂类型 - Parquet:支持所有简单标量列类型;只支持 `array` 这样的复杂类型
200 1
|
6月前
|
SQL 分布式计算 资源调度
Hive 优化总结
Hive优化主要涉及HDFS和MapReduce的使用。问题包括数据倾斜、操作过多和不当使用。识别倾斜可通过检查分区文件大小或执行聚合抽样。解决方案包括整体优化模型设计,如星型、雪花模型,合理分区和分桶,以及压缩。内存管理需调整mapred和yarn参数。倾斜数据处理通过选择均衡连接键、使用map join和combiner。控制Mapper和Reducer数量以避免小文件和资源浪费。减少数据规模可调整存储格式和压缩,动态或静态分区管理,以及优化CBO和执行引擎设置。其他策略包括JVM重用、本地化运算和LLAP缓存。
118 4
Hive 优化总结
|
5月前
|
SQL 资源调度 数据库连接
Hive怎么调整优化Tez引擎的查询?在Tez上优化Hive查询的指南
在Tez上优化Hive查询,包括配置参数调整、理解并行化机制以及容器管理。关键步骤包括YARN调度器配置、安全阀设置、识别性能瓶颈(如mapper/reducer任务和连接操作),理解Tez如何动态调整mapper和reducer数量。例如,`tez.grouping.max-size` 影响mapper数量,`hive.exec.reducers.bytes.per.reducer` 控制reducer数量。调整并发和容器复用参数如`hive.server2.tez.sessions.per.default.queue` 和 `tez.am.container.reuse.enabled`
338 0
|
6月前
|
SQL 存储 大数据
Hive的查询、数据加载和交换、聚合、排序、优化
Hive的查询、数据加载和交换、聚合、排序、优化
127 2
|
6月前
|
SQL 分布式计算 资源调度
一文看懂 Hive 优化大全(参数配置、语法优化)
以下是对提供的内容的摘要,总长度为240个字符: 在Hadoop集群中,服务器环境包括3台机器,分别运行不同的服务,如NodeManager、DataNode、NameNode等。集群组件版本包括jdk 1.8、mysql 5.7、hadoop 3.1.3和hive 3.1.2。文章讨论了YARN的配置优化,如`yarn.nodemanager.resource.memory-mb`、`yarn.nodemanager.vmem-check-enabled`和`hive.map.aggr`等参数,以及Map-Side聚合优化、Map Join和Bucket Map Join。
312 0
|
6月前
|
SQL 关系型数据库 MySQL
Hive 表注释乱码解决
Hive元数据在MySQL默认使用`latin1`字符集导致注释乱码。可通过修改MySQL配置文件`/etc/my.cnf`,在`[mysqld]`和末尾添加`character-set-server=utf8`等设置,重启MySQL。然后在Hive数据库中调整表字段、分区字段、索引注释的字符集。注意,这仅对新表生效。测试创建带注释的Hive表,问题解决。
84 0
|
6月前
|
SQL 存储 分布式计算
【Hive】Hive优化有哪些?
【4月更文挑战第16天】【Hive】Hive优化有哪些?
|
6月前
|
SQL HIVE
Hive表删除数据不支持使用Delete From...
Hive表删除数据不支持使用Delete From...
242 0
|
6月前
|
SQL 存储 分布式计算
【Hive】hive内部表和外部表的区别
【4月更文挑战第14天】【Hive】hive内部表和外部表的区别
|
6月前
|
SQL 数据库 HIVE
Hive【基础知识 05】常用DDL操作(数据库操作+创建表+修改表+清空删除表+其他命令)
【4月更文挑战第8天】Hive【基础知识 05】常用DDL操作(数据库操作+创建表+修改表+清空删除表+其他命令)
92 0