Using request and response translators with data for a remote service¶

Assigning a translator function to an external function¶

To specify which user-defined function to use as a translator, include REQUEST_TRANSLATOR and RESPONSE_TRANSLATOR clauses in the CREATE EXTERNAL FUNCTION statement. Each takes the name of the translator function to use at run time.

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>;

Copy

The syntax for specifying 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> ]
    ...

Copy

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 parameters each take one parameter of type OBJECT.

You can also specify a request or response translator in an ALTER FUNCTION command. You can:

• Add a translator if the external function does not already have one.

• Replace an existing translator.

• Remove a translator.

Use the SET keyword to add a new translator or to replace an existing translator.

To add or replace a translator:

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

Copy

where

udf_name

The name of a previously-created JavaScript UDF.

To remove a translator:

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

Copy

Requirements for the SQL¶

• The name of the 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 translator is specified in a CREATE EXTERNAL FUNCTION or ALTER FUNCTION statement, the 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 UDF used as 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 translator does not exist.)

• If the 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.

Implementing a request translator¶

Implementing a response translator¶

Requirements for the translator function¶

Each translator UDF 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 translator is scalar, the OBJECT passed to the 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 UDF must be the same as the number and order of the rows passed to the request translator UDF (inside the OBJECT).

Setup¶

Set up a database to hold demo data.

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');

Request body before translation¶

This external function 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';

You can call the external function with your test data from the demo table:

SELECT ComprehendSentiment(vc), vc FROM demo;

The generated request body uses the Snowflake external function data format:

{"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"]}}

The next section describes how you can add a request translator to change the request body to the required format.

Convert the request body¶

By using a request translator, you can convert the default input described above (in the Snowflake data format) to the format that the external service requires.

The following SQL creates an awscomprehendrequest_translator translator function.

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 }
';

In the request translator function, the code:

• 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.

• Returns a JSON body that has the language code and text list that matches the requirements of the external service.

• 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.

You can 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."]]
                     }
}

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 }}
                ]
         }
}

Convert the response body¶

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.

The following SQL creates an awscomprehendresponse_translator translator function.

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, set ErrorMessage; otherwise, set SentimentScore.
// 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 } };
';

In the response translator function, the code:

• 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.

• Loops through each of the non-error results and puts them into the result list.

• 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.

The following direct test will return a JSON body with the correct format.

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 }
                            }
                          ]
            },
        }'
    )
);

Assign the translators to the external function¶

To the external function, add the request and response translator functions by assigning the function names as values to the request_translator and response_translator parameters. This way, they’ll be 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.

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.