topic Re: Can we assigee default value in select columns in Spark sql when the column is not present? in Data Engineering

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

Manojkumar — Fri, 06 Jan 2023 12:10:50 GMT

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.

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

Hubert-Dudek — Fri, 06 Jan 2023 12:14:13 GMT

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.

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

UmaMahesh1 — Fri, 06 Jan 2023 12:54:58 GMT

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.

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

Manojkumar — Fri, 06 Jan 2023 14:01:59 GMT

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

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

Hubert-Dudek — Fri, 06 Jan 2023 22:28:22 GMT

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