背景

データベースにエンコードされたURLが入っていた場合、そのURLをデコードしなければいけませんよね。
既存の関数で一発！というわけにはいかないURLのデコードの関数をUDF(User-Defined Function)として作ってみました。

URLエンコード、デコードとは

まず初めに、URLのエンコードとデコードとはなんぞやということを書いておきます。
URLのエンコードとは、本来URLの中で使えない文字を、使用できる文字の特殊な組み合わせによって置き換えることです。日本語の入っているURLなどもエンコードすることで機能します。

具体的には、 %の後に 0~9,A~Fの16進数2桁で1Biteの文字を表します。日本語などの1Biteで表しきれないものは、その組み合わせで表します。(例：%41がA、%23が#、%E3%81%82 があなど)

デコードとは、エンコードされたURLを元の状態に戻すことです。

では、本題に入ります。

BigQueryではJavaScriptが使える

BigQueryのUDFは、実はJavaScriptで書くことができます。つまり、SQLでどうやったらいいかわからない処理も、JavaScriptを使えば簡単にできたりするってことなんですね。

使い方は簡単で、UDFの中身にJavaScriptを書くだけです。

しかし、欠点としてJavaScriptはSQLと比べBigQuery上での動作が遅くなります。
なんと、クエリの高速化に関して言及している公式ドキュメントでは、「JavaScript ユーザー定義関数を避ける」と明記されているんですね笑
なので、JavaScriptで作るUDFの乱用は要注意です！

▼Google公式ドキュメント『クエリ計算の最適化』

URLデコードのUDF

URLをデコードする一次関数の作り方は、

CREATE TEMPORARY FUNCTION url_decode(url STRING)
RETURNS STRING
LANGUAGE js AS "return decodeURIComponent(url);";

となります。

似た関数に decodeURI()があるのですが、URL内で特別な意味を持つ「; , / ? : @ & = + $ #」の扱いが若干異なります。
decodeURI()は上記の文字がエンコードされていた場合デコードに対応しないのに対し、decodeURIComponent() は対応します。用途に応じて使い分けてみてください。

select url_decode ('https://qiita.com/%41%41%41') as decoded_url

を実行してみると、結果は

decoded_url
https://qiita.com/AAA

となります。

この関数にデコードできないURLを引数として渡した場合、
『URIError: URI malformed at url_decode(STRING)』 というエラーが出ます。

▼デコードできない引数の例

select url_decode ('https://qiita.com/%4') as decoded_url

デコードできない場合はnullを返したい

デコードできるURLは全てデコードしたいが、デコードできない場合にエラーでクエリが回らなくなるのは避けたい... そんな時もありますよね。

そんな時は、JavaScriptの try...catch文 を用いてエラー回避しましょう。今回はJavaScriptをで書いたUDFを使うため、エラーへの対処をSQLではなくJavaScript側で行います。

▼URLをデコードし、デコードできない場合はnullを返す関数

CREATE TEMPORARY FUNCTION url_decode_try(url STRING)
    RETURNS STRING
    LANGUAGE js AS """
    try{
    return decodeURIComponent(url);
    }catch(e){
    return null;
    }
    """ 
    ;

この関数を使って

select 
  url_decode_try ('https://qiita.com/%41%41%41') as not_error,
  url_decode_try('https://qiita.com/%4') as error_occurred

実行すると、結果は

not_error	error_occurred
https://qiita.com/AAA	null

となります。

これを使えば、例えば　table01 というテーブルの url というカラムにデコードできるURLとできないURLが混じっていた場合も、

select coalesce(decode_url_try(url),url)
from table01

で問題なく処理できます！