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.

Unter diesem Thema:

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

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

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

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

Abrufen von MD5-Digest in Base64

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

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

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

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

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 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
Sprache: Deutsch