时间戳

首页 > 解决方案 > 在 Hive SQL 中 - 加入没有 UDF 的间隔

sql - 在 Hive SQL 中 - 加入没有 UDF 的间隔

问题描述

我遇到过要求将一个表中与事件相关的 IP 与另一表中的国家/地区 IP 范围相匹配的练习。即它可能看起来像这样（简化）：

table: events

event_id  |  source_ip
----------------------
12345678  |  3.15.49.5
31234314  |  7.1.8.190

table: geoips

country  |  start_ip  |  end_ip
-----------------------------------
us       |  1.0.0.0   |  1.127.255.255
us       |  1.128.0.0 |  1.255.255.255
us       |  3.0.0.0   |  3.255.255.255

我们想要得到：

event_id  |  source_ip  |  country
----------------------------------
12345678  |  3.15.49.5  |  us
31234314  |  7.1.8.190  |  uk

假设，我们可以将 IP 转换为整数以简化比较（或转换为零填充字符串，以便按字母顺序进行比较）。

所以就像一个 join on event_ip >= start_ip and event_ip <= end_ip。但是，据我了解，它在 Hive 中不会那么简单，因为“仅支持相等连接”。

最常见的建议（以及在本练习中）是使用 UDF - 据我所知，只有当包含范围的表适合内存时才有可能。

虽然我确实知道如何编写 UDF，但我对这种方法并不满意。特别是因为它没有说明如果范围表非常大（当然不是这种情况）并且不容易放入内存中该怎么办。

直观上看来，除了 Hive 之外，如果我们将两个表都按 IP 排序，我们可以一次性解决问题，维护“当前范围”并将所有即将到来的 IP 与其匹配，然后更新到下一个范围。这甚至应该很容易并行化......

所以我想知道，如果（也许，在 Hive 的更高版本中）有一个依赖于 HQL 本身的解决方案。

标签： sqljoinhivebigdatahiveql

解决方案

Hive 中不允许不等式连接。并且不带 On 条件的连接是交叉连接，它将增加行数，而应用于结果数据集的 Where 子句将过滤大量行，例如 x195（国家/地区的数量），这将导致性能问题。

但是在这种情况下，您可以尝试将 CROSS 连接转换为映射连接，因为国家/地区表适合内存。解决方案是 MAP-JOIN。小表将被广播到每个映射器，加载到内存中并用于连接。EXPLAIN 应该在计划中反映 MAP-JOIN，检查它。Mapjoin 将工作得很快。

使用 Tez、向量化执行、mapjoin 和 cbo：

set hive.cbo.enable=true;
set hive.execution.engine=tez;
set hive.vectorized.execution.enabled = true;
set hive.vectorized.execution.reduce.enabled = true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;
set hive.optimize.ppd=true;
--enable mapjoin
set hive.auto.convert.join=true;

select e.event_id, e.source_ip, i.country
    from events e 
         cross join geoips i 
   where e.event_ip >= i.start_ip and e.event_ip <= i.end_ip;

推荐阅读