Snowflake External Functions

Introduction

Snowflake has recently announced external functions available in public preview. This allows developers to invoke external APIs from within their Snowflake SQL queries and blend the response into their query result, in the same way as if they were internal Snowflake functions.

In this article, we will demonstrate how to invoke an API via Amazon Web Services API Gateway that will trigger an AWS Lambda function. The Lambda function (written in Python) then  invokes a public API from to return the exchange rate for USD and multiple foreign currencies that can be used to calculate our sales values in USD and a number of selected currencies in SQL query running in our Snowflake warehouse. This solution eliminates the need for loading exchange rates into Snowflake regularly and also guarantees accurate, reliable real-time currency values.

Architecture

The solution architecture is as follows:

As mentioned about, Snowflake initiates the API request to AWS API Gateway using an External Function that is referred in an SQL statement.  AWS API Gateway triggers the Lambda function that will call the ExchangeRate-APO REST API and process the response returned in JSON. I will then pack the requested exchange rate into a Snowflake-defined JSON format so the External function can interpret the values and blend it into the query result in Snowflake. Similar architecture could be used to handle stock values, translated texts and many other publicly available APIs.

AWS Configuration

First we will need define the Lambda function, in our case it is written in Python (note: in the code below you need to replace XXXXXXX with your own API key for )

x

import json

from botocore.vendored import requests

import logging

logger = logging.getLogger()

logger.setLevel(logging.DEBUG)

def lambda_handler(event, context):

    logger.debug('event input')

    logger.debug(event)

    event_data = event['data']

    logger.debug(event_data)

    currency = event_data[0][1]

    logger.debug(currency)

    requested_rate = []

    # HTTP Request using Exchange Rate API

    response = requests.get('https://v6.exchangerate-api.com/v6/XXXXXXXX/latest/USD')

    logger.debug(response.text)

    exchange_rates = json.loads(response.text) #load data into a dict of objects

    conversion_rates = exchange_rates['conversion_rates']

    logger.debug(conversion_rates)

    row_to_return = [0, conversion_rates[currency]]

    requested_rate.append(row_to_return)

    logger.debug(requested_rate)

    json_response = json.dumps({"data" : requested_rate})

    status_code = 200

    return {

        'statusCode': status_code,

        'data': requested_rate

    }

We can test the Lambda function using the Test feature e.g. with the following test event:

xxxxxxxxxx

{

  "data": [

    [

      0,

      "GBP"

    ]

  ]

}

If the Lambda function returned the JSON response as expected then we can expose it via API Gateway:

We need to define a REST POST method that can be invoked using a specific URL:

AWS API Gateway also provides a Client Test feature where we can specify the incoming request body and execute the end-to-end flow including the Lambda function.

Once the API Gateway works as expected, we can move over to Snowflake.

Snowflake Confguration

The first step is to create the API using the specific Snowflake SQL statement (note: the Account Id and the API-GW values need to be replaced with your own parameters from the AWS configuration described above) This will also require a particuar AWS IAM role (in our case called APIGW-Lambda) that allows the API Gateway call from Snowflake. This parameter is referred in the API_AWS_ROLE_ARN attribute below:

x

-- create API integration

create or replace api integration exchange_rate_api

   api_provider=aws_api_gateway

   api_aws_role_arn='arn:aws:iam::<Account ID>:role/APIGW-Lambda'

   api_allowed_prefixes=('https://<API-GW>.execute-api.us-east-1.amazonaws.com/Dev')

   enabled=true;

Then the next step is to define the External Function:

x

-- create external function

create or replace external function exchange_rate(input string)

   returns string

   api_integration = exchange_rate_api

   as 'https://<API-GW>.execute-api.us-east-1.amazonaws.com/Dev';

The AWS IAM Role needs to have an established trust relationship so we need to define a Trust Policy for this role:

x

    {

      "Effect": "Allow",

      "Principal": {

        "AWS": "arn:aws:iam::<ACCOUNT>:user/<SNOWFLAKE>"

      },

      "Action": "sts:AssumeRole",

      "Condition": {

        "StringEquals": {

          "sts:ExternalId": "<EXTERNAL ID>"

        }

      }

    }

The required values for the AWS trust policy can be retrieved using :

xxxxxxxxxx

describe integration exchange_rate_api;

And now we have everything ready to invoke the external function from Snowflake Worksheet:

x

create or replace table sales(name string, value_in_USD number);

insert into sales values ('Sales_1', 100);

select * from sales;

# Invoke the external function

select value_in_USD, exchange_rate('EUR')*value_in_USD as value_in_EUR, exchange_rate('GBP')*value_in_USD as value_in_GBP   from sales;   

And the result will look like this:

Conclusion

This tutorial demonstrates a simple use case of how we can embed external functions into Snowflake SQL and invoke 3rd-party APIs from within SQL. The Lambda function could be more elaborate to handle multiple rows but in general, it should provide you a fairly good understanding of how these functions and the API calls can be used. This can take our data warehouse toolset of built-in and user-defined-functions to the next level; I am really looking forward to seeing various real-life scenarios where external functions can provide great value. More details about Snowflake External Functions can be found  here.