HOW TO: Make External API calls from Snowflake?

1. Introduction

Many real-world use cases require integration with external systems via APIs to extract, enrich, and augment data for more comprehensive analytics. Snowflake, as a powerful cloud data platform, offers a secure and efficient way to make external API calls directly within its environment.

In this article, we’ll explore the steps to set up and execute external API calls directly from Snowflake.

2. Steps to Make External API calls from Snowflake

The following steps outline how to make external API calls from Snowflake:

1. Create a Network Rule: Define the endpoints to which traffic to be allowed.
2. Create a Secret: Securely store authentication details such as usernames, passwords, tokens, etc.
3. Create an External Access Integration: Combine the network rules and secrets into a single configuration.
4. Create a Snowflake Stored Procedure or Function: Use the External Access Integration and Secrets to execute API calls from Stored Procedure or a Function.

For this demonstration, we will connect to the GoRest external public API and extract user details.

Request Method: GET
Request URL: https://gorest.co.in/public/v1/users/
Query Params = {"id":user_id}
headers = {"Authorization": "Bearer " + access_token}

The image below shows the user details retrieved from the API using Postman.

2.1. Create Network Rule

Network Rules are schema-level objects that store information about network endpoints. A Network Rule does not specify whether endpoints are allowed or blocked. This is determined by the Snowflake feature using the rule i.e. External Access Integration in our scenario.

When creating a network rule for use in an external access integration, the parameters should be defined as follows:

• EGRESS as the value for the MODE parameter.
• HOST_PORT as the value for the TYPE parameter, indicating the type of network.
• The external location’s endpoint in the VALUE_LIST parameter.

The following statement creates a network rule that defines the GoRest endpoint.

CREATE OR REPLACE NETWORK RULE DEMO_DB.DEMO_SCHEMA.NR_GOREST
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('gorest.co.in');

2.2. Create Secret

Secrets are schema-level objects that securely store authentication credentials for external network access. They can hold sensitive information such as usernames, passwords, tokens, and other authentication details required for secure communication.

The following statement creates a Snowflake secret that stores the access token required for connecting to the GoRest API.

CREATE OR REPLACE SECRET DEMO_DB.DEMO_SCHEMA.SECRET_GOREST
  TYPE = GENERIC_STRING
  SECRET_STRING = 'f313426cafa7e95b61fa3ef4133afc32';

2.3. Create External Access Integration

External Access Integrations are account-level objects that aggregates allowed network rules and allowed secrets for use with UDFs and procedures. They can be enabled or disabled to manage the access to external locations.

The following statement creates an external access integration that specifies the network rule and secret configured for connecting to the GoRest API.

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION EAI_GOREST
  ALLOWED_NETWORK_RULES = (NR_GOREST)
  ALLOWED_AUTHENTICATION_SECRETS = (SECRET_GOREST)
  ENABLED = true;

2.4. Create Snowflake Snowpark Stored Procedure

A Stored Procedure must be created to make the actual API call. The defined external access integration and secret need to be assigned to the EXTERNAL_ACCESS_INTEGRATIONS and SECRETS parameters within the procedure to enable secure interaction with the external API.

The following stored procedure connects to the GoRest API and retrieves user details based on the USER_ID passed as an input parameter.

CREATE OR REPLACE PROCEDURE SP_GET_USER_DETAILS(USER_ID NUMBER)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.9
HANDLER = 'get_user_details'
EXTERNAL_ACCESS_INTEGRATIONS = (EAI_GOREST)
PACKAGES = ('snowflake-snowpark-python', 'requests')
SECRETS = ('cred' = SECRET_GOREST)
AS
$$
import _snowflake
import requests
import snowflake.snowpark as snowpark

def get_user_details(session: snowpark.Session, USER_ID):
    # Retrieve the access token from Snowflake secret
    access_token = _snowflake.get_generic_secret_string('cred')

    # Define the API URL, query parameters and headers
    url = "https://gorest.co.in/public/v1/users/"
    params = {"id": USER_ID}
    headers = {"Authorization": f"Bearer {access_token}"}
    
    # Make the API request
    response = requests.get(url, headers=headers, params=params)

    # Parse the JSON response to extract user data
    response_data = response.json()
    user_data = response_data.get('data', [])

    # Return User data
    if user_data:
        user = user_data[0]
        return f"User ID: {user.get('id')}, Name: {user.get('name')}, Email: {user.get('email')}, Status: {user.get('status')}"
    else:
        return "No user data found."
$$;

In the stored procedure:

• Line 6 enables the Stored Procedure to leverage the external access integration.
• Line 8 enables the Stored Procedure to leverage the Secret.
• Line 17 retrieves the access token from Snowflake secret.  The _snowflake module provides functions for accessing data in a secret.
• Line 19-21 defines the API URL, query parameters and headers required for making the API call.
• Line 23 make the API call using request.get method.
• Line 25 parses the JSON response from the API into a Python dictionary.
• Line 26 retrieves the user data from the ‘data‘ key from the response. It defaults to an empty list if no data is found.
• Line 28-30 checks if user data exists and returns the user details. Otherwise, it returns a “No user data found” message if no data is available.

2.5. Call Stored Procedure

The successful execution of the stored procedure returns the details of the user associated with the provided ID. The stored procedure is executed as follows, which retrieves the details of the user with ID 7673075.

CALL SP_GET_USER_DETAILS(7673075);

Output:

3. Summary

In this article, we took a deep dive into understanding how external API calls can be made from Snowflake using network rules, secrets, external access integrations. We also demonstrated how to integrate these components into a Snowflake stored procedure to securely retrieve data from an external API.

Subscribe to our Newsletter !!

Related Articles: