topic Re: tXMLMap taking long time to process XML data to load to snowflake in Talend Studio

tXMLMap taking long time to process XML data to load to snowflake

vvazza10 — Sat, 16 Nov 2024 00:13:43 GMT

I have an XML file (2MB) that was various child elements - Employee, chemical, electrical, transport, mechanical, which I am trying to load to Snowflake table. When the job was executed, tXMLMap seem to take most of the time in processing the data.

The tXMLMap read is taking around 15-20 minutes to complete for a small set of data. The job design would look as below -

Please advise how to design a job that reads XML which contains various sub elements and attributes.

Re: tXMLMap taking long time to process XML data to load to snowflake

gjeremy1617088143 — Fri, 23 Apr 2021 07:24:35 GMT

Hi, maybe you can cut your job in two, first read the xml transform it and write the output on other files.

then read the files to load data to snowflakes and by this way you can also load all your data in parallel if you make each (read --> load) in separate job.

Send Me Love and Kudos

Re: tXMLMap taking long time to process XML data to load to snowflake

vvazza10 — Fri, 23 Apr 2021 12:57:37 GMT

Thanks for the response ! Are you suggesting to transform the xml and write it to a file in S3 and load to snowflake from there? Could you please tell me what components i can use?

Re: tXMLMap taking long time to process XML data to load to snowflake

gjeremy1617088143 — Fri, 23 Apr 2021 13:30:38 GMT

Re: tXMLMap taking long time to process XML data to load to snowflake

gjeremy1617088143 — Fri, 23 Apr 2021 13:34:11 GMT

so first you write all your data in files, then you run a job which execute all your output in parrallel in simple job (read csv --> load snowflake).

You could use the component textractxmlfield wich is lighter than txmlMap.

And you could also increase your Jvm max memory for better performance

Re: tXMLMap taking long time to process XML data to load to snowflake

vvazza10 — Fri, 23 Apr 2021 13:38:14 GMT

Thank you ! Let me try this approach. I still feel the tXMLMap is going to take time to process the data. Even in the approach that i followed, the snowflake load happened in seconds, but the tXMLMap processing took a good amount of time. Am thinking if it could be because of the loop conditions set to read XML.

Re: tXMLMap taking long time to process XML data to load to snowflake

vvazza10 — Fri, 23 Apr 2021 18:43:12 GMT

@guenneguez jeremy - I went with the approach you had suggested. The xml read completed in 0.64 seconds, but tXMLMap took 20 minutes to spit out to delimited files. What is going wrong here?

Re: tXMLMap taking long time to process XML data to load to snowflake

gjeremy1617088143 — Fri, 23 Apr 2021 18:53:31 GMT

so an other way : use textractxmlfield for each output instead of one xmlMap. And run them in parrallel.

Re: tXMLMap taking long time to process XML data to load to snowflake

vvazza10 — Fri, 23 Apr 2021 19:32:35 GMT

@guenneguez jeremy - A small correction. The first approach of tfileInputXML -> tXMLMap -> tFileDelimitedOutput

and parallel runs of tFileDelimitedInput -> tSnowflakeOutput

took 7 minutes to complete.

Let me try using tExtractXMLField component and check how long it takes

Re: tXMLMap taking long time to process XML data to load to snowflake

vvazza10 — Wed, 28 Apr 2021 11:03:50 GMT

@guenneguez jeremy @Shicong Hong Jeremy, in your approach, won't the same XML file be read multiple times based on number of tExtractXMLField that's there in the design? I would want XML to be read once and written to multiple tables. How can this be achieved?

Re: tXMLMap taking long time to process XML data to load to snowflake

gjeremy1617088143 — Wed, 28 Apr 2021 12:20:36 GMT

the purpose of tExtactXmlField in my scenario is to be used in parrallel job, so you read the xml for chemical, you read the xml for Electrical And all the read are made in parallel.

Re: tXMLMap taking long time to process XML data to load to snowflake

Anonymous — Thu, 29 Apr 2021 08:24:45 GMT

You can use tReplicate component, this component replicate the source data so that you can use the source data many times.