Apache Spark: how to transform Data Frame column with regex to another Data Frame?

Question

I have Spark Data Frame 1 of several columns: (user_uuid, url, date_visit)

I want to transform this DF1 to Data Frame 2 with form : (user_uuid, domain, date_visit)

What I wanted to use is regular expression to detect domain and apply it to DF1 val regexpr = """(?i)^((https?):\/\/)?((www|www1)\.)?([\w-\.]+)""".r

Could you please help me composing code to transform Data Frames in Scala? I am completely new to Spark and Scala and syntax is hard. Thanks!

" I am completely new to Spark and Scala " This is very much a "give me the code" question currently. What have you tried? How are you planning to learn Spark/Scala? SO is best used by trying something yourself and asking specific questions when you are stuck. As you should know, as you've been here for 4 years! — The Archetypal Paul
– The Archetypal Paul, Commented Aug 20, 2015 at 15:21
@Paul, this is really an atomic operation, but it unable to find it over the internet. When you ask for, say, regex, you don't present the ugly tries, right? I evaluated the code I've got and saw it makes no sense to have it here - it's defining a DF solely, how could it have helped? Yes, I am here for 4 years already, with plenty of other experience, lecturing me may feel pleasant, but in this question I don't find it appropriate. — snowindy
– snowindy, Commented Aug 20, 2015 at 16:57
I would be more convinced with evidence you'd looked through the scaladoc for DataFrame. There aren't really many operations there, and even fewer that return a DataFrame. If your question had been around "how to I use select for this", it would have been clear you had put some work in, not just thrown this over the wall to SO. I'm not (intentionally) lecturing, but I'm trying to do my tiny bit to encourage people to do the work necessary to avoid wasting the time of the man y others who might read a question.Got two comment upvotes too, so looks like I'm not completely isolated on this — The Archetypal Paul
– The Archetypal Paul, Commented Aug 20, 2015 at 17:03
@TheArchetypalPaul Over time, I see answer for this question helped at least 9 people who upvoted it. It proves it does not matter who I should "convince" about my clumsy tries before asking, as question is helpful for community anyway. — snowindy
– snowindy, Commented Aug 16, 2017 at 6:58

zero323 · Accepted Answer · 2017-08-15 10:56:46Z

19

Spark >= 1.5:

You can use regexp_extract function:

import org.apache.spark.sql.functions.regexp_extract

val patter: String = ??? 
val groupIdx: Int = ???

df.withColumn("domain", regexp_extract(url, pattern, groupIdx))

Spark < 1.5.0

Define an UDF

val pattern: scala.util.matching.Regex = ???

def getFirst(pattern: scala.util.matching.Regex) = udf(
  (url: String) => pattern.findFirstIn(url) match { 
    case Some(domain) => domain
    case None => "unknown"
  }
)

Use defined UDF:

df.select(
  $"user_uuid",
  getFirst(pattern)($"url").alias("domain"),
  $"date_visit"
)

or register temp table:

df.registerTempTable("df")

sqlContext.sql(s"""
  SELECT user_uuid, regexp_extract(url, '$pattern', $group_idx) AS domain, date_visit FROM df""")

Replace pattern with a valid Java regexp and group_id with an index of the group.

edited Aug 15, 2017 at 10:56

answered Aug 20, 2015 at 15:33

zero323

331k108 gold badges982 silver badges958 bronze badges

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

1 Comment

Vasile Surdu Over a year ago

it worked, just don't forget to add before ' before regex and ' after.

Collectives™ on Stack Overflow

Apache Spark: how to transform Data Frame column with regex to another Data Frame?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related