BigQuery Graph を使用して顧客 360 レコメンデーション アプリケーションを構築する

1. はじめに

この Codelab では、BigQuery Graph を使用して、架空の小売企業である Cymbal Pets の顧客 360 度ビューとレコメンデーション エンジンを構築する方法を学びます。SQL の機能を利用して、BigQuery 内でグラフデータを作成、クエリ、分析し、ベクトル検索と組み合わせて高度な商品レコメンデーションを実現します。

BigQuery Graph を使用すると、データ エンティティ（顧客、商品、注文など）間の関係をグラフとしてモデル化できるため、顧客の行動や商品の関連性に関する複雑な質問に簡単に答えることができます。

演習内容

• Cymbal Pets グラフの BigQuery データセットとスキーマを作成する
• Cloud Storage からサンプルデータ（顧客、商品、注文、店舗）を読み込む
• これらのエンティティを接続するプロパティ グラフを BigQuery に作成する
• グラフクエリを使用して顧客の購入履歴を可視化する
• ベクトル検索 を使用して商品レコメンデーション システムを構築する
• 「一緒に購入された商品」のグラフ関係を使用してレコメンデーションを強化する

必要なもの

• ウェブブラウザ（Chrome など）
• 課金を有効にした Google Cloud プロジェクト

この Codelab は、初心者を含むあらゆるレベルのデベロッパーを対象としています。

2. 始める前に

Google Cloud プロジェクトの作成

1. Google Cloud コンソールで、Google Cloud プロジェクトを作成または選択します。
2. Cloud プロジェクトで課金が有効になっていることを確認します。

Cloud Shell の起動

1. Google Cloud コンソールの上部にある「Cloud Shell をアクティブにする 」アイコン をクリックします。
2. 認証を検証する。

gcloud auth list

3. プロジェクトを確認する。

gcloud config get project

4. 必要に応じて設定する。

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

API を有効にする

次のコマンドを実行して、必要な BigQuery API を有効にします。

gcloud services enable bigquery.googleapis.com

3. スキーマを定義する

まず、グラフ関連のテーブルを保存するデータセットを作成し、ノードとエッジのスキーマを定義する必要があります。

1. この Codelab では、SQL コマンドを実行します。これらのコマンドは、[BigQuery Studio] > [SQL エディタ] で実行するか、Cloud Shell で bq query コマンドを使用します。複数行の作成ステートメントをより快適に使用できるように、[BigQuery SQL エディタ] を使用することを前提としています。
2. cymbal_pets_demo データセットを作成します。

CREATE SCHEMA IF NOT EXISTS cymbal_pets_demo;

3. order_items、products、orders、stores、customers、co_related_products_for_angelica のテーブルを作成します。これらのテーブルは、グラフのソースデータとして使用されます。

CREATE TABLE IF NOT EXISTS cymbal_pets_demo.order_items
(
  order_id INT64,
  product_id INT64,
  order_item_id INT64,
  quantity INT64,
  price FLOAT64,
  PRIMARY KEY (order_id, product_id, order_item_id) NOT ENFORCED
)
CLUSTER BY order_item_id;

CREATE TABLE IF NOT EXISTS cymbal_pets_demo.products
(
  product_id INT64,
  product_name STRING,
  brand STRING,
  category STRING,
  subcategory INT64,
  animal_type INT64,
  search_keywords INT64,
  price FLOAT64,
  description STRING,
  inventory_level INT64,
  supplier_id INT64,
  average_rating FLOAT64,
  uri STRING,
  embedding ARRAY<FLOAT64>,
  PRIMARY KEY (product_id) NOT ENFORCED
)
CLUSTER BY product_id;

CREATE TABLE IF NOT EXISTS cymbal_pets_demo.orders
(
  customer_id INT64,
  order_id INT64,
  shipping_address_city STRING,
  store_id INT64,
  order_date DATE,
  order_type STRING,
  payment_method STRING,
  PRIMARY KEY (order_id) NOT ENFORCED
)
PARTITION BY order_date
CLUSTER BY order_id;

CREATE TABLE IF NOT EXISTS cymbal_pets_demo.stores
(
  store_id INT64,
  store_name STRING,
  address_state STRING,
  address_city STRING,
  latitude FLOAT64,
  longitude FLOAT64,
  opening_hours STRUCT<Monday STRING, Tuesday STRING, Wednesday STRING, Thursday STRING, Friday STRING, Saturday STRING, Sunday STRING>,
  manager_id INT64,
  PRIMARY KEY (store_id) NOT ENFORCED
)
CLUSTER BY store_id;

CREATE TABLE IF NOT EXISTS cymbal_pets_demo.customers
(
  customer_id INT64,
  first_name STRING,
  last_name STRING,
  email STRING,
  gender STRING,
  address_city STRING,
  address_state STRING,
  loyalty_member BOOL,
  PRIMARY KEY (customer_id) NOT ENFORCED
)
CLUSTER BY customer_id;

CREATE TABLE IF NOT EXISTS cymbal_pets_demo.co_related_products_for_angelica
(
  angelica_product_id INT64,
  other_product_id INT64,
  co_purchase_count INT64
);

これで、グラフデータの構造が定義されました。

4. データを読み込む

次に、Cloud Storage からのサンプルデータをテーブルに入力します。

BigQuery SQL エディタで次の LOAD DATA ステートメントを実行します。

LOAD DATA INTO `cymbal_pets_demo.customers`
FROM FILES (
    format = 'AVRO',
    uris = ['gs://sample-data-and-media/cymbal-pets/tables/customers/*.avro']
);

LOAD DATA INTO `cymbal_pets_demo.order_items`
FROM FILES (
    format = 'AVRO',
    uris = ['gs://sample-data-and-media/cymbal-pets/tables/order_items/*.avro']
);

LOAD DATA INTO `cymbal_pets_demo.orders`
FROM FILES (
    format = 'AVRO',
    uris = ['gs://sample-data-and-media/cymbal-pets/tables/orders/*.avro']
);

LOAD DATA INTO `cymbal_pets_demo.products`
FROM FILES (
    format = 'AVRO',
    uris = ['gs://sample-data-and-media/cymbal-pets/tables/products/*.avro']
);

LOAD DATA INTO `cymbal_pets_demo.stores`
FROM FILES (
    format = 'AVRO',
    uris = ['gs://sample-data-and-media/cymbal-pets/tables/stores/*.avro']
);

各テーブルに行が読み込まれたことを示す確認メッセージが表示されます。

5. プロパティ グラフを作成する

データが読み込まれたので、プロパティ グラフ を定義できます。これにより、BigQuery はどのテーブルがノード （顧客、商品などのエンティティ）を表し、どのテーブルがエッジ （「訪問した」、「注文した」、「持っている」などの関係）を表すかを認識できます。

次の DDL ステートメントを実行します。

CREATE OR REPLACE PROPERTY GRAPH cymbal_pets_demo.PetsOrderGraph
NODE TABLES (
  cymbal_pets_demo.customers KEY(customer_id) LABEL Customer,
  cymbal_pets_demo.products KEY(product_id) LABEL Products,
  cymbal_pets_demo.stores KEY(store_id) LABEL Stores,
  cymbal_pets_demo.orders KEY(order_id) LABEL Orders
)
EDGE TABLES (
  cymbal_pets_demo.orders as customer_to_store_edge
    KEY (order_id)
    SOURCE KEY (customer_id) references customers(customer_id)
    DESTINATION KEY (store_id) references stores(store_id)
    LABEL Visited
    PROPERTIES ALL COLUMNS,

  cymbal_pets_demo.order_items
    KEY (order_item_id)
    SOURCE KEY (order_id) references orders(order_id)
    DESTINATION KEY (product_id) references products(product_id)
    LABEL Has
    PROPERTIES ALL COLUMNS,

  cymbal_pets_demo.orders as customer_to_orders_edge
    KEY (order_id)
    SOURCE KEY (customer_id) references customers(customer_id)
    DESTINATION KEY (order_id) references orders(order_id)
    LABEL Placed
    PROPERTIES ALL COLUMNS,

  cymbal_pets_demo.co_related_products_for_angelica
    KEY (angelica_product_id)
    SOURCE KEY (angelica_product_id) references products(product_id)
    DESTINATION KEY (other_product_id) references products(product_id)
    LABEL BoughtTogether
    PROPERTIES ALL COLUMNS
);

これにより、GRAPH_TABLE 演算子を使用してグラフのトラバーサルを実行できるグラフ PetsOrderGraph が作成されます。

6. すべての顧客の購入履歴を可視化する

BigQuery Studio で [新しいノートブック] を開きます。

この Codelab の可視化とレコメンデーションの部分では、BigQuery Studio の Google Colab ノートブックを使用します。これにより、グラフの結果を簡単に可視化できます。

BigQuery Graph Notebook は IPython Magics として実装されています。TO_JSON 関数を使用して %%bigquery マジック コマンドを追加すると、次のセクションに示すように結果を可視化できます。

Cymbal Pets が、特定の期間にすべての顧客とその購入履歴を 360 度で可視化したいと考えているとします。

新しいセルで次を実行します。

%%bigquery --graph

GRAPH cymbal_pets_demo.PetsOrderGraph
  # finds the customer node and then finds all
  # the Orders nodes that are connected to that customer through the
  # Placed relationship
  MATCH (customer:Customer)-[placed:Placed]->(ordr:Orders)-[has:Has]->(product:Products)
  # filters the Orders nodes to only include those where the
  # order_date is within the last 3 months.
  WHERE ordr.order_date >= date('2024-11-27')
  # # This line finds all the Products nodes that are connected to the
  # # filtered Orders nodes through the Has relationship.
  MATCH p=(customer:Customer)-[placed:Placed]->(ordr:Orders)-[has:Has]->(product:Products)
  LIMIT 40
  RETURN 
    TO_JSON(p) as paths

BigQuery Graph を使用すると、複雑な JOIN 構文を使用せずに、複数のテーブルにわたってパターンを照合できます。

グラフの結果が視覚的に表示されます。

7. Angelica の購入履歴を可視化する

Cymbal Pets が Angelica Russell という顧客について詳しく調べたいと考えているとします。過去 3 か月以内に Angelica が購入した商品と、顧客が訪問した店舗を分析したいと考えています。

この特定のシナリオは、従来のリレーショナル結合を使用して実現できますが、ここでは BigQuery Graph を使用して、複雑な関係分析を可視化する基本的なグラフのコンセプトと構文を示します。

%%bigquery --graph

GRAPH cymbal_pets_demo.PetsOrderGraph
  # finds the customer node with the name "Angelica Russell" and then finds all
  # the Orders nodes that are connected to that customer through the
  # Placed relationship and all the Products nodes that are connected to the
  # filtered Orders nodes through the Has relationship.
   MATCH p=(customer:Customer {first_name: 'Angelica', last_name: 'Russell'})-[placed:Placed]->(ordr:Orders)-[has:Has]->(product:Products)
  # filters the Orders nodes to only include those where the
  # order_date is within the last 3 months.
  WHERE ordr.order_date >= date('2024-11-27')
  # finds the Stores nodes where Angelica placed order from
  MATCH p2=(customer)-[visited:Visited]->(store:Stores)
  RETURN
    TO_JSON(p) as path, TO_JSON(p2) as path2

8. ベクトル検索を使用した商品レコメンデーション

Cymbal Pets は、Angelica が最近購入した商品に基づいて、Angelica におすすめの商品を提示したいと考えています。ベクトル検索 を使用して、過去の購入履歴とエンベディングが類似している商品を見つけることができます。

新しい Colab セルで次の SQL スクリプトを実行します。このスクリプトは次の処理を行います。

1. Angelica が最近購入した商品を特定します。
2. VECTOR_SEARCH を使用して、products テーブルから上位 4 つの類似商品を検索します。

注: このステップでは、AI.GENERATE_EMBEDDINGS を実行して、商品テーブルにエンベディング列を作成していることを前提としています。

%%bigquery
DECLARE products_bought_by_angelica ARRAY<INT64>;

-- 1. Get IDs of products bought by Angelica
SET products_bought_by_angelica = (
  SELECT ARRAY_AGG(product_id) FROM
   GRAPH_TABLE(
    cymbal_pets_demo.PetsOrderGraph
      MATCH (c:Customer {first_name: 'Angelica', last_name: 'Russell'})-[placed:Placed]->(o:Orders)
      WHERE o.order_date >= date('2024-11-27')
      MATCH (o)-[has_edge:Has]->(p:Products)
      RETURN DISTINCT p.product_id as product_id
  ));

-- 2. Find similar products using vector search
SELECT 
  query.product_name as AngelicaBought, 
  base.product_name as RecommendedProducts, 
  base.category
FROM
  VECTOR_SEARCH(
    TABLE cymbal_pets_demo.products,
    'embedding',
    (SELECT * FROM cymbal_pets_demo.products
     WHERE product_id IN UNNEST(products_bought_by_angelica)),
    'embedding',
    top_k => 4)
WHERE query.product_name <> base.product_name;

Angelica が購入した商品と意味的に類似したおすすめ商品のリストが表示されます。

9. 「一緒に購入された商品」の関係を使用したレコメンデーション

もう 1 つの強力なレコメンデーション手法は「協調フィルタリング」です。これは、他のユーザーが一緒に購入することが多い商品を推奨するものです。これは、グラフで BoughtTogether エッジとしてモデル化されています。

一緒に購入された商品を推奨するために、Cymbal Pets は分析オフライン グラフクエリを実行して、Angelica が購入した各商品におすすめの上位商品を検索しました。

%%bigquery
CREATE OR REPLACE TABLE cymbal_pets_demo.co_related_products_for_angelica AS
SELECT
    angelica_product_id,
    other_product_id,
    co_purchase_count
FROM (
    SELECT
        angelicaProduct.product_id AS angelica_product_id,
        otherProduct.product_id AS other_product_id,
        count(otherProduct) AS co_purchase_count,
        # ensures that the row numbering is done separately for each angelica_product_id
        ROW_NUMBER() OVER (PARTITION BY angelicaProduct.product_id ORDER BY count(otherProduct) DESC) AS rn
    FROM
        GRAPH_TABLE (cymbal_pets_demo.PetsOrderGraph
          MATCH (angelica:Customer {first_name: 'Angelica', last_name: 'Russell'})-[:Placed]->(o:Orders)-[:Has]->(angelicaProduct:Products)
          WHERE o.order_date >= date('2024-11-27')
          WITH angelica, angelicaProduct
          MATCH (otherCustomer:Customer)-[:Placed]->(otherOrder:Orders)-[:Has]->(angelicaProduct) # Find orders where Angelica's products were bought
          WHERE otherCustomer <> angelica # Exclude Angelica's own orders
          WITH angelicaProduct, otherOrder
          MATCH (otherOrder)-[:HAS]->(otherProduct:Products) # Find other products in those orders
          WHERE angelicaProduct <> otherProduct # Exclude the original product.
          RETURN angelicaProduct, otherProduct, otherOrder
        )
    GROUP BY
        angelicaProduct.product_id, otherProduct.product_id
)
WHERE rn <= 3; # only keep top 3 co-related products

このクエリを実行して、BoughtTogether エッジを介して Angelica の購入に直接接続されている商品を推奨します。

%%bigquery
SELECT * FROM GRAPH_TABLE(
  cymbal_pets_demo.PetsOrderGraph
  MATCH (customer:Customer {first_name: 'Angelica', last_name: 'Russell'})-[placed:Placed]->(ordr:Orders)
  WHERE ordr.order_date >= date('2024-11-27')
  MATCH (ordr)-[has:Has]->(product:Products)
  MATCH (product)-[bought_together:BoughtTogether]->(recommended_product:Products)
  RETURN 
    product.product_name AS OriginalProduct,
    recommended_product.product_name AS Recommended,
    bought_together.co_purchase_count AS Strength
);

このクエリは、顧客 -> 注文 -> 商品 ->（BoughtTogether）-> おすすめ商品に移動し、集団購入行動に基づいてレコメンデーションを表示します。

10. クリーンアップ

Google Cloud アカウントに継続的に課金されないようにするには、この Codelab で作成したリソースを削除します。

データセットとすべてのテーブルを削除します。

DROP SCHEMA IF EXISTS cymbal_pets_demo CASCADE;

この Codelab 用に新しいプロジェクトを作成した場合は、そのプロジェクトを削除することもできます。

gcloud projects delete $PROJECT_ID

11. 完了

おめでとうございます！BigQuery Graph を使用して、顧客 360 度ビューとレコメンデーション エンジンを構築できました。

学習した内容

• BigQuery でプロパティ グラフを作成する方法。
• グラフのノードとエッジにデータを読み込む方法。
• GRAPH_TABLE と MATCH を使用してグラフパターンをクエリする方法。
• グラフクエリとベクトル検索を組み合わせてハイブリッド レコメンデーションを行う方法。

次のステップ

• BigQuery Graph のドキュメントを確認する。
• BigQuery のベクトル検索の詳細を確認する。