hive查询语言只返回与另一个表中的值不同的值

cl25kdpy 于 2021-06-03 发布在 Hadoop

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

我正在尝试查找我的hosts表中的所有值，其中不包含与我的maildomains表中的值部分匹配的值。

hosts
+-------------------+-------+
|       host        | score |
+-------------------+-------+
| www.gmail.com     |   489 |
| www.hotmail.com   |   653 |
| www.google.com    |   411 |
| w3.hotmail.ca     |   223 |
| stackexchange.com |   950 |
+-------------------+-------+
maildomains 
+---------------+
| email         |
+---------------+
| gmail         |
| hotmail       |
| outlook       |
| mail          |
+---------------+

具体地说，我希望对hosts.host不喜欢“%.maildomains.email%”中任何值的主机执行select*

Desired output:
+-------------------+-------+
|       host        | score |
+-------------------+-------+
| www.google.com    |   411 |
| stackexchange.com |   950 |
+-------------------+-------+

以下是我认为它应该如何逻辑地工作：

SELECT h.*, m.email FROM (SELECT h.* FROM hosts WHERE score > 100 as h)
h LEFT OUTER JOIN maildomains m ON (h.host LIKE CONCAT('%.',m.email,'%'))
WHERE m.email IS NULL

这将导致错误10017:在联接“%”中同时遇到左别名和右别名
我还成功地将类似的查询作为交叉连接运行而不会出错，但它会产生错误的结果：

SELECT h.*, m.email FROM (SELECT h.* FROM hosts WHERE score > 100 as h)
h CROSS JOIN maildomains m 
WHERE h.host NOT LIKE CONCAT('%.',m.email,'%')

+-------------------+---------+---------+
|      p.host       | p.score | m.email |
+-------------------+---------+---------+
| www.gmail.com     |     489 | hotmail |
| www.gmail.com     |     489 | outlook |
| www.gmail.com     |     489 | mail    |
| www.hotmail.com   |     653 | gmail   |
| www.hotmail.com   |     653 | outlook |
| www.hotmail.com   |     653 | mail    |
| www.google.com    |     411 | gmail   |
| www.google.com    |     411 | hotmail |
| www.google.com    |     411 | outlook |
| www.google.com    |     411 | mail    |
| w3.hotmail.ca     |     223 | gmail   |
| w3.hotmail.ca     |     223 | outlook |
| w3.hotmail.ca     |     223 | mail    |
| stackexchange.com |     950 | gmail   |
| stackexchange.com |     950 | hotmail |
| stackexchange.com |     950 | outlook |
| stackexchange.com |     950 | mail    |
+-------------------+---------+---------+

我感谢所有的指导。

hadoop Hive impala cloudera hiveql

来源：https://stackoverflow.com/questions/28688812/hive-query-language-return-only-values-where-not-like-a-value-in-another-table

2条答案

按热度按时间

jtjikinw1#

如果您的配置单元版本是0.13或更新版本，则可以在 WHERE 子句从 hosts table。以下是一种更通用的方法，它不需要您枚举在数据中可能找到的所有顶级域：

SELECT host, score
FROM hosts
WHERE
  regexp_extract(hosts.host, "(?:.*?\\.)?([^.]+)\\.[^.]+", 1) NOT IN
    (SELECT email FROM maildomains);

这种方法将主机域的一部分隔离在tld之前 regexp_extract 然后检查该域名是否出现在 maildomains table。

赞(0）回复(0）举报 2021-06-04

3ks5zfa02#

你可以这样做：

select host from hosts h left outer join maildomains m on (regexp_replace(regexp_replace(regexp_replace(regexp_replace(h.host,'www.',''),'.com',''),'.ca',''),'w3.','') = m.email) where email is NULL;

赞(0）回复(0）举报 2021-06-03

我来回答

hive查询语言只返回与另一个表中的值不同的值

2条答案

相关问题

热门标签

最新问答