Step 3: Create the API Integration for AWS in Snowflake

This topic provides instructions for creating an API integration object in Snowflake to work with your proxy service (i.e. Amazon API Gateway). The instructions are the same regardless of whether you are using the Management Console or the CloudFormation template.

In this Topic:

Prerequisites

You need the following information to create the API integration for AWS in Snowflake:

  • The New IAM Role ARN (from your tracking worksheet).

  • The Resource Invocation URL (from your tracking worksheet).

Create the API Integration Object

  1. Open a Snowflake session, typically a Snowflake web interface session.

  2. Use a Snowflake role with ACCOUNTADMIN privileges or the CREATE INTEGRATION privilege, for example:

    use role <has_accountadmin_privileges>;
    
  3. Type the CREATE API INTEGRATION command to create an API integration. The command should look similar to the following:

    CREATE OR REPLACE API INTEGRATION my_api_integration_01
      api_provider = aws_api_gateway
      api_aws_role_arn = '<new_IAM_role_ARN>'
      api_allowed_prefixes = ('https://')
      enabled = true;
    

    Customize the command:

    • If you are using a private endpoint, the api_provider clause should be set to aws_private_api_gateway. Otherwise, the api_provider clause should be set to aws_api_gateway.

    • The <new_IAM_role_ARN> should be the value in the “New IAM Role ARN” field in the tracking worksheet.

    • The api_allowed_prefixes field should contain the resource invocation URL that you recorded earlier.

    Below is an example of a complete CREATE API INTEGRATION statement:

    create or replace api integration demonstration_external_api_integration_01
        api_provider=aws_api_gateway
        api_aws_role_arn='arn:aws:iam::123456789012:role/my_cloud_account_role'
        api_allowed_prefixes=('https://xyz.execute-api.us-west-2.amazonaws.com/production/')
        enabled=true;
    
  4. In the tracking worksheet field titled “API Integration Name”, record the name of the API integration that you created. You need the API integration name when you execute the CREATE EXTERNAL FUNCTION command later.

  5. Execute the CREATE API INTEGRATION command you typed above.

Record the API_AWS_IAM_USER_ARN and API_AWS_EXTERNAL_ID

  1. Execute the DESCRIBE INTEGRATION command.

    DESCRIBE INTEGRATION <my_integration_name>;
    

    For example:

    DESCRIBE INTEGRATION my_api_integration_01;
    
  2. Look for the property named API_AWS_IAM_USER_ARN and then record that property’s property_value in the tracking worksheet.

  3. Find the property named API_AWS_EXTERNAL_ID and record that property’s property_value in the tracking worksheet.

    Note that the property_value of the API_AWS_EXTERNAL_ID often ends with an equals sign (“=”). That equals sign is part of the value; make sure that you cut and paste it along with the rest of the property_value.

For the next few steps, you return to your AWS administration window. Do not close your Snowflake administration window now; you must return to it later.