CODEWITHSUNDEEP
talendsnowflake-cloud-data-platform
FlashUltron
FlashUltron

Reputation: 181

Issues while using Snowflake component In Talend

To transfer data from Ms sql server 2008 to Snowflake I used talend , but every time I get error as 

java.io.IOException: net.snowflake.client.loader.Loader$ConnectionError: State: CREATE_TEMP_TABLE, SQL compilation error: error line 1 at position 68
invalid identifier '"columnname"'
                at org.talend.components.snowflake.runtime.SnowflakeWriter.close(SnowflakeWriter.java:397)
                at org.talend.components.snowflake.runtime.SnowflakeWriter.close(SnowflakeWriter.java:52)
                at local_project.load_jobnotes_0_1.Load_Jobnotes.tMSSqlInput_1Process(Load_Jobnotes.java:2684)
                at local_project.load_jobnotes_0_1.Load_Jobnotes.runJobInTOS(Load_Jobnotes.java:3435)
                at local_project.load_jobnotes_0_1.Load_Jobnotes.main(Load_Jobnotes.java:2978)
Caused by: net.snowflake.client.loader.Loader$ConnectionError: State: CREATE_TEMP_TABLE, SQL compilation error: error line 1 at position 68
invalid identifier '"ID"'
                at net.snowflake.client.loader.ProcessQueue.run(ProcessQueue.java:349)
                at java.lang.Thread.run(Thread.java:748)
Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error: error line 1 at position 68

The column does exist in my Snowflake DB still I get error as column does not exist

On analysing what query Talend executing in snowflake I found that It tries to create a temporary table to store data but in doing so it selects all column from table between “ ” double quotes and hence error comes as invalid identifier '"columnname"'

If I execute the same query manually without double quotes its works fine , can you please let us know what is workaround of this issue

Query executed by talend in snowflake for your reference 

CREATE TEMPORARY TABLE "Tablename_20171024_115736_814_1" 
AS SELECT "column1","column2","column3"
FROM "database"."schema"."table" WHERE FALSE

Upvotes: 1

Views: 3483

Answers (2)

Eddy Chan
Eddy Chan

Reputation: 11

I found that this issue is due to mixed case database or schema names not properly being applied by Talend. I discover a hack by updating the Snowflake connector role parameter and added something such as this screenshot:

Screenshot

Upvotes: 1

Marcin Zukowski
Marcin Zukowski

Reputation: 4739

The issue is most likely due to a case mismatch between the object names in Snowflake and what is being sent through the connector. On the Snowflake side, all object names are stored as UPPER CASE. Suggest you try passing COLUMN1, COLUMN2, etc and see if that works.

You can also try setting the QUOTED_IDENTIFIERS_IGNORE_CASE to true, it might help.

Upvotes: 1

Related Questions