Support Questions

Find answers, ask questions, and share your expertise

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

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

Expert Contributor

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

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

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 

Expert Contributor

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

Master Collaborator

@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.

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:
Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.