Hive - LIKE 运算符 [英] Hive - LIKE Operator

问题描述

我不知道我是如何处理这个问题的:

I can not figure out how I deal with that problem:

这是我的数据:

Table1:         Table2:
BRAND           PRODUCT           SOLD
Sony            Sony ABCD         1233
Apple           Sony adv          1233
Google          Sony aaaa         1233
IBM             Apple 123         1233
etc.            Apple 345         1233
                IBM 13123         1233

是否可以过滤查询，我有一个表格，其中包含品牌和总销售额?我的想法是:

Is it possible to filter the query that I have a table where stands the brand and the total solds? My idea is:

Select table1.brand, sum(table2.sold) from table1
join table2
on (table1.brand LIKE '%table2.product%')
group by table.1.brand

这是我的想法，但我总是收到错误

That was my idea, but i always get an Error

最大的问题是Like-Operator还是有其他解决方案?

The biggest problem is the Like-Operator or is there any other solution?

推荐答案

我看到两个问题:首先，hive 中的 JOIN 仅适用于相等条件，而 like 不会在那里工作.

I see two issues: First of all, JOINs in hive only work with equality conditions, that like isn't going to work there.

https://cwiki.apache.org/confluence/display/Hive/语言手册+加入

Hive 中仅支持等式连接、外连接和左半连接.Hive 不支持非等式条件的连接条件，因为很难将此类条件表达为 map/reduce 作业.

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.

相反，它想进入 where 子句.

Instead, that wants to go into a where clause.

其次，我也发现 like 语句本身存在问题:'%table2.product%' 被解释为字面上的字符串 '%table2.product%'.此外，即使这是按预期进行的，它也会尝试在品牌内部寻找 table2.product，当您似乎想要其他方式时.为了得到你想要的评价，你需要在table1.brand的内容中添加通配符；为此，您需要将通配符连接到表达式中.

Secondly, I also see a problem with the like statement itself: '%table2.product%' is being interpreted as literally the string '%table2.product%'. Additionally, even if this was doing what was intended, it would try to look for table2.product inside of brand, when you seem to want it the other way. To get the evaluation you intended, you need to add the wildcard to the contents of table1.brand; to accomplish this, you want to concatenate your wildcards into your expression.

table2.product LIKE concat('%',table1.brand,'%'))

通过这样做，您喜欢的将评估字符串 '%Sony%'、'%Apple%'...等而不是 '%table2.product%'.

By doing this, your like will evaluate for strings '%Sony%', '%Apple%'...etc instead of '%table2.product%'.

您想要的是 Brandon Bell 的查询，我已将其合并到此答案中:

What you want is Brandon Bell's query, which I've merged into this answer:

SELECT table1.brand, SUM(table2.sold) 
FROM table1, table2
WHERE table2.product LIKE concat('%', table1.brand, '%') 
GROUP BY table1.brand;

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