join - 连接表时 Hive 查询出错

Question

我无法使用以下 HIVE 查询通过相等性检查。

我有 3 个表，我想加入这些表。我尝试如下，但出现错误:

失败:语义分析错误:第 3:40 行在 JOIN 'visit_date' 中遇到左右别名

select t1.*, t99.* from table1 t1 JOIN 
    (select v3.*, t3.* from table2 v3 JOIN table3 t3 ON
    ( v3.AS_upc= t3.upc_no AND v3.start_dt <= t3.visit_date  AND v3.end_dt >= t3.visit_date AND v3.adv_price <= t3.comp_price ) ) t99 ON
    (t1.comp_store_id = t99.cpnumber AND t1.AS_store_nbr = t99.store_no);

根据 FuzzyTree 的帮助编辑:

第一:

我们尝试使用 between 和 where 子句编辑上述查询，但没有从查询中获得任何输出。

但是如果我们通过删除带有日期的 between 子句来更改上述查询，那么我会得到一些基于“v3.adv_price <= t3.comp_price”但不使用“日期过滤器”的输出。

select t1.*, t99.* from table1 t1 JOIN
    (select v3.*, t3.* from table2 v3 JOIN table3 t3 on (v3.AS_upc= t3.upc_no) 
        where v3.adv_price <= t3.comp_price
    ) t99 ON
    (t1.comp_store_id = t99.cpnumber AND t1.AS_store_nbr = t99.store_no);

第二个:

接下来我们尝试只传递一个日期:

select t1.*, t99.* from table1 t1 JOIN
        (select v3.*, t3.* from table2 v3 JOIN table3 t3 on (v3.AS_upc= t3.upc_no) 
            where v3.adv_price <= t3.comp_price and v3.start_dt <= t3.visit_date
        ) t99 ON
        (t1.comp_store_id = t99.cpnumber AND t1.AS_store_nbr = t99.store_no);

所以，现在它显示了一些结果，但如果我们同时通过了开始日期和结束日期过滤器，它；没有显示任何结果。

Answer 1

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins

Only equality joins, outer joins, and left semi joins are supported in Hive. Hive does not support join conditions that are not equality conditions as it is very difficult to express such conditions as a map/reduce job.

尝试将不等式移动到 where 子句

select t1.*, t99.* from table1 t1 JOIN
    (select v3.*, t3.* from table2 v3 JOIN table3 t3 on (v3.AS_upc= t3.upc_no) 
        where t3.visit_date between v3.start_dt and v3.end_dt
        and v3.adv_price <= t3.comp_price
    ) t99 ON
    (t1.comp_store_id = t99.cpnumber AND t1.AS_store_nbr = t99.store_no);