Skip to content
  • AT SNOWFLAKE
  • Industry solutions
  • Partner & Customer Value
  • Product & Technology
  • Strategy & Insights
Languages
  • 한국어
  • Français
  • 日本語
  • English
  • Italiano
  • Português
  • Español
  • Deutsch
  • 한국어
  • Français
  • 日本語
  • English
  • Italiano
  • Português
  • Español
  • Deutsch
  • AT SNOWFLAKE
  • Industry solutions
  • Partner & Customer Value
  • Product & Technology
  • Strategy & Insights
  • 한국어
  • Français
  • 日本語
  • English
  • Italiano
  • Português
  • Español
  • Deutsch
  • Panoramica
    • Perché Snowflake
    • Storie di clienti
    • Partners
    • Servizi
  • Panoramica
    • La piattaforma
    • Il Marketplace Snowflake
    • Snowpark
    • Powered by Snowflake
    • Demo live
  • Workloads
    • Collaboration
    • Data Science & ML
    • Cybersecurity
    • Applications
    • Data Warehouse
    • Data Lake
    • Data Engineering
    • Unistore
  • Prezzi
    • Tutte le opzioni
  • Settori
    • Pubblicità, Media e Entertainment
    • Servizi finanziari
    • Healthcare e Life Sciences
    • Settore manifatturiero
    • Settore pubblico
    • Retail / CPG
    • Technology
  • For Departments
    • Marketing
    • IT
  • Imparare
    • Libreria risorse
    • Sviluppatori
    • Avvio rapido
    • Documentazione
    • Lab virtuali
    • Formazione
    • Le guide
    • Glossario
  • Collegamento
    • Blog
    • Comunità
    • Eventi
    • Webinars
    • Podcast
    • Supporto
    • Tendenza
  • Panoramica
    • Informazioni su Snowflake
    • Investor Relations
    • Leadership e CdA
    • Opportunità di lavoro
    • Sala stampa
    • ESG
    • Snowflake Ventures
Author
Sri Chintala Sri Chintala
Contributing Author
External Functions Team
Share
Subscribe
Apr 09, 2021 10 min read

Support for Calling External Functions via Google Cloud API Gateway Now in Public Preview

  • Prodotto e tecnologia
Support for Calling External Functions via Google Cloud API Gateway Now in Public Preview

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:

  1. Creating a Python Google Cloud Function that responds to HTTP requests
  2. Creating and configuring the Google Cloud API Gateway service
  3. Creating an API integration and external function in Snowflake
  4. Securing the Google Cloud API Gateway endpoint
  5. 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.

Share

Artificial Intelligence Products

Artificial intelligence products are growing in number and utility and fueled by technological advances.

See how
Read More

How Does Data Replication Work?

Learn about data replication and how it is evolving to meet the needs of the modern cloud-based data warehouse.

More
Read More

Use Your Existing API to Become a Snowflake Data Marketplace Provider

For a Data Marketplace provider, Snowflake’s recently announced external functions capability allows Snowflake accounts to...

Learn More
Read More

Support for Calling External Functions via Azure API Management...

In June, Snowflake announced the public preview of the external functions feature with support for calling external APIs via...

See how
Read More
Snowflake Inc.
  • La piattaforma
    • Il Data Cloud
    • L’architettura
    • Prezzi
    • Il Marketplace Snowflake
    • Sicurezza e fiducia
  • SOLUZIONI
    • Servizi finanziari
    • Pubblicità, Media e Entertainment
    • Retail / CPG
    • Marketing Analitico
  • RISORSE
    • Libreria risorse
    • Webinar
    • Documentazione
    • Community
    • Procuratore
    • Legale
  • Esplorare
    • Notizie
    • Blog
    • Tendenza
    • Guide
    • Sviluppatori
  • CIRCA
    • Informazioni su Snowflake
    • Investor Relations
    • Leadership e CdA
    • Snowflake Ventures
    • Opportunità di lavoro
    • Contatto

Thanks for signing up!

  • Privacy Policy
  • Site Terms
  • Cookie Settings
  • Do Not Share My Personal Information

© 2023 Snowflake Inc. All Rights Reserved |  If you’d rather not receive future emails from Snowflake, unsubscribe here or customize your communication preferences