Spark SQL中的数组交集 [英] Array Intersection in Spark SQL

问题描述

我有一个表，该表的数组类型列名为writer，其值类似于array[value1, value2]，array[value2, value3] ...等.

I have a table with a array type column named writer which has the values like array[value1, value2], array[value2, value3].... etc.

我正在执行self join以获得数组之间具有公共值的结果.我试过了:

I am doing self join to get results which have common values between arrays. I tried:

sqlContext.sql("SELECT R2.writer FROM table R1 JOIN table R2 ON R1.id != R2.id WHERE ARRAY_INTERSECTION(R1.writer, R2.writer)[0] is not null ")

还有

sqlContext.sql("SELECT R2.writer FROM table R1 JOIN table R2 ON R1.id != R2.id WHERE ARRAY_INTERSECT(R1.writer, R2.writer)[0] is not null ")

但是有同样的例外:

线程主要" org.apache.spark.sql.AnalysisException中的异常: 未定义的函数:"ARRAY_INTERSECT".此功能既不是 已注册的临时功能或已注册的永久功能 数据库默认".第1行pos 80

Exception in thread "main" org.apache.spark.sql.AnalysisException: Undefined function: 'ARRAY_INTERSECT'. This function is neither a registered temporary function nor a permanent function registered in the database 'default'.; line 1 pos 80

Spark SQL可能不支持ARRAY_INTERSECTION和ARRAY_INTERSECT.如何在Spark SQL中实现我的目标?

Probably Spark SQL does not support ARRAY_INTERSECTION and ARRAY_INTERSECT. How can I achieve my goal in Spark SQL?

推荐答案

您将需要udf:

import org.apache.spark.sql.functions.udf

spark.udf.register("array_intersect", 
  (xs: Seq[String], ys: Seq[String]) => xs.intersect(ys))

，然后检查交集是否为空:

and then check if intersection is empty:

scala> spark.sql("SELECT size(array_intersect(array('1', '2'), array('3', '4'))) = 0").show
+-----------------------------------------+
|(size(UDF(array(1, 2), array(3, 4))) = 0)|
+-----------------------------------------+
|                                     true|
+-----------------------------------------+


scala> spark.sql("SELECT size(array_intersect(array('1', '2'), array('1', '4'))) = 0").show
+-----------------------------------------+
|(size(UDF(array(1, 2), array(1, 4))) = 0)|
+-----------------------------------------+
|                                    false|
+-----------------------------------------+

这篇关于Spark SQL中的数组交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！