How to join 2 dataframes in Spark based on a wildcard/regex condition?

Question

I have 2 dataframes df1 and df2. Suppose there is a location column in df1 which may contain a regular URL or a URL with a wildcard, e.g.:

stackoverflow.com/questions/*
*.cnn.com
cnn.com/*/politics

The seconds dataframe df2 has url field which may contain only valid URLs without wildcards.

I need to join these two dataframes, something like df1.join(df2, $"location" matches $"url") if there was magic matches operator in join conditions.

After some googling I still don't see a way how to achieve this. How would you approach solving such problem?

score 7 · Accepted Answer · answered Nov 13 '17 at 20:18

7

There exist "magic" matches operator - it is called rlike

val df1 = Seq("stackoverflow.com/questions/.*$","^*.cnn.com$", "nn.com/*/politics").toDF("location")
val df2 = Seq("stackoverflow.com/questions/47272330").toDF("url")

df2.join(df1, expr("url rlike location")).show
+--------------------+--------------------+
|                 url|            location|
+--------------------+--------------------+
|stackoverflow.com...|stackoverflow.com...|
+--------------------+--------------------+

however there are some caveats:

Patterns have to be proper regular expressions, anchored in case of leading / trailing wildcards.

It is executed with Cartesian product (How can we JOIN two Spark SQL dataframes using a SQL-esque "LIKE" criterion?):

== Physical Plan ==
BroadcastNestedLoopJoin BuildRight, Inner, url#217 RLIKE location#211
:- *Project [value#215 AS url#217]
:  +- *Filter isnotnull(value#215)
:     +- LocalTableScan [value#215]
+- BroadcastExchange IdentityBroadcastMode
   +- *Project [value#209 AS location#211]
      +- *Filter isnotnull(value#209)
         +- LocalTableScan [value#209]

It is possible to filter candidates using method I described in Efficient string matching in Apache Spark

answered Nov 13 '17 at 20:18

Alper t. Turker

34,230
9
83
115

1

Thanks, I've added an extra step to replace "\*" with ".*" and add anchors so was able to solve my problem – Alexander Arendar Nov 14 '17 at 11:10
rlike just for dataframes ? Do you know a similar way but for RDDs ? – Mariam Mar 11 '18 at 17:04
Thank you in advance for answer, this rlike operation not working with java spark. Programmatically also when I create join Expression as functions.col(“url”).rlike(functions.col(“location”)), it gives compile time error as it except literal value. – Mahendra Kapadne Jun 19 '18 at 06:44
@MahendraKapadne That's expected. I intentionally uses `expr` here. There is no `(Column, Column) => Column` variant. – Alper t. Turker Jun 19 '18 at 09:25
@user8371915 yes you are correct but if I write functions.expr(“url rlike location”); then it compiles but it throws runtime exception. I am stuck here to use spark joins with java ☹️ – Mahendra Kapadne Jun 19 '18 at 11:10
@MahendraKapadne What is the exception? – Alper t. Turker Jun 19 '18 at 11:23
Hi Can you please tell me an equivalent query for HIVE as well. – Akshat Chaturvedi Feb 15 '19 at 15:11
This doesnt work if the string contains parenthesis. The `rlike` method neglects those. How do we include parentheses when using `rlike` ? – thentangler Mar 19 '21 at 21:28

How to join 2 dataframes in Spark based on a wildcard/regex condition?

1 Answers1

Linked

Related