Error using DB connection writer node (SQL-92)

labs
#1

I am using Knime with the Snowflake JDBC driver which is great to leverage the computation power of Snowflake.
I need to save the result of my workflow (completely realised with DB nodes) into a new table and thus trying to use the DB Connection table writer (Labs).
I get the following error:
ERROR DB Connection Table Writer (Labs) 0:828 Execute failed: CREATE TABLE AS SELECT is not part of the SQL-92 syntax.
Which is true. But Snowflake supports this simple but useful instruction.
But declaring my Snowflake JDBC driver as “default” (only possible choice) does forces me to select the SQL-92 database dialect in the DB Connector node.

Do you see any solution to this (beyond using the DB reader node to store locally the data and then insert them into the Snowflake table using the DB Insert node)?

0 Likes

#2

You can use Database Query node with Select * Into … statement.

0 Likes

#3

Thanks @izaychik63 for your advice and fast answer. Unfortunately, I had already tried this, without success.
I am getting the following error message when trying this in the DB Query node.

SELECT * INTO TMA.FACT_EVENTS_REGISTERED2 FROM #table#

Error message:
WARN DB Query (Labs) 2:832 net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error: syntax error line 1 at position 24 unexpected 'INTO'. syntax error line 2 at position 15,134 unexpected 'FROM'. syntax error line 2 at position 30,193 unexpected 'FROM'. syntax error line 2 at position 34,699 unexpected 'FROM'. syntax error line 2 at position 49,565 unexpected 'FROM'. syntax error line 2 at position 53,888 unexpected 'FROM'. syntax error line 2 at position 68,091 unexpected 'FROM'. syntax error line 2 at position 81,573 unexpected 'FROM'. syntax error line 2 at position 94,021 unexpected 'FROM'. syntax error line 5 at position 0 unexpected ')'.

0 Likes

#4

Hello, any idea on how to solve this issue?

Any plan on Knime side to co-develop with Snowflake a connector node that would allow to use the complete set of commands and functions available in Snowflake.

Thanks in advance!

0 Likes