Forum Discussion
XML parsing fabric notebooks
How I personally would approach loading 8k files with inconsistent schemas;
- If I can't identify the schema from the filename/metadata, process each file separately.
- Convert each file to flat pyspark/pandas data frames - the schemas I've see so far are hierarchical which can be a bit painful to merge.
- Once everything has been flattened I'd then consider writing to either a single table or a single pyspark data frame making sure I allow the process to add new columns (easier when appending to a table, but not hard)
- I should now have a table with 1 row per entry, and null values where columns don't exist in one row but do in another.
- From this point onwards it's now a data processing/analysis problem 😊
Impactful IndividualWhat happens if you print the schema of the dataframe? Does _VALUE appear in the nested schema?
df_xml.printSchema()
I made a very hooky nested xml file and the _VALUE still appears, so there may be something afoot in how you combined the files
Also, 'amen' to JSON over XML. JSON converts directly into a python structure - you screw the schema up, you soon know about it when you json.loads().
I think the problem could be that abstract does not have a consistent schema across different files which I honestly don't know how to deal with it.
So if I take a single file that abstract schema works correctly, the schema looks like this:
root
|-- _country: string (nullable = true)
|-- _date-produced: long (nullable = true)
|-- _date-publ: long (nullable = true)
|-- _dtd-version: string (nullable = true)
|-- _file: string (nullable = true)
|-- _id: string (nullable = true)
|-- _lang: string (nullable = true)
|-- _status: string (nullable = true)
|-- abstract: struct (nullable = true)
| |-- _id: string (nullable = true)
| |-- p: struct (nullable = true)
| | |-- _VALUE: string (nullable = true)
| | |-- _id: string (nullable = true)
| | |-- _num: long (nullable = true)
|-- claims: struct (nullable = true)
| |-- _id: string (nullable = true)
| |-- claim: array (nullable = true)
but when files are combined and I print the schema, it looks like below and loses the _value even for the file that individually worked before being combined with the rest:
root
|-- _country: string (nullable = true)
|-- _date-produced: long (nullable = true)
|-- _date-publ: long (nullable = true)
|-- _dtd-version: string (nullable = true)
|-- _file: string (nullable = true)
|-- _id: string (nullable = true)
|-- _lang: string (nullable = true)
|-- _status: string (nullable = true)
|-- abstract: struct (nullable = true)
| |-- _id: string (nullable = true)
| |-- p: array (nullable = true)
| | |-- element: struct (containsNull = true)
| | | |-- _VALUE: string (nullable = true)
| | | |-- _id: string (nullable = true)
| | | |-- _num: long (nullable = true)
| | | |-- b: array (nullable = true)
| | | | |-- element: string (containsNull = true)
| | | |-- br: string (nullable = true)
| | | |-- chemistry: struct (nullable = true)
| | | | |-- _id: string (nullable = true)
| | | | |-- _num: long (nullable = true)
| | | | |-- img: struct (nullable = true)
| | | | | |-- _VALUE: string (nullable = true)
| | | | | |-- _alt: string (nullable = true)
| | | | | |-- _file: string (nullable = true)
| | | | | |-- _he: string (nullable = true)
| | | | | |-- _id: string (nullable = true)
| | | | | |-- _img-content: string (nullable = true)
| | | | | |-- _img-format: string (nullable = true)
| | | | | |-- _wi: string (nullable = true)
| | | |-- i: array (nullable = true)
| | | | |-- element: string (containsNull = true)
| | | |-- img: array (nullable = true)- spencer_sa
How I personally would approach loading 8k files with inconsistent schemas;
- If I can't identify the schema from the filename/metadata, process each file separately.
- Convert each file to flat pyspark/pandas data frames - the schemas I've see so far are hierarchical which can be a bit painful to merge.
- Once everything has been flattened I'd then consider writing to either a single table or a single pyspark data frame making sure I allow the process to add new columns (easier when appending to a table, but not hard)
- I should now have a table with 1 row per entry, and null values where columns don't exist in one row but do in another.
- From this point onwards it's now a data processing/analysis problem 😊