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

问题描述

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

我有3个表，我想加入这些表。我试图如下，但得到错误：

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

 选择t1。*，t99。* from table1 t1 JOIN 
（从表格2选择v3。*，t3。* * v3 JOIN表格3 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的帮助编辑：

1：

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

但是，如果我们通过删除between子句和date来更改上述查询，那么我得到了一些基于v3.adv_price <= t3.comp_price的输出，但没有使用date filter。

  select t1。*，t99。* from table1 t1 JOIN 
（select v3。*，t3。* from table3 v3 JOIN table3 t3 on（v3.AS_upc = t3.upc_no）
其中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）; 
  

所以，现在它显示了一些结果，但如果我们同时通过开始和结束日期过滤器，没有显示任何结果。 > https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins

只有平等连接，
Hive支持外连接和左半连接。 Hive不支持不等于
条件的连接条件，因为很难将这种条件表达为
map / reduce作业。

尝试将您的不平等移动到其中子句

 从table1中选择t1。*，t99。* * t1 JOIN 
（从表2中选择v3。*，t3。* v3 JOIN table3 t3 on（v3.AS_upc = t3.upc_no）
其中t3 v3.start_dt和v3.end_dt之间的.visit_date 
和v3.adv_price< = t3.comp_price 
）t99 ON 
（t1.comp_store_id = t99.cpnumber AND t1.AS_store_nbr = t99。 store_no）; 
  

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

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

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

EDITED based on help from FuzzyTree:

1st:

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

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

2nd :

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

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.

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屋！