question Re: How can I solve this sql problem? (like join in Hive) in Support Questions

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

ho_ddeok — Mon, 08 Aug 2022 07:22:54 GMT

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.

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

nramanaiah — Tue, 09 Aug 2022 00:51:26 GMT

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

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

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

ho_ddeok — Tue, 09 Aug 2022 04:39:29 GMT

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

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

nramanaiah — Tue, 09 Aug 2022 05:45:20 GMT

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

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

araujo — Tue, 09 Aug 2022 06:25:44 GMT

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

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

VidyaSargur — Wed, 17 Aug 2022 06:02:50 GMT

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