?Hive sql和spark sql语法
Hive sql和spark sql都支持相同的sql语法,基本所有支持sql的数据库或sql引擎,支持的语法都基本相同。数据库领域得join者得天下。
- inner join
- left join
- right join
- full outer join
- left semi join
?Hive sql join原理
- map join(内存关联,无reduce)
适用场景:大表join小表
开关参数:hive.auto.convert.join控制,hive在0.7版本以后默认为true,就是默认开启,不必sql中hint
表大小限制:hive.mapjoin.smalltable.filesize,默认25M,小于等于25M,会启动mapjoin
流程:
1、小表加载DistributeCache阶段
MR local task读取小表内容—?转换成HashTable存为本地文件(key为join的字段)—?加载到DistributeCache里为hashTable
2、hash join阶段
大表启动map读取每条数据,并拿key(join的字段)去DistributeCache查询,并输出关联结果
整个过程无需reduce过程,map搞定。
- common join(正常join,有reduce)
适用场景:大表join大表
开关参数:无(始终开启)
表大小限制:无限制
流程:
1、map阶段
读取表内容,输出key-value类型数据,key为join字段,value为关心字段(select字段+where条件字段)+tag(标识来源表);
2、shuffle阶段
按map阶段的key进行hash,分发到reduce,join两表相同的key分发到同一个reduce;
3、reduce阶段
按key相等+tag不同,条件完成join,输出结果,每个reduce输出一个文件。
?spark sql原理
- broadcast/map join(无reduce,内存计算)
适用场景:大表join小表
开关参数:无(始终开启)
表大小限制:spark.sql.autoBroadcastJoinThreshold参数,默认10M(hive map join默认小表25M)
流程:
1、broadcast阶段
小表先发给driver,driver再发给各个executor内存中,并存储为HashTable形式
2、hash join阶段
每个exector本机执行join,拿大表每条记录的join字段为key,hash查找小表,得到结果输出
- shuffle sort merge join
适用场景:大表join大表
开关参数:无(始终开启)
要求join的key可排序
流程:
1、map阶段
读取表内容,输出key-value类型数据,key为join字段,value为关心字段(select字段+where条件字段)+tag(标识来源表);
2、shuffle + sort阶段
shuffle输出按key排序(spark shuffle天然支持排序),并将相同排序范围的key输出到相同的分区里;
3、merge join阶段
分区内对排好序的两张表数据执行join操作。join操作很简单,分别遍历两个有序序列,碰到相同join key就merge输出。
- shuffle hash join
适用场景:大表join中表(性能较sort merge join稍好,耗费内存资源过多,不建议使用)
开关参数:spark.sql.join.preferSortMergeJoin默认false关闭(官方不推荐,所以默认关闭)
表大小限制:
(1)中表 > spark.sql.autoBroadcastJoinThreshold(默认25M)
(2)中表 < 大表的1/3
(3)中表 < spark.sql.autoBroadcastJoinThreshold * 分区数目(即中表在每个分区内小于broadcast join小表限制)
流程:
1、map阶段
读取表内容,输出key-value类型数据,key为join字段,value为关心字段(select字段+where条件字段)+tag(标识来源表);
2、shuffle阶段
按map阶段的key进行hash,分发到reduce,join两表相同的key分发到同一个reduce;
3、hash join阶段
中表在每个分区内以HashTable形式加载到内存,key为join字段,大表每条记录拿着join字段去查HashTable,并输出join结果。