Hive性能调优之表的优化

小表Join大表

把key相对分散，并且数据量小的表放在join左边，这样可以有效减少内存溢出错误发生的几率。
但是新版的hive已经对大小表join进行了优化，hive会自己检测大小表。

准备三个表，小表10万数据，大表100万数据以及承载join结果的表。

--创建大表
create table bigtable(
    id bigint, 
    time bigint, 
    uid string, 
    keyword string, 
    url_rank int, 
    click_num int, 
    click_url string
)
row format delimited fields terminated by '\t';

--创建小表
create table smalltable(
    id bigint, 
    time bigint, 
    uid string, 
    keyword string, 
    url_rank int, 
    click_num int, 
    click_url string
) 
row format delimited fields terminated by '\t';
 
--创建 join 后表的语句
create table jointable(
    id bigint, 
    time 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 '/usr/hdk/data/bigtable' into table bigtable;

load data local inpath '/usr/hdk/data/smalltable' into table smalltable;

关联查询

insert 
    overwrite 
table 
    jointable
    select 
        b.id, 
        b.time, 
        b.uid, 
        b.keyword, 
        b.url_rank, 
        b.click_num,  
        b.click_url
    from 
        smalltable s
left join 
    bigtable b
on 
    b.id = s.id;

Time taken: 26.644 seconds
大概要27S。

关闭hive默认做的表优化，情况表数据。

set hive.auto.convert.join = false;
truncate table jointable;

再次执行插入。

Time taken: 30.149 seconds
大概要30S，多用了10%左右的时间。

使用大表join小表的话也差不多，因为已经默认优化了就不再讨论。

大表join大表

准备两个大表，一个id自增的100万数据，另一个是id随机为空的100万数据。

--创建自增大表
create table ori(
    id bigint, 
    time bigint, 
    uid string, 
    keyword string, 
    url_rank int, 
    click_num int, 
    click_url string
)
row format delimited fields terminated by '\t';

--创建小表
create table nullidtable(
    id bigint, 
    time bigint, 
    uid string, 
    keyword string, 
    url_rank int, 
    click_num int, 
    click_url string
) 
row format delimited fields terminated by '\t';
 
--创建 join 后表的语句
create table jointable(
    id bigint, 
    time 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 '/usr/hdk/data/ori' into table ori;

load data local inpath '/usr/hdk/data/nullid' into table nullidtable;

insert 
    overwrite 
table 
    jointable 
    select 
        n.* 
    from 
        nullidtable n 
left join 
    ori o 
on 
    n.id = o.id;

可以发现发生了很严重的数据倾斜。

然后过滤空id。

insert 
    overwrite 
table 
    jointable 
    select 
        n.* 
    from 
        (select * from nullidtable where id is not null ) n 
left join 
    ori o 
on 
    n.id = o.id;

数据倾斜就消失了。

除了过滤的方式，还有另外一种方式:

insert 
    overwrite 
table 
    jointable
    select 
        n.* 
    from 
        nullidtable n 
full join 
    ori o 
on 
    case when 
        -- 增加了一个判断，只有n.id不为null，才能与o表进行join
        n.id is null 
    then 
        concat('hive', rand()) 
    else 
        n.id end = o.id;