在Snowflake中解析具有未知结构的XML

Question

我有一堆XML文件，它们通过嵌套标记定义树层次结构(ID:s之间的关系(。我想使用Snowflake的SQL语法将其解析为半结构化数据的表格格式。对于具有已知结构的XML文件，我知道如何做到这一点。但对于这些树，在解析时结构是未知的，在这种情况下，我不知道如何解决它。重复的通用模式是

<Nodes>
<Node>
...
</Node>
</Nodes>

请参阅下面的示例数据和所需输出。

有没有一种方法可以使用Snowflake的SQL语法来实现这一点？

示例数据：

<Nodes>
<Node Id="1">
<Nodes>
<Node Id="2">
</Node>
<Node Id="3">
<Nodes>
<Node Id="4">
</Node>
<Node Id="5">
<Nodes>
<Node Id="6">
</Node>
</Nodes>
</Node>
<Node Id="7">
</Node>
</Nodes>
</Node>
<Node Id="8">
</Node>
</Nodes>
</Node>
<Node Id="9">
<Nodes>
<Node Id="10">
</Node>
</Nodes>
</Node>
</Nodes>

所需的表格输出为：

|-----------|---------|
| parent_id | node_id |
|-----------|---------|
|      null |       1 |
|         1 |       2 |
|         1 |       3 |
|         3 |       4 |
|         3 |       5 |
|         5 |       6 |
|         3 |       7 |
|         1 |       8 |
|      null |       9 |
|         9 |      10 |
|-----------|---------|

Answer 1

所以RECURSIVE是你想在这里使用的FLATTEN上的属性：

with data as (
select parse_xml('<Nodes>
<Node Id="1">
<Nodes>
<Node Id="2">
</Node>
<Node Id="3">
<Nodes>
<Node Id="4">
</Node>
<Node Id="5">
<Nodes>
<Node Id="6">
</Node>
</Nodes>
</Node>
<Node Id="7">
</Node>
</Nodes>
</Node>
<Node Id="8">
</Node>
</Nodes>
</Node>
<Node Id="9">
<Nodes>
<Node Id="10">
</Node>
</Nodes>
</Node>
</Nodes>') as xml
)
select 
GET(f.value, '@Id') as id
,f.path as path
,len(path) as p_len
from data,
TABLE(FLATTEN(INPUT=>get(xml,'$'), recursive=>true)) f
where get(f.value, '@') = 'Node'
;

给出：

ID  PATH    P_LEN
1   [0] 3
2   [0]['$']['$'][0]    16
3   [0]['$']['$'][1]    16
4   [0]['$']['$'][1]['$']['$'][0]   29
5   [0]['$']['$'][1]['$']['$'][1]   29
6   [0]['$']['$'][1]['$']['$'][1]['$']['$'] 39
7   [0]['$']['$'][1]['$']['$'][2]   29
8   [0]['$']['$'][2]    16
9   [1] 3
10  [1]['$']['$']   13

根据这个，您现在可以通过查找路径的所有匹配项并获取最长的匹配项来重建层次结构。

或

你可以做一个双嵌套循环，比如：

select 
GET(f1.value, '@Id') as id
,GET(f2.value, '@Id') as id
,f1.value
,f2.*
, get(f2.value, '@') 
from data,
TABLE(FLATTEN(INPUT=>get(xml,'$'), recursive=>true)) f1,
TABLE(FLATTEN(INPUT=>GET(xmlget(f1.value,'Nodes'), '$'))) f2
where get(f1.value, '@') = 'Node'
;

但它没有给你第一行，雪花在扩展节点时表现不同

<node>
<nodes>
<node></node>
</nodes>
<node>

和

<node>
<nodes>
<node></node>
<node></node>
</nodes>
<node>

这意味着你必须尝试处理两者，这真的很恶心。

编辑：

因此，您可以更接近，但请注意，如果发生第二个子情况，您可以获得节点名称get(f2.value, '@') = 'Node'，因此我们可以将一些东西填充到IFF中，在第一种情况下，扁平化的value是'Node'，因此我们可以硬代码获取父级->节点->节点，因此：

select 
GET(f1.value, '@Id') as parent_id
,iff(get(f2.value, '@')  = 'Node', GET(f2.value, '@Id'), GET(xmlget(xmlget(f1.value,'Nodes'),'Node'), '@Id')) as child_id
from data,
TABLE(FLATTEN(INPUT=>get(xml,'$'), recursive=>true)) f1,
TABLE(FLATTEN(INPUT=>GET(xmlget(f1.value,'Nodes'), '$'))) f2
where get(f1.value, '@') = 'Node'
and (get(f2.value, '@')  = 'Node' OR f2.value = 'Node')
;

给你：

PARENT_ID   CHILD_ID
1   2
1   3
1   8
3   4
3   5
3   7
5   6
9   10

它只缺少您想要的CCD_ 5和CCD_。

编辑2

因此，回到我最初的建议，取出节点id和路径，然后用QUALIFY对节点进行LEFT JOIN，以保持最长的匹配，可以这样做，并给出所需的输出：

with data as (
select parse_xml('<Nodes>
<Node Id="1">
<Nodes>
<Node Id="2">
</Node>
<Node Id="3">
<Nodes>
<Node Id="4">
</Node>
<Node Id="5">
<Nodes>
<Node Id="6">
</Node>
</Nodes>
</Node>
<Node Id="7">
</Node>
</Nodes>
</Node>
<Node Id="8">
</Node>
</Nodes>
</Node>
<Node Id="9">
<Nodes>
<Node Id="10">
</Node>
</Nodes>
</Node>
</Nodes>') as xml
), nodes AS (
select 
GET(f1.value, '@Id') as id
,f1.path as path
,len(path) as l_path
from data,
TABLE(FLATTEN(INPUT=>get(xml,'$'), recursive=>true)) f1
where get(f1.value, '@') = 'Node'
)
SELECT p.id as parent_id
,c.id as child_id
FROM nodes c
LEFT JOIN nodes p
ON LEFT(c.path,p.l_path) = p.path AND c.id <> p.id
QUALIFY row_number() over (partition by c.id order by p.l_path desc ) = 1
;

给出：

PARENT_ID   CHILD_ID
null    1
1       2
1       3
3       4
3       5
5       6
3       7
1       8
null    9
9       10