Support for Calling External Functions via Azure API Management Now in Public Preview

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. 

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.

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.

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.

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

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.)

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

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.

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. 

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.

• We create the Azure API Management Service by following the instructions provided by Microsoft.
• We then import and publish our Azure function app to be called through the Azure API Management service.

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:

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:

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. 

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.

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!

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!