We’ve tried out Snowflake’s SQL API (and we like it!)

Recently, cloud warehouse giant, Snowflake launched the Snowflake SQL API and we were very excited to try out this new addition!

The Snowflake SQL API makes it possible for custom-built and third-party applications to call Snowflake’s Data Cloud through a REST application programming interface without the need for client-side drivers. The SQL API is all about reducing complexity and administration overheads which is a much needed feature for the data warehouse space.

So, if you’re looking to use Snowflake SQL API, look no further. In this post, our CTO goes through how he uses this new feature quite easily. There is also a tutorial and video to get you started!

What is the Snowflake SQL API?

The Snowflake SQL API is a REST API that you can use to access and update data in a Snowflake database. You can use this API to develop custom applications and integrations that can perform simple queries or manage your deployment (such as provisioning users and roles, creating tables, etc.)

In addition to this, the Snowflake SQL API provides operations that you can use to do the following:

• Submit SQL statements for execution
• Check the status of the execution of a statement
• Cancel the execution of a statement

You can also use this API to execute standard queries and most DDL and DML statements.

Limitations of the Snowflake SQL API

Unfortunately, there are some limitations and points to note when using the SQL API. The SQL API has the following limitations:

• The maximum size of a page of results is approximately 10 MB.
• The maximum number of rows returned in a page of results is 10,000.
• The service does not currently support fetching results concurrently (i.e. in parallel with multiple threads).
• Requests are supported only from a single thread.

Also note the following statements which are not supported:

• Statements with the Snowflake SQL PUT command
• Statements with the Snowflake SQL GET command

Example of the Snowflake SQL API

Now, let’s move on to an example! In this example, Ryan takes us through how we would call a stored procedure. Here we can really see how easy it is to call a stored procedure using the SQL API instead of traditional methods.

If you’d like to read the Snowflake documents before beginning, we’ve listed the links below.

1. https://docs.snowflake.com/en/developer-guide/sql-api/index.html

2. https://docs.snowflake.com/en/developer-guide/sql-api/guide.html#snowflake-sql-api-developer-guide

3. https://docs.snowflake.com/en/developer-guide/sql-api/guide.html#using-key-pair-authentication

Ryan also has recorded a quick video tutorial of this example which will be linked at the bottom of this post.

In this example, we will learn the following:

Using Key Pair Authentication:

We are going to use key pair authentication in this example. Key pair authentication is an alternative means of identifying yourself to a login server, instead of typing a password. It is more secure and more flexible than conventional password authentication.

You can check out the Snowflake Documentation about Key Pair Authentication here:

https://docs.snowflake.com/en/user-guide/key-pair-auth.html
https://docs.snowflake.com/en/developer-guide/sql-api/guide.html#using-key-pair-authentication)

Setting up the Public Key for the User: Here we go through how to set up the public key which is based on the private key.

For an example of generating a JWT in Python, see the following:

https://docs.snowflake.com/en/_downloads/8aa3ccaf2cddbcb24e5990d6ab79082d/jwt-generator.py

How to assign the Public Key to a Snowflake User:

Here we learn how to assign the public key to a Snowflake user by using the “ALTER USER” command.

Verify the Generated Private Key:

Using SnowSQL, we learn how to easily verify the generated private key.

Generating a JSON web token:

Here we see how we can generate a JSON web token.

Submitting an API Request:

Now we go through what API endpoints to use as well as any additional fields that we should note when setting up the headers. Ryan also goes through how to submit an API request using POSTMAN as well as some useful tips and tricks.

If you need more information on how this can be done, check out the link below:

https://docs.snowflake.com/en/developer-guide/sql-api/reference.html#label-sql-api-reference-post-statements-request-body

Creating and Calling Store Procedures:

Here we learn how we can use the SQL API to create and call stored procedures. In this example, our store procedure returns an average temperature on a specific day.

The Snowflake documents are a great resource for more information on creating and calling stored procedures. Have a look at the below:

https://docs.snowflake.com/en/developer-guide/sql-api/guide.html#creating-and-calling-stored-procedures

https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#label-stored-procedure-examples

Converting from SQL to Javascript

Using the Snowflake SQL data types, you can easily convert SQL data types to corresponding JavaScript data types. For more information, see the following link:

https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#converting-from-sql-to-javascript

The Snowflake SQL API proves to be a great addition to Snowflake’s Data Platform. This feature will save users time and money by enabling off-the-shelf applications to call Snowflake directly without the need for client-side drivers.

Vantage CTO Ryan Evan

Written by

Ryan Evans

Chief Technology Officer

Log4j Broke the Internet. Here’s what Happened.

I’m sure you must have seen the word “log4j” pop up on emails, messages and any sort of social media feed, everywhere! If you know a cyber security professional please give them a Christmas cookie and some words of encouragement- because the odds are high that they’re...

Tips on How to Increase Tableau Dashboard Performance

Tune your Tableau Workbook like a Gibson Guitar “My report is so slow!” How many times have you heard that from a client (or from yourself)?I’m sure I am not alone when it comes to the frustration of working with a slow Tableau dashboard! And of course, the client,...

Dashboards Are Dead, Long Live Contextual Analytics

In this post, I will be doing a deep dive into why contextual analytics should be the core of a business’s data-driven decision-making strategy. Gone are the days where reporting was done off hundreds of printed pages, multiple punch folders, a calculator, and a...

REQUEST A DEMO

8 + 6 =