sql - 在雪花中解析具有未知结构的 XML
问题描述
我有一堆通过嵌套标签定义树层次结构(ID:s 之间的关系)的 XML 文件。我想使用 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 |
|-----------|---------|
解决方案
所以 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
flatten 是'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
andNULL, 9
行。
编辑 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
推荐阅读
- ios - Swift:从数组中选择两个随机但唯一的元素
- python - 如何使用 Pandas 或 Numpy 优化大循环
- python - utf-8和utf-8 BOM之间的不同python?
- android - 不打印 describeMismatchSafely 文本
- python - 使用python从当前月份和年份显示/返回过去12个月和24个月的年份
- python - 如何以汇总形式显示按类别发布的提要总数并向用户发送新提要总数的通知
- c - 如何在覆盖之前打印的内容时将几行打印到标准输出?
- css - CSS Grid:居中和重叠的困难
- kubernetes - 我应该创建一个 API 来让 readinessprobe 工作 kubernetes
- javascript - 函数可以修改 Javascript 中的“this”对象吗?