加入表时 Hive 查询出错 [英] Error in Hive Query while joining tables

问题描述

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

I am unable to pass the equality check using the below HIVE query.

我有 3 张桌子，我想加入这些桌子.我尝试如下，但出现错误:

I have 3 table and i want to join these table. I trying as below, but get error :

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

FAILED: Error in semantic analysis: Line 3:40 Both left and right aliases encountered in 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 的帮助进行

EDITED based on help from FuzzyTree:

第一个:

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

We tried to edit above query using between and where clause, but not getting any output from the query.

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

But If we changed the above query by removing the between clause with date, then I got some output based on "v3.adv_price <= t3.comp_price", but not using "date filter".

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);

第二个:

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

Next we tried to pass only one date as :

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);

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

So, now it's showing some result but if we pass both the start and end date filter, it; not showing any result.

推荐答案

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

仅支持等式连接、外连接和左半连接蜂巢.Hive 不支持不相等的连接条件条件，因为很难表达这样的条件映射/减少作业.

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 子句

Try moving your inequalities to the where clause

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);

这篇关于加入表时 Hive 查询出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！