How do I create and perform set operations using Spark SQL?

user_5865514 0
Viewed 359

I've used Spark SQL to create an array of IDs called todays_ids and previous_days_ids. I'd like to be able to use Spark SQL directly to convert these arrays of IDs into sets, and then calculate the difference between one column's IDs and another column's IDs. So far, I've used a UDF:

df = spark.sql("""
... query to generate today and previous day's ID array columns
""")

# udf that calculates a size of the difference between two sets 
differencer=udf(lambda x,y: len(set(x)-set(y)), IntegerType())

df = df.withColumn('difference', differencer('todays_ids', 'previous_days_ids'))
df.createOrReplaceTempView("differences")

I can then take the difference column and compute a "freshness" score for how much the IDs change each day relative to the previous day.

Is there a way I can accomplish this via Spark SQL, and not using UDFs? I can't find anything in the Spark SQL documentation about using sets.

pyspark apache-spark-sql apache-spark python
1 Answers

size and array_except should be equivalent to your operation of len(set(x)-set(y)):

import pyspark.sql.functions as F

df2 = df.withColumn('difference', F.size(F.array_except('todays_ids', 'previous_days_ids')))
user_141657300
Related