この記事は trocco Advent Calendar 2023 の 2 日目の記事となります。

この記事では、trocco のカスタム変数ループ実行を使い、複数の BigQuery テーブルを一括でクローンする方法について記します。

はじめに：利用技術の紹介

BigQueryの「テーブルクローン」機能について

「テーブルクローン」はテーブルを複製する機能です。今年の5月に GA となりました。この機能を用いて複製したテーブル（以下、クローンテーブル）は複製元テーブル（以下、ベーステーブル）と独立しており、片方に変更を加えてももう片方には反映されません。

この機能の特徴は、ただテーブルを複製しただけではストレージ料金は発生せず、そこから追加・変更されたデータ分のみにストレージ料金が発生することです。これにより、単純にテーブルを複製するより費用を抑えることができます。

「テーブルクローン」についての詳細は公式ドキュメントをご確認ください:

troccoの「カスタム変数ループ実行」機能について

カスタム変数を埋め込んだ転送設定やデータマート定義をワークフローに組み込んだ際に、カスタム変数の値をループで変更できる機能です。これにより、同じジョブを値を変えながら複数回実行できるようになります。

公式ドキュメントではユースケースとして次のような場合が挙げられていました:

転送元データベース系コネクタから、同一スキーマのテーブルをまとめて転送する
Google Analyticsから転送するデータがサンプリングされないようにする

詳細は公式ドキュメントをご確認ください:

何が嬉しいのか？

複数のテーブルのクローンを簡単に行えることです。

Google Cloud 公式ではテーブルクローンのユースケースとして次の場合が挙げられていました¹:

開発とテストに使用できる本番環境テーブルのコピーを作成する
本番環境のすべてのデータを物理的にコピーすることなく、ユーザーが独自の分析やデータ操作を生成できるサンドボックスを作成する

いずれのユースケースもクローンしたいテーブルが複数になることが予想されます。

テーブルクローンの作成方法はいくつかありますが、いずれの方法もテーブルを1つずつ作成していきます。例えば、 SQL を用いた方法では次ようなの CREATE TABLE CLONE ステートメントを使用します:

CREATE TABLE
  myproject.myDataset_backup.myTableClone
CLONE
  myproject.myDataset.myTable;

この方法で複数のテーブルをクローンしたい場合、テーブル毎にクローンクエリを実行することになります。 trocco のカスタム変数ループ実行を用いると、クローンしたいテーブルが多い場合やクローン対象としたいテーブルの条件を変更したい場合に対応することができます。

このことは dbt Core で本番/開発の環境分けを行う際に大いに役立ちます。 dbt の公式ドキュメントでは、複数人で dbt のコードを書いている場合、ユーザ毎にスキーマ（ BigQuery の場合データセット）を分ける方法が有用であるとして紹介されています ² 。

trocco のカスタム変数ループ実行を用いたテーブルクローンを行えば、次の流れで前述の方法を実現することができます:

INFORMATION_SCHEMA.TABLES へクエリして本番データセット内のテーブル一覧を取得
カスタム変数を用いて、クエリ結果（＝テーブル名）を CREATE TABLE CLONE ステートメントに埋め込む

CREATE TABLE CLONE ステートメントでデータセットの部分もカスタム変数にすれば、データセットの切り替えも簡単に行えます。

次章で実際に trocco のカスタム変数ループ実行を用いたテーブルクローンを行ってみます。

実現方法

想定ケース

今回は次のような場合を想定することにします:

本番データセット production_dataset に raw_customers 、 raw_orders 、 raw_payments の3つのテーブルがある
この3つのテーブルを、 dbt ユーザ1、2のそれぞれの開発用データセット（ development_dataset_for_user_1 、 development_dataset_for_user_2 ）へクローンする

図にすると次のようになります:

raw_customers 、 raw_orders 、 raw_payments のデータは dbt 公式の jaffle_shop のデータを使用することにします。

実際に手順を進める前に、あらかじめ production_dataset データセットを作成し、その中に raw_customers テーブル、 raw_orders テーブル、 raw_payments テーブルを用意しておきます。

STEP 1 ｜開発用データセットを用意する

BigQuery コンソール上で開発用データセット development_dataset_for_user_1 、 development_dataset_for_user_2 を作成します。

開発用データセットのリージョンは本番データセットと同じにしてください。リージョンが異なるとクローンできません³。

STEP 2 ｜データマート定義を作成する

trocco でデータマート定義を作成します。その際、次のカスタム変数とクエリを次のように設定します。

カスタム変数:

変数名	データ形	値
$destination_dataset$	文字列	クローン先の BigQuery データセット名を入れてください（例: development_dataset_for_user_X ）
$table$	文字列	クローンしたい BigQuery テーブルの名前を入れてください（例: raw_customers ）

クエリ（ YOUR_PROJECT の部分は自身の環境に合わせて変更してください）:

CREATE OR REPLACE TABLE
  `YOUR_PROJECT.$destination_dataset$.$table$` -- コピー先
CLONE
  `YOUR_PROJECT.production_dataset.$table$` -- コピー元

2024/04/07 追記
CREATE OR REPLACE TABLE より DROP TABLE IF EXISTS を使った方が良いそうです。

なお、「DROP TABLE IF EXISTS」を使わずに「CREATE TABLE」を「CREATE OR REPLACE TABLE」とすると、クローンを実行した時刻が更新されませんでしたので、ストレージ料金を考えると「CREATE OR REPLACE TABLE」を使わないほうがよいでしょう。

引用元: BigQueryのテーブルコピー方法3選

ここでは次のように設定したとします:

データマート定義の名前: 【ワークフロー組込用】本番データセットから開発用データセットへのクローン用ジョブ

STEP 3 ｜ワークフロー定義を作成する

trocco でワークフロー定義を作成し、先ほど作成したデータマート定義を組み込みます。その際、組み込むデータマート定義のタスク編集画面を開き、カスタム変数ループを設定します。

カスタム変数ループの設定方法は次の公式ドキュメントをご確認ください:

設定項目はそれぞれ次のようにします:

ループの種類: 「Google BigQueryの結果でループ」
対象カスタム変数: $table$
クエリ（ YOUR_PROJECT の部分は自身の環境に合わせて変更してください）:

SELECT
  table_name,
FROM
  `YOUR_PROJECT.production_dataset.INFORMATION_SCHEMA.TABLES`

設定例:

BigQuery の INFORMATION_SCHEMA を用いると、データセットやテーブルなどのメタデータ情報を取得することができます。その中の INFORMATION_SCHEMA.TABLES へクエリし table_name カラムの値を SELECT することで、データセット内にあるテーブル名を取得することができます。

INFOMATION_SCHEMA についての詳細は次の公式ドキュメントをご確認ください:

STEP 4 ｜ワークフローを実行する

作成したワークフローを実行し、開発用データセットへテーブルをクローンします。

まずは development_dataset_for_user_1 データセットへクローンしてみます。

実行する際、実行設定を次のようにします:

カスタム変数展開方法:
- 「変数ごとに値を指定」
展開値:
- $destination_dataset$ : development_dataset_for_user_1
- $table$ : （空欄のままにする）

設定例:

実行が完了すると、 development_dataset_for_user_1 データセットに raw_customers 、 raw_orders 、 raw_payments の 3 つのテーブルが追加されていることが確認できます。

$destination_dataset$ の展開値を development_dataset_for_user_2 にすれば、 development_dataset_for_user_2 データセットへもクローンできます。

Q. 間違えて実行設定を変更せず、デフォルト設定（＝「現在時刻を基準に展開」）で実行してしまったら？

その場合、各カスタム変数にはデータマート定義で設定した際の値が入ります。今回の場合、それぞれ次の値が入ります:

変数名	値
$destination_dataset$	クローン先の BigQuery データセット名を入れてください（例: development_dataset_for_user_X ）
$table$	クローンしたい BigQuery テーブルの名前を入れてください（例: raw_customers ）

すると、存在しないデータセットやテーブルを参照することになるため、エラーとなります。

エラーログ:

2023-11-22 02:21:19.408 +0900 [info]: enqueued...
2023-11-22 02:21:20.580 +0900 [info]: Preparing your trocco environment...
2023-11-22 02:21:35.507 +0900 [info]: Successfully created your environment
2023-11-22 02:21:35.707 +0900 [info]: start syncing Data Mart [【ワークフロー組込用】本番データセットから開発用データセットへのクローン用ジョブ]
2023-11-22 02:21:35.738 +0900 [info]: check your query:
2023-11-22 02:21:35.738 +0900 [info]: CREATE OR REPLACE TABLE
2023-11-22 02:21:35.738 +0900 [info]:   `YOUR_PROJECT.クローン先の BigQuery データセット名を入れてください（例: development_dataset_for_user_X ）.raw_payments` -- コピー先
2023-11-22 02:21:35.738 +0900 [info]: CLONE
2023-11-22 02:21:35.738 +0900 [info]:   `YOUR_PROJECT.production_dataset.raw_payments` -- コピー元
2023-11-22 02:21:36.015 +0900 [info]: start executing your query: 
2023-11-22 02:21:36.015 +0900 [info]: CREATE OR REPLACE TABLE
2023-11-22 02:21:36.015 +0900 [info]:   `YOUR_PROJECT.クローン先の BigQuery データセット名を入れてください（例: development_dataset_for_user_X ）.raw_payments` -- コピー先
2023-11-22 02:21:36.015 +0900 [info]: CLONE
2023-11-22 02:21:36.015 +0900 [info]:   `YOUR_PROJECT.production_dataset.raw_payments` -- コピー元
2023-11-22 02:21:36.234 +0900 [error]: Google::Apis::ClientError invalid: Project name needs to be separated by dot from dataset name, not by colon in table name "YOUR_PROJECT.クローン先の BigQuery データセット名を入れてください（例: development_dataset_for_user_X ）.raw_payments".
2023-11-22 02:21:36.253 +0900 [error]: failed to sync your datamart [【ワークフロー組込用】本番データセットから開発用データセットへのクローン用ジョブ]

以上で、想定ケースのテーブルクローンが完了しました。

TIPS

タスクを並列実行して処理時間を短縮する

タスクの並列実行を有効にすることで、テーブルクローンにかかる時間を短縮することができます。

有効にするには次の 3 つの設定を行います:

データマート定義で、並列でのジョブ実行を許可する

ワークフロー定義で、タスク同時実行上限数を増やす（最大 10 個まで⁴）

ワークフロー定義のタスク編集画面で、並列実行を ON にする

並列実行なしの場合と最大 3 タスクまで並列実行した場合で実行時間を比較してみました:

試行回数	並列実行なし	最大 3 タスクまで並列実行
1回目	0:01:33	0:00:26
2回目	0:01:05	0:00:25
3回目	0:00:57	0:00:27

並列実行なしと比べて、実行時間が半分〜1/3程度に短縮されていることが確認できます。

別プロジェクトにクローンする

データマート定義でプロジェクト ID もカスタム変数にすることで、別プロジェクトへテーブルクローンを実行させることができます。

データマート定義のカスタム変数の設定:

データマート定義のクエリ:

CREATE OR REPLACE TABLE
  `$destination_project$.$destination_dataset$.$table$` -- コピー先
CLONE
  `YOUR_PROJECT.production_dataset.$table$` -- コピー元

ワークフロー実行時の実行設定:

結び

今回は trocco のカスタム変数ループ実行で複数の BigQuery テーブルを一括クローンする方法について記しました。

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

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