Create a single schema dataframe when reading multiple csv files under a directory

Viewed 1053

I have thousands of CSV files that have similar but non-identical headers under a single directory. The structure is as follow:

path/to/files/unique_parent_directory/*.csv

One csv file can be:

|Column_A|Column_B|Column_C|Column_D|
|V1 |V2 |V3 |V4 |

The second CSV file can be:

|Coulmn_A|Coulmn_B|Coulmn_E|Coulmn_F|
|V5 |V6 |V7 |V8 |

The result I want to create is a single Spark Dataframe that merges the files correctly without overlapping columns, the output for the previous example should be like this:

|Column_A|Column_B|Column_C|Column_D|Coulmn_E|Coulmn_F|
|V1 |V2 |V3 |V4 |Null |Null |
|V5 |V6 |Null |Null |V7 |V8 |

The code I am using to create the dataframes is:

val df = sparkSession.read
          .format("csv")
          .option("header", "true")
          .option("inferSchema", "true")
          .option("mergeSchema", "true")
          .load(path/to/files/unique_parent_directory/*.csv)
          .persist(StorageLevel.MEMORY_AND_DISK_SER)

But I get the following result:

|Column_A|Column_B|Column_C|Column_D|
|V1 |V2 |V3 |V4 |
|V5 |V6 |V7 |V8 |

Is there a way to obtain the desired dataframe without running a header unification process?

0 Answers
Related