Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

How can I solve this sql problem? (like join in Hive)

avatar
New Contributor

I know Hive only provide equi join. For example, below sql statement.

select *

from A join B

        on A.c1 = B.c2

where 1=1;

 

But I want to execute Like join Query in Hive. For example, below sql statement.

select *

from A join B

        on A.c1 like B.c2

where 1=1;

 

Please let me know if you know the solution in Hive.

5 REPLIES 5

avatar
Expert Contributor

Both the queries should work fine in CDP-7.x Hive

Can you share the exception trace if you face any issue ?

avatar
New Contributor

FAILED: SemanticException [Error 10017]: Line 5:8 Both left and right aliases encountered in JOIN ''%''

 

executed sql >

select *

from table_A a

        join

        table_B b

        on a.col_1 like concat('%', b.col_2, '%')

where 1=1

;

 

I am using HDP 2.6.2 version and Hive 1.2.1000 version 

avatar
Expert Contributor

I am able to run the above SQL in CDP 7.1.7 SP1 without any issue. Please try it in CDP.

avatar
Super Guru

@ho_ddeok ,

 

Hive 1 is a very old version and and equi-join was probably the only option available back then. As @nramanaiah mentioned, you should consider upgrading to the latest version, where this statement works.

 

Nevertheless, you can try the following alternative query. I'm not sure if it works, though, since I don't have a Hive 1 environment to test this:

select *
from A cross join B
where A.c1 like B.c2;

 

Cheers,

André

 

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.

avatar
Community Manager

@ho_ddeok, Has any of the replies helped resolve your issue? If so, can you please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future? 



Regards,

Vidya Sargur,
Community Manager


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Learn more about the Cloudera Community: