Using Request and Response Translators with Data for a Remote Service

Request translators and response translators allow you to change the format of data sent to, and received from, remote services used by external functions.

In this Topic:

Purpose

When Snowflake sends data to a remote service, Snowflake formats the data according to these rules. Similarly, when Snowflake receives data from a remote service, Snowflake expects the data to be formatted according to the same rules.

Many remote services expect to receive (and return) data in a different format. The External Function Request and Response Translator feature allows you to conveniently:

  • Convert data from Snowflake’s format to the remote service’s native input format (request translator).

  • Convert data from the remote service’s native output format to Snowflake’s format (response translator).

SQL Implementation

Introduction

Translating data between Snowflake’s format and the remote service’s native input format is done inside JavaScript UDFs (user-defined functions). You almost always write a pair of UDFs: one to translate the request and one to translate the response.

Snowflake calls these functions as part of each external function call. Specifically, Snowflake calls the request_translator function, passes it the Snowflake-formatted data, then takes the returned data and sends it to the remote service. Similarly, after the remote service returns data, Snowflake calls the response_translator function to convert the data back to the format that Snowflake understands.

From the user perspective, calling an external function with a request_translator or response_translator is the same as calling any other external function. After the request_translator and response_translator are written and are specified as part of the CREATE EXTERNAL FUNCTION statement, they are called automatically.

To specify which request_translator and response_translator to use with an external function, include the REQUEST_TRANSLATOR and RESPONSE_TRANSLATOR clauses in the CREATE EXTERNAL FUNCTION statement.

For example:

CREATE EXTERNAL FUNCTION f(...)
    RETURNS OBJECT
    ...
    REQUEST_TRANSLATOR = my_request_translator_udf
    RESPONSE_TRANSLATOR = my_response_translator_udf
    ...
    AS <url_of_proxy_and_resource>;

The syntax for specifying request_translators and response_translators as part of a CREATE EXTERNAL FUNCTION statement is shown below:

CREATE EXTERNAL FUNCTION f(...)
    RETURNS OBJECT
    ...
    [ REQUEST_TRANSLATOR = <request_translator_udf_name> ]
    [ RESPONSE_TRANSLATOR = <response_translator_udf_name> ]
    ...

where:

request_translator_udf_name

The name of the request_translator function.

response_translator_udf_name

The name of the response_translator function.

The request_translator and response_translator must each take one parameter of type OBJECT.

A request_translator or response_translator can be specified not only in a CREATE EXTERNAL FUNCTION command, but also in an ALTER EXTERNAL FUNCTION command. You can:

  • Add a request_translator or response_translator if the external function does not already have one.

  • Replace an existing request_translator or response_translator.

  • Remove a request_translator or response_translator.

The SET keyword is used both to add a new request_translator (or response_translator) and to replace an existing request_translator (or response_translator). The syntax for adding, replacing, and removing request_translators is shown below.

To add or replace a request_translator or response_translator:

ALTER FUNCTION ...
    SET [REQUEST_TRANSLATOR | RESPONSE_TRANSLATOR] = <udf_name>;

where

udf_name

The name of a previously-created JavaScript UDF.

To remove a request_translator or response_translator:

ALTER FUNCTION ...
    UNSET [REQUEST_TRANSLATOR | RESPONSE_TRANSLATOR];

An external function can have a maximum of one request_translator and one response_translator at a time.

The request_translator and response_translator UDFs can be secure UDFs.

Requirements

Each request_translator and response_translator must meet the following requirements:

  • It must be a JavaScript UDF.

  • It must take exactly one parameter of type OBJECT, which represents a batch of rows.

  • It must return one value of type OBJECT, which also represents a batch of rows.

  • It must be a scalar UDF (returning one row for each row (OBJECT) passed in).

    Note

    Although the request_translator or response_translator is scalar, the OBJECT passed to the request_translator or response_translator can (and usually does) have multiple rows embedded inside the JSON in the OBJECT.

  • The number and order of the rows (inside the OBJECT) returned by the response_translator must be the same as the number and order of the rows passed to the request_translator (inside the OBJECT).

  • The name of the request_translator or response_translator function in the CREATE EXTERNAL FUNCTION or ALTER FUNCTION statement should be either:

    • A qualified name (e.g. MyDatabase.MySchema.MyJavaScriptUDF).

    • Defined in the same database and schema as the external function that uses them.

  • When the request_translator or response_translator is specified in a CREATE EXTERNAL FUNCTION or ALTER FUNCTION statement, the request_translator or response_translator UDF must already exist. (You can’t specify the name first and create the UDF later – even if you don’t call the external function before you create the UDF.)

  • A request_translator UDF or response_translator UDF used in a translator should not be dropped without first removing it from all external functions that use it. (At the time the external function is called, Snowflake fails with an error if the request_translator or response_translator does not exist.)

  • If the request_translator or response_translator UDF is modified (via ALTER FUNCTION), it must retain the same interface requirements. If it does not retain the interface requirements, an exception is raised before running the external function.

JavaScript Implementation

From the SQL side, an OBJECT is passed to the UDF. The JavaScript code receives this as a JavaScript object.

Request_translator Input Properties

For the request_translator example, the JavaScript object is named event and contains the following properties:

  • body: The format of the data field is the same as the existing Snowflake rowset batch (i.e. an array of rows).

    For example,

    {
      "body": {
              "data": [
                        [0,"cat"],
                        [1,"dog"]
                      ]
              }
    }
    

    The existing data is nested under the outer body.

  • serviceUrl: The serviceUrl is the external function’s defined URL to call.

  • contextHeaders: The field contextHeaders is an object that contains all the context-related headers, where the names are the field names. For example, the object could contain the field name “SF_CONTEXT_CURRENT_DATABASE”, and the corresponding value would be a string containing the current database name.

Request_translator Output Properties

The request_translator returns an object with fields used to communicate with the external service API gateway. That object has three optional fields:

  • body: The field body defines the actual body to be passed to the service, and if this is not defined then there is no body. This value for the body should be a string or a JSON object in the format that the remote service expects. If the value is a string, that string can contain internal structure (e.g. be JSON-compatible). If the value is a JSON object, that object is converted to a string so that it can be included as part of the HTTP POST command string.

  • urlSuffix: The request_translator can set the suffix of the serviceUrl, which is added to the end of the serviceUrl. This suffix is also allowed to contain query parameters. Parameter names and values must be URL encoded. For example, if you want to set a parameter named a to value my param you need to do URL encoding of the space character, so the parameter would be ?a=my%20param.

  • translatorData: This field is passed from the request_translator to the response_translator. This field can pass context information, such as the input body, the service URL or suffix, or context headers.

All three fields are optional. However, as a practical matter, most request_translators return at least the body data.

Response_translator Input Properties

The input parameter for the response_translator is an object. In this example we use EVENT, which contains two properties:

  • body: The response to be decoded from the external service response.

  • translatorData: If this field is returned by the request_translator, then Snowflake passes it to the response_translator.

Response_translator Output Properties

The response_translator response is returned as an object under the body element; the format is the existing external function format (array of rows). For example:

{
  "body": {
          "data": [
                    [0, "Life"],
                    [1, "the universe"],
                    [2, "and everything"]
                  ]
           }
}

Example Request_translator and Response_translator

This example shows a request_translator and response_translator being used to convert data into the format required by an external service that does sentiment analysis, Amazon Comprehend BatchDetectSentiment. The request_translator shapes the HTTP request to match the format that the backend service expects.

Setup

Choose a role that has permission to create external functions.

USE ROLE ACCOUNTADMIN;

Specify which warehouse, database and schema to use.

USE WAREHOUSE w;
USE DATABASE a;
USE SCHEMA b;

Create a table to hold your test sentences.

CREATE TABLE demo(vc varchar);
INSERT INTO demo VALUES('Today is a good day'),('I am feeling mopey');

To use translators, an API gateway is required. This example uses an API gateway that is already configured to talk to the sentiment analysis service. For more information about how to integrate with an Amazon Web Services (AWS) service as the backend, see Set up an API integration request using the API Gateway console in the AWS documentation.

It is helpful to get your API integration working successfully before adding translators.

The Request Body Before Adding a Request_translator

Here is an example external function that doesn’t have a request_translator or response_translator.

CREATE OR REPLACE EXTERNAL FUNCTION ComprehendSentiment(thought varchar)
RETURNS VARIANT
API_INTEGRATION = aws_comprehend_gateway
AS 'https://<MY_GATEWAY>.execute-api.us-east-1.amazonaws.com/test/comprehend_proxy';

Call the external function with your test data from the demo table.

SELECT ComprehendSentiment(vc), vc FROM demo;

The request body that is generated uses the Snowflake external function data format that looks like this:

{"body":{"data:" [[0, "Today is a good day"],[1,"I am feeling mopey"]]}}

However, the external sentiment analysis service expects a different format that specifies the language and an array of strings.

{"body": { Language: "en", TextList: [ "Today is a good day", "I am feeling mopey"]}}

In the next section, you add a request_translator to change the request body to the required format.

Request_translator

The request_translator receives input in the Snowflake data format and converts it to the format that the external service requires.

CREATE OR REPLACE FUNCTION AWSComprehendrequest_translator(EVENT OBJECT)
RETURNS OBJECT
LANGUAGE JAVASCRIPT AS
'
var textlist = []
for(i = 0; i < EVENT.body.data.length; i++) {
   let row = EVENT.body.data[i];
   // row[0] is the row number and row[1] is the input text.
   textlist.push(row[1]); //put text into the textlist
}
// create the request for the service. Also pass the input request as part of the output.
return { "body": { "LanguageCode": "en", "TextList" : textlist }, "translatorData": EVENT.body }
';

The request_translator loops through each of the input rows. For each row, it adds the string, which is in row[1], to the textlist array. The value at row[0] is the row number and it can be ignored.

The request_translator returns a JSON body that has the language code and text list that matches the requirements of the external service. The request_translator also returns data via the translatorData field. This is used by the response_translator. In this example, you are sending the original input data. You will use the length of the input data in the response_translator to know how many input requests there were.

Test the request_translator by calling it directly.

SELECT AWSComprehendrequest_translator(parse_json('{"body":{"data": [[0, "I am so happy we got a sunny day for my birthday."], [1, "$$$$$."], [2, "Today is my last day in the old house."]]}}'));

The request_translator puts the body into the shape expected by the external service.

{"body":{
   "LanguageCode": "en",
   "TextList": [
      "I am so happy we got a sunny day for my birthday.",
      "$$$$$.",
      "Today is my last day in the old house."
               ]
         },
   "translatorData": {
      "data": [[0, "I am so happy we got a sunny day for my birthday."],
               [1, "$$$$$."],
               [2, "Today is my last day in the old house."]]
                     }
}

The Response Body Before Adding a Response_translator

A response body from the external service looks something like this.

{"body":{
   "ErrorList": [ { "ErrorCode": 57, "ErrorMessage": "Language unknown", "Index": 1} ],
   "ResultList":[ { "Index": 0, "Sentiment": "POSITIVE",
                    "SentimentScore": { "Mixed": 25, "Negative": 5, "Neutral": 1, "Positive": 90 }},
                  { "Index": 2, "Sentiment": "NEGATIVE",
                    "SentimentScore": { "Mixed": 25, "Negative": 75, "Neutral": 30, "Positive": 20 }}
                ]
         }
}

Response_translator

The response_translator processes the results that you get back from the external service. The results contain a combination of errors in the ErrorList and results in the ResultList. The response_translator code combines these results together to make a complete set that matches the order of the rows that were passed to the external service. The response_translator returns the results in the Snowflake format.

CREATE OR REPLACE FUNCTION AWSComprehendresponse_translator(EVENT OBJECT)
RETURNS OBJECT
LANGUAGE JAVASCRIPT AS
'
// Combine the scored results and the errors into a single list.
var responses = new Array(EVENT.translatorData.data.length);
// output format: array of {
// "Sentiment": (POSITIVE, NEUTRAL, MIXED, NEGATIVE, or ERROR),
// "SentimentScore": <score>, "ErrorMessage": ErrorMessage }.
// If error, errorMessage is set, otherwise sentimentScore is set.
// Insert good results into proper position.
for(i = 0; i < EVENT.body.ResultList.length; i++) {
   let row = EVENT.body.ResultList[i];
   let result = [row.Index, {"Sentiment": row.Sentiment, "SentimentScore": row.SentimentScore}]
   responses[row.Index] = result
}
// Insert errors.
for(i = 0; i < EVENT.body.ErrorList.length; i++) {
   let row = EVENT.body.ErrorList[i];
   let result = [row.Index, {"Sentiment": "Error", "ErrorMessage": row.ErrorMessage}]
   responses[row.Index] = result
}
return { "body": { "data" : responses } };
';

The response_translator initializes an array called responses with the size of the input from the translatorData array length. You sent translatorData from the request_translator to the response_translator to pass the original list of test strings.

The response_translator loops through each of the non-error results and puts them into the result list. Next, it loops through the error results and puts them into the result list. The result list has an index position which tells you what entry it is. The order of the produced results must match the input order. The result list also contains the sentiment information.

After all of the responses have been gathered, they are returned in a JSON body in the format that Snowflake expects.

Test the response_translator by calling it directly.

SELECT AWSComprehendresponse_translator(
    parse_json('{
        "translatorData": {
            "data": [[0, "I am so happy we got a sunny day for my birthday."],
                    [1, "$$$$$."],
                    [2, "Today is my last day in the old house."]]
                          }
        "body": {
            "ErrorList":  [ { "ErrorCode": 57,  "ErrorMessage": "Language unknown",  "Index": 1 } ],
            "ResultList": [
                            { "Index": 0,  "Sentiment": "POSITIVE",
                              "SentimentScore": { "Mixed": 25,  "Negative": 5,  "Neutral": 1,  "Positive": 90 }
                            },
                            { "Index": 2, "Sentiment": "NEGATIVE",
                              "SentimentScore": { "Mixed": 25,  "Negative": 75,  "Neutral": 30,  "Positive": 20 }
                            }
                          ]
            },
        }'
    )
);

A JSON body with the correct format is returned.

Add a Request_translator and Response_translator to your External Function

In the external function, add the request_translator and response_translator so they are called automatically when the external function runs.

CREATE OR REPLACE EXTERNAL FUNCTION ComprehendSentiment(thought varchar)
RETURNS VARIANT
API_INTEGRATION = aws_comprehend_gateway
Request_translator = db_name.schema_name.AWSComprehendrequest_translator
Response_translator = db_name.schema_name.AWSComprehendresponse_translator
AS 'https://<MY_GATEWAY>.execute-api.us-east-1.amazonaws.com/test/comprehend_proxy';

You can describe the function to get information about it.

DESCRIBE FUNCTION ComprehendSentiment(VARCHAR);

Call the External Function

Test the external function by calling it with a single sentence.

SELECT ComprehendSentiment('Today is a good day');

You see the sentiment analysis results.

{"Sentiment": "POSITIVE",
 "SentimentScore":{"Mixed":0.002436627633869648,
                   "Negative":0.0014803812373429537,
                   "Neutral":0.015923455357551575,
                   "Positive": 0.9801595211029053}}

Test the external function by calling it with multiple sentences. Use the same demo table that you created earlier.

SELECT ComprehendSentiment(vc), vc FROM demo;

The sentiment analysis results are displayed.

A table showing the sentiment analysis results.

When the external function was called, the request_translator automatically converted data into the format required by the external service. Then, the response_translator automatically converted the response from the external service back into the format required by Snowflake.

Tips for Testing Request_translators and Response_translators

  • Test case values are typically OBJECT values (collections of key-value pairs). These should be formatted to meet the requirements in these rules.

  • You can start testing your request_translator or response_translator by passing in an example input converted to a string. For example:

    select my_request_translator_function(parse_json('{"body": {"data": [ [0,"cat",867], [1,"dog",5309] ] } }'));
    

    (The input to PARSE_JSON() must be a JSON-formatted string.)

  • Test with NULL values if appropriate.

    • Include at least one SQL NULL value in your test cases.

    • Include at least one JSON NULL value in your test cases.

  • Translating a request and translating a response are often converse processes. Conceptually:

    my_response_translator_udf(my_request_translator_udf(x)) = x
    

    You can use this characteristic to help test your request_translator and response_translator if the data formats match. Create a table with good test values, then execute a command similar to:

    SELECT test_case_column
        FROM test_table
        WHERE my_response_translator_udf(my_request_translator_udf(x)) != x;
    

    The query should not return any rows.

    Note that translating a request and translating a response are not always exactly converse. For an example of where they might not be converse, see the discussion of converse functions in the “Usage Notes” section of the documentation for the TO_JSON() function.