Does anyone know how to connect to Snowflake? I have followed several instruction and still no luck. A step by step would be helpful.
I go to:
Preferences
Databases
Add
I add jdbc 3.12.0
and I tried to add the URL (This might be the problem):
jdbc:snowflake://name.azure.snowflakecomputing.com:###
After this I add the node reader and i get an error stating that the password and user name is wrong.
I also tried installing the Snowflake extension and that also gave me an error:
Error. HTTP request remote host termindated the handshake
Hello @etor182 and @bruno29a ,
with the 4.4 release we released the Snowflake Integration which comes with a dedicated Snowflake Connector and the driver. The integration also comes with support for the DB Loader to upload large amounts of data into Snowflake.
Simply install the extension and use the Snowflake Connector and you should be fine.
Hi @etor182 , can you try to connect to Snowflake via another client, but from the same system, just to see if the issue is related to Knime or to your system not being able to connect to Snowflake?
@etor182 , so it looks like it was able to connect this time, but credentials were not good.
I can’t really help there, it’s about your credentials. Can you check what you are using in PowerBi or DBeaver? Also, try to leave the Virtual Warehouse empty. You can always point to a warehouse after connecting.
Ok, you are using the External Browser option. When using this option, it will popup a browser for you to authenticate - this is usually the case when using ldap. I’m not sure where to specify the option for externalbrowser.
Normally, you can pass it in the url as a parameter authenticator=externalbrowser, but as we found out, you can’t specify the url and definitely you can’t add the parameters, since Knime will complete the url for you.
Try this from the Snowflake Connector. Go to the JDBC Parameters tab and try to add the parameter there:
It should popup a browser for authentication when trying to connect if the parameter works.
FYI: When using externalbrowser, it does not matter what you add as credentials in Knime, or DBeaver or PowerBi. Snowflake will authenticate what you submit in the popup browser
So, the Node now turns green; however, when I try to run a query I get this error and I also noticed that the Database browser does not show my actual tables. Am I using the wrong Query node?
Just a side node to @tobias.koetter here. The authenticator method is something that is quite common when connecting to Snowflake. It would make more sense having it as an option in the Connection Settings tab than having to manually add it as a JDBC parameter. The externalbrowser is used for SSO and a lot of companies use SSO (usually ldap) for snowflake rather than individual db user. With SSO, it is more manageable for them to automatically disable a snowflake access for a user when an employee leaves by simply disabling the user’s company’s access. This option is as popular as MSSQL connecting using your windows credentials (which basically uses ldap - and for the same reason I stated (easier to manage access when employee leaves)). The MSSQL Connector does have this option:
It would make sense that the Snowflake Connector have the option to choose the Authenticator method.
Back to @etor182 's issue. As per your screenshot, you did not choose a database during the connection, so after connecting, you are not pointing to any database, and you can see that’s what it’s complaining about in the message. The message also suggests to you what to do. You can select a database by using the USE command - and this is applicable for any DB client you are using (PowerBi, DBeaver)
So, in your DB SQL Executor there, you can write:
USE your_database_you_want_to_use
And execute it (run the workflow).
You can then go back to the Executor and write whatever you want or browse.
EDIT: FYI, the DB SQL Executor will only execute an SQL Statement on the server side. You will not be able to query data via this node. You should use the DB Query Reader node instead if you want to see data.
Also, alternatively, if you don’t choose the DB, you can always query by explicitly mention what db (and schema) you want to query. For example: SELECT * FROM DB.SCHEMA.TABLE LIMIT 10;
@bruno29a , I see. I have tried every role and the only one that works is Public, but public does not have access to that table. The company I work for uses, User specific log in, so I am not sure why none of the options allow me to connect.