I have a Spark Dataframe in Databricks that I am using to save the data to an existing table:
sparkDF.write.mode("append").saveAsTable("dev.bronze_data_table")
As long as the schema between the table and the dataframe match, then everything works great. However, when the schemas don't match, then I get a mismatch error. I have very little control over the incoming schema - it could be missing columns, have mistyped column names and have extra columns.
I have over 1 billion incoming rows to process and many more rows coming everyday. I expect the vast majority of the rows will match the table schema. I would love to get the rows that don't match the table schema to be saved to a separate table. I know I can do this with up front processing (looking at column names and comparing them to the known schema), but this is a waste of resources for all the rows that match. Is there an inventive way to extract the error rows from the Spark dataframe at runtime?
Essentially, I try to write all the rows to the table. The ones that match the table schema are saved, the ones that don't fail. Then I can process only the failed rows?
Thanks.