hadoop - Hive - LIKE Operator -

i can not figure out how deal problem:

these data:

table1:         table2: brand           product           sold sony            sony abcd         1233 apple           sony adv          1233 google          sony aaaa         1233 ibm             apple 123         1233 etc.            apple 345         1233                 ibm 13123         1233 

is possible filter query have table stands brand , total solds? idea is:

select table1.brand, sum(table2.sold) table1 join table2 on (table1.brand '%table2.product%') group table.1.brand 

that idea, error

the biggest problem like-operator or there other solution?

i see 2 issues: first of all, joins in hive work equality conditions, isn't going work there.

https://cwiki.apache.org/confluence/display/hive/languagemanual+joins

only equality joins, outer joins, , left semi joins supported in hive. hive not support join conditions not equality conditions difficult express such conditions map/reduce job.

instead, wants go clause.

secondly, see problem statement itself: '%table2.product%' being interpreted literally string '%table2.product%'. additionally, if doing intended, try table2.product inside of brand, when seem want other way. evaluation intended, need add wildcard contents of table1.brand; accomplish this, want concatenate wildcards expression.

table2.product concat('%',table1.brand,'%')) 

by doing this, evaluate strings '%sony%', '%apple%'...etc instead of '%table2.product%'.

what want brandon bell's query, i've merged answer:

select table1.brand, sum(table2.sold)  table1, table2 table2.product concat('%', table1.brand, '%')  group table1.brand;