Hive QL Except子句 [英] Hive QL Except clause

问题描述

如何在Hive QL中执行 EXCEPT 子句（如SQL）

我有2个表，每个表都是一列唯一的ID。

我想查找仅在表1中但不在表2中的ID列表

表1

  apple 
 orange 
 pear 
  

表2

  apple 
 orange 
  

在SQL中，您可以执行EXCEPT子句（ http://en.wikipedia.org/wiki / Set_operations_％28SQL％29 ），但你不能在Hive QL中做到这一点

认为有任何内置的方法可以做到这一点，但是 LEFT OUTER JOIN 应该有效。在 table2 中存在来自 table1 的所有id not ：

  SE LECT t1.id FROM table1 t1 LEFT OUTER JOIN table2 t2 ON（t1.id = t2.id）WHERE t2.id IS NULL; 
  

How do I do an EXCEPT clause (like SQL) in Hive QL

I have 2 tables, and each table is a column of unique ids.

I want to find the list of ids that are only in table 1 but not in table 2

Table 1

apple
orange
pear

Table 2

apple
orange

In SQL you can do an EXCEPT clause (http://en.wikipedia.org/wiki/Set_operations_%28SQL%29) but you can't do that in Hive QL

解决方案

I don't think there's any built-in way to do this but a LEFT OUTER JOIN should do the trick.

This selects all Ids from table1 that do not exist in table2:

SELECT t1.id FROM table1 t1 LEFT OUTER JOIN table2 t2 ON (t1.id=t2.id) WHERE t2.id IS NULL;

这篇关于Hive QL Except子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！