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.
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:
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:
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:
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:
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.
Chief Technology Officer