Find out substring from url/value of a key from url

user_16405245 0
Viewed 333

I have a table which has url column

I need to find out all the values correspond to tag

TableA

#+---------------------------------------------------------------------+
#|   url                                                               |
#+---------------------------------------------------------------------+
#|   https://www.amazon.in/primeday?tag=final&value=true               | 
#|   https://www.filipkart.in/status?tag=presubmitted&Id=124&key=2     | 
#|   https://www.google.com/active/search?tag=inreview&type=addtional  |
#|   https://www.google.com/filter/search?&type=nonactive              |

output

#+------------------+
#|   Tag            |
#+------------------+
#|   final          | 
#|   presubmitted   | 
#|   inreview       |

I am able to do it in spark sql via below

 spark.sql("""select parse_url(url,'QUERY','tag') as Tag from TableA""")

Any option via dataframe or regular expression.

pyspark apache-spark-sql apache-spark
3 Answers

PySpark:

df \
  .withColumn("partialURL", split("url", "tag=")[1]) \
  .withColumn("tag", split("partialURL", "&")[0]) \
  .drop("partialURL")
user_59938540

You can try the below implementation -

    val spark = SparkSession.builder().appName("test").master("local[*]").getOrCreate()
      import spark.implicits._
      val extract: String => String = StringUtils.substringBetween(_,"tag=","&")
      val parse = udf(extract)
      val urlDS = Seq("https://www.amazon.in/primeday?tag=final&value=true",
                      "https://www.filipkart.in/status?tag=presubmitted&Id=124&key=2",
                      "https://www.google.com/active/search?tag=inreview&type=addtional",
                      "https://www.google.com/filter/search?&type=nonactive").toDS
    
      urlDS.withColumn("tag",parse($"value")).show()


+----------------------------------------------------------------+------------+
|value                                                           |tag         |
+----------------------------------------------------------------+------------+
|https://www.amazon.in/primeday?tag=final&value=true             |final       |
|https://www.filipkart.in/status?tag=presubmitted&Id=124&key=2   |presubmitted|
|https://www.google.com/active/search?tag=inreview&type=addtional|inreview    |
|https://www.google.com/filter/search?&type=nonactive            |null        |
+----------------------------------------------------------------+------------+
user_54622590

The fastest solution is likely substring based, similar to Pardeep's answer. An alternative approach is to use a regex that does some light input checking, similar to:

^(?:(?:(?:https?|ftp):)?\/\/).+?tag=(.*?)(?:&.*?$|$)

This checks that the string starts with a http/https/ftp protocol, the colon and slashes, at least one character (lazily), and either tag=<string of interest> appears somewhere in the middle or at the very end of the string.

Visually (courtesy of regex101), the matches look like: An image showing different test strings, where only proper URLs containing tag= match.

The tag values you want are in capture group 1, so if you use regexp_extract (PySpark docs), you'll want to use idx of 1 to extract them.

The main difference between this answer and Pardeep's is that this one won't extract values from strings that don't conform to the regex, e.g. the last string in the image above doesn't match. In these edge cases, regexp_extract will return a NULL, which you can process as you wish afterwards.

Since we're invoking a regex engine, this approach is likely a little slower, but the performance difference might be imperceptible in your application.

user_26728030
Related