Loading xml data from sql table into snowflake variant data type

ar7368 — Fri, 15 Nov 2024 23:34:47 GMT

I'm trying to load xml data from sql server table ( has varcharmax column with xml data) to snowflake table with variant data type.. i get the following error. I'm able to load the same into snowflake if the datatype in snowflake is varchar. How do i load this data into variant datatype in snowflake?

[FATAL] 10:54:18 snowflake.bedmaster_snowflake_0_1.Bedmaster_Snowflake- tDBOutput_2 net.snowflake.client.loader.Loader$ConnectionError: State: CREATE_TEMP_TABLE, CREATE TEMPORARY TABLE "TESTDT_20211028_105416_423_1" LIKE "TESTDT", SQL compilation error:

cannot drop all the columns of a table

java.io.IOException: net.snowflake.client.loader.Loader$ConnectionError: State: CREATE_TEMP_TABLE, CREATE TEMPORARY TABLE "TESTDT_20211028_105416_423_1" LIKE "TESTDT", SQL compilation error:

cannot drop all the columns of a table

at org.talend.components.snowflake.runtime.SnowflakeWriter.close(SnowflakeWriter.java:502) ~[components-snowflake-runtime-0.34.0.jar:?]

at org.talend.components.snowflake.runtime.SnowflakeWriter.close(SnowflakeWriter.java:59) ~[components-snowflake-runtime-0.34.0.jar:?]

at snowflake.bedmaster_snowflake_0_1.Bedmaster_Snowflake.tDBInput_1Process(Bedmaster_Snowflake.java:2062) [bedmaster_snowflake_0_1.jar:?]

at snowflake.bedmaster_snowflake_0_1.Bedmaster_Snowflake.runJobInTOS(Bedmaster_Snowflake.java:3354) [bedmaster_snowflake_0_1.jar:?]

at snowflake.bedmaster_snowflake_0_1.Bedmaster_Snowflake.main(Bedmaster_Snowflake.java:3114) [bedmaster_snowflake_0_1.jar:?]

Caused by: net.snowflake.client.loader.Loader$ConnectionError: State: CREATE_TEMP_TABLE, CREATE TEMPORARY TABLE "TESTDT_20211028_105416_423_1" LIKE "TESTDT", SQL compilation error:

cannot drop all the columns of a table

at net.snowflake.client.loader.ProcessQueue.run(ProcessQueue.java:365) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_301]

Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:

cannot drop all the columns of a table

at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:124) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:64) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:434) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:338) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:482) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:194) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:131) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.core.SFStatement.execute(SFStatement.java:737) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.core.SFStatement.execute(SFStatement.java:635) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.jdbc.SnowflakeStatementV1.executeInternal(SnowflakeStatementV1.java:287) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.jdbc.SnowflakeStatementV1.execute(SnowflakeStatementV1.java:337) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at net.snowflake.client.loader.ProcessQueue.run(ProcessQueue.java:122) ~[snowflake-jdbc-3.13.1.jar:3.13.1]

at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_301]

Oct 28, 2021 10:54:18 AM net.snowflake.client.loader.ProcessQueue run

SEVERE: State: CREATE_TEMP_TABLE, CREATE TEMPORARY TABLE "TESTDT_20211028_105416_423_1" LIKE "TESTDT", SQL compilation error:

cannot drop all the columns of a table

Exception in component tDBOutput_2 (Bedmaster_Snowflake)

java.io.IOException: net.snowflake.client.loader.Loader$ConnectionError: State: CREATE_TEMP_TABLE, CREATE TEMPORARY TABLE "TESTDT_20211028_105416_423_1" LIKE "TESTDT", SQL compilation error:

cannot drop all the columns of a table

at org.talend.components.snowflake.runtime.SnowflakeWriter.close(SnowflakeWriter.java:502)

at org.talend.components.snowflake.runtime.SnowflakeWriter.close(SnowflakeWriter.java:59)

at snowflake.bedmaster_snowflake_0_1.Bedmaster_Snowflake.tDBInput_1Process(Bedmaster_Snowflake.java:2062)

at snowflake.bedmaster_snowflake_0_1.Bedmaster_Snowflake.runJobInTOS(Bedmaster_Snowflake.java:3354)

at snowflake.bedmaster_snowflake_0_1.Bedmaster_Snowflake.main(Bedmaster_Snowflake.java:3114)

Caused by: net.snowflake.client.loader.Loader$ConnectionError: State: CREATE_TEMP_TABLE, CREATE TEMPORARY TABLE "TESTDT_20211028_105416_423_1" LIKE "TESTDT", SQL compilation error:

cannot drop all the columns of a table

at net.snowflake.client.loader.ProcessQueue.run(ProcessQueue.java:365)

at java.lang.Thread.run(Thread.java:748)

Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:

cannot drop all the columns of a table

at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:124)

at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:64)

at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:434)

at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:338)

at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:482)

at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:194)

at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:131)

at net.snowflake.client.core.SFStatement.execute(SFStatement.java:737)

at net.snowflake.client.core.SFStatement.execute(SFStatement.java:635)

at net.snowflake.client.jdbc.SnowflakeStatementV1.executeInternal(SnowflakeStatementV1.java:287)

at net.snowflake.client.jdbc.SnowflakeStatementV1.execute(SnowflakeStatementV1.java:337)

at net.snowflake.client.loader.ProcessQueue.run(ProcessQueue.java:122)

... 1 more

Re: Loading xml data from sql table into snowflake variant data type

ar7368 — Thu, 28 Oct 2021 16:00:09 GMT

topic Re: Loading xml data from sql table into snowflake variant data type in Talend Studio

Loading xml data from sql table into snowflake variant data type

Re: Loading xml data from sql table into snowflake variant data type