雪花中结构未知的XML解析 [英] Parsing XML with unknown structure in Snowflake
本文介绍了雪花中结构未知的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 |
|-----------|---------|
推荐答案
因此递归是您要在此处使用的Flat上的属性:
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'
;
但它不会提供第一行,而且Snowflake在展开节点时的行为不同
<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
只缺少您需要的NULL, 1
和NULL, 9
行。
编辑%2
因此,回到我最初的建议,取出节点id和路径,然后对具有限定条件的节点进行左连接以保持最长匹配,可以这样做,并给出所需的输出:
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
这篇关于雪花中结构未知的XML解析的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文