Creating a Sample External Function Using an AWS CloudFormation Template

This document shows how to create a sample external function on AWS by using a CloudFormation 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 stage 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 a Customizable External Function on AWS.

If you would like to customize the template, you can read more about AWS CloudFormation .

Note

These instructions assume that you are already familiar with AWS 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 CloudFormation template performs both of the following steps in creating an external function:

  • Creating the remote service (e.g. AWS lambda).

  • Creating and configuring the proxy service (e.g. the Amazon API Gateway).

The template also:

  • Creates two IAM roles (one for the Lambda Function and one for the API Gateway).

  • Configures a resource policy for the API Gateway.

To download the template from Snowflake, point your browser to:

Prerequisites

You need:

  • An account with AWS, including privileges to:

    • Create AWS roles via IAM (identity and access management).

    • Create AWS Lambda functions.

    • Create an API Gateway endpoint.

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

This document assumes that you are an experienced AWS administrator.

Quick-start Worksheet

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

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

New IAM Role Name........: _____________________________________________
New IAM Role ARN.........: _____________________________________________
Resource Invocation URL..: _____________________________________________
API_AWS_IAM_USER_ARN.....: _____________________________________________
API_AWS_EXTERNAL_ID......: _____________________________________________

Step 1: Create the AWS Lambda and API Gateway by Using the Template

  1. Go to the AWS Management Console.

  2. In the top search bar, search for CloudFormation.

  3. Under Services, click on CloudFormation.

  4. Click on Create stack.

    If given a choice between With new resources (standard) or With existing resources (import resources), then choose With new resources (standard).

  5. On the Create stack page, under Prepare template, select Template is ready.

  6. Select Upload a template file.

  7. Select Choose file.

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

  9. Click Next to reach the page on which you enter names for roles, etc.

    Note

    The template uses default names for some resources. You can change the names.

  10. Enter a name for the stack.

  11. Enter a name for the API Gateway IAM role (parameter apiGatewayIAMRoleName). This is the role assumed by Snowflake for authorizing with the API Gateway. Make sure this role does not already exist because the template will try to update the role if it exists.

    Record the role name in the quick-start worksheet field titled “New IAM Role Name”.

  12. Enter a name for the Lambda Execution role (parameter lambdaExecutionRoleName). This role is used by the Lambda service for adding CloudWatch logs. Make sure this role does not already exist because the template will try to update the role if it exists.

  13. Click Next.

    This page has some advanced options for template deployment.

    1. Optionally, set advanced options, such as stack policy. (These are usually not needed for demonstrations.)

    2. Click Next.

  14. On the review page, scroll down to the end and acknowledge that the CloudFormation template might create IAM resources with custom names. This is needed because the template creates two IAM roles as part of the deployment.

  15. Click on Create stack.

The deployment will take a few seconds. After the deployment is complete, you should be on the Events tab for the newly created stack. The created resources will be listed under the Resources tab.

In order to create the API Integration and the external function, you need the API gateway URL and the New IAM Role ARN, which you can find by following the steps below.

  1. Click on the Outputs tab.

  2. Copy the value for resourceInvocationUrl to the quick-start worksheet field titled “Resource Invocation URL”.

  3. Copy the value for awsRoleArn to the quick-start worksheet field titled “New IAM Role ARN”.

Step 2: Create the API Integration

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

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

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

  1. Create API Integration

  2. Set up the trust relationship between Snowflake and the new IAM role

Then return to this page.

Step 3: Create the External Function

Now that you have created the API Integration, you need to create the external function.

When you create the external function, use the value in the quick-start worksheet field titled “Resource Invocation URL” for the invocation_URL.

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

Create the External Function

Then return to this page.

Step 4: Call the Function

Test the function by following these instructions:

Call the external function

Troubleshooting

CloudFormation stack creation fails

Possible cause:

You do not have required permissions for creating the resources specified in the CloudFormation template. Check the Events tab for the stack to see the error details.

Also look at the AWS external functions troubleshooting page for additional troubleshooting tips.