Split a column in multiple columns using Spark SQL

Question

I have a column col1 that represents a GPS coordinate format:

25 4.1866N 55 8.3824E

I would like to split it in multiple columns based on white-space as separator, as in the output example table_example below:

| 1st_split  | 2nd_split   | 3rd_split    | 4th_split    |
|:-----------|------------:|:------------:|:------------:|
|    25      |   4.1866N   |     55       |     8.3824E  |

Considering the fact that there is the split() function, I have tried in this way:

SELECT explode(split(`col1`, ' ')) AS `col` FROM table_example;

But, instead of splitting per multiple columns, it splits per multiple rows, like in the output below:

Can someone clarify me which would be the worth approach for get the expected result?

Anahcolus · Accepted Answer · 2018-03-21 16:11:19Z

4

If you have a dataframe as

+---------------------+
|col                  |
+---------------------+
|25 4.1866N 55 8.3824E|
+---------------------+

Using Scala API

You can simply use split inbuilt function and select appropriately as

import org.apache.spark.sql.functions._
df.withColumn("split", split(col("col"), " "))
    .select(col("split")(0).as("1st_split"), col("split")(1).as("2nd_split"),col("split")(2).as("3rd_split"),col("split")(3).as("4th_split"))
  .show(false)

which would give you

+---------+---------+---------+---------+
|1st_split|2nd_split|3rd_split|4th_split|
+---------+---------+---------+---------+
|25       |4.1866N  |55       |8.3824E  |
+---------+---------+---------+---------+

Using SQL way

Sql is much easier and similar to the api way

df.createOrReplaceTempView("table_example")
val splitted = sqlContext.sql("SELECT split(`col`, ' ') AS `col` FROM table_example")

splitted.createOrReplaceTempView("splitted_table")
val result = sqlContext.sql("SELECT `col`[0] AS `1st_split`, `col`[1] AS `2nd_split`, `col`[2] AS `3rd_split`, `col`[3] AS `4th_split` FROM splitted_table")
result.show(false)

I hope the answer is helpful

edited Mar 21, 2018 at 16:11

answered Mar 21, 2018 at 15:42

Anahcolus

42.1k6 gold badges75 silver badges101 bronze badges

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

6 Comments

UgoL Over a year ago

But this is slightly different from a standard SQL approach. I mean... I cannot use that syntax within a SQL context.

UgoL Over a year ago

On your answer you're using pyspark syntax for dataframe I supposed.

UgoL Over a year ago

Assuming that you cannot help me to solve the problem on SQL side, do you have an idea of how the same scala snippet would be represented using java, or python syntax?

UgoL Over a year ago

Thanks for the SQL example! If you would provide me also a sort of java example it would be great. Cause the Scala example is not so useful for my case/scenario. I supposed that they would be quite similar.

UgoL Over a year ago

This is what I was searching for (just plain SQL within just one query): SELECT col[0] AS 1st_split, col[1] AS 2nd_split, col[2] AS 3rd_split, col[3] AS 4th_split FROM ( SELECT split(col, ' ') AS col FROM example_table )... but you suggested me the right way with your SQL example :-)

|

Collectives™ on Stack Overflow

Split a column in multiple columns using Spark SQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related