Tworzenie aplikacji rekomendacyjnych Customer 360 za pomocą BigQuery Graph

1. Wprowadzenie

Z tego ćwiczenia dowiesz się, jak za pomocą BigQuery Graph utworzyć widok klienta 360 i system rekomendacji dla fikcyjnej firmy handlowej Cymbal Pets. Wykorzystasz możliwości SQL do tworzenia, wysyłania zapytań i analizowania danych grafu bezpośrednio w BigQuery, łącząc je z wyszukiwaniem wektorowym w celu uzyskania zaawansowanych rekomendacji produktów.

BigQuery Graph umożliwia modelowanie relacji między elementami danych (takimi jak klienci, produkty i zamówienia) w postaci grafu, co ułatwia odpowiadanie na złożone pytania dotyczące zachowań klientów i powiązań między produktami.

Jakie zadania wykonasz

• Tworzenie zbioru danych i schematu BigQuery dla grafu Cymbal Pets
• Wczytywanie przykładowych danych (klienci, produkty, zamówienia, sklepy) z Cloud Storage
• Utwórz w BigQuery graf właściwości łączący te encje.
• Wizualizacja historii zakupów klientów za pomocą zapytań grafu
• tworzenie systemu rekomendacji produktów za pomocą wyszukiwania wektorowego,
• Ulepszanie rekomendacji za pomocą relacji grafu „Kupione razem” i podobieństwa Jaccarda

Czego potrzebujesz

• przeglądarkę internetową, np. Chrome;
• projekt Google Cloud z włączonymi rozliczeniami,

To ćwiczenie jest przeznaczone dla deweloperów na wszystkich poziomach zaawansowania, w tym dla początkujących.

2. Zanim zaczniesz

Utwórz projekt Google Cloud

1. W konsoli Google Cloud wybierz lub utwórz projekt w chmurze Google Cloud.
2. Sprawdź, czy w projekcie Cloud włączone są płatności.

Uruchamianie Cloud Shell

1. Kliknij Aktywuj Cloud Shell na górze konsoli Google Cloud.
2. Weryfikacja uwierzytelniania:

gcloud auth list

3. Potwierdź projekt:

gcloud config get project

4. W razie potrzeby ustaw:

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

Włącz interfejsy API

Uruchom to polecenie, aby włączyć wymagany interfejs BigQuery API:

gcloud services enable bigquery.googleapis.com

3. Definiowanie schematu

Najpierw musisz utworzyć zbiór danych do przechowywania tabel związanych z grafem i zdefiniować schemat węzłów i krawędzi.

1. W tym ćwiczeniu z programowania będziemy wykonywać polecenia SQL. Możesz uruchomić te polecenia w BigQuery Studio > Edytor SQL lub użyć polecenia bq query w Cloud Shell. Zakładamy, że używasz edytora SQL BigQuery, aby ułatwić sobie pracę z wielowierszowymi instrukcjami tworzenia.
2. Utwórz zbiór danych cymbal_pets_demo:

CREATE SCHEMA IF NOT EXISTS cymbal_pets_demo;

3. Utwórz tabele dla order_items, products, orders, stores, customers i co_related_products_for_angelica. Te tabele będą służyć jako dane źródłowe dla naszego grafu.

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 STRING,
  order_type STRING,
  payment_method STRING,
  PRIMARY KEY (order_id) NOT ENFORCED
);

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,
  jaccard_similarity FLOAT64
);

Struktura danych wykresu została zdefiniowana.

4. Wczytywanie danych

Teraz wypełnij tabele danymi próbkowanymi z Cloud Storage.

W edytorze SQL BigQuery uruchom te instrukcje LOAD DATA:

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

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

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

-- 1. Create a new partitioned and clustered table from your current data
CREATE TABLE cymbal_pets_demo.orders_temp
PARTITION BY order_date
CLUSTER BY order_id
AS
SELECT
  customer_id,
  order_id,
  shipping_address_city,
  store_id,
  -- Parse the string to a DATE type. Adjust format string ('%Y-%m-%d') if necessary.
  PARSE_DATE('%Y-%m-%d', order_date) AS order_date,
  order_type,
  payment_method
FROM
  cymbal_pets_demo.orders;

-- 2. Drop the original, non-partitioned table
DROP TABLE cymbal_pets_demo.orders;

-- 3. Rename the temporary table to the original table name
ALTER TABLE cymbal_pets_demo.orders_temp RENAME TO orders;

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

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

Powinno pojawić się potwierdzenie, że wiersze zostały wczytane do każdej tabeli.

5. Tworzenie grafu właściwości

Po wczytaniu danych możesz zdefiniować graf właściwości. Dzięki temu BigQuery wie, które tabele reprezentują węzły (elementy takie jak klienci czy produkty), a które krawędzie (relacje takie jak „odwiedził”, „złożył” czy „ma”).

Uruchom tę instrukcję 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
);

Spowoduje to utworzenie wykresu PetsOrderGraph, który umożliwia przechodzenie po wykresie za pomocą operatora GRAPH_TABLE.

6. Wizualizacja historii zakupów wszystkich klientów

W częściach tego ćwiczenia dotyczących wizualizacji i rekomendacji użyjemy natywnej wizualizacji grafu w BigQuery Studio. Dzięki temu możemy łatwo wizualizować wyniki wykresu.

Możesz też wizualizować dane w BigQuery Graph Notebook za pomocą IPython Magics. Dodając polecenie magiczne %%bigquery z funkcją TO_JSON, możesz wizualizować wyniki, jak pokazano w kolejnych sekcjach.

Załóżmy, że firma Cymbal Pets chce uzyskać pełną wizualizację wszystkich klientów i ich zakupów w określonym przedziale czasu.

Uruchom to polecenie w nowej karcie BigQuery Studio:

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 umożliwia dopasowywanie wzorców w wielu tabelach bez złożonej składni JOIN.

Aby zwizualizować wyniki, w panelu Wyniki zapytania kliknij Wykres.

7. Wizualizacja historii zakupów Angeliki

Załóżmy, że firma Cymbal Pets chce dowiedzieć się więcej o klientce o imieniu Angelica Russell. Chce przeanalizować produkty, które Angelica kupiła w ciągu ostatnich 3 miesięcy, oraz sklepy, które odwiedziła.

Chociaż ten konkretny scenariusz można zrealizować za pomocą tradycyjnego złączenia relacyjnego, używamy tutaj BigQuery Graph, aby zademonstrować podstawowe pojęcia i składnię grafu, które wizualizują złożone analizy relacji.

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. Rekomendacja produktu za pomocą wyszukiwania wektorowego

Firma Cymbal Pets chce polecać Angelice produkty na podstawie jej ostatnich zakupów. Możemy użyć wyszukiwania wektorowego, aby znaleźć produkty o podobnych wektorach dystrybucyjnych do jej wcześniejszych zakupów.

Uruchom ten skrypt SQL w nowej komórce Colab. Ten skrypt:

1. Identyfikuje produkty, które Angelica kupiła niedawno.
2. Używa VECTOR_SEARCH, aby znaleźć 4 najbardziej podobne produkty z tabeli products.

Uwaga: ten krok zakłada, że funkcja AI.GENERATE_EMBEDDINGS została już uruchomiona, aby utworzyć kolumnę wektorów dystrybucyjnych w tabeli produktów.

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;

Powinna wyświetlić się lista polecanych produktów, które są semantycznie podobne do tego, co kupiła Angelica.

9. Rekomendacja z wykorzystaniem funkcji „Kupowane razem” i podobieństwa Jaccarda

Inną skuteczną techniką rekomendacji jest „filtrowanie oparte na współpracy” – polecanie produktów, które są często kupowane razem przez innych użytkowników.

Możemy znaleźć te produkty, przechodząc przez wykres od klienta do zakupionych przez niego produktów, następnie do innych klientów, którzy kupili te produkty, i wreszcie do innych produktów, które kupili ci klienci.

Overcoming Popularity Bias with Jaccard Similarity

Chociaż nieprzetworzone liczby wspólnych zakupów są przydatne, mogą być stronnicze w stosunku do popularnych produktów. Bardzo popularny produkt może być kupowany z wieloma rzeczami przez przypadek.

Podobieństwo Jaccarda idzie o krok dalej, normalizując liczbę wspólnych zakupów. Mierzy podobieństwo między 2 zbiorami (w tym przypadku zbiorami zamówień zawierających dany produkt).

Wzór na podobieństwo Jaccarda:

Gdzie:

• A intersect B to liczba zamówień zawierających zarówno produkt A, jak i produkt B (liczba wspólnych zakupów).
• A to łączna liczba zamówień zawierających produkt A.
• B to łączna liczba zamówień zawierających produkt B.

W tym przykładzie zbiór A = {b, c,e,f,g}, zbiór B = {a,d,b,g}, ich część wspólna A⋂B = {b,g}, ich suma A⋃B = {a,b,c,d,e,f,g}, więc podobieństwo Jaccarda między zbiorami A i B wynosi 2 / 7 = 0,285714.

Generowanie i ponowne pozycjonowanie kandydatów

W systemach rekomendacji działających w świecie rzeczywistym na ogromnych zbiorach danych obliczanie złożonych wyników podobieństwa (takich jak Jaccard) dla wszystkich możliwych par produktów jest często niepraktyczne. Zamiast tego często stosuje się podejście dwuetapowe:

1. Generowanie kandydatów: użyj prostego, szybkiego wskaźnika (np.nieprzetworzonej liczby wspólnych zakupów), aby przefiltrować przestrzeń wyszukiwania i znaleźć możliwą do zarządzania liczbę kandydatów (np. 10 najlepszych).
2. Ponowne określanie rankingu: zastosuj bardziej precyzyjne, ale bardziej wymagające obliczeniowo dane (np. podobieństwo Jaccarda), aby określić ranking tego małego zbioru kandydatów i wybrać ostateczne najlepsze rekomendacje.

W tym ćwiczeniu będziemy postępować według tego wzorca:

• Etap 1. Uruchom zapytanie, aby znaleźć 10 najczęściej kupowanych razem produktów dla każdego produktu na podstawie nieprzetworzonej liczby zakupów i zapisz je w tabeli.
• Etap 2. Użyj zapytania grafowego, aby pobrać te kandydatury, uszeregować je według podobieństwa Jaccarda i zwrócić 3 najlepsze.

[!WARNING] Wada: filtrowanie według nieprzetworzonej liczby na etapie 1 może spowodować utratę „czułości” w przypadku bardzo konkretnych, ale rzadkich zakupów wspólnych. Jeśli produkt jest bardzo podobny do innego, ale oba są rzadko kupowane, może nie znaleźć się wśród 10 najlepszych kandydatów i zostanie pominięty.

Uruchom poniższe zapytanie, aby obliczyć zarówno nieprzetworzoną liczbę wspólnych zakupów, jak i podobieństwo Jaccarda, oraz zapisać 10 najlepszych kandydatów według nieprzetworzonej liczby:

CREATE OR REPLACE TABLE cymbal_pets_demo.co_related_products_for_angelica AS
-- Calculate the total number of orders for each product
WITH ProductOrderCounts AS (
    SELECT product_id, COUNT(DISTINCT order_id) as total_count
    FROM cymbal_pets_demo.order_items
    GROUP BY product_id
),
-- Calculate the intersection of each product pairs
CoPurchases AS (
    SELECT
        angelicaProduct.product_id AS angelica_product_id,
        otherProduct.product_id AS other_product_id,
        count(DISTINCT otherOrder.order_id) AS co_purchase_count
    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)
          WHERE otherCustomer <> angelica
          WITH angelicaProduct, otherOrder
          MATCH (otherOrder)-[:HAS]->(otherProduct:Products)
          WHERE angelicaProduct <> otherProduct
          RETURN angelicaProduct, otherProduct, otherOrder
        )
    GROUP BY
        angelicaProduct.product_id, otherProduct.product_id
)
SELECT * FROM (
    SELECT
        cp.angelica_product_id,
        cp.other_product_id,
        cp.co_purchase_count,
        -- The Jaccard calculation, which is the intersection of A and B divided by (A + B - intersection)
        SAFE_DIVIDE(cp.co_purchase_count, (poc1.total_count + poc2.total_count - cp.co_purchase_count)) AS jaccard_similarity,
        ROW_NUMBER() OVER (PARTITION BY cp.angelica_product_id ORDER BY cp.co_purchase_count DESC) AS rn
    FROM CoPurchases cp
    JOIN ProductOrderCounts poc1 ON cp.angelica_product_id = poc1.product_id
    JOIN ProductOrderCounts poc2 ON cp.other_product_id = poc2.product_id
)
WHERE rn <= 10;

Uruchom to zapytanie, aby polecić 3 najpopularniejsze produkty dla każdego zakupu Angeliki, połączone bezpośrednio za pomocą krawędzi BoughtTogether, pokazując zarówno liczbę wspólnych zakupów, jak i podobieństwo Jaccarda:

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,
    bought_together.jaccard_similarity AS JaccardSimilarity
)
-- Rank product recommendations by Jaccard Similarity
QUALIFY ROW_NUMBER() OVER (PARTITION BY OriginalProduct ORDER BY JaccardSimilarity DESC) <= 3
ORDER BY OriginalProduct;

To zapytanie przechodzi od klienta do zamówienia, produktu, (BoughtTogether) i polecanego produktu, wyświetlając rekomendacje na podstawie zbiorczych zachowań zakupowych i pobierając wyniki podobieństwa.

10. Czyszczenie danych

Aby uniknąć bieżących opłat na koncie Google Cloud, usuń zasoby utworzone podczas tego ćwiczenia.

Usuń zbiór danych i wszystkie tabele:

DROP SCHEMA IF EXISTS cymbal_pets_demo CASCADE;

Jeśli na potrzeby tego ćwiczenia został przez Ciebie utworzony nowy projekt, możesz go usunąć:

gcloud projects delete $PROJECT_ID

11. Gratulacje

Gratulacje! Udało Ci się utworzyć widok klienta 360 i system rekomendacji przy użyciu BigQuery Graph.

Czego się nauczysz

• Jak utworzyć graf właściwości w BigQuery.
• Jak wczytywać dane do węzłów i krawędzi grafu.
• Jak wysyłać zapytania o wzorce grafów za pomocą GRAPH_TABLE i MATCH.
• jak łączyć zapytania grafowe z wyszukiwaniem wektorowym na potrzeby rekomendacji hybrydowych.

Dalsze kroki

• Zapoznaj się z dokumentacją BigQuery Graph.
• Dowiedz się więcej o wyszukiwaniu wektorowym w BigQuery.