In February, Snowflake announced GA of the External Functions feature. This feature supports calling external APIs via AWS API Gateway and Azure API Management. Now Snowflake is excited to announce the public preview of External Functions support for Google Cloud API Gateway.
With Snowflake’s External Functions feature, you can easily extend your data pipelines by calling out to external services, third-party libraries, or even your own custom logic, enabling exciting new use cases. This blog post shows an example of translating messages in a Snowflake table from English to French by using external functions to call the Google Cloud Translation API through the Google Cloud API Gateway. With external functions, the workflow is greatly simplified because you don’t need to manually export the data out of Snowflake, translate it, and then reimport it.
Example
In the following example, we demonstrate how to use external functions to translate a text string from English to French.
Solution architecture
As shown in Figure 1, when the client executes an external function referred to in a SQL statement, Snowflake initiates the API request to Google Cloud API Gateway, triggering a Google Cloud Function that formats the Snowflake-provided JSON, calls the Google Cloud Translation API, and processes the response. The function then packs the requested translation into a Snowflake-defined JSON format so the external function can interpret the values and blend it into the query result in Snowflake.
Figure 1: External functions architecture
You could use a similar architecture to handle sentiment analysis, fetch real-time stock prices and weather forecasts, or perform pretty much any functionality provided by publicly available APIs.
The example includes five high-level steps:
- Creating a Python Google Cloud Function that responds to HTTP requests
- Creating and configuring the Google Cloud API Gateway service
- Creating an API integration and external function in Snowflake
- Securing the Google Cloud API Gateway endpoint
- Calling the external function
Creating a Python Google Cloud Function
First, we create a new Python Google Cloud Function that responds to HTTP requests. The default code is replaced with the code shown below. (Note: Remember to enable the Google Cloud Translation API for your project and while creating the cloud function, update the requirements.txt file to add google-cloud-translate>=2.0.1 as a dependency.)
import json
HTTP_SUCCESS = 200
HTTP_FAILURE = 400
def translate(request):
from google.cloud import translate_v2 as translate
trans_client = translate.Client()
try:
# The list of translated rows to return.
translated = []
payload = request.get_json()
rows = payload["data"]
# For each input row call the translate API.
for row in rows:
input_string = row[1]
trans = trans_client.translate(input_string, target_language='fr')
row_to_return = [row[0], trans['translatedText']]
translated.append(row_to_return)
json_compatible_string_to_return = json.dumps( { "data" : translated } )
return (json_compatible_string_to_return, HTTP_SUCCESS)
except:
return(request.data, HTTP_FAILURE)
After creating the Google Cloud function, you can test it with the test data below:
{
"data": [
[
0,
"hello"
]
]
}
Creating and configuring Google Cloud API Gateway service
The next step is to create and configure the Google Cloud API Gateway service to route the incoming requests to the Google Cloud Function created in the previous step.
As shown in the architecture diagram (Figure 1), Snowflake does not send data (HTTP POST requests) directly to the remote service (Google Cloud Function). Instead, Snowflake sends the data to the proxy service, Google Cloud API Gateway, which relays the data from Snowflake to the Google Cloud Function and from the Google Cloud Function back to Snowflake.
See the instructions provided by GCP for creating and configuring the Google Cloud API Gateway service.
Creating an API integration and external function in Snowflake
After setting up and configuring resources on GCP, we enable Snowflake to securely access the Google Cloud API Gateway endpoint by using the following code to create the API integration and external function:
-- create API integration
create or replace api integration external_api_integration
api_provider = google_api_gateway
google_audience = '<google_audience_claim>'
api_allowed_prefixes = ('<your-google-cloud-api-gateway-base-url>')
enabled = true;
-- create external function
create or replace external function translate_en_french(input string)
returns variant
api_integration = external_api_integration
as 'https://<your-google-cloud-api-gateway-base-url>/<path-suffix>’;
Replace the values in <> with the appropriate values by following the instructions in the Snowflake documentation.
Securing the Google Cloud API Gateway endpoint
Next, we want to ensure that only Snowflake can access the APIs available through the Google Cloud API Gateway instance. We do this by setting up authentication via Google service accounts.
We execute the following DESCRIBE INTEGRATION command and record the value of the API_GCP_SERVICE_ACCOUNT.
describe api integration external_api_integration;Follow the instructions to add a customized securityDefinitions section to the configuration file for the Google Cloud API gateway definition.
Calling the external function
Now everything is in place to invoke the external function on the table that contains messages to be translated. Figure 2 shows that the call to the external function goes through, translating English messages to French.
Figure 2: External function translation results
Conclusion
This blog post demonstrated a simple example of using external functions to invoke third-party APIs via a Google Cloud API Gateway service. Snowflake will expand support to other common proxies and configurations, so watch for updates.
Note on costs: In addition to Snowflake costs, the setting up of the above example will incur charges from Google Cloud’s Translation API, Cloud Functions and API Gateway services. Please check out the individual pricing pages for more details.
Get more from Snowflake
To try external functions on GCP, see the detailed documentation. We look forward to seeing all the interesting use cases you create.