I recently received some requests to share information on using the REST-based Snowflake data connector with JMP 19.
Luckily, I had already made a video about it! Easiest post ever.
For those who want to skip straight to the JMP usage, go to timestamp 2:11.
Transcript (approximate):
My name is Chris, I’m a software developer with JMP and I want to share some information about the REST-based Snowflake data connector, new in JMP 19!
With this connector, users will see increased performance relative to the ODBC-based Snowflake connector. Additionally, the REST-based connector works in JMP without needing to install the ODBC Snowflake driver.
Objectives
Today, I’m going to cover:
- What is Snowflake?
- Why would you want to use the REST-based Snowflake connector?
- How to configure and connect with the REST-based Snowflake connector.
- A few troubleshooting tips.
What is Snowflake?
For those new to Snowflake, one of its descriptions is “Snowflake combines a completely new SQL query engine with an innovative architecture natively designed for the cloud.”
So from a user perspective, Snowflake holds data. Users can submit SQL queries to access that data, just like they would with any SQL-based database, but on the backend, the data is stored in one or more cloud providers like AWS, Google Cloud, or Azure. It excels in scaling resources up or down depending on customers’ needs.
Snowflake also provides a web interface called Snowsight, where users can run queries, manage Snowflake, upload data, build data pipelines, and monitor activity and usage.
ODBC vs REST
And now in JMP 19, users have two options for accessing Snowflake data within JMP: one way is with the ODBC Snowflake data connector, the other way is to use the REST-based Snowflake connector. The REST-based connector takes advantage of multithreaded processing for fetching data, meaning queries can complete much more quickly compared to the ODBC driver.
Limitations of REST
Currently, the supported authentication methods are “username and password” used with Snowflake’s default authenticator. Another way is with OAuth, and the last is programmatic access tokens intended for things like automated scripts.
The REST-based connector uses the Snowflake SQL API under the hood, so some operations like GET and PUT with data staging are not supported.
Connecting
Knowing all that, let’s look at configuring a REST-based connector and making the connection in JMP.
(open JMP)
To start, let’s open the JMP data connector interface.
(navigate… and talk)
Search for Snowflake in the search bar, select the built-in Snowflake connector, and create a new one.
Auth with user password
Before we start, many of these options may require information from a Snowflake administrator and potentially other parties, depending on your particular authentication systems. But let’s create a connection using username and password.
The first thing we need is the Snowflake URL, which is often how users access the Snowsight web interface and normally ends with <snowflakecomputing.com>.
The warehouse field is the warehouse you want to connect to, same with database and role. Keeping them blank will use your user’s default values if you have them defined. It’s common that users are restricted to certain roles or even which tables they can access, so having incorrect values for these may prevent connecting.
And finally, fill out the username and password fields with your credentials. You can keep the password field blank, and it will prompt you when connecting. Test the connection using the test button and save the connection if you wish.
2FA
You may find that JMP stalls at the point of connection, which may indicate Snowflake waiting for two-factor authentication (2FA). If your account is configured with 2FA, you will need to respond to the prompt. An example is using the Duo app on your phone to allow the connection.
With a successful connection, you will be able to see the schemas and tables in the given database you have connected to. Then utilize open table or Query Builder to import the data into JMP.
SQL console
New in JMP 19 is the SQL console, which provides a path to executing multistatement queries with supporting connectors. It is similar to running a custom SQL, but also allows for opening a number of tables at once with one query. You can save your work as an .sql file and open it later for repeated use.
Connecting with programmatic access token
For users needing to bypass 2FA for scripting purposes or for use with JMP Live refresh scripts, another way to connect is with a programmatic access token. This temporary token is valid for up to a year. Contact your Snowflake administrator to generate a token for your user.
To use the token with JMP, follow the same steps as before with creating a username/password Snowflake connector, but input the token into the password field of the connector instead of your usual password. You should be able to connect normally following this process without needing 2FA.
Connect with OAuth
For connecting using OAuth, most of the fields come from your Snowflake and security administrators. Make sure the username and password fields are blank for this authentication type. Like before, fill out the Snowflake URL field and also the database, warehouse, and role fields, if applicable. The remaining fields are listed in the OAuth section and these will come from your administrator. When connecting with OAuth, you will likely need to authenticate yourself and may also need to complete a two-factor authentication. In my case, I am directed to a browser window to enter my credentials.
Troubleshooting
Let’s also mention a few issues you may run into and some potential solutions.
- Bad role/warehouse: When connecting, you may see a generic error saying: “SQL compilation error: Object does not exist, or operation cannot be performed.”
The first thing I would check is if your role, warehouse, and database are correct. If you have left them blank, you may need to explicitly say which resource you are trying to access. If you have filled them out, double check that they are spelled correctly and that your user has access to those resources. To do that, check with your Snowflake administrator, verify your setup using Snowsight, or use a console tool like snowsql or the Snowflake CLI.
- HTTPS: Some setups of Snowflake are IP-restricted or resources exist behind a company firewall. Make sure you have access to those resources, which may include accessing your company network or setting the proxy and noproxy fields in the data connector. Additionally, you may need to disable TLS using the Secure field if, for example, your infrastructure uses self-signed certificates. Do not disable TLS unless you know this is the correct configuration, as this may allow man-in-the-middle attacks.
Thanks for watching, I hope this was helpful seeing the benefits of the REST-based Snowflake connector, learning how to configure a new Snowflake data connector, and ultimately connecting to Snowflake data within JMP.
There is a lot more information on using Snowflake and running Snowflake queries available at snowflake.com.
We hope users will find the performance improvements of this connector valuable and look forward to improving things further. Thank you!
