What is going wrong with `unionAll` of Spark `DataFrame`?

by

It doesn’t look like a bug at all. What you see is a standard SQL behavior and every major RDMBS, including PostgreSQL, MySQL, Oracle and MS SQL behaves exactly the same. You’ll find SQL Fiddle examples linked with names.

To quote PostgreSQL manual:

In order to calculate the union, intersection, or difference of two queries, the two queries must be “union compatible”, which means that they return the same number of columns and the corresponding columns have compatible data types

Column names, excluding the first table in the set operation, are simply ignored.

This behavior comes directly form the Relational Algebra where basic building block is a tuple. Since tuples are ordered an union of two sets of tuples is equivalent (ignoring duplicates handling) to the output you get here.

If you want to match using names you can do something like this

import org.apache.spark.sql.DataFrame
import org.apache.spark.sql.functions.col

def unionByName(a: DataFrame, b: DataFrame): DataFrame = {
  val columns = a.columns.toSet.intersect(b.columns.toSet).map(col).toSeq
  a.select(columns: _*).unionAll(b.select(columns: _*))
}

To check both names and types it is should be enough to replace columns with:

a.dtypes.toSet.intersect(b.dtypes.toSet).map{case (c, _) => col(c)}.toSeq

More Related Contents:

  1. How to query JSON data column using Spark DataFrames?
  2. How to define partitioning of DataFrame?
  3. How do I detect if a Spark DataFrame has a column
  4. Caused by: java.lang.NullPointerException at org.apache.spark.sql.Dataset
  5. How to split a dataframe into dataframes with same column values?
  6. Provide schema while reading csv file as a dataframe
  7. Dropping a nested column from Spark DataFrame
  8. Spark Dataframe :How to add a index Column : Aka Distributed Data Index
  9. Defining a UDF that accepts an Array of objects in a Spark DataFrame?
  10. How to zip two (or more) DataFrame in Spark
  11. Joining Spark dataframes on the key
  12. Apache Spark how to append new column from list/array to Spark dataframe
  13. Create new Dataframe with empty/null field values
  14. Provide schema while reading csv file as a dataframe in Scala Spark
  15. Renaming column names of a DataFrame in Spark Scala
  16. Derive multiple columns from a single column in a Spark DataFrame
  17. Replace missing values with mean – Spark Dataframe
  18. Apache Spark, add an “CASE WHEN … ELSE …” calculated column to an existing DataFrame
  19. How to get keys and values from MapType column in SparkSQL DataFrame
  20. Spark DataFrames when udf functions do not accept large enough input variables
  21. Filter spark DataFrame on string contains
  22. Spark – load CSV file as DataFrame?
  23. Filling gaps in timeseries Spark
  24. How to use COGROUP for large datasets
  25. Spark Dataframes UPSERT to Postgres Table
  26. What are possible reasons for receiving TimeoutException: Futures timed out after [n seconds] when working with Spark [duplicate]
  27. Why does join fail with “java.util.concurrent.TimeoutException: Futures timed out after [300 seconds]”?
  28. Perform a typed join in Scala with Spark Datasets
  29. Spark: Add column to dataframe conditionally
  30. Why is join not possible after show operator?

Leave a Comment