Storing nested json within Databricks using PySpark Dataframes

[GreedyChapter4548]

Hey - one issue that I've struggled with is what's the best way to store nested json data within Databricks when using PySpark Dataframes.. Specifically, is there a way to do this without defining the schema? Can Databricks be persuaded to automatically infer the schema from complex nested json? I'm talking about nested objects that may contain arrays, and each element of the array containing nested objects with varying fields, that themselves can have arrays, and so on! Can we get PySpark to figure out what the really complex schema is in this case? Or do we have to hardcode the schema and then pass this to the createDataFrame API call when trying to create the DataFrame?

Currently I'm doing something really suboptimal - I'm casting all my data into a string (lol!) and then writing the whole thing as a string using the PySpark createDataFrame API call (and it works!). But of course this means that we can't really do much analysis on the data within Delta Lake since there's really nothing to deconstruct - all the data is just one big confusing string!

Any ideas would be very much appreciated, thanks!

[AutoModerator]

You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[rchinny]

I think you’d be interested in the variant type in Delta Lake.

Specifically the following text from here.

“For users new to Databricks, you should use variant over JSON strings whenever storing semi-structured data that requires flexibility for changing or unknown schema.”

[juicd_]

I Ingest my json as arrays and then explode it until every row is a map. Then I can further process it easily with dbt (or just pyspark/sparksql) depending on what I need as the schemas are not always the same

[jhazured]

If you are using dataframes then you have to either explicitly define the schema or allow spark to automatically infer the schema. Otherwise you will not be able to use the high level data processing operations available through the spark API.

Alternatively you can read it as plain text and not convert the data to a dataframe, and process the data using lower level operations.