BigQuery から AlloyDB へのゼロ ETL データ連携

1. 概要

パート 1 では、Knowledge Catalog と DataScan を使用して、混沌とした非構造化 PDF を BigQuery のクリーンでインテリジェントな構造化テーブルに変換しました。現在、当社は堅牢なデータ ウェアハウスを保有しています。

パート 1 のラボでは、架空のフローズン ヨーグルト フランチャイズのユースケースを取り上げ、テキスト、表、画像を含む 400 個の非構造化 PDF ファイルを、BigQuery Knowledge Catalog と Dataplex を使用して、関係が自動的に推論される構造化された BigQuery テーブルに変換しました。

作成するアプリの概要

このセッションでは、AlloyDB for PostgreSQL を設定し、BigQuery データを AlloyDB に直接統合するという魔法のようなことを行います。つまり、トランザクション アプリは、データをコピーまたは複製することなく、ウェアハウス データをリアルタイムでクエリできます。

デベロッパーは、この段階で次の質問をする必要があります。

「データがすでに BigQuery にあるのに、なぜ AlloyDB を使用するのですか？アプリケーションが BigQuery に対して SELECT ステートメントを直接実行しないのはなぜですか？」

理由:

Lakehouse Federation を使用すると、AlloyDB のクエリエンジンを利用して、同じインターフェース内でアプリケーションのトランザクション ワークロードと分析ワークロードを強化できます。さらに、このデータを AlloyDB で具体化またはインポートして、アプリケーションで使用する際のアクセスを高速化し、AlloyDB AI およびカラム型エンジンを使用できるようになります。

AlloyDB をトランザクション データベースとして使用しつつ、BigQuery または BigLake に大量のデータを保存できます。通常、アプリケーションは、こうした異なる Google Cloud サービスにまたがるデータにアクセスするために両システムとは個別に統合されます。AlloyDB 向けレイクハウス フェデレーションを使用すると、外部データラッパーとして実装された AlloyDB の連携クエリサポートを利用して、AlloyDB の SQL インターフェースを通じて BigQuery と AlloyDB のデータにアクセスできます。

AlloyDB から BigQuery データをクエリする脆弱な ETL パイプラインを構築する代わりに、連携クエリを使用します。AlloyDB は統合エンドポイントとして機能し、必要に応じて BigQuery にシームレスにアクセスします。

構築を始めましょう。

学習内容

• ボタンをクリックするだけで AlloyDB クラスタ、インスタンス、ネットワーキングを設定する方法
• フェデレーションの準備として拡張機能を設定する方法
• BigQuery から AlloyDB への連携を設定する方法
• テストする

要件

• ブラウザ（Chrome、Firefox など）
• 課金を有効にした Google Cloud プロジェクト
• SQL に関する基本的な知識。

2. 始める前に

プロジェクトを作成する

1. Google Cloud コンソールのプロジェクト選択ページで、Google Cloud プロジェクトを選択または作成します。
2. Cloud プロジェクトに対して課金が有効になっていることを確認します。詳しくは、プロジェクトで課金が有効になっているかどうかを確認する方法をご覧ください。

このラボでは、請求先アカウントがなくても利用できる機能が限られています。その権限で実際に操作してみたい場合は、「請求先アカウントがない場合」の限定条件に該当するメモを参照してください。ご注意ください。

3. Google Cloud 上で動作するコマンドライン環境の Cloud Shell を使用します。Google Cloud コンソールの上部にある [Cloud Shell をアクティブにする] をクリックします。

4. Cloud Shell に接続したら、次のコマンドを使用して、すでに認証済みであることと、プロジェクトがプロジェクト ID に設定されていることを確認します。

gcloud auth list

5. Cloud Shell で次のコマンドを実行して、gcloud コマンドがプロジェクトを認識していることを確認します。

gcloud config list project

6. 認証を行う場合

gcloud auth login

7. プロジェクトが設定されていない場合は、次のコマンドを使用して設定します。

export PROJECT_ID=<YOUR_PROJECT_ID>
gcloud config set project <YOUR_PROJECT_ID>

8. 必要な API を有効にする: 次のコマンドを実行して、必要な API をすべて有効にします。

gcloud services enable alloydb.googleapis.com

注意点とトラブルシューティング

「ゴースト プロジェクト」症候群	gcloud config set project を実行したが、実際にはコンソール UI で別のプロジェクトが表示されている。左上のプルダウンでプロジェクト ID を確認してください。
請求のバリケード	プロジェクトを有効にしたが、請求先アカウントを忘れた。AlloyDB は高性能エンジンです。ガソリン タンク（課金）が空の場合、起動しません。
API 伝播の遅延	[API を有効にする] をクリックしたのに、コマンドラインに Service Not Enabled と表示される。60 秒ほど待ちます。クラウドがニューロンを起動するまでしばらくお待ちください。
割り当て Quags	新しいトライアル アカウントを使用している場合は、AlloyDB インスタンスのリージョン割り当てに達する可能性があります。us-central1 が失敗した場合は、us-east1 を試してください。

3. パート 1 のデータの簡単なまとめ

このセクションでは、非構造化 PDF から抽出した構造化データが BigQuery で使用可能であることを確認する必要があります。パート 1 を見逃した場合や、請求先アカウントをお持ちでない場合でも、次の手順を完了すれば、すぐに始めることができます。

個人の Gmail アカウントから Google Cloud コンソールに移動し、コンソールの右上にある [Cloud Shell をアクティブにする] ボタンをクリックします。

その後、以下の「請求先アカウントがない場合」の手順に沿って操作します。

請求先アカウントをお持ちでない場合

この Codelab をざっと読んで、請求先アカウントなしで実践的な概要を把握しようとした場合、Datascan ジョブを設定したり、Knowledge Catalog の推論を実行したりできなかった可能性があります。そこで、上記のステップで 400 個の レシピとサプライヤーの PDF から推論したデータが、こちらで参照できるように用意されています。https://github.com/AbiramiSukumaran/raw-data-to-gold/tree/main/data。

以下のセクションでは、データを引き続き利用する方法について説明します。

請求先アカウントをお持ちでない場合

請求先アカウントなしでデータを引き続き利用する手順:

1. 上記の github repo リンクから、データ csv ファイル（BigQuery データ）を取得できます。
2. まず、Cloud Shell ターミナルから次のコマンドを実行して、BigQuery データセットを作成します。

bq mk --location us-central1 --dataset froyo_data

3. 次に、次のコマンドを 1 つずつ実行して、GitHub リポジトリから 8 個のデータファイル（CSV ファイル）を作業ディレクトリにダウンロードします。

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.allergen.csv

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.consistsof.csv

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.containsallergen.csv

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.froyo_data_materialized.csv

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.ingredient.csv

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.product.csv

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.suppliedby.csv

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.supplier.csv

4. 次のコマンドを 1 つずつ実行して、新しく作成したデータセットのデータを使用してこれらのテーブルを作成します。

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.allergen ./froyo_data.allergen.csv allergen_name:STRING

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.consistsof ./froyo_data.consistsof.csv product_id:STRING,ingredient_id:STRING

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.containsallergen ./froyo_data.containsallergen.csv ingredient_id:STRING,allergen_name:STRING

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.froyo_data_materialized ./froyo_data.froyo_data_materialized.csv allergen:STRING,containsallergen:STRING,ingredient:STRING,product:STRING,suppliedby:STRING,supplier:STRING,ref:STRING,md5_hash:STRING

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.ingredient ./froyo_data.ingredient.csv ingredient_id:STRING,ingredient_name:STRING,purity:STRING,moisture_content:STRING,ph_range:STRING,specific_gravity_range:STRING

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.product ./froyo_data.product.csv product_id:STRING,product_name:STRING,category:STRING,stability:STRING

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.suppliedby ./froyo_data.suppliedby.csv ingredient_id:STRING,supplier_id:STRING

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.supplier ./froyo_data.supplier.csv supplier_id:STRING,supplier_name:STRING,production_site_id:STRING,facility_grade:STRING

BigQuery にデータが格納されたので、次のステップに進みましょう。

4. AlloyDB クラスタ、インスタンス、ネットワークを設定する

AlloyDB クラスタ、インスタンス、その他の依存関係の設定に役立つウェブベースのクイック スタート アプリケーションがあります。このラボの手順 2 ～ 4 に沿って、ボタンをクリックするだけで設定できます。

https://codelabs.developers.google.com/quick-alloydb-setup

クラスタが作成されたら、[クラスタの概要] ページに移動し、そこからサービス アカウントの詳細をコピーします。

5. 権限の設定

このサービス アカウントに BigQuery の権限を付与する

1. [IAM と管理] > [IAM] に移動します。
2. [アクセス権を付与] をクリックします。
3. AlloyDB サービス アカウントのアドレスを [新しいプリンシパル] フィールドに貼り付けます。
4. 次のロールを割り当てます。

• BigQuery データ閲覧者（roles/bigquery.dataViewer）: データの読み取りを許可します。
• BigQuery ユーザー（roles/bigquery.user）: クエリの実行を許可します。
• （省略可、推奨）BigQuery 読み取りセッション ユーザー（roles/bigquery.readSessionUser）: Storage Read API を介して大規模なデータセットの読み取りを最適化します。

6. AlloyDB に接続して BigQuery 拡張機能を有効にする

次に、新しい AlloyDB インスタンスに接続して、フェデレーション拡張機能を構成します。ここでは AlloyDB Studio を使用します。

1. クラスタの概要ページ（AlloyDB コンソール）で、プライマリ インスタンスの [プライマリを編集] をクリックし、一番下までスクロールして [詳細構成オプション] を表示します。
2. [フラグ] セクションに移動し、以下の図のように 2 つのフラグを [オン] に設定します。

3. [**インスタンスを更新**] ボタンをクリックします。更新が完了するまで数分かかります。4. クラスタの概要ページ（AlloyDB コンソール）で、[AlloyDB Studio] をクリックします。

5. AlloyDB のクイック設定の手順で構成したデータベース、ユーザー名、パスワードを使用して接続します。
6. 接続したら、右側の [クエリエディタ] タブで次のステートメントを入力し、1 つずつ実行します。

CREATE EXTENSION IF NOT EXISTS  bigquery_fdw;

CREATE SERVER bigquery_server FOREIGN DATA WRAPPER bigquery_fdw;

CREATE USER MAPPING FOR postgres SERVER bigquery_server;

7. 正常に完了したら、左側のエクスプローラ ペインに移動し、BigQuery テーブルまで下にスクロールします。

8. 3 つのドットをクリックし、[BigQuery テーブルを接続する] をクリックします。
9. 開いた [Connect BigQuery Table] ポップアップで、AlloyDB データベースのデータのクエリに使用する project_id と BigQuery データセット名（パート 1 で作成）を選択します。

10. [各テーブルを 1 つずつ選択] を選択して、AlloyDB に接続されているすべてのデータを取得します。これは、AlloyDB でサポートされていることを確認するために列の型を検証するためです。

ポイント アンド クリックではなく、SQL で同じことを行う場合は、次の操作を行います。

CREATE FOREIGN TABLE <<TABLE_NAME>> (
      "cas_number" VARCHAR, "ingredient_name" VARCHAR, "max_moisture_percentage" DOUBLE PRECISION, "ph_range" VARCHAR, "purity_percentage" DOUBLE PRECISION, "shelf_life_months" BIGINT, "specific_gravity_range" VARCHAR
    ) SERVER "bigquery_server" OPTIONS (
      project '<<PROJECT_ID>>',
      dataset 'froyo_data',
      table '<<BQ_TABLE_NAME>>'
    );

魔法の力

AlloyDB に「外部テーブル」を作成しました。これらは通常の PostgreSQL テーブルのように見えますが、データを保存しません。クエリを実行すると、AlloyDB はクエリを BigQuery に即座に渡し、結果を取得して返します。

7. AlloyDB で連携をテストする

トランザクション PostgreSQL データベースから大規模な分析用 BigQuery データセットに直接クエリを実行できることを確認しましょう。

AlloyDB Studio で、クエリを実行して「Midnight Swirl」に含まれるアレルゲンを調べます（パート 1 で質問した内容と同じですが、今回は AlloyDB から質問します）。

SELECT
    p.product_name,
    i.ingredient_name,
    a.allergen_name
FROM
    consistsof c
INNER JOIN product p
    ON c.product_id = p.product_id
INNER JOIN ingredient i
    ON c.ingredient_id = i.ingredient_name
LEFT OUTER JOIN containsallergen a
    ON i.ingredient_id = a.ingredient_id
WHERE
    UPPER(p.product_name) LIKE '%MIDNIGHT%SWIRL%'
    AND a.allergen_name IS NOT NULL;

完了。BigQuery で取得した結果とまったく同じ結果が表示されます。

8. クリーンアップ

このラボが完了したら、AlloyDB クラスタとインスタンスを削除することを忘れないでください。

https://console.cloud.google.com/alloydb/clusters に移動します。削除するクラスタを選択するには、クラスタの横にある縦の省略記号をクリックして [削除] をクリックします。

クラスタとそのインスタンスをクリーンアップする必要があります。

9. Unified Data Layer の取得おめでとうございます

達成したことを振り返ってみましょう。

1. Google のトランザクション アプリ（AlloyDB で実行）は、高速で同時ユーザー セッションを処理できます。
2. 分析データや履歴コンテキスト（サプライヤーの詳細や複雑な成分マッピングなど）が必要な場合は、BigQuery froyo_dataschema をクエリします。
3. ゼロ ETL。データ パイプラインが中断されない。同期されていないデータベースはありません。一度保存（BQ）し、必要な場所で計算します。

分析データとトランザクション データの両方の基盤が強固になり、相互接続されたので、楽しい部分に取り掛かりましょう。

パート 3 では、このアーキテクチャの上に構築されたマルチエージェント アプリケーションを構築して、Froyo のビジネス オペレーションを実行します。