BigQueryでULIDをエンコードするUDF

TL;DR;

BigQueryでBYTES型として格納されているULIDをCrockford's Base32でエンコードした文字列型にするUDFの実装を共有します。

実装

CREATE OR REPLACE encodeULID_Internal(ulid_hex STRING)
RETURNS STRING
LANGUAGE js AS """
  const CHARSET = "0123456789ABCDEFGHJKMNPQRSTVWXYZ";
  let digits = BigInt("0x" + ulid_hex);
  let encoded = new Array(26);

  for (let i = 26; i >= 0; i--) {
    encoded[i] = CHARSET[Number(digits & 31n)];
    digits >>= 5n;
  };

  return encoded.join("");
""";

CREATE OR REPLACE encodeULID(ulid BYTES)
RETURNS STRING AS (
  IF(
    ulid IS NULL OR BYTES_LENGTH(ulid) <> 16,
    NULL,
    encodeULID_Internal(TO_HEX(ulid))
  )
);

自分の考える限りでは、多分これがいちばん速くてスッキリ書けるはず。
もっと単純で速い実装があればかかってこいや。

命名がsnake_caseだったりcamelCaseだったりするのはご愛嬌ということでどうか頼む。

モチベ

ULIDはソートができるIDとして便利だけど、IDを表示するときにはCrockford's Base32とかいうわけわからんエンコードがされることが多い。

一方で、BigQueryはBYTES型のデータを表示するときに勝手にBase64エンコードして表示する。

おかげで、ULIDを含むデータをBigQueryにBYTES型として保持していると、BigQueryコンソール上ではBase64エンコードされた値が表示されるのに、実際のシステムを見に行くとBase32エンコードの全然違う値でIDが表示されてたりして混乱する。

データ基盤としては、ULIDを直接入力して検索できるように、BYTES型で格納されているULIDをBase32エンコードしたいのだけれど、ULIDのCrockford's Base32とBigQuery標準のTO_BASE32は仕様が異なるらしく、単純にSELECT TO_BASE32(ulid)とかしても全然違うものになってしまう。

それならそれでとAIくんにエンコード用のUDFの実装をお願いしたのだが、なかなかいい実装にならなかったので、あれやこれやと悩むことになってしまった。

そんな悩みの果てにたどり着いた実装がなかなか自分でも満足いくものだったので、この機会にご共有したかったってわけ。