Spark将DataFrame字符串列拆分为固定数量新列的技术问询

Let's dive into your current implementation and figure out how to make it work correctly and efficiently for your use case.

Current Implementation: Rationality Check #

Your core approach—using split() to break the string into an array, then expanding that array into fixed columns with getItem()—is logically sound for splitting a string into a fixed number of columns. However, there's a critical issue with your regex pattern that will likely lead to incorrect splitting results.

Looking at your regex: \\[|\\[.*?\\]\\|(^;\\])
This pattern matches three separate cases:

1. A single opening bracket [
2. A full [...]+| sequence (a bracket-enclosed segment followed by a pipe)
3. A start-of-string ;] (which doesn't even appear in your sample input)

This will not correctly split on the outer pipes that are not inside brackets—which is what you actually need for your sample string structure. For example, your input has segments like [ts1:tssub2|ts1:tssub2] that should stay as a single element, but your current regex will split this into parts, breaking the intended structure.

Optimization & Fixes #

Here are two robust approaches to solve this problem correctly:

1. Use a Custom UDF for Nested Bracket Handling #

Since Spark's built-in split() doesn't support balanced regex groups (to handle nested brackets), a UDF is the most reliable way to split on outer pipes while preserving bracket-enclosed content.

import org.apache.spark.sql.functions.udf
import org.apache.spark.sql.Column

// UDF to split string on | that are NOT inside nested []
val splitOuterPipe = udf((input: String) => {
  var bracketDepth = 0
  val currentSegment = new StringBuilder()
  val segments = scala.collection.mutable.ListBuffer[String]()

  for (char <- input) {
    char match {
      case '[' => bracketDepth += 1; currentSegment.append(char)
      case ']' => bracketDepth -= 1; currentSegment.append(char)
      case '|' if bracketDepth == 0 =>
        segments += currentSegment.toString().trim
        currentSegment.clear()
      case _ => currentSegment.append(char)
    }
  }
  // Add the final segment after loop ends
  if (currentSegment.nonEmpty) segments += currentSegment.toString().trim
  
  // Ensure we have exactly 12 elements (pad with null if needed)
  segments.padTo(12, null).toArray
})

// Apply the UDF and expand to columns
val dfSplitted = df
  .withColumn("splc", splitOuterPipe(col("c_001")))
  .select(
    col("*") +: (0 until 12).map(i => col("splc").getItem(i).as(s"spl_c$i")): _*
  )
  .drop("splc", "c_001")

2. Hacky Regex Workaround (If UDFs Are Not Preferred) #

If you want to avoid UDFs, you can temporarily replace pipes inside brackets with a unique placeholder, split on the remaining pipes, then restore the original pipes. This works only if your placeholder character doesn't exist in the input:

import org.apache.spark.sql.functions.{regexp_replace, split, col}

// Step 1: Replace | inside [] with a placeholder (e.g., ###)
val tempReplaced = df.withColumn(
  "temp_str",
  regexp_replace(col("c_001"), "\\[(.*?)\\]", m => m.group(1).replace("|", "###"))
)

// Step 2: Split on outer |, then restore the original | in each segment
val dfSplitted = tempReplaced
  .withColumn("splc", split(col("temp_str"), "\\|"))
  .select(
    col("*") +: (0 until 12).map(i => 
      regexp_replace(col("splc").getItem(i), "###", "|").as(s"spl_c$i")
    ): _*
  )
  .drop("splc", "c_001", "temp_str")

Note: This regex approach works for simple nested brackets but may fail if you have deeply nested or unbalanced brackets. The UDF is more reliable for complex cases.

Additional Efficiency Tips #

• If your input strings are guaranteed to have at least 12 segments, you can remove the padTo(12, null) in the UDF to save a tiny bit of overhead.
• For very large datasets, UDFs can be slower than built-in functions—so if your bracket structure is simple, the regex workaround might be faster.
• Consider adding a check to verify that the split produces at least 12 segments (if required) using size(col("splc")) >= 12 to avoid unexpected nulls.

内容的提问来源于stack exchange，提问作者Robert Knox