For a set of dataframes
val df1 = sc.parallelize(1 to 4).map(i => (i,i*10)).toDF("id","x")
val df2 = sc.parallelize(1 to 4).map(i => (i,i*100)).toDF("id","y")
val df3 = sc.parallelize(1 to 4).map(i => (i,i*1000)).toDF("id","z")
to union all of them I do
df1.unionAll(df2).unionAll(df3)
Is there a more elegant and scalable way of doing this for any number of dataframes, for example from
Seq(df1, df2, df3)
For pyspark you can do the following:
from functools import reduce
from pyspark.sql import DataFrame
dfs = [df1,df2,df3]
df = reduce(DataFrame.unionAll, dfs)
It's also worth noting that the order of all the columns in all the dataframes in the list should be the same for this to work. This can silently give unexpected results if you don't have the correct column orders!!
If you are using pyspark 2.3 or greater, you can use unionByName so you don't have to reorder the columns.
reduce means that the operations don't occur in parallel though.. correct?
allowMissingColumns=True?
The simplest solution is to reduce with union (unionAll in Spark < 2.0):
val dfs = Seq(df1, df2, df3)
dfs.reduce(_ union _)
This is relatively concise and shouldn't move data from off-heap storage but extends lineage with each union requires non-linear time to perform plan analysis. what can be a problem if you try to merge large number of DataFrames.
You can also convert to RDDs and use SparkContext.union:
dfs match {
case h :: Nil => Some(h)
case h :: _ => Some(h.sqlContext.createDataFrame(
h.sqlContext.sparkContext.union(dfs.map(_.rdd)),
h.schema
))
case Nil => None
}
It keeps lineage short analysis cost low but otherwise it is less efficient than merging DataFrames directly.
You can add parameters like allowMissingColumns by using reduce with lambda
from functools import reduce
from pyspark.sql import DataFrame
dfs = [df1, df2]
df = reduce(lambda x, y: x.unionByName(y, allowMissingColumns=True), dfs)
Under the Hood spark flattens union expressions. So it takes longer when the Union is done linearly.
The best solution is spark to have a union function that supports multiple DataFrames.
But the following code might speed up the union of multiple DataFrames (or DataSets)somewhat.
def union[T : ClassTag](datasets : TraversableOnce[Dataset[T]]) : Dataset[T] = {
binaryReduce[Dataset[T]](datasets, _.union(_))
}
def binaryReduce[T : ClassTag](ts : TraversableOnce[T], op: (T, T) => T) : T = {
if (ts.isEmpty) {
throw new IllegalArgumentException
}
var array = ts toArray
var size = array.size
while(size > 1) {
val newSize = (size + 1) / 2
for (i <- 0 until newSize) {
val index = i*2
val index2 = index + 1
if (index2 >= size) {
array(i) = array(index) // last remaining
} else {
array(i) = op(array(index), array(index2))
}
}
size = newSize
}
array(0)
}
In case some dataframes have missing columns, one can used a partially applied function:
from functools import reduce
from pyspark.sql import DataFrame
# Union dataframes by name (missing columns filled with null)
union_by_name = partial(DataFrame.unionByName, allowMissingColumns=True)
df_output = reduce(union_by_name, [df1, df2, ...])
