【数据开发】Hive 多表join中的条件过滤与指定分区

1、条件过滤

left join 中 on 后面加条件 where 和 and 的区别

  • 1、 on条件是在生成临时表时使用的条件,它不管and中的条件是否为真,都会保留左边表中的全部记录。
  • 2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左表的记录)了,条件不为真的就全部过滤掉。

条件加在where可能会导致主表/左表的最终记录数变少的情况发生。

举个例子:
有表a和表b

表a:

id name
1 a
2 b
3 c
4 d

表b:

id name
1 A
2 B

1、on 后面条件用and ,不管条件是否成立 都会把左表的数据全部展示

select * from a left join b on a.id = b.id  and b.name = 'B';

此时会有4条记录

id name id2 name2
1 a
2 b 2 B
3 c
4 d

2、on 后面条件用where ,在left join 生成的表上在做筛选,这时会把 where中不成立的筛选掉

select * from a left join b on a.id = b.id  where b.`name` = 'B';

此时只有1条记录

id name id2 name2
2 b 2 B

2、指定分区

结论:指定分区时优先采用写法3,即 partition 的写法。

1、left join xxx::xxx on 分区字段 = 其他表的分区字段
where 其他表的分区字段 = 2023110700
2、left join xxx::xxx on 分区字段 =2023110700
3、left join xxx::xxx partition(p_2023110700)t
4、left join (select xxx from xxx::xxx where 分区字段 =2023110700) on xxx
5、left join (select xxx from xxx::xxx partition(p_2023110700)t ) on xxx

写法1 是最慢的,因为join两边的表都没有提前过滤分区,所以执行时大概率导致全表扫描。
写法2,3 一般情况下差异不大,写法3是最规范的写法。 写法3必定是指定分区,写法2大概率会优化为指定分区,小概率全表扫描。
写法4,5是Hive的标准写法,但是一般场景下没有子查询的必要,效果上与写法2,3相同。

3、数据倾斜

1、优化大小表join,采用map join的方式优化

使用map join的必要条件:
a. 参与连接的小表的行数,以不超过2万条为宜。
b.连接类型是inner join、right outer join(小表不能是右表)、left outer join(小表不能是左表)、left semi join。

使用方法示例:

     INSERT OVERWRITE TABLE xxx
      SELECT /*+ MAPJOIN(aa) */   
                    aa.pageid, u.age                                 
      FROM page_view pv
                 JOIN user u
                 ON (aa.userid = u.userid);
 
注:当大表存在数据倾斜时,如果小表符合map join的要求,使用map join会极大加速计算。
/*+ MAPJOIN(pv) */   

参考资料

2、避免大小表join
再单独清洗一张dwd,设定保留分区的个数,构造一张特定大小的总表,进行全表扫描再过滤字段。

相关推荐

  1. 数据库开发设计过程问题分析总结

    2023-12-09 06:26:06       35 阅读
  2. 间隔分区导出指定分区数据

    2023-12-09 06:26:06       23 阅读

最近更新

  1. TCP协议是安全的吗?

    2023-12-09 06:26:06       18 阅读
  2. 阿里云服务器执行yum,一直下载docker-ce-stable失败

    2023-12-09 06:26:06       19 阅读
  3. 【Python教程】压缩PDF文件大小

    2023-12-09 06:26:06       18 阅读
  4. 通过文章id递归查询所有评论(xml)

    2023-12-09 06:26:06       20 阅读

热门阅读

  1. ssh框架原理及流程

    2023-12-09 06:26:06       38 阅读
  2. G1 GC基本逻辑

    2023-12-09 06:26:06       27 阅读
  3. LeetCode738. Monotone Increasing Digits

    2023-12-09 06:26:06       28 阅读
  4. ES6中新增的基本数据类型----symbol

    2023-12-09 06:26:06       29 阅读
  5. postgresql数据库配置主从并配置ssl加密

    2023-12-09 06:26:06       42 阅读
  6. markdown快捷键

    2023-12-09 06:26:06       35 阅读
  7. Netty多路复用机制select、poll 和 epoll的区别

    2023-12-09 06:26:06       33 阅读
  8. CentOS常用基础命令大全(linux命令)2

    2023-12-09 06:26:06       28 阅读