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

1. 概要

In パート 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 用 Lakehouse Federation を使用すると、外部データラッパーとして実装された AlloyDB の連携クエリサポートを利用して、AlloyDB の SQL インターフェースを通じて BigQuery と AlloyDB のデータにアクセスできます。

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

構築を開始しましょう。

1a3f48f6d70b16ad.png

学習内容

  • ボタンをクリックして AlloyDB クラスタ、インスタンス、ネットワークを設定する方法
  • 連携の準備として拡張機能を設定する方法
  • BigQuery から AlloyDB への連携を設定する方法
  • テストする

要件

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

2. 始める前に

プロジェクトを作成する

  1. Google Cloud コンソールのプロジェクト選択ページで、Google Cloud プロジェクトを選択または作成します。
  2. Cloud プロジェクトに対して課金が有効になっていることを確認します。プロジェクトで課金が有効になっているかどうかを確認する方法をご確認ください。
  1. Google Cloud 上で動作するコマンドライン環境の Cloud Shell を使用します。Google Cloud コンソールの上部にある「Cloud Shell をアクティブにする」アイコン をクリックします。

[Cloud Shell をアクティブにする] ボタンの画像

  1. Cloud Shell に接続したら、次のコマンドを使用して、すでに認証済みであることと、プロジェクトがプロジェクト ID に設定されていることを確認します。
gcloud auth list
  1. Cloud Shell で次のコマンドを実行して、gcloud コマンドがプロジェクトを認識していることを確認します。
gcloud config list project
  1. 認証する場合は
gcloud auth login
  1. プロジェクトが設定されていない場合は、次のコマンドを使用して設定します。
export PROJECT_ID=<YOUR_PROJECT_ID>
gcloud config set project <YOUR_PROJECT_ID>
  1. 必要な API を有効にします。次のコマンドを実行して、必要な API をすべて有効にします。
gcloud services enable alloydb.googleapis.com

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

「ゴースト プロジェクト」 シンドローム

gcloud config set project を実行しましたが、コンソール UI に別のプロジェクトが表示されています。左上のプルダウンでプロジェクト ID を確認してください。

**請求** の障壁

プロジェクトを有効にしましたが、請求先アカウントを忘れていました。AlloyDB は高性能エンジンです。「ガソリン タンク」（請求）が空の場合、起動しません。

API の伝播 の遅延

[API を有効にする] をクリックしましたが、コマンドラインに Service Not Enabled と表示されます。60 秒ほど待ちます。クラウドがニューロンを起動するまでに時間がかかります。

割り当て の問題

新しいトライアル アカウントを使用している場合は、AlloyDB インスタンスのリージョン割り当てに達している可能性があります。us-central1 が失敗した場合は、us-east1 を試してください。

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

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

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

91567e2f55467574.png

次に、以下の請求先アカウントがない場合のセクションの手順に沿って操作します。

BigQuery にデータが用意できたので、次のステップに進みましょう。

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

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

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

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

7cd9d04e06c826d8.png

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 コンソール）で、[AlloyDB Studio] をクリックします。

1dd78902dc2b4f39.png

  1. AlloyDB のクイック設定の手順で構成したデータベース、ユーザー名、パスワードで接続します。
  2. 接続したら、右側の [クエリエディタ] タブに次のステートメントを入力し、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;
  1. 完了したら、左側のエクスプローラ ペインに移動し、[BigQuery テーブル] までスクロールします。

efe4f8be930824aa.png

  1. 3 つのドットをクリックし、[BigQuery テーブルを接続] をクリックします。
  2. 表示された [BigQuery テーブルを接続] ポップアップで、project_id と BigQuery データセット名（パート 1 で作成）を選択します。ここから AlloyDB データベースのデータにクエリを実行します。

1cc345197b0fbddf.png

  1. 各テーブルを 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 で表示されたものとまったく同じ結果が表示されます。

7d0be18295c5dfbd.png

8. クリーンアップ

このラボが完了したら、AlloyDB クラスタとインスタンスを削除してください。

クラスタとそのインスタンスがクリーンアップされます。

9. 統合データレイヤの完成

ここで、達成したことを考えてみましょう。

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

分析とトランザクションの両方のデータ基盤が堅牢で相互接続されたので、楽しい部分に進みましょう。

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