Hive 调优 分区裁剪、列裁剪

使用分区剪裁、列剪裁

在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。

在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤，比如：

环境准备：

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 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';

load data local inpath '/home/admin/softwares/data/加递增id的原始数据/ori' into table ori;

load data local inpath '/home/admin/softwares/data/100万条大表数据（id除以10取整）/bigtable' into table bigtable;

先关联再Where：

SELECT a.id
FROM bigtable a
LEFT JOIN ori b ON a.id = b.id
WHERE b.id <= 10;

正确的写法是写在ON后面：先Where再关联

SELECT a.id
FROM ori a
LEFT JOIN bigtable b ON (b.id <= 10 AND a.id = b.id);

或者直接写成子查询：

SELECT a.id
FROM bigtable a
RIGHT JOIN (SELECT id
FROM ori
WHERE id <= 10
) b ON a.id = b.id;