Step 1: Create the Remote Service (Google Cloud Function)

These instructions show how to create a Google Cloud Function that you can use as your remote service.

This documentation assumes that you are already familiar with GCP administration. These instructions describe the general steps that you need to execute, but do not describe the user interface in detail because the details could change.

Google also provides a command-line interface that you can use for many of these steps. For details about that, see the GCP documentation.

In this Topic:

Create the Google Cloud Function

Instructions for Creating a Google Cloud Function

  1. Create a Google Cloud Function by following Google’s instructions to create a Cloud Function . If you are creating the sample Python-language function that Snowflake supplies, then choose the Python Quickstart; otherwise, choose the appropriate QuickStart based on the language that you need. As you follow Google’s instructions, make sure that you do the following:

    • Specify that the trigger for the function is HTTP.

    • Copy the trigger URL to the “Cloud Function Trigger URL” field in the worksheet above.

    • In the Authentication section, select Require authentication.

      The GCP instructions say to select Allow unauthenticated invocations. That is acceptable for sample functions, including the sample function provided by Snowflake, but most production systems should require authentication.

    • If Require HTTPS is not already enabled, then enable it.

    • Click Save.

    • Select an appropriate Runtime. If you are creating the sample Python function supplied by Snowflake, then choose the Python 3.7 runtime.

      Important

      Select the Runtime value before you paste in the code.

    • Replace the default code with either the Snowflake sample code or with your own custom code. The Snowflake sample code is at Sample Synchronous Google Cloud Function.

    • Make sure that the Entry point matches the name of the function (in this case, echo).

  2. After you finish creating the Cloud Function, use the Testing tab to call the function to make sure that it works as expected.

    For the sample Python function provided by Snowflake, use the following test data (replace any default data in the Testing tab with the data below):

    { "data":
      [
        [ 0, 43, "page" ],
        [ 1, 42, "life, the universe, and everything" ]
      ]
    }
    

    The execution results should be similar to:

    {"data":
      [
        [0, [43, "page"] ],
        [1, [42, "life, the universe, and everything"] ]
      ]
    }
    

    (The results might be displayed in a different format from the example shown above.)

    If the preceding worked correctly, you now have a Google Cloud Function that you can use as the remote service for your external function.

Sample Synchronous Google Cloud Function

This sample code combines the input parameter values into a single list (array) and returns that list as a single value of SQL type VARIANT. The code is written in Python 3.7.

This function accepts and returns data in the same format (JSON) that Snowflake sends and reads. (For more information about data formats, see Remote Service Input and Output Data Formats .)

import json

HTTP_SUCCESS = 200
HTTP_FAILURE = 400

def echo(request):
    try:
        # The list of rows to return.
        return_value = []

        payload = request.get_json()
        rows = payload["data"]

        # For each input row
        for row in rows:
            # Include the row number.
            row_number = row[0]
            # Combine the value(s) in the row into a Python list that will be treated as an SQL VARIANT.
            row_value = row[1:]
            row_to_return = [row_number, row_value]
            return_value.append(row_to_return)

        json_compatible_string_to_return = json.dumps( { "data" : return_value } )
        return (json_compatible_string_to_return, HTTP_SUCCESS)

    except:
        return(request.data, HTTP_FAILURE)