Loading large xml files into Snowflake and flattening by tag

user_7586785 0
Viewed 859

I have some extremely large XML files that I need to process. I used to process them using Spark, but I am moving away from SQLDW and onto Snowflake, so I can no longer use Spark. In Spark, there was a concept of flattening XML files by providing a "rowTag" to a spark function. Let us say we have this persons.xml file:

<persons>
    <person id="1">
        <firstname>James</firstname>
        <lastname>Smith</lastname>
        <middlename></middlename>
        <dob_year>1980</dob_year>
        <dob_month>1</dob_month>
        <gender>M</gender>
        <salary currency="Euro">10000</salary>
        <addresses>
            <address>
                <street>123 ABC street</street>
                <city>NewJersy</city>
                <state>NJ</state>    
            </address>
            <address>
                <street>456 apple street</street>
                <city>newark</city>
                <state>DE</state>    
            </address>    
        </addresses>    
    </person>
    <person id="2">
        <firstname>Michael</firstname>
        <lastname></lastname>
        <middlename>Rose</middlename>
        <dob_year>1990</dob_year>
        <dob_month>6</dob_month>
        <gender>M</gender>
        <salary currency="Dollor">10000</salary>
        <addresses>
            <address>
                <street>4512 main st</street>
                <city>new york</city>
                <state>NY</state>    
            </address>
            <address>
                <street>4367 orange st</street>
                <city>sandiago</city>
                <state>CA</state>    
            </address>    
        </addresses>            
    </person>
</persons>

If I want to flatten this XML file to look like a CSV with headers firstname, lastname, middlename, dob_year, dob_month... etc, I would run a function that looks like this:

val df = spark.read
      .format("com.databricks.spark.xml")
      .option("rowTag", "person")
      .load("persons.xml");
display(df);

By providing spark the rowTag person in the .option() function, we get a dataframe that looks like this:

_id addresses   dob_month   dob_year    firstname   gender  lastname    middlename  salary                          
1   {"address":[{"city":"NewJersy","state":"NJ","street":"123 ABC street"},{"city":"newark","state":"DE","street":"456 apple street"}]} 1   1980    James   M   Smith       {"_VALUE":10000,"_currency":"Euro"}
2   {"address":[{"city":"new york","state":"NY","street":"4512 main st"},{"city":"sandiago","state":"CA","street":"4367 orange st"}]}   6   1990    Michael M       Rose    {"_VALUE":10000,"_currency":"Dollor"}

It's a little difficult to read, so here is an image to help... Image

Anyways, I was wondering how I could do this with Snowflake, if it is possible? I would like to avoid pre-processing my xml file if possible.

Remember, these files are large. 1Gb+. There is also no guarantee that the files will have the rowTag in the beginning or near the beginning - it could be several hundred lines down the file.

snowflake-cloud-data-platform xml java
1 Answers

Some ideas for you:

  1. On load, use STRIP_OUTER_ELEMENT = TRUE to eliminate the PERSONS tag, and have each PERSON object land in it's own row. This simplifies the data and allows you to load larger files.

  2. Flatten the XML to find all the paths. For example, select * from my_table a, lateral flatten(input=>a.data, recursive=>true) b;

  3. Translate the paths from the flatten notation into the field notation and build your query:

For example (assuming PERSONS outer tag removed):

select 
  data:"@id"::number id,
  data:"$"[0]."$"::text first_name,
  data:"$"[1]."$"::text last_name
from my_table;

Where data is your XML column.

Hope that helps.

UPDATE -- Sample XML to use with query above:

create or replace table my_table as
select parse_xml($1) as data 
from values ('
    <person id="1">
        <firstname>James</firstname>
        <lastname>Smith</lastname>
        <middlename></middlename>
        <dob_year>1980</dob_year>
        <dob_month>1</dob_month>
        <gender>M</gender>
        <salary currency="Euro">10000</salary>
        <addresses>
            <address>
                <street>123 ABC street</street>
                <city>NewJersy</city>
                <state>NJ</state>    
            </address>
            <address>
                <street>456 apple street</street>
                <city>newark</city>
                <state>DE</state>    
            </address>    
        </addresses>    
    </person>'),('
    <person id="2">
        <firstname>Michael</firstname>
        <lastname></lastname>
        <middlename>Rose</middlename>
        <dob_year>1990</dob_year>
        <dob_month>6</dob_month>
        <gender>M</gender>
        <salary currency="Dollor">10000</salary>
        <addresses>
            <address>
                <street>4512 main st</street>
                <city>new york</city>
                <state>NY</state>    
            </address>
            <address>
                <street>4367 orange st</street>
                <city>sandiago</city>
                <state>CA</state>    
            </address>    
        </addresses>            
    </person>
');
user_47440960
Related