Guide du développeur pour l’API SQL Snowflake

Ce guide explique comment utiliser l’API SQL de Snowflake.

Dans ce chapitre :

Introduction

L’API SQL de Snowflake est une API REST que vous pouvez utiliser pour accéder aux données d’une base de données Snowflake et les mettre à jour. Vous pouvez utiliser cette API pour développer des applications personnalisées et des intégrations qui :

  • Effectuent de simples requêtes

  • Gèrent votre déploiement (par exemple, mise en service d’utilisateurs et de rôles, création de tables, etc.)

Capacités de l’API SQL

L’API SQL de Snowflake fournit des opérations que vous pouvez utiliser pour :

  • Soumettre des instructions SQL pour exécution.

  • Vérifier le statut d’exécution d’une instruction.

  • Annuler l’exécution d’une instruction.

Vous pouvez utiliser cette API pour exécuter des requêtes standard et la plupart des instructions DDL et DML. Voir Limitations de l’API SQL pour connaître les types d’instructions qui ne sont pas pris en charge.

Pour les requêtes, vous pouvez paginer les résultats (par exemple, récupérer les résultats dans des pages de 10 lignes chacune).

Le point de terminaison pour l’API SQL (/api/statements) est protégé par les politiques réseau que vous avez configurées pour restreindre l’accès à votre compte.

Limitations de l’API SQL

L’API SQL a les limitations suivantes :

  • La taille maximale d’une page de résultats est d’environ 10 MB.

  • Le nombre maximum de lignes retournées dans une page de résultats est de 10 000.

  • Le service ne prend actuellement pas en charge la récupération simultanée des résultats (c’est-à-dire en parallèle avec plusieurs threads). Les demandes ne sont prises en charge qu’à partir d’un seul thread.

  • Les instructions suivantes ne sont pas prises en charge :

    • PUT

    • GET

Utilisation de l’API

L’API SQL est disponible à https://identificateur_de_compte.snowflakecomputing.com/api, où identificateur_de_compte est votre identificateur de compte.

L’API se compose de la ressource /api/statements/ et fournit les points de terminaison suivants :

Point de terminaison

Description

/api/statements/

Utilisez ce point de terminaison pour soumettre des instructions SQL pour exécution.

/api/statements/instructionGestion

Utilisez ce point de terminaison pour vérifier le statut de l’exécution d’une instruction. (instructionGestion est un identificateur unique de l’instruction soumise pour exécution).

/api/statements/instructionGestion/cancel

Utilisez ce point de terminaison pour annuler l’exécution d’une instruction.

Vous pouvez utiliser des outils de développement et des bibliothèques pour les APIs REST (par exemple, Postman) pour envoyer des requêtes et traiter les réponses.

Authentification auprès du serveur

Lorsque vous envoyez une requête, celle-ci doit inclure des informations d’authentification. Les sections suivantes expliquent comment ajouter ces informations à la requête :

Utilisation de OAuth

Pour utiliser OAuth, suivez les étapes suivantes :

  1. Configurez OAuth pour l’authentification.

    Voir OAuth pour plus de détails sur la façon de configurer OAuth et d’obtenir un jeton OAuth.

  2. Utilisez SnowSQL pour vérifier que vous pouvez utiliser un jeton OAuth généré pour vous connecter à Snowflake :

    $ snowsql -a <account_identifier> -u <user> --authenticator=oauth --token=<oauth_token>
    
  3. Dans le code de votre application, dans chaque requête API que vous envoyez, définissez les en-têtes suivants :

    • Authorization: Bearer jeton_oauth

      jeton_oauth est le jeton OAuth généré.

    • X-Snowflake-Authorization-Token-Type: OAUTH

      Notez que vous pouvez également choisir d’omettre l’en-tête X-Snowflake-Authorization-Token-Type. Si cet en-tête n’est pas présent, Snowflake suppose que le jeton dans l’en-tête Authorization est un jeton OAuth.

Utilisation de l’authentification par paire de clés

Pour utiliser l’authentification par paire de clés, suivez ces étapes :

  1. Configurez l’authentification par paire de clés.

    Dans le cadre de ce processus, vous devez :

    1. Générer une paire de clés publique-privée. La clé privée générée doit se trouver dans un fichier (par exemple, nommé rsa_key.p8).

    2. Attribuez la clé publique à votre utilisateur Snowflake. Après avoir attribué la clé à l’utilisateur, exécutez la commande DESCRIBE USER. Dans la sortie, la propriété RSA_PUBLIC_KEY_FP doit être définie comme l’empreinte de la clé publique attribuée à l’utilisateur.

    Pour des instructions sur la façon de générer la paire de clés et d’attribuer une clé à un utilisateur, voir Authentification par paires de clés et rotation des paires de clés.

  2. Utilisez SnowSQL pour vérifier que vous pouvez utiliser la clé privée générée pour vous connecter à Snowflake :

    $ snowsql -a <account_identifier> -u <user> --private-key-path <path>/rsa_key.p8
    

    Si vous avez généré une clé privée cryptée, SnowSQL vous invite à saisir la phrase secrète que vous avez créée lors de la génération de la clé.

  3. Dans le code de votre application :

    1. Générez l’empreinte digitale (un hachage SHA-256) de la clé publique de l’utilisateur. Préfixez l’empreinte digitale avec SHA256:. Par exemple :

      SHA256:hachage

      Pour un exemple de génération de l’empreinte digitale à partir d’un fichier de clé privée en Python, voir jwt-generator.

      Les sections de code suivantes montrent comment générer l’empreinte digitale.

      from cryptography.hazmat.primitives.serialization import load_pem_private_key
      from cryptography.hazmat.primitives.serialization import Encoding
      from cryptography.hazmat.primitives.serialization import PublicFormat
      from cryptography.hazmat.backends import default_backend
      ..
      import base64
      from getpass import getpass
      import hashlib
      ..
      # If you generated an encrypted private key, implement this method to return
      # the passphrase for decrypting your private key. As an example, this function
      # prompts the user for the passphrase.
      def get_private_key_passphrase():
          return getpass('Passphrase for private key: ')
      
      # Private key that you will load from the private key file.
      private_key = None
      
      # Open the private key file.
      # Replace <private_key_file_path> with the path to your private key file (e.g. /x/y/z/rsa_key.p8).
      with open('<private_key_file_path>', 'rb') as pem_in:
          pemlines = pem_in.read()
          try:
              # Try to access the private key without a passphrase.
              self.private_key = load_pem_private_key(pemlines, None, default_backend())
          except TypeError:
              # If that fails, provide the passphrase returned from get_private_key_passphrase().
              self.private_key = load_pem_private_key(pemlines, get_private_key_passphrase().encode(), default_backend())
      
      # Get the raw bytes of the public key.
      public_key_raw = private_key.public_key().public_bytes(Encoding.DER, PublicFormat.SubjectPublicKeyInfo)
      
      # Get the sha256 hash of the raw bytes.
      sha256hash = hashlib.sha256()
      sha256hash.update(public_key_raw)
      
      # Base64-encode the value and prepend the prefix 'SHA256:'.
      public_key_fp = 'SHA256:' + base64.b64encode(sha256hash.digest()).decode('utf-8')
      
    2. Générez un jeton Web JSON (JWT) avec les champs suivants dans la charge utile :

      Champ

      Description

      Exemple

      iss

      Il s’agit du champ pour l’émetteur du JWT. Réglez-le sur la valeur suivante :

      identificateur_de_compte.utilisateur.empreinte_cle_publique

      où :

      • identificateur_de_compte est votre identificateur de compte Snowflake.

        Si vous utilisez le localisateur de compte, excluez toute information sur la région du localisateur de compte.

      • utilisateur est votre nom d’utilisateur Snowflake.

      • empreinte_cle_publique est l’empreinte de la clé que vous avez générée à l’étape précédente.

      MYACCOUNT.MYUSER.SHA256:empreinte_cle_publique

      sub

      Il s’agit du champ pour l’objet du JWT. Réglez-le sur la valeur suivante :

      identificateur_de_compte.utilisateur

      MYORGANIZATION-MYACCOUNT.MYUSER

      iat

      Il s’agit du champ correspondant à l’heure à laquelle le JWT a été émis. Définit l’heure actuelle (le nombre de secondes depuis le début de l’epoch dans UTC).

      Remarque : lorsque vous choisissez l’heure d’émission, utilisez le fuseau horaire de votre déploiement Snowflake.

      1615370644

      exp

      Il s’agit du champ pour l’heure à laquelle d’expiration du JWT. Définissez ce champ sur l’heure d’expiration (le nombre de secondes depuis le début de l’epoch dans UTC).

      Remarque : le JWT est valide au maximum une heure après l’émission du jeton, même si vous spécifiez un délai d’expiration plus long.

      1615374184

      Pour un exemple de génération d’un JWT en Python, voir jwt-generator.

      Les sections de code suivantes démontrent comment générer un JWT. L’exemple utilise le module PyJWT, que vous pouvez installer en exécutant :

      pip install pyjwt
      
      from datetime import timedelta, timezone, datetime
      
      # This example relies on the PyJWT module (https://pypi.org/project/PyJWT/).
      import jwt
      
      # Construct the fully qualified name of the user in uppercase.
      # - Replace <account_identifier> with your account identifier.
      #   (See https://docs.snowflake.com/en/user-guide/admin-account-identifier.html .)
      # - Replace <user_name> with your Snowflake user name.
      account = "<account_identifier>"
      # Get the account identifier without the region, cloud provider, or subdomain.
      if not '.global' in account:
          idx = account.find('.')
          if idx > 0:
              account = account[0:idx]
          else:
              # Handle the replication case.
              idx = account.find('-')
              if idx > 0:
                  account = account[0:idx]
      # Use uppercase for the account identifier and user name.
      account = account.upper()
      user = "<user_name>".upper()
      qualified_username = account + "." + user
      
      # Get the current time in order to specify the time when the JWT was issued and the expiration time of the JWT.
      now = datetime.now(timezone.utc)
      # Specify the length of time during which the JWT will be valid. You can specify at most 1 hour.
      lifetime = timedelta(minutes=59)
      
      # Create the payload for the token.
      payload = {
          # Set the issuer to the fully qualified username concatenated with the public key fingerprint (calculated in the
          # previous step).
          "iss": qualified_username + '.' + public_key_fp,
      
          # Set the subject to the fully qualified username.
          "sub": qualified_username,
      
          # Set the issue time to now.
          "iat": now,
      
          # Set the expiration time, based on the lifetime specified for this object.
          "exp": now + lifetime
      }
      
      # Generate the JWT. private_key is the private key that you read from the private key file in the previous step when you
      # generated the public key fingerprint.
      encoding_algorithm="RS256"
      token = jwt.encode(payload, key=private_key, algorithm=encoding_algorithm)
      # If you are using a version of PyJWT prior to 2.0, jwt.encode returns a byte string, rather than a string.
      # If the token is a byte string, convert it to a string.
      if isinstance(token, bytes):
        token = token.decode('utf-8')
      decoded_token = jwt.decode(token, key=private_key.public_key(), algorithm=[encoding_algorithm])
      print("Generated a JWT with the following payload:\n{}".format(decoded_token))
      
    3. Dans chaque requête API que vous envoyez, définissez les en-têtes suivants :

      • Authorization: Bearer JWT

        JWT est le jeton que vous avez généré.

      • X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT

Attribution d’un ID de requête unique pour la resoumission des requêtes

Dans certains cas, il peut être difficile de savoir si Snowflake a exécuté l’instruction SQL dans une requête API (par exemple, en raison d’une erreur de réseau ou d’un dépassement de délai). Vous pouvez choisir de soumettre à nouveau la même requête à Snowflake, au cas où Snowflake n’aurait pas exécuté l’instruction.

Si Snowflake a déjà exécuté l’instruction dans la requête initiale et que vous soumettez à nouveau la requête, l’instruction est exécutée deux fois. Pour certains types de requêtes, l’exécution répétée de la même instruction peut avoir des conséquences inattendues (par exemple, l’insertion de données en double dans une table).

Pour éviter que Snowflake n’exécute deux fois la même instruction lorsque vous soumettez à nouveau une requête, vous pouvez utiliser un ID de requête pour distinguer votre requête des autres requêtes. Si vous spécifiez le même ID de requête dans la requête initiale et dans la requête resoumise, Snowflake n’exécute pas à nouveau l’instruction si celle-ci a déjà été exécutée avec succès.

Pour spécifier un ID de requête, générez un identificateur unique universel (UUID) et incluez cet identificateur dans le paramètre de requête requestId. Par exemple :

POST /api/statements?requestId=ea7b46ed-bdc1-8c32-d593-764fcad64e83 HTTP/1.1

Si Snowflake ne parvient pas à traiter une requête, vous pouvez soumettre à nouveau la même requête avec le même ID de requête. L’utilisation du même ID de requête indique au serveur que vous soumettez à nouveau la même requête.

Soumission d’une requête d’exécution d’instructions SQL

Pour soumettre des instructions SQL à exécuter, envoyez une requête POST à /statements/endpoint :

POST /api/statements HTTP/1.1
(request body)

Création de la requête

Dans l’URL de la requête, vous pouvez définir les paramètres de la requête pour :

Pour le corps de la requête, définissez les champs suivants :

  • Définissez le champ statement sur l’instruction SQL que vous voulez exécuter.

    Si vous souhaitez soumettre plusieurs instructions dans une seule requête, utilisez un point-virgule (;) entre les instructions. Voir Soumission de plusieurs instructions SQL dans une seule requête pour plus de détails.

  • Si vous incluez des variables de liaison (caractères de remplacement ?) dans l’instruction, définissez le champ bindings sur un objet qui spécifie les types de données Snowflake correspondants et les valeurs pour chaque variable.

    Pour plus de détails, voir Utilisation de variables de liaison dans une instruction.

  • Pour spécifier l’entrepôt, la base de données, le schéma et le rôle à utiliser, définissez les champs warehouse, database, schema et role.

    Les valeurs de ces champs sont sensibles à la casse.

  • Pour définir un délai d’expiration pour l’exécution de l’instruction, définissez le champ timeout sur le nombre maximal de secondes à attendre. Si le champ timeout n’est pas défini, le délai d’expiration spécifié par le paramètre STATEMENT_TIMEOUT_IN_SECONDS est utilisé.

  • Si la requête renvoie un grand nombre de lignes, définissez le paramètre pageSize. Sinon, le nombre de lignes peut dépasser le nombre de lignes par défaut dans une page.

Exemple de requête

Par exemple, la commande curl suivante envoie une instruction SQL pour exécution. L’exemple utilise le fichier request-body.json pour spécifier le corps de la requête.

curl -i -X POST \
    -H "Authorization: Bearer <jwt>" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "User-Agent: myApplicationName/1.0" \
    -H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" \
    -d "@request-body.json" \
    https://<account_identifier>.snowflakecomputing.com/api/statements

où :

Dans cet exemple, request-body.json contient le corps de la requête :

{
  "statement": "select * from T where c1=?",
  "timeout": 60,
  "resultSetMetaData": {
    "format": "json"
  },
  "database": "TESTDB",
  "schema": "TESTSCHEMA",
  "warehouse": "TESTWH",
  "role": "TESTROLE",
  "bindings": {
    "1": {
      "type": "FIXED",
      "value": "123"
    }
  }
}

Dans l’exemple ci-dessus :

  • Dans l’URL de la requête, le paramètre de la requête pageSize (&pageSize=10) spécifie que le jeu de résultats de chaque réponse doit comprendre un maximum de 10 lignes.

  • Dans le corps de la requête :

    • Le champ statement spécifie l’instruction SQL à exécuter.

      L’instruction comprend une variable de liaison (le point d’interrogation dans "cl=?"), qui est évaluée à la première liaison ("1") spécifiée dans le champ bindings.

    • Le champ timeout indique que le serveur accorde 60 secondes pour l’exécution de l’instruction.

    • Les champs database, schema, warehouse et role indiquent que la base de données TESTDB, le schéma TESTSCHEMA, l’entrepôt TESTWH et le rôle TESTROLE doivent être utilisés lors de l’exécution de l’instruction.

Utilisation de variables de liaison dans une instruction

Si vous souhaitez utiliser des variables de liaison (caractères de remplacement ?) dans l’instruction, utilisez le champ bindings pour spécifier les valeurs à insérer.

Définissez ce champ comme un objet JSON qui spécifie le type de données Snowflake et la valeur de chaque variable de liaison.

...
"statement": "select * from T where c1=?",
...
"bindings": {
  "1": {
    "type": "FIXED",
    "value": "123"
  }
},
...

Sélectionnez le type de liaison qui correspond au type de la valeur que vous liez. Par exemple, si la valeur est une chaîne représentant une date (par exemple 2021-04-15) et que vous souhaitez insérer cette valeur dans une colonne DATE, utilisez le type de liaison TEXT.

Le tableau suivant spécifie les valeurs du champ type que vous pouvez utiliser pour lier différents types de données Snowflake pour cette version préliminaire.

  • La première colonne de gauche indique les types de liaisons que vous pouvez utiliser.

  • Le reste des colonnes spécifie le type de données Snowflake de la colonne dans laquelle vous prévoyez d’insérer les données.

  • Chaque cellule spécifie le type de valeur que vous pouvez utiliser avec un type de liaison pour insérer des données dans une colonne d’un type de données Snowflake particulier.

    Si la cellule d’un type de liaison et d’un type de données Snowflake est vide, vous ne pouvez pas utiliser le type de liaison spécifié pour insérer des données dans une colonne de ce type de données Snowflake.

Types de liaison pris en charge pour différents types de données Snowflake

Types de données Snowflake

INT / NUMBER

FLOAT

VARCHAR

BINARY

BOOLEAN

DATE

TIME

TIMESTAMP_TZ

TIMESTAMP_LTZ

TIMESTAMP_NTZ

Types de . liaison

FIXED

entier

entier

entier

0 (faux) / non-zéro (vrai)

REAL

entier

entier ou flottant

entier ou flottant

0/non-0

TEXT

entier

entier ou flottant

tout texte

hexdec

"true" / "false"

voir les remarques ci-dessous

voir les remarques ci-dessous

voir les remarques ci-dessous

voir les remarques ci-dessous

voir les remarques ci-dessous

BINARY

hexdec

BOOLEAN

vrai/faux, 0/1

vrai/faux

DATE

epoch (ms)

epoch (ms)

epoch (ms)

epoch (ms)

epoch (ms)

TIME

epoch (nano)

epoch (nano)

TIMESTAMP_TZ

epoch (nano)

epoch (nano)

epoch (nano)

epoch (nano)

TIMESTAMP_LTZ

epoch (nano)

epoch (nano)

epoch (nano)

epoch (nano)

epoch (nano)

epoch (nano)

TIMESTAMP_NTZ

epoch (nano)

epoch (nano)

epoch (nano)

epoch (nano)

epoch (nano)

epoch (nano)

Remarques :

  • Les valeurs des variables de liaison doivent être des chaînes de caractères (par exemple, "1.0" pour la valeur 1.0).

  • Lorsque vous utilisez le type de liaison DATE, indiquez le nombre de millisecondes depuis l’epoch.

  • Lorsque vous utilisez le type de liaison TIME ou TIMESTAMP*, indiquez le nombre de nanosecondes depuis l’epoch.

  • Lorsque vous utilisez le type de liaison TIMESTAMP_TZ, indiquez le nombre de nanosecondes depuis l’epoch, suivi d’un espace et du décalage du fuseau horaire en minutes (par exemple, 1616173619000000000 960).

  • Lorsque vous utilisez le type de liaison TEXT :

    • Pour insérer des données dans une colonne DATE, vous pouvez utiliser tout format de date pris en charge par la détection AUTO.

    • Pour insérer des données dans une colonne TIME, vous pouvez utiliser n’importe quel format d’heure qui est pris en charge par la détection AUTO.

    • Pour insérer des données dans une colonne TIMEZONE*, vous pouvez utiliser n’importe quel format de date-heure pris en charge par la détection AUTO.

Si la valeur est dans un format non pris en charge par Snowflake, l’API renvoie une erreur :

{
  code: "100037",
  message: "<bind type> value '<value>' is not recognized",
  sqlState: "22018",
  statementHandle: "<ID>"
}

Gestion de la réponse

Par défaut, Snowflake exécute l’instruction de manière synchrone et renvoie l’un des codes de réponse, présentés dans l’organigramme ci-dessous :

Flow chart for submitting a statement for execution

Comme le montre l’organigramme ci-dessus :

Vérification du statut de l’exécution de l’instruction et récupération des données

Parfois, il est nécessaire d’envoyer une requête pour vérifier le statut de l’exécution d’une instruction :

  • Lorsque vous soumettez une instruction SQL pour exécution, Snowflake renvoie un code de réponse 202 si l’exécution de l’instruction n’est pas encore terminée ou si vous avez soumis une requête asynchrone.

    Pour voir si l’exécution de l’instruction est terminée, vous devez envoyer une requête pour vérifier le statut de l’instruction.

  • Si vous avez soumis plusieurs instructions SQL dans une seule requête, vous obtenez les résultats de chaque instruction individuelle en envoyant une requête visant à vérifier le statut de l’instruction.

Dans les deux cas, vous envoyez une requête GET au point de terminaison /api/statements/ et joignez l’handle d’instruction à la fin du chemin d’URL comme paramètre de chemin :

GET /api/statements/{statementHandle}

{statementHandle} est le handle de l’instruction que vous voulez vérifier. Pour obtenir l’handle d’instruction :

  • Si vous recevez une réponse avec un code 202, le corps de la réponse inclut un objet QueryStatus. Vous pouvez obtenir l’handle d’instruction à partir du champ statementHandle de cet objet.

    Notez que vous pouvez aussi obtenir l’URL complète de la requête à partir du champ statementStatusUrl de cet objet.

    {
      "code": "090001",
      "sqlState": "00000",
      "message": "successfully executed",
      "statementHandle": "e4ce975e-f7ff-4b5e-b15e-bf25f59371ae",
      "statementStatusUrl": "/api/statements/e4ce975e-f7ff-4b5e-b15e-bf25f59371ae"
    }
    
  • Si vous avez soumis une requête contenant plusieurs instructions SQL, le corps de la réponse inclut un objet ResultSet qui contient un champ statementHandles. Vous pouvez obtenir les handles des instructions individuelles à partir de ce champ.

    {
      ...
      "statementHandles" : [ "019c9fce-0502-f1fc-0000-438300e02412", "019c9fce-0502-f1fc-0000-438300e02416" ],
      ...
    

Par exemple, la commande curl suivante vérifie le statut de l’instruction avec le handle e4ce975e-f7ff-4b5e-b15e-bf25f59371ae :

curl -i -X GET \
    -H "Authorization: Bearer <jwt>" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "User-Agent: myApplicationName/1.0" \
    -H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" \
    https://<account_identifier>.snowflakecomputing.com/api/statements/e4ce975e-f7ff-4b5e-b15e-bf25f59371ae

où :

Lorsque vous envoyez une requête pour vérifier le statut, Snowflake renvoie l’un des codes de réponse, présentés dans l’organigramme ci-dessous :

Flow chart for checking the status of a statement submitted for execution

Comme le montre l’organigramme ci-dessus :

Obtention des résultats à partir de la réponse

Si vous soumettez une instruction SQL pour exécution ou vérifiez le statut d’exécution de l’instruction, Snowflake renvoie un objet ResultSet dans le corps de la réponse si l’instruction a été exécutée avec succès.

Voici un exemple d’un objet ResultSet qui est renvoyé pour une requête. La requête spécifie que les résultats doivent être paginés avec 10 lignes par page. Le champ numPages de l’objet resultSetMetaData indique qu’il y a 10 pages de résultats et le champ numRows indique que la requête trouve un total de 100 lignes.

{
 "code": "090001",
 "statementHandle": "536fad38-b564-4dc5-9892-a4543504df6c",
 "sqlState": "00000",
 "message": "successfully executed",
 "createdOn": 1597090533987,
 "statementStatusUrl": "/api/statements/536fad38-b564-4dc5-9892-a4543504df6c",
 "resultSetMetaData": {
  "page": 1,
  "pageSize": 10,
  "numPages": 10,
  "numRows": 100,
  "format": "json",
  "rowType": [
   {
    "name":"ROWNUM",
    "type":"FIXED",
    "length":0,
    "precision":38,
    "scale":0,
    "nullable":false
   }, {
    "name":"ACCOUNT_NAME",
    "type":"TEXT",
    "length":1024,
    "precision":0,
    "scale":0,
    "nullable":false
   }, {
    "name":"ADDRESS",
    "type":"TEXT",
    "length":16777216,
    "precision":0,
    "scale":0,
    "nullable":true
   }, {
    "name":"ZIP",
    "type":"TEXT",
    "length":100,
    "precision":0,
    "scale":0,
    "nullable":true
   }, {
    "name":"CREATED_ON",
    "type":"TIMESTAMP_NTZ",
    "length":0,
    "precision":0,
    "scale":3,
    "nullable":false
   }
  ]
 },
 "data": [
  ["0","customer1","1234 A Avenue","98765","1565481394123000000"],
  ["1","customer2","987 B Street","98765","1565516712912012345"],
  ["2","customer3","8777 C Blvd","98765","1565605431999999999"],
  ["3","customer4","64646 D Circle","98765","1565661272000000000"]
  ...
 ]
}

Vérification de la taille de la page du jeu de résultats par rapport à la limite autorisée

L’API SQL peut renvoyer une page de jeu de résultats qui a une taille maximale d’environ 10 MB. Si la page du jeu de résultats dépasse cette taille, le point de terminaison renvoie une réponse HTTP avec un jeu de résultats tronqué dans le corps et le champ code défini sur 391908 :

HTTP/1.1 200 OK
...
{
  "code": "391908",
  ...

Si cela se produit, envoyez à nouveau la requête avec le paramètre pageSize fixé à une valeur plus petite qui respecte la taille maximale d’une page.

Note

Actuellement, Snowflake renvoie un code de réponse HTTP 200 lorsque cela se produit, mais cela est susceptible de changer.

Obtenir des métadonnées sur les résultats

Dans l’objet ResultSet renvoyé dans la réponse, le champ resultSetMetaData contient un objet ResultSet_resultSetMetaData qui décrit le jeu de résultats (par exemple, le format des résultats, le nombre de pages de résultats, etc.)

Dans cet objet, le champ rowType contient un tableau d’objets ResultSet_resultSetMetaData_rowType. Chaque objet décrit une colonne dans les résultats. Le champ type spécifie le type de données Snowflake de la colonne.

{
 "resultSetMetaData": {
  "rowType": [
   {
    "name":"ROWNUM",
    "type":"FIXED",
    "length":0,
    "precision":38,
    "scale":0,
    "nullable":false
   }, {
    "name":"ACCOUNT_NAME",
    "type":"TEXT",
    "length":1024,
    "precision":0,
    "scale":0,
    "nullable":false
   }, {
    "name":"ADDRESS",
    "type":"TEXT",
    "length":16777216,
    "precision":0,
    "scale":0,
    "nullable":true
   }, {
    "name":"ZIP",
    "type":"TEXT",
    "length":100,
    "precision":0,
    "scale":0,
    "nullable":true
   }, {
    "name":"CREATED_ON",
    "type":"TIMESTAMP_NTZ",
    "length":0,
    "precision":0,
    "scale":3,
    "nullable":false
   }
  ]
 },
}

Extraction des données à partir des résultats

Dans l’objet ResultSet de la réponse, les résultats se trouvent dans le champ data. Le champ data contient un tableau de tableaux en JSON. Par exemple :

{
 "data": [
  ["0","customer1","1234 A Avenue","98765","1565481394123000000"],
  ["1","customer2","987 B Street","98765","1565516712912012345"],
  ["2","customer3","8777 C Blvd","98765","1565605431999999999"],
  ["3","customer4","64646 D Circle","98765","1565661272000000000"]
 ],
}

Chaque tableau dans le tableau contient les données d’une ligne :

  • Le premier élément de chaque tableau est une chaîne JSON contenant un ID de séquence qui commence à partir de 0.

  • Le reste des éléments de chaque tableau représente les données d’une ligne.

Les données du jeu de résultats sont codées en JSON v1.0, ce qui signifie que toutes les données sont exprimées sous forme de chaînes, quel que soit le type de données Snowflake de la colonne.

Par exemple, la valeur 1.0 d’une colonne NUMBER est renvoyée sous la forme de la chaîne "1.0". Autre exemple, les horodatages sont renvoyés sous la forme du nombre de nanosecondes depuis l’epoch. Par exemple, l’horodatage du jeudi 28 janvier, 2021 10:09:37.123456789 PM est renvoyé sous la forme "1611871777123456789".

Vous êtes responsable de la conversion des chaînes dans les types de données appropriés.

Snowflake renvoie les valeurs sous forme de chaînes dans les formats suivants, en fonction du type de données Snowflake :

INT / NUMBER

Nombre décimal dans une chaîne.

FLOAT

Entier ou flottant dans une chaîne.

VARCHAR

Chaîne.

BINARY

Nombre hexadécimal dans une chaîne.

BOOLEAN

0 (faux) ou 1 (vrai) dans une chaîne.

DATE

Valeur entière (dans une chaîne) du nombre de jours depuis l’epoch (par exemple 18262).

TIME, TIMESTAMP_LTZ, TIMESTAMP_NTZ

Valeur flottante (avec 9 décimales) du nombre de secondes écoulées depuis l’epoch (par exemple, 82919.000000000).

TIMESTAMP_TZ

Valeur flottante (avec 9 décimales) du nombre de secondes écoulées depuis l’epoch, suivie d’un espace et du décalage de fuseau horaire en minutes (par exemple, 1616173619000000000 960).

Récupération de pages supplémentaires de résultats

Si vous définissez le paramètre de requête pageSize pour paginer les résultats, Snowflake renvoie la première page de résultats dans la réponse. Vous pouvez utiliser le champ numPages de l’objet ResultSet_resultSetMetaData dans l’objet ResultSet pour déterminer le nombre total de pages de résultats.

Pour obtenir la page de résultats suivante ou d’autres pages de résultats, utilisez les URLs fournies dans l’en-tête Link dans la réponse HTTP. L’en-tête Link spécifie les URLs pour récupérer la première page de résultats, la suivante, la précédente et la dernière :

HTTP/1.1 200 OK
Link: </api/statements/e127cc7c-7812-4e72-9a55-3b4d4f969840?page=1>;rel="last",
      </api/statements/e127cc7c-7812-4e72-9a55-3b4d4f969840?page=1>;rel="next",
      </api/statements/e127cc7c-7812-4e72-9a55-3b4d4f969840512c?page=0>;rel="first"
...

Chaque URL de l’en-tête possède un attribut rel ayant l’une des valeurs suivantes :

  • first : la première page de résultats.

  • next : la page suivante de résultats.

  • prev : la page précédente de résultats.

  • last : la dernière page de résultats.

Soumission de plusieurs instructions SQL dans une seule requête

Parfois, vous pouvez avoir besoin de spécifier plusieurs instructions SQL dans une requête. Par exemple, vous pourriez avoir besoin de :

  • Définir une transaction explicite

  • Créer une procédure stockée

  • Définir et utiliser des variables de session dans des instructions dans une requête

  • Créer et utiliser des tables temporaires dans des instructions dans une requête

  • Modifier la base de données, le schéma, l’entrepôt ou le rôle pour les instructions dans une requête

Les sections suivantes expliquent comment soumettre une requête contenant plusieurs instructions SQL.

Spécifier plusieurs instructions SQL dans la requête

Pour soumettre plusieurs instructions SQL dans une seule requête, utilisez un point-virgule (;) entre chaque instruction. Par exemple :

POST /api/statements HTTP/1.1
Authorization: Bearer <jwt>
Content-Type: application/json
Accept: application/json
User-Agent: myApplication/1.0
X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT

{
  "statement": "alter session set QUERY_TAG='mytesttag'; select count(*) from mytable",
  ...

Obtenir les résultats pour chaque instruction SQL dans la requête

Pour qu’une requête contenant plusieurs instructions SQL soit traitée correctement, la réponse n’inclut pas les données renvoyées par l’exécution des instructions individuelles. À la place, la réponse contient un champ statementHandles qui contient un tableau de handles pour les instructions individuelles.

Note

Le champ statementHandles est différent du champ statementHandle :

  • Le champ statementHandle spécifie le handle pour l’ensemble des instructions SQL de la requête.

  • Le champ statementHandles est un tableau des handles des différentes instructions SQL de la requête.

Par exemple, supposons que vous envoyez une requête qui spécifie deux instructions SQL à exécuter :

POST /api/statements HTTP/1.1
Authorization: Bearer <jwt>
Content-Type: application/json
Accept: application/json
User-Agent: myApplication/1.0
X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT

{
  "statement": "select * from A; select * from B",
  ...

La réponse contient un champ statementHandles qui contient un tableau de handles pour les instructions individuelles.

HTTP/1.1 200 OK
...
{
  ...
  "statementHandles" : [ "019c9fce-0502-f1fc-0000-438300e02412", "019c9fce-0502-f1fc-0000-438300e02416" ],
  ...

Pour vérifier le statut et récupérer les données de chaque instruction individuelle, envoyez une requête GET au point de terminaison /statements/ et joignez l’handle de chaque instruction au chemin de l’URL :

GET /api/statements/019c9fce-0502-f1fc-0000-438300e02412
...
GET /api/statements/019c9fce-0502-f1fc-0000-438300e02416
...

Traitement des erreurs lors de la spécification de plusieurs instructions dans une requête

Si vous avez spécifié plusieurs instructions SQL dans la requête et qu’une erreur s’est produite lors de l’exécution de l’une d’entre elles, Snowflake renvoie le code de réponse HTTP 422 avec un objet QueryFailureStatus.

Vous pouvez obtenir des détails sur l’erreur à partir de cet objet.

Par exemple, supposons que votre demande spécifie les instructions suivantes, dans lesquelles la deuxième instruction INSERT contient une erreur :

{
  "statement": "create or replace table table1 (i int); insert into table1 (i) values (1); insert into table1 (i) values ('This is not a valid integer.'); insert into table1 (i) values (2); select i from table1 order by i",
  ...

Snowflake renvoie une réponse avec le code de réponse HTTP 422 et un objet QueryFailureStatus qui contient les détails de l’erreur :

HTTP/1.1 422 Unprocessable Entity
Content-Type: application/json
...
{
  "code" : "100132",
  "message" : "JavaScript execution error: Uncaught Execution of multiple statements failed on statement \"insert into table1 (i) values ...\" (at line 1, position 75).\nNumeric value 'This is not a valid integer.' is not recognized in SYSTEM$MULTISTMT at '    throw `Execution of multiple statements failed on statement {0} (at line {1}, position {2}).`.replace('{1}', LINES[i])' position 4\nstackstrace: \nSYSTEM$MULTISTMT line: 10",
  "sqlState" : "P0000",
  "statementHandle" : "019d6e97-0502-317e-0000-096d0041f036"
}

Dans l’exemple ci-dessus, l’instruction INSERT contenant l’erreur commence à la position du caractère 75 dans la valeur du champ statement.

Les instructions précédant l’instruction contenant l’erreur sont exécutées avec succès (l’instruction CREATE TABLE et la première instruction INSERT dans cet exemple). Les instructions qui suivent l’instruction contenant l’erreur ne sont pas exécutées.

Création et appel de procédures stockées

Vous pouvez utiliser l’API SQL pour créer et appeler des procédures stockées. Voici un exemple du corps d’une requête POST qui crée une nouvelle procédure stockée transmettant le nom d’une table et renvoyant le nombre de lignes dans cette table :

{
  "statement": "create or replace procedure sql_api_stored_proc(table_name varchar) returns varchar language javascript as $$var sql_command = \"select count(*) from \" + TABLE_NAME; var rs = snowflake.execute({sqlText: sql_command}); rs.next(); var rowCount = rs.getColumnValue(1); return rowCount; $$;",
  "resultSetMetaData": {
    "format": "json"
  },
  "role": "MY_ROLE",
  "warehouse": "MY_WAREHOUSE",
  "database": "MY_DB",
  "schema": "MY_SCHEMA"
}

Voici un exemple du corps de la réponse pour cette requête :

{
  "resultSetMetaData": {
    "page": 0,
    "numPages": 1,
    "numRows": 1,
    "format": "json",
    "rowType": [ {
      "name": "status",
      "database": "",
      "schema": "",
      "table": "",
      "type": "text",
      "byteLength": 16777216,
      "scale": null,
      "precision": null,
      "nullable": true,
      "collation": null,
      "length": 16777216
    } ]
  },
  "data": [ [ "0", "Function SQL_API_STORED_PROC successfully created." ] ],
  "code": "090001",
  "statementStatusUrl": "/api/statements/019c9f28-0502-f257-0000-438300e0a02a?requestId=...",
  "sqlState": "00000",
  "statementHandle": "019c9f28-0502-f257-0000-438300e0a02a",
  "message": "Statement executed successfully.",
  "createdOn": 1622494569592
}

Voici un exemple du corps d’une requête POST qui appelle la procédure stockée, en transmettant le nom de la table « prices »:

{
  "statement": "call sql_api_stored_proc('prices');",
  "resultSetMetaData": {
    "format": "json"
  },
  "role": "MY_ROLE",
  "warehouse": "MY_WAREHOUSE",
  "database": "MY_DB",
  "schema": "MY_SCHEMA"
}

Voici un exemple du corps de la réponse pour cette requête :

{
  "resultSetMetaData": {
    "page": 0,
    "numPages": 1,
    "numRows": 1,
    "format": "json",
    "rowType": [ {
      "name": "SQL_API_STORED_PROC",
      "database": "",
      "schema": "",
      "table": "",
      "type": "text",
      "byteLength": 16777216,
      "length": 16777216,
      "scale": null,
      "precision": null,
      "nullable": true,
      "collation": null
    } ]
  },
  "data": [ [ "0", "4" ] ],
  "code": "090001",
  "statementStatusUrl": "/api/statements/019c9f2a-0502-f244-0000-438300e04496?requestId=...",
  "sqlState": "00000",
  "statementHandle": "019c9f2a-0502-f244-0000-438300e04496",
  "message": "Statement executed successfully.",
  "createdOn": 1622494718694
}

Utilisation de transactions explicites

Pour exécuter des instructions SQL dans une transaction explicite, vous devez utiliser une requête simple HTTP pour spécifier le début, la fin et les instructions de la transaction. Par exemple :

{
  "statement": "begin transaction; insert into table2 (i) values (1); commit; select i from table1 order by i",
  ...

Comme c’est le cas lorsque vous spécifiez plusieurs instructions dans une requête, si la requête a été traitée avec succès, Snowflake renvoie une réponse contenant le champ statementHandles, qui est défini comme un tableau de handles pour les instructions de la requête (y compris les instructions BEGIN TRANSACTION et COMMIT).

HTTP/1.1 200 OK
Content-Type: application/json

{
  "resultSetMetaData" : {
    "page" : 0,
    "numPages" : 1,
    "numRows" : 1,
    "format" : "json",
    "rowType" : [ {
      "name" : "multiple statement execution",
      "database" : "",
      "schema" : "",
      "table" : "",
      "type" : "text",
      "byteLength" : 16777216,
      "scale" : null,
      "precision" : null,
      "nullable" : false,
      "collation" : null,
      "length" : 16777216
    } ]
  },
  "data" : [ [ "0", "Multiple statements executed successfully." ] ],
  "code" : "090001",
  "statementHandles" : [ "019d6ed0-0502-3101-0000-096d00421082", "019d6ed0-0502-3101-0000-096d00421086", "019d6ed0-0502-3101-0000-096d0042108a", "019d6ed0-0502-3101-0000-096d0042108e" ],
  "statementStatusUrl" : "/api/statements/019d6ed0-0502-3101-0000-096d0042107e?requestId=066920fa-e589-43c6-8cca-9dcb2d4be978",
  "sqlState" : "00000",
  "statementHandle" : "019d6ed0-0502-3101-0000-096d0042107e",
  "message" : "Statement executed successfully.",
  "createdOn" : 1625684162876
}

Les handles du champ statementHandles correspondent aux instructions de la requête. Dans cet exemple, les instructions et leurs handles correspondants sont les suivants :

  • BEGIN TRANSACTION (019d6ed0-0502-3101-0000-096d00421082)

  • INSERT (019d6ed0-0502-3101-0000-096d00421086)

  • COMMIT (019d6ed0-0502-3101-0000-096d0042108a)

  • SELECT (019d6ed0-0502-3101-0000-096d0042108e)

Vous pouvez utiliser ces handles pour vérifier le statut de chaque instruction.

Obtention des détails sur une erreur

Si l’instruction ne s’exécute pas correctement, Snowflake renvoie l’un des codes de réponse suivants, comme indiqué dans le diagramme de flux ci-dessous :

Flow chart for handling errors during statement execution

Comme le montre cet organigramme :

  • Si l’exécution de l’instruction prend plus de temps que le délai d’expiration spécifié par le champ timeout de la requête (ou le délai d’expiration spécifié par le paramètre STATEMENT_TIMEOUT_IN_SECONDS si le champ timeout n’est pas défini), Snowflake renvoie le code de réponse HTTP 408 avec un objet QueryStatus.

    Utilisez cet objet pour obtenir des détails sur l’annulation de l’exécution de l’instruction.

  • Si une erreur s’est produite lors de l’exécution de l’instruction, Snowflake renvoie le code de réponse HTTP 422 avec un objet QueryFailureStatus.

    Vous pouvez obtenir des détails sur l’erreur à partir de cet objet.

Annulation de l’exécution d’une instruction SQL

Pour annuler l’exécution d’une instruction, envoyez une requête POST au point de terminaison d’annulation.

POST /api/statements/{statementHandle}/cancel

L’organigramme suivant illustre les étapes permettant d’annuler une requête.

Flow chart for cancelling the execution of a statement