[ Hive ] hive优化一 : 表设计优化_hive数据表设计

表设计优化

1 普通表

1.1 Hive查询基本原理

1. 创建数据库(chapter_6)

2. 创建表(tb_login),hdfs上自动在数仓的数据库目录下创建表目录

   

3. 关联数据:使用load命令将数据加载到表中,数据会被自动放入hdfs中对应表目录下

   

4. 当执行查询计划时,Hive使用表的最后一级目录作为底层处理数据的输入

   在元数据库metastore中表现为:

   1)在TBLS表中根据表明查得SD_ID

   2)SDS元数据表中的记录查询SD_ID对应的HDFS的表目录位置,即底层处理数据的输入

5. 使用explain+query命令查看执行计划依赖的数据

   

1.2 结构问题

1. 大量不必要的数据被程序加载，在程序中被过滤，导致大量不必要的计算资源的浪费
2. 大量的磁盘和网络的IO的损耗。
3. 考虑到以上因素,使用分区裁剪思想

2 分区表

2.1 设计思想

1. 将数据按照查询的条件【一般都以时间】进行划分分区存储，将不同分区的数据单独使用一个HDFS目录来进行存储
2. 当底层实现计算时，根据查询的条件，只读取对应分区的数据作为输入，减少不必要的数据加载，提高程序的性能

2.2 查询基本原理

1. 按登录日期(logindate)分区创建分区表(tb_login_part)

2. 将登录数据写入分区表(insert+select),hdfs中自动在表目录下创建每个分区的目录

   

3. 按分区字段(logindate)过滤查询

   在元数据库中表现为:

   1)元数据中记录该表为分区表并且查询过滤条件为分区字段，所以找到该分区对应的HDFS目录

   2)加载对应分区的目录作为计算程序的输入

4. 查看执行计划

   

3 分桶表

3.1 join的问题

如果有两张非常大的表要进行Join，两张表的数据量都很大，Hive底层通过MapReduce实现时，无法使用MapJoin提高Join的性能，只能走默认的ReduceJoin，而ReduceJoin必须经过Shuffle过程，相对性能比较差，而且容易产生数据倾斜

3.2 设计思想

1. 分桶表是将数据划分不同的文件进行存储

2. 底层是多个reducer将数据划分到不同分区(mr中的分区)生成多个文件

3. 如果有两张表按照相同的划分规则【按照Join的关联字段】将各自的数据进行划分，在Join时，就可以实现Bucket与Bucket的Join，避免不必要的比较

4. 例如:

   1)有两张表，订单表有1000万条，用户表有10万条，两张表的关联字段是userid，现在要实现两张表的Join。

   2)订单表和用户表都按userid的hash取模来分桶

   

   

   3)join时,只需要将两张表的Bucket0与Bucket0进行Join，Bucket1与Bucket1进行Join，Bucket2与Bucket2进行Join即可

   4)不用让所有的数据挨个比较，降低了比较次数，提高了Join的性能

   

3.3 分桶表join

1. 普通表join执行计划(inner join)

   

2. 分桶的Join执行计划(开启分桶SMB join)

   

4 索引

1. 索引功能支持是从Hive0.7版本开始，到Hive3.0不再支持
2. 索引表不会自动更新,必须手动执行更新
3. 整体性能较差，维护相对繁琐
4. 实际工作场景中，一般不推荐使用Hive Index，推荐使用ORC文件格式中的索引或者物化视图来代替Hive Index提高查询性能