An API endpoint is a URL that accepts a request and returns a response. Endpoint documentation identifies available input parameters, the returned output format, and the authentication needed to call it, such as an API key or token. section 7.1
Endpoints let a Snowflake pipeline retrieve data from network locations outside Snowflake and augment tables with external results. A Python UDF or stored procedure can call an endpoint, then store and process the returned data in Snowflake. section 7.0 section 7.5
Figure 7.1 Snowflake's external network access architecture consists of a network rule pointing to an external network location and a secret containing a token, key, or password. The network rule and secret are included as parameters when creating an external access integration object. This object is passed as a parameter to a Python function or stored procedure so that the function or procedure can call the external API.
External endpoint access is controlled through an external access integration. A network rule identifies permitted locations, while a secret holds the credential; both are supplied to the integration used by a Python function or stored procedure. section 7.1
Calling an endpoint from a Snowflake pipeline requires controlled setup:
- Identify the endpoint URL and obtain the required API key or other authentication method. section 7.1 section 7.5
- Create a network rule for the external host, a secret for the credential, and an external access integration that includes both. section 7.1
- Grant the pipeline user access to the integration and read access to the secret; administrators can monitor requests to external locations. section 7.1 section 7.5
- Attach the integration to a Snowpark Python UDF or stored procedure and use the requests package to issue the API call. section 7.5
- Parse a JSON response, flatten it when needed for SQL, and clean unprintable characters before querying the result. section 7.5
Ingesting customer reviews
A Snowpark Python UDF can call Yelp’s reviews endpoint with a bakery business alias taken from the business page URL, return up to three review excerpts under the free API, and load them into a Snowflake table for later sentiment analysis. section 7.1 section 7.2 section 7.0
