cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Can we assigee default value in select columns in Spark sql when the column is not present?

Manojkumar
New Contributor II

Im reading avro file and loading into table. The avro data is nested data.

Now from this table im trying to extract the necessary elements using spark sql. Using explode function when there is array data. Now the challenge is there are cases like the element that needs to be extracted might not present in avro data in that case default null value should be returned in select statement instead of throwing error.

4 REPLIES 4

Hubert-Dudek
Esteemed Contributor III

Usually, in such cases, I create an empty (template) table with all necessary columns and then append data to it.

So read Avro, explode and then append to the template table.

Hi Hubert, thank you for the quick reply,

to append data, extraction of data from nested data itself is failing in case if the respective derive element is failing.

example:

| Col1 | col2 |

-------------

| Hello | { A:1, B:2, C: [AA:11, BB: 22]}

My sql is like

select col2.b, explode(col2.c) from tab;

now in the above case if C element is missing then above select should not fail but return null.

Kindly help

Hubert-Dudek
Esteemed Contributor III

As it requires some manipulation, it will be easier to handle it as dataframe in Python as there you can just use:

df.schema.fieldNames.contains("col2.C")

and apply logic accoridngly

UmaMahesh1
Honored Contributor III

Hi @manoj kumar​ 

An easiest way would be to make use of unmanaged delta tables and while loading data into the path of that table, you can enable mergeSchema to be true. This handles all the schema differences, incase column is not present as null and if new column pops up, then all the previous records as null etc.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!