使用大型查找桌

Question

问题语句：

我有两个表 - 数据(40个COL(和查找(2个COLS(。我需要在带有查找表的数据表中使用COL10来提取相关值。但是我不能让Equi加入。我需要一个基于like/contains的加入，作为查找表中的值，数据表中仅包含一个值的部分内容，而不是完整的值。因此，需要一些基于正则的匹配。

数据大小：

1. 数据表：大约23亿个条目(数据的1 tb(
2. 查找表格：大约140万个条目(50 MB数据(

方法1：

1.使用数据库(我正在使用Google Big查询( - 基于类似的连接接近3小时，但它却没有结果。我相信基于Regex的加入导致笛卡尔加入。

2. 使用Apache Beam/Spark-我试图为查找表构建一个Trie，然后将其共享/广播到Worker节点。但是，通过这种方法，我正在创建太多字符串时，我正在变得OOM。我尝试将记忆力增加到每个工人节点的4GB ，但无济于事。我正在使用Trie提取最长的匹配前缀。

我愿意使用其他技术，例如Apache Spark，Redis等。请建议我如何处理这个问题。

此处理需要日常执行，因此时间和资源都需要优化。

Answer 1

但是我不能让Equi加入

下面只是为了给您一个想法，可以探索在纯bigquery中解决您的equi join相关问题

它是基于我从您的评论中得出的假设 - 当您寻找最长的匹配从非常右到左至左的最长匹配时 - 中间的匹配不合格

该方法是将URL(Col10(和Shortened_url(Col2(字段转换，然后将((和unnest((带有保存位置

UNNEST(SPLIT(REVERSE(field), '.')) part WITH OFFSET position  

完成此操作，现在您可以进行equi join，可能会在某些扩展中解决您的问题。
因此，您可以按零件和位置加入，然后按原始URL和Shortened_url进行分组，同时只剩下那些具有相当于零件计数的匹配项的组，最后您通过URL进行了分组，并且只剩下匹配零件数量最高的入口

希望这可以有所帮助：o(

这是针对BigQuery Standard SQL

#standardSQL
WITH data_table AS (
  SELECT 'cn456.abcd.tech.com' url UNION ALL
  SELECT 'cn457.abc.tech.com' UNION ALL
  SELECT 'cn458.ab.com'
), lookup_table AS (
  SELECT 'tech.com' shortened_url, 1 val UNION ALL
  SELECT 'abcd.tech.com', 2
), data_table_parts AS (
  SELECT url, x, y
  FROM data_table, UNNEST(SPLIT(REVERSE(url), '.')) x WITH OFFSET y
), lookup_table_parts AS (
  SELECT shortened_url, a, b, val, 
    ARRAY_LENGTH(SPLIT(REVERSE(shortened_url), '.')) len
  FROM lookup_table, UNNEST(SPLIT(REVERSE(shortened_url), '.')) a WITH OFFSET b
)
SELECT url, 
  ARRAY_AGG(STRUCT(shortened_url, val) ORDER BY weight DESC LIMIT 1)[OFFSET(0)].* 
FROM (
  SELECT url, shortened_url, COUNT(1) weight, ANY_VALUE(val) val
  FROM data_table_parts d
  JOIN lookup_table_parts l
  ON x = a AND y = b
  GROUP BY url, shortened_url
  HAVING weight = ANY_VALUE(len)
)
GROUP BY url  

结果为

Row url                 shortened_url   val  
1   cn457.abc.tech.com  tech.com        1    
2   cn456.abcd.tech.com abcd.tech.com   2