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.

Dans ce chapitre :

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 , TO_VARCHAR :

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                                |
+----------------------+-----------------------------------------+
Copy

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                            |
+------------------+-----------------------------------------+
Copy

Conversion texte/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, on peut 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                   |
+----+------------------------+
Copy

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 ❄                               |
+-------------------------------------+
Copy

Obtenir le MD5 Digest en Base64

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

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==          |
+----------+-----------------------------------+
Copy

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                 |
+-------------------------+----------------------+
Copy

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                 |
+------------------+------------------------+
Copy

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 pas être analysée avec le format donné.

Convertir les résultats de l’exemple précédent en chaînes à l’aide de SUBSTR , SUBSTRING 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             |
+------------------------+------------------+
Copy

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 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;';
Copy
SELECT c1, my_decoder(c1) FROM bin;

+----------------+----------------+
| C1             | MY_DECODER(C1) |
|----------------+----------------|
| 002A           | 42             |
| 0148656C6C6F21 | "Hello!"       |
| 00FFFF         | 65535          |
| 020B1701       | null           |
+----------------+----------------+
Copy
Langage: Français