减去HIVE中的查询 [英] Minus query in HIVE

问题描述

尝试过：

 从abc中选择x 
减去
从bcd中选择x; 
  

我是不是为HIVE定义了错误或减号查询？如果是这样，是否有任何其他方式来得到这个结果？

解决方案

HQL不支持 MINUS 运算符。看到这个相关的，虽然有点老，资源：

http://www.quora.com/Apache-Hive/What-are-the-biggest-feature-gaps-between-HiveQL -and-SQL

您可以使用 LEFT JOIN 或 NOT EXISTS ：

 选择x 
从abc 
 LEFT JOIN bcd 
 ON abc.x = bcd.x 
 WHERE bcd.x IS NULL 
  

编辑：下面的注释不支持 NOT EXISTS 。

  SELECT x 
 FROM abc 
 WHERE NOT EXISTS（SELECT x FROM bcd）
  

Minus query seems to not work in HIVE.

Tried ex:

select x from abc 
minus 
select x from bcd ; 

Am I doing this wrong or minus query isn't defined for HIVE? If so, is there any other way to get the result for this?

解决方案

It does not appear that HQL supports the MINUS operator. See this relevant, albeit a bit old, resource:

http://www.quora.com/Apache-Hive/What-are-the-biggest-feature-gaps-between-HiveQL-and-SQL

What you want to do can be done with a LEFT JOIN or NOT EXISTS:

SELECT x
FROM abc
LEFT JOIN bcd
ON abc.x = bcd.x
WHERE bcd.x IS NULL

EDIT: Per comments below, NOT EXISTS is not supported.

SELECT x 
FROM abc
WHERE NOT EXISTS (SELECT x FROM bcd)

这篇关于减去HIVE中的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！