topic Re: Load XML file to variant data type column in Snowflake in Talend Studio

Load XML file to variant data type column in Snowflake

vvazza10 — Sat, 16 Nov 2024 00:20:57 GMT

Hi ! I am trying to load contents of a complete XML file to variant column in Snowflake. tFileInputXML component seem to read rows from the fields in XML. Please advise what components and design approach can i use to load complete XML to variant column of a table in snowflake?

Re: Load XML file to variant data type column in Snowflake

Anonymous — Thu, 01 Apr 2021 09:34:56 GMT

Hello,

Are you able to insert an entire XML into Snowflake via tDBRow component?

Best regards

Sabrina

Re: Load XML file to variant data type column in Snowflake

vvazza10 — Fri, 02 Apr 2021 13:01:53 GMT

Hi Sabrina ! I was able to load the entire XML through tFileInputRaw with schema being document and and moved the file to S3 and loaded to snowflake table using tDBBulkExec. Is there any other way?

Re: Load XML file to variant data type column in Snowflake

Anonymous — Tue, 06 Apr 2021 04:46:01 GMT

Hello,

Please try to insert an entire XML into Snowflake via tDBRow component and the sql is this with Prepared Statement :

"INSERT INTO <tablename> SELECT ?, PARSE_XML".

Best regards

Sabrina

Re: Load XML file to variant data type column in Snowflake

vvazza10 — Thu, 08 Apr 2021 19:08:37 GMT

Thank you, Sabrina !

Is there any way to insert multiple rows to snowflake table from tXMLMap using user defined Insert sql query?

Re: Load XML file to variant data type column in Snowflake

vvazza10 — Sun, 25 Apr 2021 02:27:13 GMT

@Xiaodi Shi Hi Sabrina ! Could you tell me how to make this prepared statement? I tried the following statement and it failed -

"INSERT INTO TABLE_A SELECT ?, PARSE_XML"

The design flow is -

tfileInputRaw -> tDBRow

In tFileInputRaw, I chose "Stream the file"

Re: Load XML file to variant data type column in Snowflake

vvazza10 — Sun, 25 Apr 2021 02:33:05 GMT

@Xiaodi Shi - It worked 🙂

Design:

tFileInputRaw -

tDBRow -

Re: Load XML file to variant data type column in Snowflake

Anonymous — Sun, 25 Apr 2021 04:33:05 GMT

Hello,

Please refer to this online component reference about: TalendHelpCenter: tSnowflakeRow

If you want to query the database using a prepared statement, you will select "Use PreparedStatement" option in advanced setting of tSnowflakeRow.

Best regards

Sabrina

Re: Load XML file to variant data type column in Snowflake

vvazza10 — Sun, 25 Apr 2021 23:04:56 GMT

Thanks, Sabrina ! I have used prepared statement and it worked fine.

But how do you generally recommend XML data to be loaded to Snowflake via Talend? Loading the XML as variant and then querying using snowflake FLATTEN queries or using Talend components and load like tFileInputXML -> tFileOutputDelimited (csv) -> Snowflake?

Re: Load XML file to variant data type column in Snowflake

Anonymous — Mon, 26 Apr 2021 05:03:00 GMT

Hello,

It usually depends on your job requirement.

We have added support VARIANT type in tSnowflakeOutput and tSnowflakeInput since V 6.5.1 and 7.0.1.

Using tSnowlakeInput, tSnowflakeOutput - easy read and write without using complex queries.

If use tSnowflakeRow be aware of write restriction - Snowflake JDBC doesn't allow complex batch queries.

But it's possible to write in standard(not batch) mode. And it's preferable to use tSnowflakeOutput for write operations. On the other hand tSnowflakeRow is very good for reading data from Snowflake, even complex since you're using query.

Feel free to let us know if it helps.

Best regards

Sabrina

Re: Load XML file to variant data type column in Snowflake

ar7368 — Wed, 27 Oct 2021 21:22:29 GMT

I'm trying to load data from SQL server to snowflake table with variant data type. The data type of said columnin sql server is varchar(max) containing xml data. I'm unable to load it into the snowflake table with Variant data type. However it does work when the snowflake table column is varchar data type. any ideas how to fix this?

tdbinput > tsnowflakeoutput