Bigquery

【Bigquery】Javascriptで書けるユーザ定義関数を使うと効率が爆上がり

ぐぅ

ぐぅ

京大工学部▶︎京大大学院情報学研究科▶︎日系企業のITエンジニア。研究室ではAI×通信の研究も行いAIにも知見あり。友人とプログラミング初学者向けブログ「はやぶさエンジニア」を運営。ノーコードで企業し、サブスク型サービスを運営。26歳大阪在住

★本記事は5分で読み終えることができます。

【本記事の信頼性】
現役エンジニアで普段業務でBigqueryを活用しデータ分析をしている筆者がJavascriptで記述できるユーザ定義関数の使い方を紹介しています。

未経験くん
Bigqueryのユーザ定義関数って何??
ループ回したり、文字列を分割したりとかSQLじゃキツイんだけど。。。
未経験ちゃん

このような悩みを解説するための記事になります。

まずはユーザ定義関数を紹介した後、書き方について解説していきますね。

この記事でわかること

  • ユーザ定義関数とは何か
  • ユーザ定義関数でできること
  • ユーザ定義関数の書き方

ユーザ定義関数とは

Googleの公式ドキュメントはこちらです。

ユーザ定義関数とはUDF(User Defined Function)と呼ばれ、Bigqueryでは「SQL」か「Javascript」で関数を作成できるものです。

SQLでもJavascriptでも書き方はほとんど同じです。

ユーザ定義関数には利用方法が以下の3種類あります。

メリット


1. 一時的なユーザ定義関数
2. 永続的なユーザ定義関数
3. 外部のスクリプトをインポートする関数

1つ目は一回限りで関数として書き出す「一時的なユーザ定義関数」があります。
2つ目は、一度関数を作成し、Bigquery上に関数を保存することで何度でも関数を呼び出すことができる機能です。
そして3つ目は、GCS(Google Cloud Storage)というクラウドストレージ上にJavascriptを保存しておくとBigqueryへ呼び出すことができるものです。

次章のできることでもいいますが、何回もこれから使うであろう処理をユーザ定義関数として保存できるので便利ですよ^ ^

ユーザ定義関数でできること

簡単にまとめると以下の2つのメリットがあります。

メリット


1. 単純なSQLでは実行できない処理ができる
2. 処理を関数としていつでも呼び出して利用できる

BigqueryではSQLを記述してデータを操作していきます。
しかし、SQLでは実装できない処理がたまに出てきます。

例えば簡単な例ですと、時刻の文字列を分割する処理です。
変な形式で時刻データを持っているシステムがうちの会社ではあるんですよね笑(ほんとやだ)
ユーザ定義関数を定義しておいて、以下のような時刻データをJavascriptでサクッとtimestamp型に変換しています。

2021617 101214 → 2021-06-17 10:12:14

こういった処理を関数化して保存しておくと効率が爆上がりです!

ユーザ定義関数の書き方

ユーザ定義関数を作るには「CREATE FUNCTION」を使います。

一時的なユーザ定義関数

まず「一時的なユーザ定義関数」から見ていきましょう。

公式サイトにも記載されている基本形は以下です。
「CREATE TEMP FUNCTION」のTEMPは一時的な関数を定義するものです。

この例では、「xとyを入力としてx*yを出力」として返します。

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64) -- x,yをfloat型と定義
RETURNS FLOAT64                                           -- 戻り値をfloat型に指定
LANGUAGE js AS r"""                                       -- 言語をjsと指定
  return x*y;                                             -- 行う処理を記述
""";

SELECT multiplyInputs(a, b) FROM (SELECT 3 as a, 2 as b); -- SQLで関数を呼び出して使う

実行結果は3*2 = 6となっている。

実行結果

永続的なユーザ定義関数

続いては、永続ユーザ定義関数です。

永続ユーザ定義関数は以下のように書きます。
ポイントはプロジェクト名.データセット名.ファンクション名を記載することです。

これを実行すると自動的に指定したデータセットにファンクションが保存されます。
*データセットはあらかじめ作っておかないとダメです!!!

CREATE OR REPLACE FUNCTION `project_id.dataset_id.function_name`(x FLOAT64, y FLOAT64) -- x,yをfloat型と定義
RETURNS FLOAT64                                                                        -- 戻り値をfloat型に指定
LANGUAGE js AS r"""                                                                    -- 言語をjsと指定
  return x*y;                                                                          -- 行う処理を記述
""";

実際に、以下を実行してみます。
testfuncの中にmultinumberという関数が保存されるはずです。

CREATE OR REPLACE FUNCTION `project_id.testfunc.multinumber`(x FLOAT64, y FLOAT64) -- x,yをfloat型と定義
RETURNS FLOAT64                                                                        -- 戻り値をfloat型に指定
LANGUAGE js AS r"""                                                                    -- 言語をjsと指定
  return x*y;                                                                          -- 行う処理を記述
""";

実行するとできていますね!関数が保存できているのでいつでも呼び出すことができます。

呼び出し方は以下のようにすればOK!

select testfunc.multinumber(2,3)   -- 入力する変数の型など間違えないように。

外部のJavascriptをインポートして使う

GCPにはGCSというストレージサービスがあります。
GCSのバケットにJavascriptを保存しておくことで呼び出すことができます。
(GCSにはバケットという概念があり、フォルダのようなものと考えてください。)

まずは、GCSのmultinumber.jsには以下のように記述する。

function multiple(x, y){   // multiple関数を定義
  return x*y;          
}

Bigqueryで以下のように呼び出す。

CREATE TEMP FUNCTION hogefunc(x FLOAT64, y FLOAT64) 
RETURNS FLOAT64                                                                        
LANGUAGE js 
OPTIONS (
   library = "gs://my-bucket/multinumber.js"                       -- my-bucketにあるmultinumber.jsを呼び出す
AS r"""                                                                    
  return multiple(x,y);                                            -- multinumber.jsに記述されているmultiple関数を利用                          
""";

select hogefunc(2,3)

複雑な関数を外部に記述しておいて、Bigqueryでは呼び出すだけ!

これでBigqueryマスターです^ ^

まとめ

今回はBigqueryでJavascriptを活用して関数を定義し、作業効率を爆上げしようという記事でした。

SQLではやれないこと、やりにくいことをサクッとやる方法でした!
良いBigqueryライフを!

Bigqueryを体系的に学びたい人はUdemyを受けてみると良いですよ。
以下の記事を参考にしてみてくださいね。

【動画で勉強したい人必見】Bigqueryを学べるUdemy講座3選

本記事は5分で読み終えることができます。【記事の信頼性】京都大学出身の現役ITエンジニアである筆者は日々の業務でBigqueryを活用しています。Bigqueryを知り尽くした筆者がBigqueryを ...

続きを見る

  • この記事を書いた人
ぐぅ

ぐぅ

京大工学部▶︎京大大学院情報学研究科▶︎日系企業のITエンジニア。研究室ではAI×通信の研究も行いAIにも知見あり。友人とプログラミング初学者向けブログ「はやぶさエンジニア」を運営。ノーコードで企業し、サブスク型サービスを運営。26歳大阪在住

-Bigquery

© 2021 はやぶさエンジニア Powered by AFFINGER5