Snowflake to Knime

Hello,

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

Hi @etor182 and welcome to the Knime Community.

You need to install the Snowflake jdbc driver. It’s basically a jar file named snowflake-jdbc-#.#.#.jar.

Here’s a repo of drivers:
https://repo1.maven.org/maven2/net/snowflake/snowflake-jdbc/

EDIT: I’m adding some instructions in case you don’t know how to add it.

  1. Download the jar file and save it somewhere (Let’s say C:\jarfiles\)
  2. Add the driver to Knime:
  • Go to File → Preferences
  • Go to KNIME → Databases
  • Click on the Add button
  • In the next window, click on Add file, and look for that jar file (from C:\jarfiles\ as per my example)
  • Click on Find driver classes button at the bottom
  • Add an ID and Name at the top (Both can be Snowflake)
  • You can leave the Database type as Default
  • Click OK
  • Click on Apply and Close
  1. In your workflow, add a DB Connector node
  2. Configure the DB Connector node:
  • Choose Snowflake (or the name you gave above) from the Driver Name
  • Add your connection URL
  • Configure your username and password

That’s it

3 Likes

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.

Bye
Tobias

5 Likes

Hi @tobias.koetter , I was unaware of this addition, that is great! No need to manually add the driver anymore.

Thanks for sharing @tobias.koetter

@tobias.koetter is there something I am missing here? I keep getting this error.

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?

@bruno29a I am able to connect to Snowflake using PowerBi and Dbeaver. I am wondering if the Full Account Name needs to have the port number?

Hi @etor182 , I don’t think there is any port to be specified.

Can you remove the .snowflakecomputing.com?

Your account name is what’s before .snowflakecomputing.com

EDIT: This is from the node’s description:
" The domain .snowflakecomputing.com will be appended automatically to the full account name."

So Knime will add .snowflakecomputing.com, which means you should not include it

@bruno29a

This is the error I received:
ERROR Snowflake Connector 0:1 Execute failed: Incorrect username or password was specified.

@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.

image

@bruno29a , I was having the same issue with DBeaver, unitl I added the Authenticator as externalbrowser. Is this something this KNIME node also has?

Leaving the virtual warehouse is not an option, it gives me an error (Warehouse name is not defined)

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:

Click on Add, and enter the parameter:

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

1 Like

@bruno29a , awesome!

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?

Hi @etor182 , great! Glad it worked.

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;

1 Like

@bruno29a , I am wondering if the connection actually worked. I have tried several ways of connection with the qualification and using USE database;

See the error I get below:

Hi @etor182 , it’s an access issue. You need to specify your role

@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.