In June, Snowflake announced the public preview of the external functions feature with support for calling external APIs via AWS API Gateway. With external functions, you can easily extend your data pipelines by calling out to external services, third-party libraries, or even your own custom logic, enabling exciting new use cases. For example, you can use external functions for external tokenization, geocoding, scoring data using pre-trained machine learning models, and much more.

Snowflake is excited to extend external functions support to Azure API Management for public preview. You can now use external functions to call endpoints through Azure API Management. If you have your Snowflake deployment and resources running on Azure, you now have the option to ensure your gateway is also running on the same cloud and region and avoid making cross-cloud calls.

This blog shows an example of translating messages in a Snowflake table from English to Italian by using external functions to call an external API through Azure API Management. 

EXAMPLE

In the following example, we demonstrate how to use external functions to invoke an API via Azure API Management that will trigger an Azure function. The Azure function is written in Python and invokes the Microsoft Translator API provided by Azure Cognitive services to return the Italian translation for a given input text string. This solution with external functions eliminates the need to manually export the data out of Snowflake, translate it, and then reimport it, greatly simplifying the workflow.

Solution Architecture

As shown in Figure 1, when the client executes an external function referred to in an SQL statement, Snowflake initiates the API request to Azure API Management. Azure API Management triggers an Azure function that formats the Snowflake-provided JSON, calls the Microsoft Translator REST API, and processes the response. The function then packs the requested translation into a Snowflake-defined JSON format so the external function can interpret the values and blend it into the query result in Snowflake.

Figure 1: External functions architecture

You could use a similar architecture to handle sentiment analysis, real-time stock prices, weather forecasts, and pretty much any functionality provided by publicly available APIs.

Set up

The example includes five high-level steps:

  1. Creating a new Python Azure Function that responds to HTTP requests
  2. Configuring the Azure Function app to require Azure AD authentication
  3. Creating and configuring the Azure API Management service
  4. Creating an API integration and external functions in Snowflake
  5. Calling the external function

Creating a Python Azure Function

First, we create a new Python Azure function that responds to HTTP requests. We replace the default code with the code shown below. (Note: Remember to replace cognitive services key and region with your own data.)

import os, requests, json
import logging, httpx
import azure.functions as func
 
async def main(req: func.HttpRequest) -> func.HttpResponse:
   endpoint = "https://api.cognitive.microsofttranslator.com/"
   path = "/translate?api-version=3.0"
   params = "&to=it"
   constructed_url = endpoint + path + params
 
   headers = {
       "Ocp-Apim-Subscription-Key": "",
       "Ocp-apim-subscription-region": "",
       "Content-Type": "application/json"
   }
 
   req_body = req.get_json()
      
   if req_body :
       translated = []
       body = []
       i = 0
 
       # Format JSON data passed from Snowflake to what Translator API expects.
       for row in req_body["data"]:
           body.append({"text": row[1]})
      
       # Microsoft recommends using asynchronous APIs for network IO.
	 # This example uses httpx library to make async calls to the API.
       client = httpx.AsyncClient()
       response = await client.post(constructed_url, headers = headers, json = body)
       response_json = response.json()
  
       # Process and format response into Snowflake expected JSON.
       for row in response_json:
           translations = row["translations"][0]
           translated_text = translations["text"]
           translated.append([req_body["data"][i][0], translated_text])
           i += 1
    
       output = {"data": translated}
       return func.HttpResponse(json.dumps(output))
  
   else:
       return func.HttpResponse(
            "Please pass data to translate in the request body",
            Status_code = 400
       )

After we have created the Azure function, we can test it with the test data below:

{
 "data": [
   [
     0,
     "hello"
   ]
 ]
}

Configuring the Function app to require Azure AD authentication 

Next, we configure the Azure Function app to require Azure AD authentication. We do this by clicking on Authentication / Authorization in the Settings section of the Function app and selecting the settings shown in Figure 2. If there is no existing Azure AD app, we can create a new Azure AD app by selecting Create new app and following the prompts.

Figure 2: Setting up authentication/authorization

We note the Application (client) ID for the Azure AD App associated with our functions app from the App registrations page. When Snowflake authenticates with Azure API Management, it uses this AD application for OAuth Client Credential grant flow. 

Creating and configuring  Azure API Management service 

The next step is to create and configure the Azure API Management service. As shown in the architecture diagram (Figure 1), Snowflake does not send data (HTTP POST requests) directly to the remote service (our Azure Function). Instead, Snowflake sends the data to the proxy service, Azure API Management, that relays the data from Snowflake to the Azure Function, and from the Azure Function back to Snowflake.

Creating an API integration and external function in Snowflake

After setting up and configuring resources on Azure, we enable Snowflake to securely access the Azure API Management endpoint by using the following code to create the API integration and external function:

-- create API integration
create or replace api integration external_api_integration   api_provider = azure_api_management   azure_tenant_id = ''   azure_ad_application_id = ''   api_allowed_prefixes = ('https://.azure-api.net/')   enabled = true;
-- create external functioncreate or replace external function translate_en_italian(input string)    returns variant    api_integration = external_api_integration    as 'https://.azure-api.net//'    ;

Before we can call the external function, we need to link the Snowflake API Integration to the Azure API Management Service. We execute the DESCRIBE API integration command below:

describe api integration external_api_integration;

To grant Snowflake access to our Azure tenant, we get the API integration’s AZURE_CONSENT_URL, paste it into our browser, and click Accept. This creates a service principal in our Azure AD tenant that allows Snowflake to authenticate with Azure AD when calling the API Management service in our tenant.

Finally we want to ensure that only Snowflake is able to access the APIs available through the Azure API management instance. We do this by adding a ‚validate-jwt‘ policy on the Azure API Management Service to validate the authorization header sent from Snowflake. 

Figure 3: Adding a validate-jwt policy

Calling the external function

And now we have everything in place to invoke the external function on our table that contains messages to be translated. In Figure 3, we can see that the call to the external function goes through, translating our English messages to Italian.

Figure 4: External function translation results

CONCLUSION

This blog demonstrated a simple example of using external functions to invoke third-party APIs via Azure API Management service. Snowflake will also be expanding support to other common proxies and configurations, so stay tuned for updates!

GET MORE FROM SNOWFLAKE

Are you interested in trying out external functions on Azure? See the detailed documentation here to get started. We look forward to seeing all the interesting use cases you create!