바이너리 데이터 사용하기

바이너리 데이터 사용하기 (Using binary data)

BINARY 데이터 타입의 유용함과 유연함은 예제로 보는 것이 가장 좋아요. 이 주제에서는 BINARY 데이터 타입과 그 세 가지 인코딩 방식(hex, base64, UTF-8)을 다루는 실질적인 예제를 제공해요. hex와 base64 변환, 텍스트와 UTF-8 바이트 변환, MD5 다이제스트 변환 같은 작업을 직접 살펴볼 수 있어요.

출처: Snowflake SQL Reference

본문

BINARY 데이터 타입의 유용함과 유연함은 예제로 보는 것이 가장 좋아요. 이 주제에서는 BINARY 데이터 타입과 그 세 가지 인코딩 방식과 관련된 작업의 실용적인 예제를 제공해요.

hex와 base64 사이 변환

BINARY 데이터 타입은 hex 문자열과 base64 문자열 사이를 변환할 때 중간 단계로 사용할 수 있어요.

TO_CHAR를 사용해 hex에서 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                                |
+----------------------+-----------------------------------------+

base64에서 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                            |
+------------------+-----------------------------------------+

텍스트와 UTF-8 바이트 사이 변환

Snowflake의 문자열은 유니코드 문자로 구성되는 반면, 바이너리 값은 바이트로 구성돼요. 문자열을 UTF-8 포맷으로 바이너리 값으로 변환하면, 유니코드 문자를 구성하는 바이트를 직접 조작할 수 있어요.

TO_BINARY를 사용해 단일 문자 문자열을 바이트 단위의 UTF-8 표현으로 변환해요:

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

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

TO_CHAR , TO_VARCHAR를 사용해 UTF-8 바이트 시퀀스를 문자열로 변환해요:

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

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

base64로 MD5 다이제스트 가져오기

TO_CHAR를 사용해 바이너리 MD5 다이제스트를 base64 문자열로 변환해요:

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

가변 포맷으로 바이너리 변환

문자열에서 추출한 바이너리 포맷을 사용해 문자열을 바이너리 값으로 변환해요. 이 문에는 TRY_TO_BINARY와 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                 |
+-------------------------+----------------------+

변환을 위해 여러 포맷을 시도해요:

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

위 쿼리들은 TRY_TO_BINARY를 사용하므로, 포맷이 인식되지 않거나 주어진 포맷으로 문자열을 파싱할 수 없으면 결과가 NULL이에요.

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

JavaScript UDF로 커스텀 디코딩

BINARY 데이터 타입은 임의의 데이터를 저장할 수 있어요. JavaScript UDF가 Uint8Array로 이 데이터 타입을 지원하므로(JavaScript UDF 소개 참고), JavaScript로 커스텀 디코딩 로직을 구현할 수 있어요. 가장 효율적인 방법은 아니지만 매우 강력한 방법이에요.

첫 번째 바이트를 기준으로 디코딩하는 함수를 만들어요:

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

더 알아보기 (Learn more)