重写hive-in子句

j2datikz 于 2021-05-29 发布在 Hadoop

关注(0)|答案(2)|浏览(453)

我正在尝试在配置单元中执行此子查询，但我得到一个错误，即配置单元版本不支持子查询，很遗憾，是的，我们使用的是旧版本的配置单元。

select col1,col2 from t1 where col1 in (select x from t2 where y = 0)

然后我用左半连接重写了子查询，如下所示，

select a.col1,a.col2
FROM t1 a LEFT SEMI JOIN t2 b on (a.col1 =b.x)
WHERE b.y = 0

如果我没有给出where条件，但是当我尝试在where条件中使用b.any列或在select子句中使用b.any列时，它无法识别表b，那么这个查询运行正常。抛出此错误-

Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 3:6 Invalid table alias or column reference 'b': (possible column names

非常感谢您的帮助。

hadoop Hive hiveql

来源：https://stackoverflow.com/questions/38891406/rewrite-hive-in-clause

2条答案

按热度按时间

hl0ma9xz1#

select a.col1,a.col2
FROM t2 b RIGHT OUTER JOIN t1 a on (b.x = a.col1)
WHERE b.y = 0

--当您使用左半联接时，其中条件是右侧表列不工作。请将脚本更改为上述条件。

赞(0）回复(0）举报 2021-05-30

aiqt4smr2#

而不是 t1 a LEFT SEMI JOIN t2 b ，您可以这样做： t1 a LEFT SEMI JOIN (select * from t2 where y = 0) b .

select a.col1,a.col2
FROM t1 a LEFT SEMI JOIN (select * from t2 where y = 0) b on (a.col1 =b.x);

请看下面的例子。

Department table:
+--------------------+----------------------+--+
| department.deptid  | department.deptname  |
+--------------------+----------------------+--+
| D101               | sales                |
| D102               | finance              |
| D103               | HR                   |
| D104               | IT                   |
| D105               | staff                |
+--------------------+----------------------+--+

Employee tabe:
+-----------------+------------------+------------------+--+
| employee.empid  | employee.salary  | employee.deptid  |
+-----------------+------------------+------------------+--+
| 1001            | 1000             | D101             |
| 1002            | 2000             | D101             |
| 1003            | 3000             | D102             |
| 1004            | 4000             | D104             |
| 1005            | 5000             | D104             |
+-----------------+------------------+------------------+--+

hive> SELECT
dept.deptid, dept.deptname 
FROM 
department dept 
LEFT SEMI JOIN 
(SELECT * FROM employee WHERE salary > 3000) emp 
ON (dept.deptid = emp.deptid);
+--------------+----------------+--+
| dept.deptid  | dept.deptname  |
+--------------+----------------+--+
| D104         | IT             |
+--------------+----------------+--+

赞(0）回复(0）举报 2021-05-29

我来回答

重写hive-in子句

2条答案

相关问题

热门标签

最新问答