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 : 220.127.116.11.6
HDP version : 18.104.22.168
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)
LLAP has better SQL support than the older versions of Hive (the CLI is Hive 1.x and LLAP is Hive 2.x).
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) ?
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.