spark regex while join data frame

Question

I need to write some regex for condition check in spark while doing some join,

My regex should match below string

n3_testindia1 = test-india-1
n2_stagamerica2 = stag-america-2
n1_prodeurope2 = prod-europe-2

df1.select("location1").distinct.show()

+----------------+
|    location1   |
+----------------+
|n3_testindia1   |
|n2_stagamerica2 |
|n1_prodeurope2  |

df2.select("loc1").distinct.show()

+--------------+
|      loc1    |
+--------------+
|test-india-1  |   
|stag-america-2|
|prod-europe-2 |
+--------------+

I want to join based on location columns like below

val joindf = df1.join(df2, df1("location1") == regex(df2("loc1")))

Tom Lous · Accepted Answer · 2018-12-21 07:05:57Z

1

Based on the information above you can do that in Spark 2.4.0 using

val joindf = df1.join(df2, 
  regexp_extract(df1("location1"), """[^_]+_(.*)""", 1) 
    === translate(df2("loc1"), "-", ""))

Or in prior versions something like

val joindf = df1.join(df2, 
  df1("location1").substr(lit(4), length(df1("location1")))
    === translate(df2("loc1"), "-", ""))

answered Dec 21, 2018 at 7:05

Tom Lous

2,9093 gold badges30 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

stack0114106 · Accepted Answer · 2018-12-21 18:16:18Z

You can split by "_" in location1 and take the 2 element, then match with the entire string of "-" removed string in loc1. Check this out:

scala> val df1 = Seq(("n3_testindia1"),("n2_stagamerica2"),("n1_prodeurope2")).toDF("location1")
df1: org.apache.spark.sql.DataFrame = [location1: string]

scala> val df2 = Seq(("test-india-1"),("stag-america-2"),("prod-europe-2")).toDF("loc1")
df2: org.apache.spark.sql.DataFrame = [loc1: string]

scala> df1.join(df2,split('location1,"_")(1) === regexp_replace('loc1,"-",""),"inner").show
+---------------+--------------+
|      location1|          loc1|
+---------------+--------------+
|  n3_testindia1|  test-india-1|
|n2_stagamerica2|stag-america-2|
| n1_prodeurope2| prod-europe-2|
+---------------+--------------+


scala>

Collectives™ on Stack Overflow

spark regex while join data frame

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related