How to Build Data APIs on Snowflake

In this post, we will learn how to use Hasura to rapidly build APIs on top of the Snowflake data to power embedded analytics in mobile/web apps. Using Hasura, we can quickly create secure and performant GraphQL/REST APIs on Snowflake, and then query the API from a React frontend.

Why APIs?

Snowflake is typically a central data source for the organization that aggregates data from different data silos, internally and externally. With all this rich and valuable information stored in a single place, businesses are looking for ways to directly use this data to power more intelligent and personalized experiences. For example, it is an in-product analytics widget showing users their usage patterns.

Embedding analytics and dashboards powered by your data warehouse directly into user-facing applications requires a secure and performant data serving layer with low latency and high concurrency. Low latency is critical for delivering a snappy user experience. Fine-grained access control is crucial when exposing a data warehouse with sensitive information to user-facing apps. Analytical APIs are the perfect tool to meet these requirements because they provide a decoupled layer to implement your security and performance needs.

Besides surfacing real-time analytics in apps, data APIs are an efficient way of sharing specific controlled views on the data warehouse with internal and external consumers. Employees, customers, and partners can hit a particular API endpoint to get the data they need in a self-serve way without being blocked on manual data-sharing workflows.

While APIs are an excellent method to implement this secure and low-latency serving layer, building APIs the traditional way can be tedious, time-consuming, and require substantial backend service logic. This post shows how to create a performant data API on Snowflake in minutes with Hasura.

Does Snowflake have an API?

Snowflake's SQL API allows users to execute SQL statements against their data warehouse through a REST API. However, this API doesn't provide additional optimization, abstraction, or security features to support the development of performant, low-latency applications that require complex authorization use cases.

How do I build an API on top of Snowflake?

Follow these 5 steps to build your first Snowflake API and use it from a React frontend app.

0. Deploy Hasura

Sign up for a forever-free Hasura Cloud account and spin up a new project.

1. Connect your Snowflake Data Source:

To set up a Snowflake Data Source for Hasura, follow our Snowflake database guide. Enter your Snowflake JDBC connection details in Hasura’s console as shown below,

Make sure to use CLIENT_SESSION_KEEP_ALIVE=TRUE param in JDBC connection URL. Snowflake closes JDBC connections after a while (max: 4 hours). This param will ensure the connection is kept alive and bypass the restriction.

2. Track tables from Snowflake

Once connected, you can see your Snowflake tables in Hasura. You can select all the tables you want to be able to query using Hasura and click on Track Selected.

3. Query your Snowflake API

Once you connect and track tables, Hasura instantly gives you a GraphQL API on top of Snowflake that you can immediately use to start querying the data.

At the bottom-right of the GraphQL API tab, you'll see the quick response times Hasura' 's high-performance GraphQL API provides. In this example, computing the average temperature over a few thousand records takes about 530 milliseconds.

Note these queries are running on Snowflake directly without replicating the data in a different datastore.

4. (Optional) Cache your Snowflake API

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

Caching is useful when querying Snowflake, as latencies could be a bottleneck, primarily when serving customers on web/mobile applications. If the same data is frequently fetched, caching can improve latencies and reduce the load on data warehouse resources.

5. Query your Snowflake API with React

We will make a chart using graphql2chartjs to reshape your GraphQL data per the ChartJS API. This makes it easy to query a GraphQL API and render the output as a ChartJS chart.

You can install it via:

npm install --save graphql2chartjs

Post that, you can create a bar chart component as an example,

import {Query} from 'react-apollo';
import gql from 'graphql-tag';
import graphql2chartjs from 'graphql2chartjs';
import {Bar} from 'react-chartjs-2';
const Chart = () => (
  <Query
    query={gql`
      query {
        WEEKLY_TEMPRATURE(order_by: {week: asc}) {
          label: week
          data: TEMPRATURE
        }
      }`}
    }>
    {({data} => {
 if (data) {
        const g2c = new graphql2chartjs(data, 'bar');
        return (<Bar data={g2c.data} />);
      }
      return null;
    }
  </Query>
);

You can use this component in your react application to render charts like the one below; it uses NYC weather data over the last six years.

Conclusion

This post taught us three easy steps to create an API over Snowflake and then call that API from a React frontend. In a follow-up post, we will learn how easy it is to add access control to your APIs using Hasura.

Hasura, unlike other solutions, doesn’t require you to maintain a copy of data, and it generates SQL queries based on your request; it also provides a way to cache and rate limit same time while providing authentication and authorization options to safeguard your data.

You can try out our Snowflake (beta) support on Hasura Cloud.