皆様こんにちは
本日はBigqueryでUDFの使い方について学びます。
UDFは日本語名でユーザー定義関数とも呼ばれていて、SQLで関数を記述すると
メンテナンスし辛い事と併せて可読性やSQL実行時にメモリ不足でエラーを起こす事もあるため
予め別で関数を定義しておいてこれらのデメリットを解消するのが目的で活用されます。
まずは使用しているBQプロジェクト内にデモ用のテーブルを作成しましょう
—
CREATE OR REPLACE TABLE
`BQを使ってるプロジェクト名.データセット名.テーブル名` (
DEMO1 INT64, –カラムの名称とデータの型を指定します
DEMO2 BOOLEAN,
DEMO3 INT64,
DEMO4 STRING, –ここだけ文字列型にしている
);
— 作成したテーブルに値を設定していく
INSERT INTO
`BQを使ってるプロジェクト名.データセット名.テーブル名`
(DEMO1, DEMO2, DEMO3, DEMO4)
VALUES
(2021, true, 1, “hogehoge”),
(2021, false, 2, “hagehage”),
(2021, false, 3, “higehige”),
(2021, true, 4, “gehogeho”)
今回はDEMO4に対して関数を定義します
本来であれば
SELECT
CASE
WHEN DEMO4 = “hogehoge” THEN “ほげほげ”
WHEN DEMO4 = “hagehage” THEN “はげはげ”
WHEN DEMO4 = “higehige” THEN “ひげひげ”
ELSE ‘その他’ END AS RENAME
FROM `BQを使ってるプロジェクト名.作成先のデータセット名.作成したテーブル名`
といったのを書きますがこれらが複数行に渡ると可読･メンテ運用が難しくなるため
予めCASE文を別で定義します。
CREATE OR REPLACE FUNCTION
–普段から使用しているBQプロジェクトを設定してを指定します。
`BQを使ってるプロジェクト名.データセット名.作成時のUDF関数テーブル名`
({ここは適当なカラム名でOK} STRING) AS (
CASE
WHEN DEMO4 = “hogehoge” THEN “ほげほげ”
WHEN DEMO4 = “hagehage” THEN “はげはげ”
WHEN DEMO4 = “higehige” THEN “ひげひげ”
ELSE ‘その他’ END
);
上記の関数を実行すると指定のプロジェクトのデータセット内にUDF関数が保存されます。
また実際にこのUDF関数を使う時ですが適用させたいカラムを指定する場合は
下記の通りに指定してください。
SELECT
BQを使ってるプロジェクト名.データセット名.UDF関数名(DEMO4)
FORM
`BQを使ってるプロジェクト名.データセット名.テーブル名`
試してみていかがでしたでしょうか。
基幹のSQLに関数を直接入れるよりもメンテナンスもしやすく実行リソースも削減できて
可読性も向上するので色々と試してみてください。
ご覧いただきましてありがとうございました。
コメントを残す