Creating a Sample External Function Using an Azure Resource Manager Template

This document shows how to create a sample external function on Microsoft Azure by using an Azure Resource Manager (ARM) template.

Snowflake provides a template you can start with. This template hides some details of the creation process and hard-codes some names (e.g. the trigger name) and functionality. When you are ready to create your own custom external function, you can either customize a copy of the template, or you can follow the more flexible instructions at Creating an External Function on Microsoft Azure Using the Azure Portal .

Note

These instructions assume that you are already familiar with Microsoft Azure administration. These instructions describe the general steps that you need to execute, but do not describe the user interface in detail because the interface could change.

In this Topic:

See also:

Introduction

The ARM template performs both of the following steps in creating an external function:

  • Creating the remote service (e.g. the Azure function).

  • Creating and configuring the proxy service (e.g. the Azure API Management service).

The template also:

  • Creates a storage account needed by the Azure Function service.

  • Adds a validate-JWT Policy to the API Management instance in order to increase security of the Azure API Management service. However, you must manually update the JWT policy before using it.

To download the template from Snowflake, point your browser to the Snowflake repository in GitHub.

Planning Your External Function on Azure

Prerequisites

You need:

  • An account with Azure, including privileges to:

    • Create Azure Functions.

    • Create an API gateway using API Management.

  • A Snowflake account in which you have ACCOUNTADMIN privileges or a role with the CREATE INTEGRATION privilege.

You should already have the following information:

Azure AD Tenant ID: _____________________________________

This is a UUID , which typically is formatted to look similar to 12345678-abcd-1234-efab-123456789012, where each non-dash character is a hexadecimal digit.

If you do not already know your Azure AD tenant ID, you can find it by doing the following:

  1. Log into the Azure portal (http://portal.azure.com).

  2. In the Azure services icons near the top of the page, click on Azure Active Directory.

  3. In the menu on the left-hand side, look for the section titled Manage, then click on Properties under that.

    The Azure AD tenant ID is displayed in the Tenant ID field.

This document assumes that you are an experienced Azure Portal user.

Worksheet

As you create your external function, you should record specific information that you enter (e.g. the API Management service name) so that you can use that information in subsequent steps. The worksheet below helps you track this information.

For information hard-coded in the Azure Resource Management template provided by Snowflake, the values have already been filled in below.

==============================================================================================
====================================== Quick-start Worksheet =================================
==============================================================================================

----------------- Information about the Azure Function (remote service) ----------------------

HTTP-Triggered Function name...........: __________________ echo ________________

Azure function AD Application ID.......: ________________________________________

    (This is the "Application (client) ID" of the Azure AD app registration for the Azure function,
    and is used to fill in the "azure_ad_application_id" field in the "CREATE API INTEGRATION" command.
    This is in the form of a UUID.)


------------ Information about the Azure API Management Service (proxy service) --------------

API Management service name......: __________________________________________

API Management URL...............: __________________________________________

Azure Function HTTP Trigger URL..: __________________________________________

API Management API URL suffix....: __________________________________________


---------------- Snowflake API Integration and External Function Information -----------------

API Integration Name.......: _______________________________________________

AZURE_MULTI_TENANT_APP_NAME: _______________________________________________

AZURE_CONSENT_URL..........: _______________________________________________

External Function Name.....: _______________________________________________

Step 1: Create an Azure AD app for the Azure function app

  1. Log in to the Azure portal.

  2. Search for the App registrations page.

  3. Click on New registration, which takes you to the Register an application screen.

  4. Enter a unique name for your Azure AD app.

  5. Record the name of the Azure AD app in the quick-start worksheet field titled “Azure function AD app registration name”.

  6. Under Supported account types, choose Accounts in this organizational directory only (Default Directory only - Single tenant).

  7. Click on Register.

    This takes you to the Home > App registrations screen and shows the newly created Azure AD app.

  8. Record the Application (client) ID from the Azure AD app you just created in the quick-start worksheet field titled “Azure function AD Application ID”. This ID should be in the form of a UUID.

Step 2: Create the Azure Function and API Management Service by Using the Template

  1. Go to the Azure portal.

  2. In the Azure search bar, search for Template.

  3. Under Services, click on Deploy a custom template.

  4. Select Build your own template in the editor.

  5. Select Load file.

  6. Navigate to the directory that contains your copy of the template, then select that template.

  7. Click Save.

    This takes you to the Custom deployment screen.

  8. Select an existing (or create a new) Resource group.

    Tip

    If you create a new resource group solely for this demonstration, then you might want to record the name so that you can delete it later when you are done with it.

  9. Select the appropriate Region.

  10. Enter an API Management Service Name.

  11. Record the API Management Service name in the quick-start worksheet field titled “API Management service name”.

  12. In the Function App Name field, enter a unique name.

  13. Record the Function App Name in the quick-start worksheet field titled “Azure function app name”.

  14. In the Publisher email field, enter your email address. (Microsoft emails you to notify you after the API Management service has been created.)

  15. In the Azuread Application Id field, enter the ID of the Azure AD application you created earlier. This is the value in the quick-start worksheet field titled “Azure function AD Application ID”.

  16. Click on Review + create.

  17. Click on Create.

Creating the Azure function app and API Management service typically takes approximately half an hour.

In order to create the API Integration and the external function, you need the API Management service’s URL, which you can find by following the steps below after Azure has finished creating the API Management service.

At this point, the Azure portal should show the message Your deployment is complete and should show the Deployment name.

  1. Click on “Outputs” in the left-hand column.

  2. Copy the api Management URL to the quick-start worksheet field titled “API Management URL”.

  3. Copy the azure Function Http Trigger URL to the quick-start worksheet field titled “Azure Function Http Trigger URL”.

Step 3: Create the API Integration

Now that you have created the remote service (Azure Function) and the proxy service (API Management service), you need to create the API Integration.

When you create the API Integration, use the value in the quick-start worksheet field titled “API Management URL” as the value for the api_allowed_prefixes clause.

Using the information above, execute the steps in the links below:

Then return to this page.

Step 4: Create the External Function

When you create the external function, you are asked for the invocation URL. You can use the value in the quick-start worksheet field titled “Azure Function HTTP Trigger URL” as the invocation URL.

Using the information above, execute the steps in the link below:

Create the External Function.

Then return to this page.

Step 5: Update the Security Policy in the Azure API Management Service (Proxy Service)

The Azure Resource Manager template supplied by Snowflake creates a security policy to validate a JWT (JSON Web Token) that authorizes Snowflake to call your Azure function.

However, the security policy is missing one field, which you need to fill in. From the Azure portal, execute the following steps:

  1. Select API Management Services.

  2. Find the API Management Service instance that you created. The name of this instance is recorded in the quick-start worksheet field titled “API Management service name”.

  3. Click on the API Management Service instance name.

  4. Select APIs -> APIs.

  5. Under All APIs, select ext-func-api.

  6. Select POST echo.

  7. Click on the button validate-JWT, which is in the Inbound processing box.

    If you cannot see this button, please scroll down.

  8. Search for “SNOWFLAKE_SERVICE_PRINCIPAL_ID”, and replace it with the Snowflake app ID.

    If you do not already have the Snowflake app ID, you can get it by performing the following steps:

    1. In the worksheet, find the AZURE_MULTI_TENANT_APP_NAME that you filled in earlier.

    2. In the Azure Portal search box, look for Enterprise Applications.

      This takes you to the Enterprise applications | All applications screen.

    3. In that screen, search for the AZURE_MULTI_TENANT_APP_NAME.

      The enterprise applications search box does not have a label. Look for a wide field immediately above the list of enterprise applications. The box might say something similar to First 50 shown, to search all of your applications, enter a display name or the application ID.

      If you do not find an exact match for the AZURE_MULTI_TENANT_APP_NAME, then search again using only the first several characters of this name (if the name contains an underscore, then do not include the underscore or any characters after the underscore).

    4. Find the Application ID value for the AZURE_MULTI_TENANT_APP_NAME.

  9. Click Save.

Step 6: Call the Function

Test the function by following these instructions:

Call the external function.

Troubleshooting

Request failed for external function <external_function_name> with remote service error: 500

Possible cause:

You might have chosen the wrong option for your Azure AD app. Specifically, you might have chosen Accounts in any organizational directory (Any Azure AD directory - Multitenant) and personal Microsoft accounts (e.g. Skype, Xbox) rather than the correct option Accounts in this organizational directory only (Default Directory only - Single tenant).

Request failed for external function <function_name> with remote service error: 401 … “Invalid JWT”

You might see the message:

Request failed for external function <function_name> with remote service error: 401 ‘{ “statusCode”: 401, “message”: “Invalid JWT.” }’

Possible Cause:

If you are using the Azure Resource Manager template, you might not have updated the JWT that the template created for you. The section Step 5: Update the Security Policy in the Azure API Management Service (Proxy Service) has instructions.