最近总结了一下hive表关联的用法,与Postgres表关联还是有细微差别,总结在这里方便以后查看。
join语法
join_table: table_reference [INNER] JOIN table_factor [join_condition] | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition | table_reference LEFT SEMI JOIN table_reference join_condition table_reference: table_factor | join_table table_factor: tbl_name [alias] | table_subquery alias | ( table_references ) join_condition: ON expression
支持join多路连接,但不支持笛卡尔积,即无条件join的连接。equality_expression是一个等式表达式,不支持非等值连接(on 后面的条件为等值)
examples
select a.* from a join b on (a.id = b.id) select a.* from a join b on (a.id = b.id and a.department = b.department)
-
两个表以上关联
select a.val. b.val, c.val from a join b on (a.key = b.key1) join c on (c.key= b.key2)
如果多个表关联使用同一个键,则只创建单个map/reduce job
select a.val, b.val, c.val from a join b on (a.key = b.key1) join c on (c.key = b.key1)
相反,如果不是都使用key1关联,而是使用key2与c表关联,则会创建两个map/reduce job,b.key1用于第一次join条件,而b.key2用于第二次join。
select a.val, b.val, c.val from a join b on (a.key = b.key1) join c on (c.key = b.key2)
join时, 每次map/reduce任务逻辑是这样的:reduce会缓存join序列中除了最后一个表的所有结果记录到buffer,再通过最后一个表将结果序列化到文件系统。这一实现机制有助于在reduce端减少内存的使用量。实践中,应该把最大的那张表卸载最后(否则会因为缓存浪费大量内存),例如在
select a.val, b.val, c.val from a join b on (a.key = b.key1) join c on (c.key = b.key1)
中, 所有的表都使用同一个join key(一次MR任务计算),reduce端会缓存a表和b表的记录,然后每次取得一个c表的记录计算一次join结果。而在
select a.val, b.val, c.val from a join b on (a.key = b.key1) join c on (c.key = b.key2)
中,使用了两次MR任务。第一次缓存a表,用b表序列化;第二次缓存第一次map/reduce的结果,然后用C表序列化.
每次join task之前都存在一个数据的shuffle的开销,所以我们为了能把相同的key的join合并到一步里完成,减少shuffle的次数,需要在写join顺序的时候把相同key的join放在一块.
-
如果要过滤join结果输出,可以在where条件里面添加过滤条件,或者是写到join子句里面。
select a.val, b.val from a left join b on (a.key = b.key) where a.ds = '2009-07-07' and b.ds = '2009-07-07'
如果b表找不到对应在a表的记录,b表的所有列都会列为NULL,包括ds列。后面where条件会过滤掉所有的行。对于出现在where条件里面的b表的列来说,与left join没有什么关系了。
我们一般选择这样写:
select a.val, b.val from a left join b on (a.key = b.key and b.ds = '2018-09-09' and a.ds = '2018-09-09')
这一查询加过是在join的时候就过滤掉的,不存在上述问题。这个貌似和关系型数据库的left join不一样,a表条件加在join之后是没有用的,总会返回a表所有数据,只能加在where条件后面!
-
left semi join/left anti join, 右表只能出现在关联条件(on-clause)里面,件不能出现在where和select子句中。left semi join相当于in的语法。left anti join相当于not in。
select a.key, a.value from a where a.key in (select b.key from b);
可以写成
SELECT a.val, a.value from a left semi join b on (a.key = b.key)
-
map join
当一个大表和一个或多个小表做Join时,可以使用MapJoin,性能比普通的Join要快很多。MapJoin 的基本原理为:在小数据量情况下,SQL会将您指定的小表全部加载到执行Join操作的程序的内存中,从而加快Join的执行速度。
使用 MapJoin 时,要注意以下问题:
left outer join的左表必须是大表。
right outer join的右表必须是大表。
inner join左表或右表均可以作为大表。
full outer join不能使用MapJoin。
MapJoin支持小表为子查询。
使用MapJoin时,需要引用小表或是子查询时,需要引用别名。
在MapJoin中,可以使用不等值连接或者使用or连接多个条件。
目前,MaxCompute在MapJoin中最多支持指定8张小表,否则报语法错误。
如果使用MapJoin,则所有小表占用的内存总和不得超过512MB。由于MaxCompute是压缩存储,因此小表在被加载到内存后,数据大小会急剧膨胀。此处的512MB限制是加载到内存后的空间大小。
多个表Join时,最左边的两个表不能同时是MapJoin的表。
示例如下:
select /* + mapjoin(a) */ a.shop_name, b.customer_id, b.total_price from shop a join sale_detail b on a.shop_name = b.shop_name;
MaxCompute SQL不支持在普通Join的on条件中使用不等值表达式,or逻辑等复杂的Join条件,但是在MapJoin中可以进行如上操作。
示例如下:
select /*+ mapjoin(a) */ a.total_price, b.total_price from shop a join sale_detail b on a.total_price < b.total_price or a.total_price + b.total_price < 500;