Hive like Operator

Question

[enter link description here][1]

Table1:

BRAND
Sony
Apple
Google
IBM
etc.

Table2:

hive> select * from PRODUCT;    
SonyABC,Applepqr       101
Sonyadvv,IBMabc        102
Sonyaaaa               103
Apple123,Sonyxyz       104
Apple345,IBMabc        105
IBM13123SonyABC        106   
My data is like above.and i need output like bellow.Please suggest a query

SonyABC,Applepqr         101     Sony,Apple
Sonyadvv,IBMabc          102     Sony,IBM
Sonyaaaa                 103     Sony
Apple123,Sonyxyz         104     Apple,Sony
Apple345,IBMabc          105     Apple,IBM
IBM13123,SonyABC         106     IBM,Sony

Possible duplicate of [Hive - LIKE Operator](http://stackoverflow.com/questions/31340218/hive-like-operator) — Daniel, Nov 16 '16 at 09:56
Thank u for quick replay. i saw your previous answer and i need little bit more.mean i need the output from two tables columns as repeated Brand column.is my query work ? Thank you. — Ramesh Muthavarapu, Nov 16 '16 at 10:49

Ravinder Karra · Answer 1 · 2016-12-14T03:58:13.730

0

May be some issue in your data, but this works as following hive> select * from BRAND; Sony Apple Google IBM

hive> select * from PRODUCT;    
SonyABC 101
Sonyadvv        102
Sonyaaaa        103
Apple123        104
Apple345        105
IBM13123        106

select PRODUCT.*, BRAND.*  from BRAND, PRODUCT where trim(PRODUCT_ID) like  concat(trim(BRAND_ID),'%');


SonyABC         101     Sony
Sonyadvv        102     Sony
Sonyaaaa        103     Sony
Apple123        104     Apple
Apple345        105     Apple
IBM13123        106     IBM
Time taken: 11.381 seconds, Fetched: 6 row(s)

edited Dec 14 '16 at 03:58

answered Nov 16 '16 at 23:50

Ravinder Karra

307
1
3
8

Thank you Ravinder Karra. Yes my input has issue.i have corrected it.Please suggest a query for it. – Ramesh Muthavarapu Nov 17 '16 at 06:51
select PRODUCT.*, BRAND.* from BRAND, PRODUCT where PRODUCT_ID like concat(BRAND_ID,'%'); is query --> does this work – Ravinder Karra Nov 18 '16 at 05:22
select PRODUCT.*, collect_set(BRAND) from BRAND, PRODUCT where PRODUCT_ID like concat(BRAND_ID,'%'); I run your query and mine. in both brand column giving nulls. – Ramesh Muthavarapu Nov 18 '16 at 09:14
Added trim, as your data may have spaces, it works fin on my machine – Ravinder Karra Dec 14 '16 at 03:59

Hive like Operator

1 Answers1

Linked