Skip to main content
ho_ddeok
Silver ⭐️
Aug 8, 2022 7:22 AM UTC
Question

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

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

nramanaiah
Gold ⭐️
Gold ⭐️
Aug 9, 2022 12:51 AM UTC

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

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

Translate
ho_ddeok
ho_ddeokAuthor
Silver ⭐️
Aug 9, 2022 4:39 AM UTC

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 

Translate
nramanaiah
Gold ⭐️
Gold ⭐️
Aug 9, 2022 5:45 AM UTC

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

Translate
araujo
Platinum ⭐️⭐️⭐️
Platinum ⭐️⭐️⭐️
Aug 9, 2022 6:25 AM UTC

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

 

Translate
-- 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.
VidyaSargur
Community Manager
Community Manager
Aug 17, 2022 6:02 AM UTC

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

Translate
Regards, Vidya Sargur Madabhushi| Senior Community Manager | Was your question answered? Make sure to mark the answer as the Best Answer. If you find a reply useful, say thanks by clicking on the thumbs up button.