如何在Spark/Scala中使用分隔符从另一列中分割一列

+-------------------+---------+
|             VALUES|Delimiter|
+-------------------+---------+
|       50000.0#0#0#|        #|
|          0@1000.0@|        @|
|                 1$|        $|
|1000.00^Test_string|        ^|
+-------------------+---------+

+-------------------+---------+----------------------+
|VALUES             |Delimiter|split_values          |
+-------------------+---------+----------------------+
|50000.0#0#0#       |#        |[50000.0, 0, 0, ]     |
|0@1000.0@          |@        |[0, 1000.0, ]         |
|1$                 |$        |[1, ]                 |
|1000.00^Test_string|^        |[1000.00, Test_string]|
+-------------------+---------+----------------------+

dept.select(split(col("VALUES"),"#|@|\$|\^").show()

+-----------------------+
|split(VALUES,#|@|$|^)|
+-----------------------+
|      [50000.0, 0, 0, ]|
|          [0, 1000.0, ]|
|                  [1, ]|
|   [1000.00, Test_st...|
+-----------------------+

df = spark.createDataFrame([("50000.0#0#0#","#"),("0@1000.0@","@")],["VALUES","Delimiter"])
df = df.withColumn("split", F.expr("""split(VALUES, Delimiter)"""))
df.show()
+------------+---------+-----------------+
|      VALUES|Delimiter|            split|
+------------+---------+-----------------+
|50000.0#0#0#|        #|[50000.0, 0, 0, ]|
|   0@1000.0@|        @|    [0, 1000.0, ]|
+------------+---------+-----------------+

您可以使用自定义的用户定义函数(pyspark.sql.functions.udf(来实现这一点。

from typing import List
from pyspark.sql import SparkSession
from pyspark.sql.functions import udf
from pyspark.sql.types import StringType, ArrayType

def split_col(value: StringType, delimiter: StringType) -> List[str]:
return str(value).split(str(delimiter))

udf_split = udf(lambda x, y: split_col(x, y), ArrayType(StringType()))
spark = SparkSession.builder.getOrCreate()
df = spark.createDataFrame([
('50000.0#0#0#', '#'), ('0@1000.0@', '@'), ('1$', '$'), ('1000.00^Test_string', '^')
], schema='VALUES String, Delimiter String')
df = df.withColumn("split_values", udf_split(df['VALUES'], df['Delimiter']))
df.show(truncate=False)

输出

+-------------------+---------+----------------------+
|VALUES             |Delimiter|split_values          |
+-------------------+---------+----------------------+
|50000.0#0#0#       |#        |[50000.0, 0, 0, ]     |
|0@1000.0@          |@        |[0, 1000.0, ]         |
|1$                 |$        |[1, ]                 |
|1000.00^Test_string|^        |[1000.00, Test_string]|
+-------------------+---------+----------------------+

请注意，split_values列包含字符串列表。您还可以更新split_col函数来对值进行更多更改。

编辑：Scala版本

import org.apache.spark.sql.functions.udf
import spark.implicits._
val data = Seq(("50000.0#0#0#", "#"), ("0@1000.0@", "@"), ("1$", "$"), ("1000.00^Test_string", "^"))
var df = data.toDF("VALUES", "Delimiter")
val udf_split_col = udf {(x:String,y:String)=> x.split(y)}
df = df.withColumn("split_values", udf_split_col(df.col("VALUES"), df.col("Delimiter")))
df.show(false)

编辑2

为了避免正则表达式中使用特殊字符的问题，在使用split()方法时，可以使用char而不是String，如下所示。

val udf_split_col = udf { (x: String, y: String) => x.split(y.charAt(0)) }

df.createOrReplaceTempView("test")
spark.sql("""select VALUES,delimiter,split(values,case when delimiter in ("$","^") then concat("\",delimiter) else delimiter end) as split_value from test""").show(false)

+-------------------+---------+----------------------+
|VALUES             |delimiter|split_value           |
+-------------------+---------+----------------------+
|50000.0#0#0#       |#        |[50000.0, 0, 0, ]     |
|0@1000.0@          |@        |[0, 1000.0, ]         |
|1$                 |$        |[1, ]                 |
|1000.00^Test_string|^        |[1000.00, Test_string]|
+-------------------+---------+----------------------+

import java.util.regex.Pattern
val split_udf = udf((value: String, delimiter: String) => value.split(Pattern.quote(delimiter), -1))
val solution = dept.withColumn("split_values", split_udf(col("VALUES"),col("Delimiter")))
solution.show(truncate = false)

("50000.0\0\0\", "\")