Issues while using Snowflake component In Talend

1
votes

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
2
talendsnowflake-cloud-data-platform
Could you please share a screenshot of what you've done so far ? – Ibrahim Mezouar
As a last resort, you can execute your own query using a tJDBCRow (by adding snowflake jar, as there is no tSnowflakeRow atm), this way you have total control of your columns, and you can reference columns from your input flow. – Ibrahim Mezouar

2 Answers

0
votes

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.

0
votes

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