将大型xml文件加载到Snowflake中并通过标签进行展平

问题描述

我有一些非常大的XML文件需要处理。我曾经使用Spark处理它们，但是我从SQLDW移到Snowflake，所以我不再可以使用Spark。在Spark中，有一个通过为spark函数提供“ rowTag”来扁平化XML文件的概念。让我们说我们有这个persons.xml文件：

JamesSmith19801M10000

                123 ABC streetNewJersyNJ

            

                456 apple streetnewarkDE
    
        MichaelRose19906M10000

                4512 main stnew yorkNY

            

                4367 orange stsandiagoCA
    
        

如果我想将此XML文件展平为带有标题firstname, lastname, middlename, dob_year, dob_month...等的CSV，我将运行一个如下所示的函数：

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

通过在rowTag函数中提供person .option()的火花，我们得到一个看起来像这样的数据帧：

_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"}

有点难读，所以这里有一张图片可以帮助...

无论如何，如果可能的话，我想知道如何使用Snowflake做到这一点？如果可能的话，我想避免预处理我的xml文件。

请记住，这些文件是large。 1Gb +。也不能保证文件的开头或结尾处都会有rowTag-可能在文件下方几百行。

思路：

一些您的想法：

1. 加载时，使用STRIP_OUTER_ELEMENT = TRUE消除PERSONS标记，并使每个PERSON对象均落在其自己的行中。这样可以简化数据，并允许您加载较大的文件。

2. 将数据XML展开以查找所有路径。例如，select * from my_table a, lateral flatten(input=>a.data, recursive=>true) b;

3. 将扁平化表示法的路径转换为字段表示法并构建查询：

例如（假设删除了PERSONS外部标签）：

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

希望有所帮助。