09-20-2024 09:24 AM
I'm not sure I'm working this correctly but I'm having some issues with the column names when I try to load to a table in our databricks catalog. I have multiple .json.gz files in our blob container that I want to load to a table:
09-20-2024 11:33 AM - edited 09-20-2024 11:35 AM
Hi @KristiLogos ,
Ok, now I understand the problem better. Set the Delta table property delta.columnMapping.mode to name to enable column mapping mode. Then try again.
09-20-2024 09:38 AM
Hi @KristiLogos ,
Check if your JSON doesn't have characters contained in error message in it's key values.
09-20-2024 09:58 AM
hi @szymon_dybczak - thanks for responding. The error is saying its in the column name. I think it has to do with the space that's in the 'child' column. However, I've been asked to not flatten the pyspark dataframe and just ingest the 'raw' data, ie. leave the nested columns nested and not flatten. I feel like that might be the issue because if I convert the pyspark to a pandas dataframe I'm able to load this without issues to my table. Is there a way to not 'flatten' and just load to pyspark dataframe?
09-20-2024 10:32 AM
Hi @KristiLogos ,
Could you show sample json?
I have run the tests, do you have a space in the App Brnd column?
Is App Brnd wrapped in double-quote?
Below the check
1. The first one are keys without double-quotes, space between App Brnd -> returns corrupt record
2. The second one are keys with double-quotes, no spaces -> returns valid json
09-20-2024 10:58 AM
hi @filipniziol - thanks for replying. I realized it might actually be a different nested (child) column that has a period for the event_properties. (I'm showing it below) This is most likely the issue, but how can i keep the nested values as nested and not split out for the event_properties? I've believe what I could do is convert to a pandas dataframe becuase I'm able to load this as such but then i have to convert my Spark df to pandas df then convert back to spark df to load to a databricks table seems like alot.
|-- event_properties: struct (nullable = true)
| |-- Active.Elapsed Minutes: double (nullable = true)
| |-- Active.Frequency: long (nullable = true)
| |-- Active.Maximum: double (nullable = true)
| |-- Active.Minimum: double (nullable = true)
This is what event_properties column looks like in a pandas dataframe which is what I want it to look like for spark
09-20-2024 11:15 AM - edited 09-20-2024 11:16 AM
Hi @KristiLogos ,
now I understand the problem. 🙂
You have a json and then you want to save this to delta table as struct, right?
The allowed characters for delta table column names are:
Letters: A-Z, a-z
Digits: 0-9
Underscores: _
The key here is that your structs must also adhere to this naming convention
Before saving structs to table you need to sanitize the column names like that:
App Brnd -> App_Brnd
Active.Elapsed Minutes -> Active_Elapsed_Minutes
Hope it helps
09-20-2024 11:29 AM
Yes thats the issue I'm facing, Im so confused about this because, do i have to rename the child columns? I just want to keep the spark dataframe as it is without flattening, . and if i do this it only updates the parent column names:
09-20-2024 11:33 AM - edited 09-20-2024 11:35 AM
Hi @KristiLogos ,
Ok, now I understand the problem better. Set the Delta table property delta.columnMapping.mode to name to enable column mapping mode. Then try again.
09-20-2024 04:17 PM
amazing thanks! that worked flawlessly
09-20-2024 10:52 PM
I'm happy that it worked for you 🙂
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.
If there isn’t a group near you, start one and help create a community that brings people together.
Request a New Group