hive in/not in替换以及hive join优化

目前hive不支持 in或not in 中包含查询子句的语法，所以只能通过left join实现。

假设有一个登陆表login(当天登陆记录,只有一个uid),和一个用户注册表regusers(当天注册用户，字段只有一个uid)，这两个表都包含一个字段,uid。 

in查询

如果要查询当天登陆的注册用户，需要用in查询，hive sql如下:

select login.uid from login left outer join regusers on login.uid=regusers.uid where regusers.uid is not null

如果login表和regusers表按天分区，字段是dt，那么查询2013年1月1号当天登陆的注册用户，hive sql如下:

select login.uid from login day_login left outer join 
   (select uid from regusers where dt='20130101') day_regusers
on day_login.uid=day_regusers.uid where day_login.dt='20130101' and day_regusers.uid is not null

 

not in查询

如果要查询当天登陆的老用户(这里假设非当天注册用户就是老用户)，需要用not in查询，hive sql如下:

select login.uid from login left outer join regusers on login.uid=regusers.uid where regusers.uid is null;

如果login表和regusers表按天分区，字段是dt，那么查询2013年1月1号当天登陆的老用户，hive sql如下:

select login.uid from login day_login left outer join 
   (select uid from regusers where dt='20130101') day_regusers
on day_login.uid=day_regusers.uid where day_login.dt='20130101' and day_regusers.uid is null;



Hive join优化
========================================================

由 于 hive 与传统关系型数据库面对的业务场景及底层技术架构都有着很大差异，因此，传统数据库领域的一些技能放到 Hive 中可能已不再适用。关于 hive 的优化与原理、应用的文章，前面也陆陆续续的介绍了一些，但大多都偏向理论层面，本文就介绍一个实例，从实例中一步步加深对 hive 调优的认识与意识。

1、需求

需求我做了简化，很简单，两张表做个 join，求指定城市，每天的 pv，用传统的 RDBMS SQL 写出来就这样的：

 

SELECT t.statdate,         c.cname,         count (t.cookieid) FROM tmpdb.city c JOIN ecdata.ext_trackflow t ON (t.area1= c.cname                                  OR t.area2 =c.cname                                  OR t.area3 = c.cname) WHERE t.statdate>= '20140818' and t.statdate<= '20140824'    AND platform= 'pc' GROUP BY t.statdate,           c.cname;

怎么样？根据 SQL 看懂需求没问题吧？

2、非等值 join 问题

然后把这条 SQL 贴到 hive 中去执行，然后你会发现报错了：

 

?

1	FAILED: SemanticException [Error 10019]: Line 5:32 OR not supported in JOIN currently 'cname'

这是因为 hive 受限于 MapReduce 算法模型，只支持 equi-joins（等值 join），要实现上述的非等值 join，你可以采用笛卡儿积（ full Cartesian product ）来实现：

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT t.statdate,         c.cname,         count (t.cookieid) FROM tmpdb.city c JOIN ecdata.ext_trackflow t WHERE t.statdate>= '20140818'    AND t.statdate<= '20140824'    AND platform= 'pc'    AND (t.area1= c.cname         OR t.area2 =c.cname         OR t.area3 = c.cname) GROUP<