Utilisation de traducteurs de requêtes et de réponses avec des données pour un service distant

Grâce aux traducteurs de requêtes et de réponses, vous pouvez modifier le format des données envoyées vers et depuis les services distants utilisés par des fonctions externes.

Dans ce chapitre :

But

Lorsque Snowflake envoie des données à un service distant, Snowflake formate les données conformément à ces règles. De même, lorsque Snowflake reçoit des données d’un service distant, Snowflake s’attend à ce que les données soient formatées selon les mêmes règles.

De nombreux services distants s’attendent à gérer des données dans un format différent. Avec les traducteurs de requêtes et de réponses, vous pouvez facilement :

  • Convertir les données du format Snowflake au format d’entrée natif du service distant (traducteur de requêtes).

  • Convertir les données du format de sortie natif du service distant au format de Snowflake (traducteur de réponses).

Mise en œuvre SQL

Pour traduire des données entre le format de Snowflake et le format d’entrée natif du service distant, vous utilisez des UDFs JavaScript (fonctions définies par l’utilisateur). Vous écrivez presque toujours une paire d’UDFs : une pour traduire la requête et une pour traduire la réponse.

Snowflake appelle ces fonctions dans le cadre de chaque appel de fonction externe. Par exemple, pour une demande adressée à un service distant, Snowflake appelle la fonction de traducteur de requêtes, lui transmet les données formatées par Snowflake, puis prend les données renvoyées et les envoie au service distant. Lorsque le service distant renvoie des données, Snowflake appelle la fonction de traducteur de requêtes pour reconvertir les données dans le format que Snowflake comprend.

Du point de vue de l’utilisateur, l’appel d’une fonction externe lorsqu’un traducteur est en cours de conversion est identique à l’appel d’une fonction externe sans traducteur. Après avoir spécifié les traducteurs dans le cadre de l’instruction CREATE EXTERNAL FUNCTION , ceux-ci sont appelés automatiquement.

Une fonction externe peut avoir au maximum un traducteur de requêtes et un traducteur de réponses à la fois.

Les UDFs de traducteur de requêtes et de réponses peuvent être des UDFs sécurisés.

Affectation d’une fonction de traducteur à une fonction externe

Pour spécifier la fonction définie par l’utilisateur à utiliser comme traducteur, incluez les clauses REQUEST_TRANSLATOR et RESPONSE_TRANSLATOR dans l’instruction CREATE EXTERNAL FUNCTION. Chacun prend le nom de la fonction de traducteur à utiliser au moment de l’exécution.

Par exemple :

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

La syntaxe permettant de spécifier des traducteurs dans le cadre d’une instruction CREATE EXTERNAL FUNCTION est présentée ci-dessous :

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

où :

request_translator_udf_name

Le nom de la fonction de traducteur de requêtes.

response_translator_udf_name

Le nom de la fonction de traducteur de réponses.

Les paramètres REQUEST_TRANSLATOR et RESPONSE_TRANSLATOR prennent chacun un paramètre de type OBJECT.

Vous pouvez également spécifier un traducteur de requêtes ou de réponses dans une commande ALTER FUNCTION. Vous pouvez :

  • Ajouter un sérialiseur si la fonction externe n’en a pas déjà un.

  • Remplacer un traducteur existant.

  • Supprimer un traducteur.

Utiliser le mot-clé SET pour ajouter un nouveau traducteur ou pour remplacer un traducteur existant.

Pour ajouter ou remplacer un traducteur :

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

où :

udf_name

Le nom d’une UDF JavaScript précédemment créée.

Pour supprimer un traducteur :

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

Exigences SQL

  • Le nom de la fonction de traducteur dans l’instruction CREATE EXTERNAL FUNCTION ou ALTER FUNCTION doit être soit :

    • Un nom qualifié (par exemple, MyDatabase.MySchema.MyJavaScriptUDF).

    • Défini dans la même base de données et le même schéma que la fonction externe qui les utilise.

  • Lorsque le traducteur est spécifié dans une instruction CREATE EXTERNAL FUNCTION ou ALTER FUNCTION l’UDF de traducteur doit déjà exister. Vous ne pouvez pas spécifier le nom d’abord et créer l’UDF après, même si vous n’appelez pas la fonction externe avant de créer l’UDF.

  • Une UDF utilisée en tant que traducteur ne doit pas être détruite sans être préalablement supprimée de toutes les fonctions externes qui l’utilisent. (Au moment où la fonction externe est appelée, Snowflake échoue avec une erreur si le traducteur n’existe pas).

  • Si l’UDF de traducteur est modifiée (via ALTER FUNCTION), elle doit conserver les mêmes exigences d’interface. Si elle ne respecte pas les exigences de l’interface, une exception est levée avant l’exécution de la fonction externe.

Mise en œuvre JavaScript

Au moment de l’exécution, SQL transmet un OBJECT à l’UDF de traducteur. Le code JavaScript le reçoit comme un objet JavaScript.

Mise en œuvre d’un traducteur de requêtes

Propriétés d’entrée d’un traducteur de requêtes

Une UDF de traducteur reçoit un objet JavaScript nommé event. L’objet contient les propriétés suivantes :

  • body : le format du champ data est le même que celui du lot d’ensemble de ligne Snowflake existant (c’est-à-dire un tableau de lignes).

    Par exemple,

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

    Les données existantes sont imbriquées sous le corps extérieur.

  • serviceUrl : l’URL définie de la fonction externe à appeler.

  • contextHeaders : un objet qui contient tous les en-têtes liés au contexte, où les noms sont les noms des champs. Par exemple, l’objet pourrait contenir le nom de champ « SF_CONTEXT_CURRENT_DATABASE » et la valeur correspondante serait une chaîne contenant le nom de la base de données actuelle.

Propriétés de sortie du traducteur de requêtes

Le traducteur de requêtes renvoie un objet avec des champs utilisés pour communiquer avec la passerelle API du service externe. Cet objet comporte trois champs facultatifs :

  • body : définit le corps réel à transmettre au service. S’il n’est pas défini, il n’y a pas de corps. Cette valeur body doit être une chaîne ou un objet JSON dans le format attendu par le service distant. Si la valeur est une chaîne, cette chaîne peut contenir une structure interne (par exemple, présenter une compatibilité JSON). Si la valeur est un objet JSON, cet objet est converti en une chaîne afin qu’il puisse être inclus dans la chaîne de commande HTTP POST.

  • urlSuffix : le suffixe de l’URL de service, qui est ajouté à la fin de la valeur serviceUrl. Ce suffixe peut également contenir des paramètres de requête. Les noms et les valeurs des paramètres doivent être encodés URL. Par exemple, si vous voulez donner la valeur my param à un paramètre nommé a, vous devez effectuer l’encodage URL du caractère espace, et le paramètre sera donc ?a=my%20param.

  • translatorData : transmis du traducteur de requêtes au traducteur de réponses. Ce champ peut transmettre des informations contextuelles, telles que le corps de l’entrée, l’URL ou le suffixe du service, ou encore des en-têtes de contexte.

Ces trois champs sont facultatifs. Toutefois, en pratique, la plupart des traducteurs de requêtes renvoient au moins les données du corps.

Mise en œuvre d’un traducteur de réponses

Propriétés d’entrée du traducteur de réponses

Le paramètre d’entrée de la fonction de traducteur de réponses est un objet. L’exemple ci-dessous utilise EVENT, qui contient deux propriétés :

  • body : la réponse à décoder à partir de la réponse du service externe.

  • translatorData : si ce champ est renvoyé par le traducteur de requêtes, alors Snowflake le transmet au traducteur de réponses.

Propriétés de sortie du traducteur de réponses

La réponse du traducteur de réponses est renvoyée en tant qu’objet sous l’élément body ; le format est celui de la fonction externe existante (tableau de lignes). Par exemple :

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

Exigences pour la fonction de traducteur

Chaque UDF de traducteur doit répondre aux exigences suivantes :

  • Ce doit être une UDF JavaScript.

  • Elle doit prendre exactement un paramètre de type OBJECT, qui représente un lot de lignes.

  • Elle doit retourner une valeur de type OBJECT, qui représente également un lot de lignes.

  • Il doit s’agir d’une UDF scalaire (renvoyant une ligne pour chaque ligne [OBJECT] transmise).

    Note

    Bien que le traducteur soit scalaire, l’OBJECT transmis au traducteur peut (et c’est généralement le cas) comporter plusieurs lignes incorporées à l’intérieur du JSON dans l’OBJECT.

  • Le nombre et l’ordre des lignes (à l’intérieur de l’OBJECT) renvoyées par l’UDF de traducteur de réponses doivent être les mêmes que le nombre et l’ordre des lignes transmises à l’UDF de traducteur de requêtes (à l’intérieur de l’OBJECT).

Exemple de traducteur de requêtes et de traducteur de réponses

Cet exemple montre l’utilisation d’un traducteur de requêtes et d’un traducteur de réponses pour convertir des données au format requis par un service externe d’analyse des sentiments, Amazon Comprehend BatchDetectSentiment. Le traducteur de requêtes met en forme la requête HTTP pour qu’elle corresponde au format attendu par le service backend.

Pour utiliser les traducteurs, vous aurez besoin d’une passerelle API. Cet exemple utilise une passerelle API qui est déjà configurée pour communiquer avec le service d’analyse des sentiments. Pour plus d’informations sur la manière d’intégrer un service Amazon Web Services (AWS) en tant que backend, voir Configurer une demande d’intégration API à l’aide de la console API Gateway dans la documentation AWS.

Il est utile de faire fonctionner votre intégration API avant d’ajouter des traducteurs.

Configuration

Créez une base de données pour contenir les données de la démo.

Choisissez un rôle qui a la permission de créer des fonctions externes :

USE ROLE ACCOUNTADMIN;
Copy

Spécifiez l’entrepôt, la base de données et le schéma à utiliser :

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

Créez un tableau pour contenir vos phrases de test :

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

Demander le corps avant la traduction

Cette fonction externe n’a pas de traducteur de requêtes ou de traducteur de réponses :

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

Appelez la fonction externe avec vos données de test provenant de la table de démonstration :

SELECT ComprehendSentiment(vc), vc FROM demo;
Copy

Le corps de la requête générée utilise le format de données de fonction externe de Snowflake :

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

Cependant, le service externe d’analyse des sentiments attend un format différent qui spécifie la langue et un tableau de chaînes :

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

La section suivante décrit comment vous pouvez ajouter un traducteur de requêtes pour modifier le corps de la requête au format requis.

Convertir le corps de la requête

En utilisant un traducteur de requêtes, vous pouvez convertir l’entrée par défaut décrite ci-dessus (dans le format de données Snowflake) au format requis par le service externe.

Le code SQL suivant crée une fonction de traducteur awscomprehendrequest_translator.

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

Dans la fonction de traducteur de requêtes, le code :

  • boucle sur chacune des lignes d’entrée. Pour chaque ligne, il ajoute la chaîne, qui se trouve dans row[1], au tableau textlist. La valeur dans row[0] est le numéro de la ligne et peut être ignorée.

  • Le traducteur de requêtes renvoie un corps JSON dont le code de langue et la liste de textes correspondent aux exigences du service externe.

  • Il renvoie également des données via le champ translatorData. Il est utilisé par le traducteur de réponses. Dans cet exemple, vous envoyez les données d’entrée d’origine. Vous utiliserez la longueur des données d’entrée dans le traducteur de réponses pour savoir combien de requêtes d’entrée il y a eu.

Vous pouvez tester le traducteur de requêtes en l’appelant directement.

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."]]}}'));
Copy

Le traducteur de requêtes met le corps dans la forme attendue par le service externe.

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

Corps de la réponse avant l’ajout d’un traducteur de réponses

Le corps de la réponse du service externe ressemble à ceci.

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

Convertir le corps de la réponse

Le traducteur de réponses traite les résultats que vous recevez du service externe. Les résultats contiennent une combinaison d’erreurs dans la ErrorList et de résultats dans la ResultList.

Le code du traducteur de réponses combine ces résultats pour en faire un ensemble complet qui correspond à l’ordre des lignes qui ont été transmises au service externe. Le traducteur de réponses renvoie les résultats au format Snowflake.

Le code SQL suivant crée une fonction de traducteur awscomprehendresponse_translator.

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

Dans la fonction de traducteur de réponses, le code :

  • Initialise un tableau appelé responses avec la taille de l’entrée de la longueur du tableau translatorData. Vous avez envoyé translatorData du traducteur de requêtes au traducteur de réponses pour transmettre la liste d’origine des chaînes de test.

  • Le traducteur de réponses boucle sur chacun des résultats sans erreur et les place dans la liste des résultats.

  • Il passe en revue les résultats avec erreurs et les place dans la liste des résultats. La liste de résultats a une position d’index qui vous indique de quelle entrée il s’agit. L’ordre des résultats produits doit correspondre à l’ordre d’entrée. La liste des résultats contient également les informations liées aux sentiments.

Une fois que toutes les réponses ont été rassemblées, elles sont renvoyées dans un corps JSON au format attendu par Snowflake.

Le test direct suivant renverra un corps JSON avec le format correct.

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

Affecter les traducteurs à la fonction externe

À la fonction externe, ajoutez les fonctions de traducteur de requêtes et de réponses en attribuant les noms des fonctions comme valeurs aux paramètres request_translator et response_translator. De cette façon, ils seront appelés automatiquement lorsque la fonction externe sera exécutée.

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

Vous pouvez décrire la fonction pour obtenir des informations à son sujet.

DESCRIBE FUNCTION ComprehendSentiment(VARCHAR);
Copy

Appeler la fonction externe

Testez la fonction externe en l’appelant avec une seule phrase.

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

Vous voyez les résultats de l’analyse des sentiments.

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

Testez la fonction externe en l’appelant avec plusieurs phrases. Utilisez la même table demo que vous avez créée précédemment.

SELECT ComprehendSentiment(vc), vc FROM demo;
Copy

Les résultats de l’analyse des sentiments s’affichent.

Un tableau montrant les résultats de l'analyse des sentiments.

Lorsque la fonction externe a été appelée, le traducteur de requêtes a converti automatiquement les données dans le format requis par le service externe. Ensuite, le traducteur de réponses a converti automatiquement la réponse du service externe dans le format requis par Snowflake.

Conseils pour tester les traducteurs de requêtes et de réponses

  • Les valeurs des cas de test sont généralement des valeurs OBJECT (collections de paires clé-valeur). Celles-ci doivent être formatées pour répondre aux exigences de ces règles.

  • Vous pouvez commencer à tester votre traducteur de requêtes ou votre traducteur de réponses en passant une entrée d’exemple convertie en chaîne. Par exemple :

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

    (L’entrée de PARSE_JSON() doit être une chaîne formatée JSON).

  • Testez avec des valeurs NULL si nécessaire.

    • Incluez au moins une valeur SQL NULL dans vos cas de test.

    • Incluez au moins une valeur JSON NULL dans vos cas de test.

  • La traduction d’une demande et la traduction d’une réponse sont souvent des processus inverses. Conceptuellement :

    my_response_translator_udf(my_request_translator_udf(x)) = x
    
    Copy

    Vous pouvez utiliser cette caractéristique pour vous aider à tester votre traducteur de requêtes et votre traducteur de réponses si les formats de données correspondent. Créez une table avec des valeurs de test correctes, puis exécutez une commande similaire à :

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

    La requête ne doit pas renvoyer de lignes.

    Notez que la traduction d’une demande et la traduction d’une réponse ne sont pas toujours exactement inverses. Pour un exemple de cas où elles peuvent ne pas être inverses, voir la discussion sur les fonctions inverses dans la section « Notes sur l’utilisation » de la documentation de la fonction TO_JSON().