SQL - How can I clone my rows, changing only one field?

user_13130155 0
Viewed 34

I am using SQL with pyspark and hive, and I'm new to all of it. I have a problem in my hands that I don't know how to solve.

If I have a table "People" , like this:

id |  name   | other_names
1  |  Alice  | Sarah;Tom
2  |  Bob    | Jane;Michael;Ben
3  | Lizzie  | John

The number of names in "other_names" column is variable. It can be 1,2,3,....

I want to create a query to obtain this:

id   |  name
1    |  Alice
1    |  Sarah
1    |  Tom
2    |  Bob 
2    |  Jane
2    |  Michael
2    |  Ben
3    |  Lizzie
3    |  John

Is there a not too complicated way to do this?

Thank you so so much in advance, and happy coding :D

pyspark-sql pyspark hive csv sql
2 Answers

To split the csv string into rows, you can make a lateral join and use split() and explode():

select t.id, n.other_name
from mytable t
lateral view explode(split(t.other_names, ';')) n as other_name

If you also wanted the primary name:

select id, name from mytable
union all
select t.id, n.other_name
from mytable t
lateral view explode(split(t.other_names, ';')) n as other_name
user_106767160

Starting from Spark-2.4+:

We can use array_union to make names,other_names columns into one array.

  • Then explode the column to create name column.
  • We don't have to union all two tables/dataframes

Example:

df=spark.createDataFrame([("1","Alice","Sarah;Tom"),("2","Bob","Jane;Micheal;Ben"),("3","Lizzie","John")],["id","name","other_names"])

from pyspark.sql.functions import *

df.withColumn("new",array_union(array(col("name")),split(col("other_names"),";"))).\
select("id",explode("new").alias("name")).\
show()

#+---+-------+
#| id|   name|
#+---+-------+
#|  1|  Alice|
#|  1|  Sarah|
#|  1|    Tom|
#|  2|    Bob|
#|  2|   Jane|
#|  2|Micheal|
#|  2|    Ben|
#|  3| Lizzie|
#|  3|   John|
#+---+-------+
user_76326950
Related