BigQuery UDFs を使ったデータ変換の実例
BigQuery UDFs を使ったデータ変換の実例
BigQuery で実行できない処理がある場合は、
bigquery-utils/udfs at master · GoogleCloudPlatform/bigquery-utils
を活用すると自分で実装する必要がなくなって便利です。
やりたいこと
Base36(36進数)の文字列を Base10(10進数)の文字列に変換。
- 例:
kf12oi->1234567890
以下の対応で変換が必要になりました。
取得したデータにおけるIDの取り扱いについて
Twitter広告APIの仕様上、TROCCO上で扱うキャンペーンID（
campaign_id）や広告グループID（line_item_id）は、X Adsの管理画面で確認できるIDをBase36形式に変換した文字列となります。 例えば、UI上では1234567890のキャンペーンIDは、TROCCOで取り扱う場合はkf12oiに変換する必要があります。
解決方法
Google が提供する BigQuery の UDF を利用することで、簡単にデータ変換を行うことができます。
BigQuery コミュニティの UDF を全世界に公開、誰もが簡単にデータ変換を行えるように | Google Cloud 公式ブログ
リージョンのサフィックスで UDF を参照するだけで、自身のリージョンで UDF を使用できます。
bqutil.<dataset>_<region>.<function>()たとえば、異なるリージョンで typeof() 関数を使用する場合は次のようにします。
bqutil.fn_eu.typeof() -- eu multi-region bqutil.fn_europe_west1.typeof() -- europe-west1 region bqutil.fn_asia_south1.typeof() -- asia-south1 region
今回のケースでは、cw_convert_base 関数を使用します。
この関数は、指定した基数から別の基数への変換を行います。
cw_convert_base(number STRING, from_base INT64, to_base INT64)
Convert string from given base to another base
SELECT bqutil.fn.cw_convert_base('001101011', 2, 10); SELECT bqutil.fn.cw_convert_base('A', 16, 2); 107 1010
実際の変換
asia-northeast1 リージョンで実行する場合、以下のように記述します。
select bqutil.fn_asia_northeast1.cw_convert_base('kf12oi', 36, 10)
このクエリを実行すると、kf12oi が 1234567890 に変換されます。
