Spark SQL row splitting on string delimiter

Question

I'm looking for a way to split some rows from a table based on a string field exactly like the problem from this post: Turning a Comma Separated string into individual rows Previous post

The issue is I can only use Queries, and Spark SQL doesn't seem to support recursive CTEs or Cross Apply so all the answers that previous post won't work in this case.

I Tried using the methods that I would use in SQL server (cross apply or recursive CTEs) but i'm not sure if Spark SQL has any way to do this without those methods.

Does this answer your question? [Spark split a column value into multiple rows](https://stackoverflow.com/questions/45171570/spark-split-a-column-value-into-multiple-rows) — nbk, May 10 '23 at 21:22
and here is more https://stackoverflow.com/questions/52912153/how-to-split-single-row-into-multiple-rows-in-spark-dataframe-using-java — nbk, May 10 '23 at 21:22

score 0 · Answer 1 · answered May 11 '23 at 05:00

Without the ability to use recursive CTEs or cross apply, splitting rows based on a string field in Spark SQL becomes more difficult.

The explode function in Spark SQL can be used to split an array or map column into multiple rows. While it do not work directly with strings, you will have to first split the string column into an array using the split function and then apply the explode function to the resulting array column.

Using the posexplode function Similar to explode, the posexplode function can be used to split an array or map column into multiple rows, but it also includes the position of each value in the array. This function can be used to split a string column by treating it as an array of characters.

For example I have created a data frame and a SQL table I have provided both the approaches enter image description here

enter image description here You have 2 approaches Using the explode function: Using the posexplode function:

The above both approaches will work in SQL and Pyspark.

Explode Function:

%sql
-- split string column using explode function
SELECT id, exploded_value
FROM fruits_table
LATERAL VIEW explode(split(fruits, ',')) exploded_table AS exploded_value;

enter image description here posexplode function:

%sql
-- split string column using posexplode function
SELECT id, pos, substr(fruits, pos, 1) AS exploded_value
FROM fruits_table
LATERAL VIEW posexplode(split(fruits, '')) exploded_table AS pos, val;

enter image description here

In Pyspark you will have to import the functions like below

from pyspark.sql import SparkSession
from pyspark.sql.functions import split, posexplode

enter image description here

split the fruits string into an array of strings
Using a new column as fruits_arr
fruits_df = fruits_df.withColumn("fruits_arr", split("fruits", ","))

enter image description here

enter image description here use posexplode to explode the fruits_arr and generate a position column

fruits_df = fruits_df.selectExpr("id", "posexplode(fruits_arr) as (pos, fruit)")

enter image description here

Spark SQL row splitting on string delimiter

1 Answers1