hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sunderlin, Mark" <mark.sunder...@teamaol.com>
Subject RE: Can't use OR in left join
Date Thu, 26 Jul 2012 17:52:27 GMT
How about:

select 
  a.pid,
  b.pid
tab1 a
  left join
tab2 b
  on (a.pid=b.pid);
union all -- Need 'union all' to remove dups for the case of when (a.pid=b.pid) and (substr(a.pid,1,27)=b.pid)

select 
  a.pid,
  b.pid
tab1 a
  left join
tab2 b
  on (substr(a.pid,1,27)=b.pid);

---
Mark E. Sunderlin
Solutions Architect |AOL Core Data Technologies
P: 703-265-6935     |C: 540-327-6222 | AIM: MESunderlin
22000 AOL Way | Dulles, VA | 20166

On Thu, Jul 26, 2012 at 5:11 PM, 周彩钦 <caiqinzhou@gmail.com> wrote:
Hi all,

I have problem when using left join with hive 0.7.1.
I have a query below:

select 
  a.pid,
  b.pid
tab1 a
  left join
tab2 b
  on (a.pid=b.pid or substr(a.pid,1,27)=b.pid);

But hive don't support "OR" in left join.
Table a is huge, and table b has 40000 rows now(will increase).
Is there any other solution to achieve this?

Thanks very much.

-- 



-- 
Bertrand Dechoux




-- 
/**********************************************************/
// 姓名:周彩钦
// 联系电话:15210364513
// E-mail:caiqinzhou@gmail.com
/**********************************************************/



-- 
Bertrand Dechoux




-- 
/**********************************************************/
// 姓名:周彩钦
// 联系电话:15210364513
// E-mail:caiqinzhou@gmail.com
/**********************************************************/
Mime
View raw message