【GCP】BigQueryの定数やユーザー定義関数（UDF）

 

BigQueryでは関数を用いることで定数を作り出すことができます。
そもそも定数とは、プログラミングをやってる人であればわかると思いますが、
一定の値のことです。そのままですね。
例えば長期的に見れば変わりますが、消費税率など。消費税率は数年ごとにここ最近は変わったりしますが、すごい頻度で変わるものではないです。
sqlの中で計算処理をする際に、直に×(1+0.1)などして計算式を入れたとします。
それらが複数の箇所で0.1を使用して計算をしていると、もし消費税率が変わった時、直に0.1と記載している箇所全てを新しい税率に変更しなければなりません。
 

そうなった場合、エラーになるリスクもあれば、どこか見落として税率の修正ができておらず計算結果が合わないなども生じる可能性がぐんと上がります。
そうならないように、定数という形で消費税率を定義して、その定数を参照して計算をするようにすれば、
定数の値を変更するだけで、消費税率を使って計算している箇所全てに新しい税率を一括適用できます。
 

プログラムを書くうえで、定数というのはエラーのリスクの軽減であったり、修正箇所の見落としリスクの軽減をすることができるため、
重要なものになります。

 

BigQueryのユーザー定義関数

BigQueryにはユーザー定義関数と呼ばれるものがあります。UDF(User Defined Function)とも略します。
ユーザー定義関数という名称なので、ユーザーが関数を作ったりすることがBigQueryの中でできるということです。
BigQueryでプログラムを書くとなると、クエリSQLを想像しますが、そのエディタ内で関数を定義することができます。
そしてユーザー定義関数はCREATE FUNCTIONで作成することができます。（そのままですね）
 

ユーザー自身で関数を定義できるので、定数などを関数と用いて作成することができます。

 

このユーザー定義関数は一時的なものと、永続的なものの2種類で作成可能です。

項目	説明
一時的	クエリ実行が完了するまで作成される。 作成はCREATE TEMP FUNCTION
永続的	クエリ実行が完了しても永続的にその関数が作られたままになる。 作成はCREATE FUNCTION

一時的なUDF作成

以下は一時的なUDFを作成しています。
1行目では、一時的なのでCREATE TEMP FUNCTION、そして関数名と引数、型を指定しています。
2行目では、この関数の結果の戻り値の型を指定します。RETURNS STRINGで文字列を返すようにしています。
3行目では、UDFでどの言語を使って処理を書くかを指定してます。ここではjavascriptを使用するので、LANGUAGE js ASとしてます。

CREATE TEMP FUNCTION getCountry(country STRING)
  RETURNS STRING
  LANGUAGE js AS 
  """
    function convert(country) {
      if(country == "Japan") return "日本";
       return country;
    }
    return convert(country);
  """;


select getCountry("Japan")    // 日本

UDFBigQuery内でJavascriptやSQLの文法を書いて、データ整形処理を行うことのできる機能です。
SQLのselect文内でデータ整形処理となると、splitやconcatなどの文字列操作だったり、case whenなどで値の表示変換などくらいしかできませんでした。
しかし、そういった処理を実行したいクエリ内で書くのではなく、別途関数を用意してその中にデータを渡して、javascriptやSQLなどを書いて変換処理を定義したりすることができるようになりました。

GoogleはSQLでの処理を推奨していますが、ここではjavascriptでデータ整形について簡単に書いてみたいと思います。
 

具体的な記載方法としては以下の通りとなります。

CREATE TEMP FUNCTION getCountry(country STRING)
  RETURNS STRING
  LANGUAGE js AS 
  """
    function convert(country) {
      if(country == "Japan") return "日本";
       return country;
    }
    return convert(country);
  """;


select getCountry("Japan")    // 日本

結果として日本が帰ってきます。

getCountry関数にJapanという値を渡しているので、日本と表示されます。
sql文なので、getCountryの引数にcolumnを指定することでそのカラム全ての値でgetCountryの処理をするようになります。
あとで紹介しますが、nestされた値つまり配列の値を渡すことで、javascript内で配列処理をすることもできます。

 

あくまで変換処理は1レコードずつに対して処理をするので、
group byなどをした場合、複数のレコードが1つにまとまり、その値を配列にして渡すなどの処理となります。
配列にして渡す処理については、次のARRAY_AGG関数で扱います。

 

CREATE TEMP FUNCTION getPrefecture(pref STRING)
  RETURNS STRING
  LANGUAGE js AS 
  """
    function convert(pref) {
      if(pref == "Tokyo") return "東京";
       return pref;
    }
    return convert(pref);
  """;

With pref_eng as (
  SELECT "Tokyo" as pref_name UNION ALL SELECT "Saitama"
)
SELECT getPrefecture(pref_name) as pref_name FROM pref_eng

 

永続的なUDF作成

永続的なUDFは、CREATE FUNCTIONで作成します。
この永続的なUDFはデータセットごとに作成されるので、永続的なUDFを作成したいデータセットを指定する必要があります。
2,3行目は一時的と同じで、
1行目は、CREATE FUNCTIONそして、作成したいデータセット今回はbigquery_testというデータセットに作成することとして、以下のように関数名にbigquery_testを指定して作成します。

CREATE FUNCTION bigquery_test.getPrefecture(pref STRING)
  RETURNS STRING
  LANGUAGE js AS 
  """
    function convert(pref) {
      if(pref == "Tokyo") return "東京";
       return pref;
    }
    return convert(pref);
  """;

を実行すると、以下のように作成されます。

 

 

永続的になったので、この状態で以下クエリを実行してみます。
ポイントとしては、上では永続的UDFはデータセットbigquery_testで作成したので、呼び出す際はbigquery_test.getPrefectureにして実行します。

With pref_eng as (
  SELECT "Tokyo" as pref_name UNION ALL SELECT "Saitama"
)
SELECT bigquery_test.getPrefecture(pref_name) as pref_name FROM pref_eng

実行すると問題なく処理されます。一時的であれば一緒にUDFを実行しないといけないですが、
永続的なUDFを先に作成しているので、上記実行する際になくても処理されます。

 

CREATE TEMP FUNCTION

BigQueryで定数を作成します。
定数は基本的に設定情報など環境変数などで使用されることもあるかと思います。
定数なので基本的に値の変わらないもの、例えば本番環境やステージング環境でのIPアドレスや、ドメインなど。
消費税などもそのうちの一つですね。
sql内で消費税込の値を出すとき、数値*1.1して計算をすると思いますが、直接計算を記載してしまうと、
今後消費税が変わったときに全て変更しないといけなくなります。
なので、1つの箇所で値を変更すれば全てに適用できるような設定をするのが定数です。
 

BigQUeryでは関数の戻り値で定数を実現します。
例えば消費税込みの値を計算したい場合の関数は以下となります。

CREATE TEMP FUNCTION getInTax(cost Integer){
   return cost*1.1   //消費税0.1を含む
}

そしてsqlでは、

select getInTax(100)    // 110

で110という数値でかえってきます。
こうすることで、今後消費税率が変わっても、関数の中の1.1を変更するだけで全ての処理に適用されるようになります。

 

BigQueryのユーザー定義関数を使用する際の注意点

以下のヘルプにまとまってます。
UDFのヘルプページ

※ javascriptが書けますが、documentなどはwebを操作するわけではないので使用できません。

定数の作成

上のUDF内で値を1つreturnさせれば、定数を作成することができます。
毎回そのユーザー定義関数を呼び出せば、必ず1つの値が常にreturnされるので、定数とすることができます。
 

このやり方も可能ですが、定数については、手続き型言語であるDECLAREやSETを使って定数を作ることも可能になりました。

DECLAER a INT64;
SET a = 10;
SELECT a*a;

DECLAREは訳で「宣言する」という意味です。なので変数を宣言しています。
そしてSETは訳で「設定する」という意味なので、変数aに10を入れるということになります。
ここまではプログラミングチックで理解しやすいと思います。
そして、SELECTでクエリ実行で表示するので、SELECT a*aで10*10で100を表示させます。
 

このようにユーザー属性関数を定義しなくても、簡単に定数を設定することが可能になりました。

以下でもBigQuery扱ってます！

【GCP】BigQueryのスケジュールクエリの使い方

続きを見る