CODEWITHSUNDEEP
apache-sparkpysparkapache-spark-sql
Charles Du
Charles Du

Reputation: 406

Spark union column order

I've come across something strange recently in Spark. As far as I understand, given the column based storage method of spark dfs, the order of the columns really don't have any meaning, they're like keys in a dictionary.

During a df.union(df2), does the order of the columns matter? I would've assumed that it shouldn't, but according to the wisdom of sql forums it does.

So we have df1

df1
|  a|   b|
+---+----+
|  1| asd|
|  2|asda|
|  3| f1f|
+---+----+

df2
|   b|  a|
+----+---+
| asd|  1|
|asda|  2|
| f1f|  3|
+----+---+

result
|   a|   b|
+----+----+
|   1| asd|
|   2|asda|
|   3| f1f|
| asd|   1|
|asda|   2|
| f1f|   3|
+----+----+

It looks like the schema from df1 was used, but the data appears to have joined following the order of their original dataframes. Obviously the solution would be to do df1.union(df2.select(df1.columns))

But the main question is, why does it do this? Is it simply because it's part of pyspark.sql, or is there some underlying data architecture in Spark that I've goofed up in understanding?

code to create test set if anyone wants to try

d1={'a':[1,2,3], 'b':['asd','asda','f1f']}
d2={ 'b':['asd','asda','f1f'], 'a':[1,2,3],}
pdf1=pd.DataFrame(d1)
pdf2=pd.DataFrame(d2)
df1=spark.createDataFrame(pdf1)
df2=spark.createDataFrame(pdf2)
test=df1.union(df2)

Upvotes: 38

Views: 33903

Answers (2)

cronoik
cronoik

Reputation: 19365

The Spark union is implemented according to standard SQL and therefore resolves the columns by position. This is also stated by the API documentation:

Return a new DataFrame containing union of rows in this and another frame.

This is equivalent to UNION ALL in SQL. To do a SQL-style set union (that does >deduplication of elements), use this function followed by a distinct.

Also as standard in SQL, this function resolves columns by position (not by name).

Since Spark >= 2.3 you can use unionByName to union two dataframes were the column names get resolved.

Upvotes: 53

Aaron
Aaron

Reputation: 686

in spark Union is not done on metadata of columns and data is not shuffled like you would think it would. rather union is done on the column numbers as in, if you are unioning 2 Df's both must have the same numbers of columns..you will have to take in consideration of positions of your columns previous to doing union. unlike SQL or Oracle or other RDBMS, underlying files in spark are physical files. hope that answers your question

Upvotes: 14

Related Questions