如何在Google BigQuery中将ULID二进制数据转换为字符串？

BigQuery版本转换函数定义 #

二进制转ULID字符串（BINARY_TO_ULID）

CREATE OR REPLACE FUNCTION `your-project.your-dataset.BINARY_TO_ULID`(ulid_bytes BYTES)
RETURNS STRING
AS (
  WITH hex_str AS (
    SELECT LPAD(FORMAT('%X', ulid_bytes), 32, '0') AS hex
  ),
  parts AS (
    SELECT
      LPAD(CONV(SUBSTR(hex, 1, 2), 16, 32), 2, '0') AS part1,
      LPAD(CONV(SUBSTR(hex, 3, 15), 16, 32), 12, '0') AS part2,
      LPAD(CONV(SUBSTR(hex, 18, 15), 16, 32), 12, '0') AS part3
    FROM hex_str
  ),
  base32_raw AS (
    SELECT CONCAT(part1, part2, part3) AS raw FROM parts
  )
  SELECT
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
      raw,
      'V', 'Z'), 'U', 'Y'), 'T', 'X'), 'S', 'W'), 'R', 'V'), 'Q', 'T'), 'P', 'S'), 'O', 'R'), 'N', 'Q'), 'M', 'P'), 'L', 'N'), 'K', 'M'), 'J', 'K'), 'I', 'J'
    ) AS ulid_str
  FROM base32_raw
);

ULID字符串转二进制（ULID_TO_BINARY，可选）

如果需要反向转换，可使用以下函数：

CREATE OR REPLACE FUNCTION `your-project.your-dataset.ULID_TO_BINARY`(ulid_str STRING)
RETURNS BYTES
AS (
  WITH base32_raw AS (
    SELECT
      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        UPPER(ulid_str),
        'J', 'I'), 'K', 'J'), 'M', 'K'), 'N', 'L'), 'P', 'M'), 'Q', 'N'), 'R', 'O'), 'S', 'P'), 'T', 'Q'), 'V', 'R'), 'W', 'S'), 'X', 'T'), 'Y', 'U'), 'Z', 'V'
      ) AS raw
  ),
  parts AS (
    SELECT
      LPAD(CONV(SUBSTR(raw, 1, 2), 32, 16), 2, '0') AS part1,
      LPAD(CONV(SUBSTR(raw, 3, 12), 32, 16), 15, '0') AS part2,
      LPAD(CONV(SUBSTR(raw, 15, 12), 32, 16), 15, '0') AS part3
    FROM base32_raw
  )
  SELECT FROM_HEX(CONCAT(part1, part2, part3)) AS ulid_bytes
  FROM parts
);

测试语句 #

SELECT
  `your-project.your-dataset.BINARY_TO_ULID`(`your-project.your-dataset.ULID_TO_BINARY`('01H2SMN7KNGNNJBB0HA155APHB')) AS new_value,
  '01H2SMN7KNGNNJBB0HA155APHB' AS original_value
WHERE
  `your-project.your-dataset.BINARY_TO_ULID`(`your-project.your-dataset.ULID_TO_BINARY`('01H2SMN7KNGNNJBB0HA155APHB')) = '01H2SMN7KNGNNJBB0HA155APHB';

输出示例 #

new_value                 |original_value            |
--------------------------+--------------------------+
01H2SMN7KNGNNJBB0HA155APHB|01H2SMN7KNGNNJBB0HA155APHB|

注意：使用时请将your-project.your-dataset替换为你实际的BigQuery项目和数据集名称。

内容的提问来源于stack exchange，提问作者bravo