TO_JSON won't work in cases where..
One file contains: <pet>dog</pet> which converts to {"pet":"dog"}
But another file contains:
<pet>dog</pet>
<pet>cat</pet>
which converts to: {"pet": ["dog", "cat"]}
pet as a column in Drill can't be both a varchar and an array of varchar
There are a ton of gotcha(s) when dealing with XML..
numeric vs string
scalar vs array
-----Original Message-----
From: Lee, David
Sent: Wednesday, April 6, 2022 10:54 AM
To: [email protected]; [email protected]
Subject: RE: [DISCUSS] Add schema support for the XML format
I wrote something to convert XML to JSON using an XSD schema file to solve
fields, types, nested structures, etc.. It's the only real way to ensure column
level data integrity.
https://github.com/davlee1972/xml_to_json
Converts XML to valid JSON or JSONL Requires only two files to get started.
Your XML file and the XSD schema file for that XML file.
-----Original Message-----
From: luoc <[email protected]>
Sent: Wednesday, April 6, 2022 5:01 AM
To: [email protected]; [email protected]
Subject: [DISCUSS] Add schema support for the XML format
External Email: Use caution with links and attachments
Hello dear driller,
Before starting the topic, I would like to do a simple survey :
1. Did you know that Drill already supports XML format?
2. If yes, what is the maximum size for the XML files you normally read? 1MB,
10MB or 100MB
3. Do you expect that reading XML will be as easy as JSON (Schema Discovery)?
Thank you for responding to those questions.
XML is different from the JSON file, and if we rely solely on the Drill drive
to deduce the structure of the data. (or called SCHEMA), the code will get very
complex and delicate.
For example, inferring array structure and numeric range. So, "provided schema"
or "TO_JSON" may be good medicine :
Provided Schema
We can add the DTD or XML Schema (XSD) support for the XML. It can build all
value vectors (Writer) before reading data, solving the fields, types, and
complex nested.
However, a definition file is actually a rule validator that allows elements to
appear 0 or more times. As a result, it is not possible to know if all elements
exist until the data is read.
Therefore, avoid creating a large number of value vectors that do not actually
exist before reading the data.
We can build the top schema at the initial stage and add new value vectors as
needed during the reading phase.
TO_JSON
Read and convert XML directly to JSON, using the JSON Reader for data
resolution.
It makes it easier for us to query the XML data such as JSON, but requires
reading the whole XML file in memory.
I think the two can be done, so I look forward to your spirited discussion.
Thanks.
- luoc
This message may contain information that is confidential or privileged. If you
are not the intended recipient, please advise the sender immediately and delete
this message. See
http://www.blackrock.com/corporate/compliance/email-disclaimers for further
information. Please refer to
http://www.blackrock.com/corporate/compliance/privacy-policy for more
information about BlackRock’s Privacy Policy.
For a list of BlackRock's office addresses worldwide, see
http://www.blackrock.com/corporate/about-us/contacts-locations.
© 2022 BlackRock, Inc. All rights reserved.
