PySpark regex join

user_2572341 0
Viewed 161

I have a Pyspark dataframe, that needs to be joined with another dataframe, based on a string column. For eg.

(bob1, "a.b.*.c") (bob2, "a.b.c")

when joined with

(tom1, "a.b.d.c") (tom2, "a.b.c")

on the second column (the pattern), should give: (bob1, tom1) (bob2, tom2). I understand this can be done using rlike but for for that I need to transform the pattern column into an actual regex. So

  • a.b.*.c becomes ^a.b.(\w+).c$
  • a.b.c becomes ^a.b.c$

Im having trouble doing this conversion. I tried using regex_replace(), but due to having \ in the output, it inserts \ twice instead of once.

pyspark apache-spark python regex
1 Answers

regexp_replace can be used:

df1 = spark.createDataFrame([
    ("bob1", "a.b.*.d"),("bob2","a.b.c")], ["col1", "col2"])
df2 = spark.createDataFrame([
    ("tom1", "a.b.c.d"),("tom2","a.b.c")], ["col3", "col4"])
df1 = df1.withColumn("join_col", F.concat(F.lit("^"), F.regexp_replace(F.col("col2"), "\\*", "(\\\\w+)"), F.lit("$")))
df_joined = df1.join(df2, F.expr("col4 rlike join_col"))
df_joined.show()

prints

+----+-------+-------------+----+-------+
|col1|   col2|     join_col|col3|   col4|
+----+-------+-------------+----+-------+
|bob1|a.b.*.d|^a.b.(\w+).d$|tom1|a.b.c.d|
|bob2|  a.b.c|      ^a.b.c$|tom2|  a.b.c|
+----+-------+-------------+----+-------+

The parentheses around \w+ could be omitted.

Unfortunately, df_joined.explain() shows that the rlike join causes a CartesianProduct:

== Physical Plan ==
CartesianProduct col4#5 RLIKE join_col#26
:- *(1) Project [col1#0, col2#1, concat(^, regexp_replace(col2#1, \*, (\\w+)), $) AS join_col#26]
:  +- *(1) Filter isnotnull(concat(^, regexp_replace(col2#1, \*, (\\w+)), $))
:     +- *(1) Scan ExistingRDD[col1#0,col2#1]
+- *(2) Filter isnotnull(col4#5)
   +- *(2) Scan ExistingRDD[col3#4,col4#5]
user_21298010
Related