CODEWITHSUNDEEP
hadoopsubqueryhivehiveql
CodeWOD
CodeWOD

Reputation: 155

Subquery with LATERAL VIEW in hive 0.13

I am using Apache Hive 0.13 (which supports Sub-Queries) and I am trying to run a query whose sub-query uses LATERAL VIEW and explode(). I keep getting NPE :

FAILED: NullPointerException null

I have tried the sub-query separately & independently, and it works fine. I also could not find any limitations in using LATERAL VIEW for sub-queries in Hive's documentation

So I am wondering what the issue is with my query. You can see the query below:

select u.name, u.employment 
from users u 
where u.id IN (
SELECT distinct su.id 
FROM users su LATERAL VIEW explode(su.employment) empTable AS emp 
where su.frCount >= 10 
and su.frCount < 20 
and emp.endDate is NULL
);

I am using Hive with JSON data. Here is the DDL for the table: 

CREATE TABLE users(
id BIGINT,   
name string, 
frCount INT, 
employment array<struct<
                  organization_name : string,
                  start_date: BIGINT, 
                  end_date: BIGINT>>
) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' STORED AS TEXTFILE;

Upvotes: 1

Views: 3935

Answers (1)

tinychen
tinychen

Reputation: 36

You could use Hive left semi join.

select u.name, u.employment
from users u left semi join
(SELECT distinct su.id 
FROM users su LATERAL VIEW explode(su.employment) empTable AS emp 
where su.frCount >= 10 
and su.frCount < 20 
and emp.endDate is NULL) t on u.id = t.id

Upvotes: 2

Related Questions