BigQueryでBase36デコードする方法

経緯

TROCCOでBigQueryに転送したX adsの情報がBase36エンコードされている
https://documents.trocco.io/docs/data-source-twitter-ads

これを元の値で利用したかった

方法1: ユーザー定義関数 (UDF)

CREATE FUNCTION
  decodeBase36(base36_string STRING)
  RETURNS INT64
  LANGUAGE js AS """
    if (base36_string === null) {
      return null;
    }
    const decoded = parseInt(base36_string, 36);
    if (isNaN(decoded)) {
        return null;
    }
    return decoded;
  """;

SELECT dataset.decodeBase36('kf12oi')

ビューを保存するために永続的なUDFを定義してますが、
クエリで保存する場合は一時的なUDF (CREATE TEMP FUNCTION) でも問題ありません。

該当のフィールドはINTEGERで出力されます。

方法2: bigquery-utils

cw_convert_base を利用する

SELECT bqutil.fn.cw_convert_base('kf12oi', 36, 10);

こちらの場合は STRING で出力されます。

結論

UDFでもbqutilでもお好きな方を使用してください。