CODEWITHSUNDEEP
hivehiveql
Shanu
Shanu

Reputation: 11

Hive Join on String returns no matching

I am trying to run a simple join on string values but getting no matching rows. However all the values are the same in both the tables

select l2.word,d.polarity from l2 join  dictionary d on (l2.word=d.word);

L2 table :
realtimesession_id      bigint                  None                
word                    string                  None   

Dictionary table :
realtimesession_id      bigint                  None                
word                    string                  None

I tried setting set hive.auto.convert.join=true; but still no records matching. Please someone help.

Upvotes: 1

Views: 1664

Answers (3)

kalpesh
kalpesh

Reputation: 328

Join on string will work. Just keep in mind that string matching will be case-sensitive.

Consider using functions like UPPER or LOWER.

For example,

select * from temp join new_temp on LOWER(temp.dept) = LOWER(new_temp.dept);

(Tested solution)

Upvotes: 1

Nikos Epitropakis
Nikos Epitropakis

Reputation: 71

Joining on strings is not the best practice, although if needed you should use:

select l2.word,d.polarity from l2 join dictionary d on (l2.word rlike d.word)

Upvotes: 0

Believer
Believer

Reputation: 64

have you tried standardizing the casing - hive is case-sensitive? I usually use lower() when comparing strings to cut down on casing issues.

Upvotes: 0

Related Questions