将多个 tXMLMap 输出值组合到一个雪花表中 [英] Combining multiple tXMLMap output values to a single snowflake table

问题描述

我有以下 XML 结构作为输入

I have the below XML structure as input

<dataexport>
<header>
     <companyinfo>
     <companyid> 100 </companyid>
     <companyname>ABC Corp</companyname>
     </companyinfo>
</header>
<deptinfo>
     <electrical>
          <response>
               <deptid> 1 </deptid>
               <totalemp> 200 </totalemp>
               <totalunits> 20 </totalunits>
          </response>
     </electrical>
     <mechanical>
          <response>
               <deptid> 2 </deptid>
               <totalemp> 150 </totalemp>
               <totalunits> 40 </totalunits>
          </response>
     </mechanical>
     <chemical>
          <response>
               <deptid> 3 </deptid>
               <totalemp> 100 </totalemp>
               <totalunits> 20 </totalunits>
          </response>
     </chemical>

我期望的输出是

<头>

公司 ID	公司名称	部门	部门 ID	总emp
100	ABC Corp	电气	1	200
100	ABC Corp	机械	2	150
100	ABC Corp	化学	3	100

我采用的设计方法如下 -

The design approach I went with was as below -

tXMLMap 组件配置如下 -

tXMLMap component has been configured like below -

通过这种方法，我只能获得一个输出，并且无法将 deptinfo_electrical、deptinfo_mechanical 和 deptinfo_chemistry 的结果合并到一个数据流中以加载到雪花表中.

With this approach, I was able to get only one output and am unable to combine results of deptinfo_electrical, deptinfo_mechanical and deptinfo_chemical into one dataflow to load to a snowflake table.

请告知这里可以使用什么设计方法.

Please advise what design approach can be used here.

推荐答案

所以使用 thia CTE 和你的数据，在雪花中:

so using thia CTE with you data in it, in snowflake:

with data as (
    select parse_xml('<dataexport>
    <header>
         <companyinfo>
         <companyid> 100 </companyid>
         <companyname>ABC Corp</companyname>
         </companyinfo>
    </header>
    <deptinfo>
         <electrical>
              <response>
                   <deptid> 1 </deptid>
                   <totalemp> 200 </totalemp>
                   <totalunits> 20 </totalunits>
              </response>
         </electrical>
         <mechanical>
              <response>
                   <deptid> 2 </deptid>
                   <totalemp> 150 </totalemp>
                   <totalunits> 40 </totalunits>
              </response>
         </mechanical>
         <chemical>
              <response>
                   <deptid> 3 </deptid>
                   <totalemp> 100 </totalemp>
                   <totalunits> 20 </totalunits>
              </response>
         </chemical>
    </deptinfo>
    </dataexport>') as xml
)

这个选择让你得到你想要的值:

this select gets you the values you want:

select xml
    ,xmlget(xmlget(xml,'header',0), 'companyinfo', 0) as comp
    ,get(xmlget(comp, 'companyid'),'$') as companyid
    ,get(xmlget(comp, 'companyname'), '$') as companyname
    ,get(d.value, '@') as depm
    ,xmlget(d.value, 'response') as r
    ,get(xmlget(r, 'deptid'),'$') as deptid
    ,get(xmlget(r, 'totalemp'), '$') as totalemp
    --,d.*
from data,
 LATERAL FLATTEN(GET(xmlget(xml,'deptinfo',0), '$')) d
         

这就是您想要进行的转换形式.

so that is the form of the transformation you are wanting todo.

可以重组为:

select 
    get(xmlget(xmlget(xmlget(xml,'header',0), 'companyinfo', 0), 'companyid'),'$') as companyid
    ,get(xmlget(xmlget(xmlget(xml,'header',0), 'companyinfo', 0), 'companyname'), '$') as companyname
    ,get(d.value, '@') as depm
    ,get(xmlget(xmlget(d.value, 'response'), 'deptid'),'$') as deptid
    ,get(xmlget(xmlget(d.value, 'response'), 'totalemp'), '$') as totalemp
from data,
 LATERAL FLATTEN(GET(xmlget(xml,'deptinfo',0), '$')) d
 

给出结果:

COMPANYID   COMPANYNAME DEPM            DEPTID  TOTALEMP
100         "ABC Corp"  "electrical"    1       200
100         "ABC Corp"  "mechanical"    2       150
100         "ABC Corp"  "chemical"      3       100

如何在 Talend 中实际执行此操作，我没有答案，但我假设您可以通过查看查询历史记录来了解当前模式如何在 Snowflake 上运行，以解决零件如何从 Talend 逻辑映射到雪花 SQL.

how to actually do that in Talend, I have no answer, but I would assume you can look to see how your current pattern is run on Snowflake by looking at the query history, to solve how the parts map from Talend logic to snowflake SQL.

这篇关于将多个 tXMLMap 输出值组合到一个雪花表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！