CODEWITHSUNDEEP
sqlhivesubquery
HP.
HP.

Reputation: 19896

subquery in SELECT without JOIN in Hive?

I know Hive doesn't support this

SELECT (CASE WHEN table1.id in (SELECT table1.id
                                from table1,table2
                                where table1.id = table2.id and table2.company like '%My Company%')
            THEN table1.email
            ELSE regexp_replace(table1.email, substr(table1.email, 1), 'XXXX') 
       END) as  email, table1.id
FROM table1

Hive cannot do SELECT within SELECT (subquery in SELECT).

But let say for some restriction I cannot do JOIN after FROM clause. Is there a "creative" way to do this? I was thinking about parsing and passing a "static list" from SELECT table1.id from table1,table2 where table1.id = table2.id and table2.company like '%My Company%' in a separate query. But this could go up to thousands.

Upvotes: 0

Views: 500

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

if you could use a select for a join the you could use a left join and check for null value 

SELECT case when t1.id is null 
               then regexp_replace(table1.email, substr(table1.email, 1), 'XXXX') 
               else table1.email 
        end
       , table1.id 
    FROM table1 
    left join (
        SELECT table1.id 
        from table1,table2 
        where table1.id = table2.id 
        and table2.company like '%My Company%'
      ) t on  table1.id = t.id

Upvotes: 2

Related Questions