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)?
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 ')'.
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.
Hello szawadski,
we plan to develop a dedicated Snowflake connector node. Until the you need to use the generic DB Connector node. Until then you can execute the statement using the DB SQL Executor node which is used to execute DML statements. The DB Query and DB Query Reader node can only be used to execute statements that return a result set.
By the way you can extract the SELECT statement into a flow variable using the SQL Extract node. Then you can append the “CREATE TABLE” part using a String Manipulation (Variable) node. Finally using this variable string in the DB SQL Executor. Let me know if I should add an example workflow that demonstrates this.
Bye
Tobias
Sorry for my late reply but I was on holidays the last two weeks. Indeed, I was contacted by both your colleagues and Snowflake about this new dedicated connector.
We also have tested the solution you proposed above and it works perfectly (thanks Alex!).
We are looking forward being able to continue developping the use of Knime within our datajedi community.
Thanks to the Knime Team and User Community for the great support offered in this forum!