Instant APIs on Snowflake using UDFs and Hasura
Tags: snowflake, tutorial, userdefinedfunctions
Published: 2023-08-10 13:27:41

---
title: Instant APIs on Snowflake using UDFs and Hasura
published: true
date: 2023-08-09 13:32:08 UTC
tags: Snowflake,Tutorial,UserDefinedFunctions
canonical_url: https://hasura.io/blog/instant-apis-on-snowflake-using-udfs-and-hasura/
---

![Instant APIs on Snowflake using UDFs and Hasura](https://lh4.googleusercontent.com/JvxZM-0b_Y4fBPn5YLtiSgSl9wLdSzU7OFaF8QbmJvTn8eGtgnbivfTviUuRj7xTeTJfwsLSHOemSsOxni_V01v-dVs_D2h_37EI8xzNd446Y5szRHuvMZrmxTMe1fAyp6K7wwnUpL5eGCkeBbvSItg)


**The Hasura Snowflake connector became** [generally available in June](https://hasura.io/blog/announcing-hasura-integration-snowflake-empowering-developers-effortless-data-apis/). Like all other Hasura connectors, the value of the Snowflake connector is the ability to generate APIs (GraphQL and REST) on Snowflake data with minimal coding.

I wanted to put it to the test by recreating [this blog](https://medium.com/snowflake/build-a-data-api-for-your-snowflake-data-b2c82ab4bbf5) from Snowflake’s team with Hasura.

The original post showed how to create an analytics app using [OAG: Global Airline Schedules](https://app.snowflake.com/marketplace/listing/GZ1M7Z2MQ39)data. The architecture includes using AWS Lambda functions to read from Snowflake and return the data as RESTful APIs.

![Instant APIs on Snowflake using UDFs and Hasura](https://lh5.googleusercontent.com/YtOgmfccuqJLV0YnCPDh-tUSFdfG9HWC03iq7RuQG5AcfPE0CVzBIKxhHYYtaVAH4vsktBHVRvim7AgsBSIOeovyOM_jO0vX42sx1zVovRw5AefIMsbmnSXxAdFgY0KdoULLr9Bqr6z2-4f4IccWpDw)
_Architecture in the original blog._

We will implement these data APIs over Snowflake using Hasura. Right off the bat, we can instantly replace all the different Amazon components with Hasura, greatly simplifying the architecture by reducing five components to one.

![Instant APIs on Snowflake using UDFs and Hasura](https://hasura.io/blog/content/images/2023/08/image5.png)
_Simplified architecture with Hasura._

## Importing sample data to Snowflake

First and foremost, we must import the sample data to our Snowflake instance – the data can be found in the marketplace [here](https://app.snowflake.com/marketplace/listing/GZ1M7Z2MQ39).

## Implementing the APIs in Hasura

This section will show how to instantly create the required APIs using Snowflake’s user-defined functions (UDF) and Hasura’s query engine.

A UDF is a function you define to call it from SQL. UDF’s logic typically extends or enhances SQL with functionality that SQL doesn’t have or doesn’t do well. A UDF also allows you to encapsulate functionality to call it repeatedly from multiple places in code.

We will implement the `busy_airports` API using UDFs and Hasura from the original blog.

### Busiest airports API

This API endpoint finds the busiest airports in the data. We will use an API key as an admin secret. We want to filter the data based on `flight_date` and restrict the number of rows returned from the API.

#### 1. Tracking UDFs in Hasura

First, we will create the following UDF in Snowflake’s SQL console:

![Instant APIs on Snowflake using UDFs and Hasura](https://lh5.googleusercontent.com/B8PvO3bT5x0YAiTLrgbN6W_JYmtB0MSGZxaj2yf9IRBEUXet8pMy22EeWUdxGCrLSHYr4tJSdK-WXYTsZF_KxkWsJhDVOBcqflfvrerxLYzQFf9_0ljQBs5rtsGp_u_PkHtIp3fMAohHve-UwxqziUE)

Now, we will import the UDF to Hasura:

![Instant APIs on Snowflake using UDFs and Hasura](https://lh6.googleusercontent.com/XzhJntVAvL9K7j_X9yCOQmQVqGfGNk1zVGoOqDf6Y7h1ZndvVD-7NzQzOzJKS6mMPxlyrp7C2yfl24F7aeAGAz7EfBaPTlc1uiYu9mnKoKenAd2_jcDAH_uUiwOCyrzLzHuPjDmNYJAQv5viULhKEDE)

After that, we can query the UDF like any other table from Hasura:

![Instant APIs on Snowflake using UDFs and Hasura](https://lh4.googleusercontent.com/qPwmOm0IIxQYZQ-ZsXYvJ_Y8jb3HiWwD9NQD_uMk60lkrrCj5uI0fEjyQNuW8RfulySwD86px85f07ZxRCjYc8N7Oa2Wb5xHAX62YJvdc1VADCIV3YOU-JaIRa1-L8paFOEIThdjcuzHMgU7MYupO54)

#### 2. Cache your Snowflake API

You can use the @cached directive to your query to decrease the response time. The default TTL is 60 seconds, reducing latency by about ~100 milliseconds.

Caching is useful when querying Snowflake, as latencies could be a bottleneck, primarily when serving customers on web/mobile applications. It can improve latencies and reduce the load on data warehouse resources by fetching the results from Redis.

You can also fetch the results using cURL:

<!--kg-card-begin: markdown-->
```
url -X POST -H "x-hasura-admin-secret: admin-secret" -H "Content-Type: application/json" -d '{"query":"query MyQuery{BUSY_AIRPORTS_2(limit:20,order_by:{COUNT:desc}){ARRAPT COUNT}}"}' https://choice-platypus-97.hasura.app/v1/graphql
{"data":{"BUSY_AIRPORTS_2":[{"ARRAPT":"ATL","COUNT":131184},{"ARRAPT":"ORD","COUNT":120075},{"ARRAPT":"LHR","COUNT":106354},{"ARRAPT":"DFW","COUNT":105660},{"ARRAPT":"JFK","COUNT":98454},{"ARRAPT":"LAX","COUNT":91109},{"ARRAPT":"CDG","COUNT":87035},{"ARRAPT":"FRA","COUNT":86363},{"ARRAPT":"AMS","COUNT":85004},{"ARRAPT":"MAD","COUNT":61921},{"ARRAPT":"BOS","COUNT":58124},{"ARRAPT":"SEA","COUNT":56016},{"ARRAPT":"SFO","COUNT":55467},{"ARRAPT":"DEN","COUNT":53891},{"ARRAPT":"EWR","COUNT":49122},{"ARRAPT":"FCO","COUNT":48736},{"ARRAPT":"IAH","COUNT":47115},{"ARRAPT":"MIA","COUNT":46552},{"ARRAPT":"YYZ","COUNT":45225},{"ARRAPT":"CLT","COUNT":44266}]}}

```
<!--kg-card-end: markdown-->

Similarly, we can implement `airport_daily` and `airport_daily_carriers` following the same steps. Thus, we can access the data in minutes without writing a single line of code, just using UDFs in Snowflake and Hasura.

The APIs are protected with API keys. Suppose you want a role-based authorization layer. In that case, this blog covers how to implement role-based access control on Snowflake: [“ **Snowflake using RBAC: A secure and scalable data access solution.”**](https://hasura.io/blog/hasura-graphql-on-snowflake-using-rbac-a-secure-and-scalable-data-access-solution/)

## Conclusion

Within a few minutes, we replaced five different AWS components, implemented an API without writing a single line of code, added an admin secret for authentication, and added the ability to cache to provide low-latency data access.

Hasura avoids getting entangled with details and lets you focus on building the end product.

## 🚀 Get Started Today!

We can't wait to see the amazing application you'll build using Hasura and Snowflake. Start by signing up for [Hasura Cloud](https://cloud.hasura.io/signup) and connecting your Snowflake data warehouse.   
  
If you have any questions or need assistance, please contact our team on [Discord](https://discord.com/invite/hasura) or [GitHub](https://github.com/hasura/graphql-engine/issues).