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

[#1]

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.

[#2]

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

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

[#3]

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 

[#4]

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

[#5]

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

[#6]

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