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

[Manojkumar]

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.

[Hubert-Dudek]

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.

[Manojkumar]

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]

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]

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.