Rewrite Hive IN clause

Question

I am trying to execute this subquery in HIVE,but i am getting error that subquery is not supported in my HIVE version, unfortunately yes we are using the old version of HIVE.

select col1,col2 from t1 where col1 in (select x from t2 where y = 0)

Then I have rewritten the subquery using left semi join like this,

select a.col1,a.col2
FROM t1 a LEFT SEMI JOIN t2 b on (a.col1 =b.x)
WHERE b.y = 0

This query is running fine if i don't give the where condition, but its not recognising the table b when I try to use b.any column in where condition or use b.any column in select clause. Throwing this error -

Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 3:6 Invalid table alias or column reference 'b': (possible column names

Any help is much appreciated.

See this as well http://stackoverflow.com/questions/26028767/why-cant-hive-recognize-alias-named-in-select-part — Suvarna Pattayil, Aug 11 '16 at 09:10
One more thing is that, when I do select * from ... Without where clause. It selects only the columns from table a. — ds_user, Aug 11 '16 at 09:27

score 2 · Answer 1 · answered Aug 11 '16 at 13:01

2

select a.col1,a.col2
FROM t2 b RIGHT OUTER JOIN t1 a on (b.x = a.col1)
WHERE b.y = 0

-- When you use LEFT SEMI JOIN, where condition is not work on right side table column. Please change your script to above condition.

answered Aug 11 '16 at 13:01

Vishwam Pula

37
6

Sai Neelakantam · Accepted Answer · 2016-08-12T15:48:59.553

Instead of t1 a LEFT SEMI JOIN t2 b, you can do something like this: t1 a LEFT SEMI JOIN (select * from t2 where y = 0) b.

select a.col1,a.col2
FROM t1 a LEFT SEMI JOIN (select * from t2 where y = 0) b on (a.col1 =b.x);

Please see below example.

Department table:
+--------------------+----------------------+--+
| department.deptid  | department.deptname  |
+--------------------+----------------------+--+
| D101               | sales                |
| D102               | finance              |
| D103               | HR                   |
| D104               | IT                   |
| D105               | staff                |
+--------------------+----------------------+--+

Employee tabe:
+-----------------+------------------+------------------+--+
| employee.empid  | employee.salary  | employee.deptid  |
+-----------------+------------------+------------------+--+
| 1001            | 1000             | D101             |
| 1002            | 2000             | D101             |
| 1003            | 3000             | D102             |
| 1004            | 4000             | D104             |
| 1005            | 5000             | D104             |
+-----------------+------------------+------------------+--+

hive> SELECT
dept.deptid, dept.deptname 
FROM 
department dept 
LEFT SEMI JOIN 
(SELECT * FROM employee WHERE salary > 3000) emp 
ON (dept.deptid = emp.deptid);
+--------------+----------------+--+
| dept.deptid  | dept.deptname  |
+--------------+----------------+--+
| D104         | IT             |
+--------------+----------------+--+

Rewrite Hive IN clause

2 Answers2