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结果。