Hive大表join大表如何调优

在Hive中，优化器会根据统计信息决定是将大表放在前面（Join的左边）还是小表放在前面。通常，优化器会选择数据量较小的表作为驱动表（小表作为左边），因为这样可以减少内存消耗并提高效率。

但是，如果你有特定的需求，比如你知道大部分数据能快速过滤掉，希望减少任务的执行时间，那么你可以强制指定某个表作为小表。在Hive中，可以使用/*+ MAPJOIN(table_name) */ 注释来强制将一个大表作为小表处理。
例如，如果你想要将big_table作为小表：

SELECT /*+ MAPJOIN(big_table) */
  a.column1, a.column2, b.column1, b.column2
FROM
  small_table a
JOIN
  big_table b
ON
  a.common_column = b.common_column;

一、调优思路

1、SQL优化

1.1 大小表join

1、mapjoin,小表使用mapjoin,或者强制hint
2、将大表放后头，原因：Hive假定查询中最后的一个表是大表。它会将其它表缓存起来，然后扫描最后那个表。因此通常需要将小表放前面，或者标记哪张表是大表：/*streamtable(table_name) */
3、过滤无效值：空值、不使用的字段等。
4、不能过滤的空值，将空值转化为随机数避免数据倾斜。

1.2 大大表join

1）创建第二张大表
create table bigtable2(
    id bigint,
    t bigint,
    uid string,
    keyword string,
    url_rank int,
    click_num int,
    click_url string)
row format delimited fields terminated by '\t';
load data local inpath '/opt/module/data/bigtable' into table bigtable2;

2）测试大表直接JOIN
insert overwrite table jointable
select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from bigtable a
join bigtable2 b
on a.id = b.id;
测试结果：Time taken: 72.289 seconds
insert overwrite table jointable
select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from bigtable a
join bigtable2 b
on a.id = b.id;

3）创建分桶表1
create table bigtable_buck1(
    id bigint,
    t bigint,
    uid string,
    keyword string,
    url_rank int,
    click_num int,
    click_url string)
clustered by(id)
sorted by(id)
into 6 buckets
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/data/bigtable' into table bigtable_buck1;

4）创建分桶表2，分桶数和第一张表的分桶数为倍数关系
create table bigtable_buck2(
    id bigint,
    t bigint,
    uid string,
    keyword string,
    url_rank int,
    click_num int,
    click_url string)
clustered by(id)
sorted by(id)
into 6 buckets
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/data/bigtable' into table bigtable_buck2;

5）设置参数
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

6）测试 Time taken: 34.685 seconds
insert overwrite table jointable
select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from bigtable_buck1 s
join bigtable_buck2 b
on b.id = s.id;

1、使用相同的连接键

• 当对3个或者更多个表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。

2、过滤无效、未使用的数据：减少每个阶段的数据量，对于分区表要加分区，同时只选择需要使用到的字段。

加随机数打散
1）空值0值 或 关联不上的，用随机数
from a join b
on <