Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

hive query with between in join working in llap but not in hive

hive query with between in join working in llap but not in hive

New Contributor

Below range query with join working fine in llap, but not in Hiveserver2/Hive.CLI.

Please suggest how to work with range join query in Hive.

Hive version : 1.2.1.2.6

HDP version : 2.6.0.3

Query:

select * from datahub.cgs_tmp_gre gre LEFT JOIN datahub.cgs_tmp_cgsrxclm_ev_ba baON gre.guar_key = ba.guar_key and gre.serviced_dte = ba.serviced_dteAND gre.ts between ba.obsv_start_ts and ba.obsv_stop_ts AND gre.phcy_claim_id=2;

Below is the error message in hiveserver 2:

Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Both left and right aliases encountered in JOIN 'obsv_stop_ts' (state=42000,code=40000)
3 REPLIES 3
Highlighted

Re: hive query with between in join working in llap but not in hive

Rising Star

LLAP has better SQL support than the older versions of Hive (the CLI is Hive 1.x and LLAP is Hive 2.x).

Re: hive query with between in join working in llap but not in hive

New Contributor

Migrating existing audit ETL report to Hive, which has 800B records, so like to use Hive CLI and not LLAP. But llap has better SQL.

In case if i migrate to HDP 2.6 with hive 2 then can i run above query in hive CLI ( Not in LLAP) ?

Please suggest.

Re: hive query with between in join working in llap but not in hive

Expert Contributor

As @gopal said, HS2 (Interactive) has better SQL Support, Hive CLI always use Hive1.x, whereas HS2 (Interactive) use Hive 2.x

If you are using beeline, u can connect to both HS2 & HS2 (Interactive) based on url provided. No need to reload the data.

If above SQL needs to be supported in HDP 2.6.0 Hive 1.x, then i think you might need HIVE-15211 & HIVE-16885.