trocco のカスタム変数ループ実行で BigQuery に作成した UDF をコピーする

※ この記事は trocco® Advent Calendar 2023 シリーズ2 、 Yappli Advent Calendar 2023 （2枚目）の 20 日目の投稿です。

この記事では、 trocco の カスタム変数ループ実行 を使い、 BigQuery に作成した UDF をコピーする方法について記します。

何が嬉しいのか

UDF の別データセットへのコピーを手軽に行えることです。

例えば、 BigQuery でデータセットを複製する場合、 BigQuery DataTransfer Service が手段として挙げられます。しかし、 2023年12月現在、 BigQuery Data Transfer Service ではルーティンはコピーされません。 UDF もコピーしたい場合、この記事の方法を用いることで実現することができます。

どうやって実現しているのか

ルーティンの情報は INFORMATION_SCHEMA.ROUTINES というテーブルに記録されています。このテーブルの ddl というカラムに、 UDF を作成した際の DDL が格納されています。

trocco のカスタム変数ループでこの ddl カラムの値をカスタム変数に渡し、カラムの値＝DDLを trocco のデータマート定義で実行します。これにより、 ddl カラムに入っていた DDL がループで実行されていき、 UDF が作成されていきます。

手順

まず、trocco に次のデータマート定義を作成します。

• カスタム変数: $ddl$
• プロジェクトID: コピー先の BigQuery 接続
• 実行クエリ: $ddl$

次に、作成したデータマート定義を使ってワークフロー定義を作成します。

最後に、カスタム変数ループを次のように設定します。

• ループの種類: 「Google BigQueryのクエリ結果でループ」
• 対象カスタム変数: $ddl$
• Google BigQuery接続情報: コピー元の BQ 接続
• クエリ:

SELECT
  REPLACE(
    ddl,
    "CREATE FUNCTION `SOURCE_PJ_ID`",
    "CREATE OR REPLACE FUNCTION `DESTINATION_PJ_ID`" -- REPLACE も入れることで、ワークフローの再実行に対応
    ) AS ddl -- プロジェクトIDをコピー先のものに変更
FROM
  `SOURCE_PJ_ID.SOURCE_DATASET`.INFORMATION_SCHEMA.ROUTINES
ORDER BY
  created -- 作った順に登録することで、 DDL 内で別の UDF が参照されていた際のエラーを回避する

手順は以上です。あとは、この状態でワークフローを実行すれば UDF をコピーすることができます。

結び

今回は trocco のカスタム変数ループ実行で BigQuery に作成した UDF をコピーする方法について記しました。

実は、今月は trocco のカスタム変数ループ実行と BigQuery の INFORMATION_SCHEMA を組み合わせた記事をもう1本書いています。

この組み合わせ、思いの外汎用性が高いかもしれません。今後も色々試してみようと思います。

もし記事内に誤りがあったり、もっと適切な方法があったりしたらコメント等でご教示いただければ幸いです。

ここまでお読みいただきありがとうございました