Utilisation des données binaires¶

L’utilité et la flexibilité du type de données BINARY sont mieux démontrées quand des exemples sont utilisés. Ce chapitre fournit des exemples pratiques de tâches qui impliquent le type de données BINARY et ses trois schémas d’encodage.

Conversion entre hex et base64¶

Le type de données BINARY peut être utilisé comme étape intermédiaire lors de la conversion entre des chaînes hexadécimales et base64.

Conversion hexadécimal>base64 avec TO_CHAR :

SELECT c1, TO_CHAR(TO_BINARY(c1, 'hex'), 'base64') FROM hex_strings;

+----------------------+-----------------------------------------+
| C1                   | TO_CHAR(TO_BINARY(C1, 'HEX'), 'BASE64') |
|----------------------+-----------------------------------------|
| df32ede209ed5a4e3c25 | 3zLt4gntWk48JQ==                        |
| AB4F3C421B           | q088Qhs=                                |
| 9324df2ecc54         | kyTfLsxU                                |
+----------------------+-----------------------------------------+

Conversion base64>hexadécimal :

SELECT c1, TO_CHAR(TO_BINARY(c1, 'base64'), 'hex') FROM base64_strings;

+------------------+-----------------------------------------+
| C1               | TO_CHAR(TO_BINARY(C1, 'BASE64'), 'HEX') |
|------------------+-----------------------------------------|
| 3zLt4gntWk48JQ== | DF32EDE209ED5A4E3C25                    |
| q088Qhs=         | AB4F3C421B                              |
| kyTfLsxU         | 9324DF2ECC54                            |
+------------------+-----------------------------------------+

Conversion entre texte et UTF-8 octets¶

Dans Snowflake, les chaînes sont composées de caractères Unicode, tandis que les valeurs binaires sont composées d’octets. En convertissant une chaîne en valeur binaire au format UTF-8, vous pouvez manipuler directement les octets qui composent les caractères Unicode.

Convertir des chaînes à un caractère en une représentation UTF-8 en octets à l’aide de la commande TO_BINARY :

SELECT c1, TO_BINARY(c1, 'utf-8') FROM characters;

+----+------------------------+
| C1 | TO_BINARY(C1, 'UTF-8') |
|----+------------------------|
| a  | 61                     |
| é  | C3A9                   |
| ❄  | E29D84                 |
| π  | CF80                   |
+----+------------------------+

Convertir une séquence d’octets UTF-8 en une chaîne à l’aide de TO_CHAR , TO_VARCHAR :

SELECT TO_CHAR(X'41424320E29D84', 'utf-8');

+-------------------------------------+
| TO_CHAR(X'41424320E29D84', 'UTF-8') |
|-------------------------------------|
| ABC ❄                               |
+-------------------------------------+

Obtenir le digest MD5 en base64¶

Convertir le Digest MD5 binaire en une chaîne base64 à l’aide de TO_CHAR :

SELECT TO_CHAR(MD5_BINARY(c1), 'base64') FROM variants;

+----------+-----------------------------------+
| C1       | TO_CHAR(MD5_BINARY(C1), 'BASE64') |
|----------+-----------------------------------|
| 3        | 7MvIfktc4v4oMI/Z8qe68w==          |
| 45       | bINJzHJgrmLjsTloMag5jw==          |
| "abcdef" | 6AtQFwmJUPxYqtg8jBSXjg==          |
| "côté"   | H6G3w1nEJsUY4Do1BFp2tw==          |
+----------+-----------------------------------+

Convertir en binaire avec un format variable¶

Convertir des chaînes en valeurs binaires en utilisant un format binaire extrait de la chaîne. L’instruction inclut les fonctions TRY_TO_BINARY et SPLIT_PART :

SELECT c1,
       TRY_TO_BINARY(SPLIT_PART(c1, ':', 2), SPLIT_PART(c1, ':', 1)) AS binary_value
  FROM strings;

+-------------------------+----------------------+
| C1                      | BINARY_VALUE         |
|-------------------------+----------------------|
| hex:AB4F3C421B          | AB4F3C421B           |
| base64:c25vd2ZsYWtlCg== | 736E6F77666C616B650A |
| utf8:côté               | 63C3B474C3A9         |
| ???:abc                 | NULL                 |
+-------------------------+----------------------+

Essayez plusieurs formats pour la conversion :

SELECT c1,
       COALESCE(
         x'00' || TRY_TO_BINARY(c1, 'hex'),
         x'01' || TRY_TO_BINARY(c1, 'base64'),
         x'02' || TRY_TO_BINARY(c1, 'utf-8')) AS binary_value
  FROM strings;

+------------------+------------------------+
| C1               | BINARY_VALUE           |
|------------------+------------------------|
| ab4f3c421b       | 00AB4F3C421B           |
| c25vd2ZsYWtlCg== | 01736E6F77666C616B650A |
| côté             | 0263C3B474C3A9         |
| 1100             | 001100                 |
+------------------+------------------------+

Note

Puisque les requêtes ci-dessus utilisent TRY_TO_BINARY, le résultat est NULL si le format n’est pas reconnu ou si la chaîne ne peut être analysée avec le format donné.

Convertir les résultats de l’exemple précédent en chaînes à l’aide de SUBSTR et de DECODE :

SELECT c1,
       TO_CHAR(
       SUBSTR(c1, 2),
       DECODE(SUBSTR(c1, 1, 1), x'00', 'hex', x'01', 'base64', x'02', 'utf-8')) AS string_value
  FROM bin;

+------------------------+------------------+
| C1                     | STRING_VALUE     |
|------------------------+------------------|
| 00AB4F3C421B           | AB4F3C421B       |
| 01736E6F77666C616B650A | c25vd2ZsYWtlCg== |
| 0263C3B474C3A9         | côté             |
| 001100                 | 1100             |
+------------------------+------------------+

Décodage personnalisé avec JavaScript UDF¶

Le type de données BINARY permet le stockage de données arbitraires. Puisque les UDFs JavaScript prennent en charge le type de données via Uint8Array (voir Présentation des UDFs JavaScript), il est possible d’implémenter une logique de décodage personnalisée dans JavaScript. Ce n’est pas la façon la plus efficace de travailler, mais elle est très puissante.

Créer une fonction de décodage à partir du premier octet :

CREATE OR REPLACE FUNCTION my_decoder (b BINARY)
  RETURNS VARIANT
  LANGUAGE JAVASCRIPT
AS '
  IF (B[0] == 0) {
      var number = 0;
      FOR (var i = 1; i < B.length; i++) {
          number = number * 256 + B[i];
      }
      RETURN number;
  }
  IF (B[0] == 1) {
      var str = "";
      FOR (var i = 1; i < B.length; i++) {
          str += String.fromCharCode(B[i]);
      }
      RETURN str;
  }
  RETURN NULL;';

SELECT c1, my_decoder(c1) FROM bin;

+----------------+----------------+
| C1             | MY_DECODER(C1) |
|----------------+----------------|
| 002A           | 42             |
| 0148656C6C6F21 | "Hello!"       |
| 00FFFF         | 65535          |
| 020B1701       | null           |
+----------------+----------------+