Verwenden von Binärdaten¶

Die Nützlichkeit und Flexibilität des Datentyps BINARY lässt sich am besten am Beispiel demonstrieren. Dieses Thema enthält praktische Beispiele für Aufgaben, die den Datentyp BINARY und seine drei Codierungsschemas betreffen.

Konvertierung zwischen Hex und Base64¶

Der Datentyp BINARY kann als Zwischenschritt bei der Konvertierung zwischen Hex- und Base64-Zeichenfolgen verwendet werden.

Konvertieren Sie mit TO_CHAR von Hex zu Base64:

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

Konvertieren Sie von Base64 nach Hex:

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

Konvertieren Sie zwischen Text und UTF-8-Bytes¶

Zeichenfolgen in Snowflake bestehen aus Unicode-Zeichen, während Binärwerte aus Bytes bestehen. Durch die Umwandlung einer Zeichenfolge in einen Binärwert mit dem UTF-8-Format können Sie die Bytes, aus denen sich die Unicode-Zeichen zusammensetzen, direkt ändern.

Konvertieren Sie einstellige Zeichenfolgen in ihre UTF-8-Darstellung in Bytes mithilfe von TO_BINARY:

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

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

Konvertieren Sie eine UTF-8-Bytefolge in eine Zeichenfolge unter Verwendung von TO_CHAR , TO_VARCHAR:

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

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

Abrufen von MD5-Digest in Base64¶

Konvertieren Sie den binären MD5-Digest in eine base64-Zeichenfolge mithilfe von 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==          |
+----------+-----------------------------------+

Konvertieren in Binärwerte mit variablem Format¶

Konvertiert Zeichenfolgen in Binärwerte unter Verwendung eines aus der Zeichenfolge extrahierten Binärformats. Die Anweisung beinhaltet die Funktionen TRY_TO_BINARY und 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                 |
+-------------------------+----------------------+

Versuchen Sie mehrere Formate für die Konvertierung:

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

Bemerkung

Da die obigen Abfragen TRY_TO_BINARY verwenden, ist das Ergebnis NULL, wenn das Format nicht erkannt wird oder wenn die Zeichenfolge nicht mit dem angegebenen Format analysiert werden kann.

Konvertieren Sie die Ergebnisse des vorherigen Beispiels wieder in Zeichenfolgen mit SUBSTR und 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             |
+------------------------+------------------+

Kundenspezifische Decodierung mit JavaScript-UDF¶

Der Datentyp BINARY ermöglicht die Speicherung beliebiger Daten. Da JavaScript UDFs den Datentyp über Uint8Array (siehe Einführung in JavaScript-UDFs) unterstützen, ist es möglich, eine kundenspezifische Dekodierungslogik in JavaScript zu implementieren. Dies ist nicht die effizienteste Art zu arbeiten, aber sie ist sehr leistungsfähig.

Erstellen Sie eine Funktion, die basierend auf dem ersten Byte dekodiert:

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