Как создавать приложения для рекомендаций Customer 360 с помощью BigQuery Graph

1. Введение

В этой практической работе вы узнаете, как с помощью BigQuery Graph создать представление Customer 360 и систему рекомендаций для вымышленной розничной компании Cymbal Pets. Вы будете использовать возможности SQL для создания, запроса и анализа данных графа непосредственно в BigQuery, комбинируя их с векторным поиском для расширенных рекомендаций продуктов.

BigQuery Graph позволяет моделировать отношения между сущностями данных (например, клиентами, товарами и заказами) в виде графа, что упрощает поиск ответов на сложные вопросы о поведении клиентов и сходстве товаров.

Что вам предстоит сделать

• Создайте набор данных и схему BigQuery для графа Cymbal Pets
• Загрузить примеры данных (клиенты, товары, заказы, магазины) из Cloud Storage
• Создайте граф свойств в BigQuery, соединяющий эти объекты
• Визуализация истории покупок клиентов с помощью запросов к графу
• Создайте систему рекомендаций товаров с помощью векторного поиска.
• Улучшение рекомендаций с помощью графа "Куплено вместе" и коэффициента Жаккара

Что вам понадобится

• Браузер, например Chrome.
• Проект Google Cloud с включенными платежными функциями.

Эта практическая работа предназначена для разработчиков любого уровня, включая начинающих.

2. Подготовка

Как создать проект Google Cloud

1. В Google Cloud Console выберите или создайте проект Google Cloud.
2. Убедитесь, что для облачного проекта включена оплата.

Запустить Cloud Shell

1. В верхней части консоли Google Cloud нажмите Activate Cloud Shell (Активировать 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. В этой практической работе мы будем выполнять команды SQL. Вы можете выполнить эти команды в BigQuery Studio > SQL Editor или использовать команду bq query в Cloud Shell. Мы предполагаем, что вы используете редактор SQL в BigQuery, поскольку в нем удобнее работать с многострочными операторами создания.
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 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
);

Вы определили структуру данных графа.

4. Загрузка данных

Теперь заполните таблицы образцами данных из Cloud Storage.

Выполните следующие операторы LOAD DATA в редакторе SQL BigQuery:

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
);

Вы должны увидеть подтверждение того, что строки загружены в каждую таблицу.

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
);

Это создает граф PetsOrderGraph, который позволяет нам выполнять обход графа с помощью оператора GRAPH_TABLE.

6. Визуализация истории покупок всех клиентов

Для визуализации и рекомендаций в этой практической работе мы будем использовать нативную визуализацию графов в BigQuery Studio. Это позволяет нам легко визуализировать результаты графика.

Вы также можете визуализировать данные в BigQuery Graph Notebook с помощью IPython Magics. Добавив магическую команду %%bigquery с функцией TO_JSON, вы можете визуализировать результаты, как показано в следующих разделах.

Допустим, компания Cymbal Pets хочет получить полную визуализацию всех клиентов и их покупок за определенный период времени.

Выполните следующую команду на новой вкладке 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 позволяет сопоставлять шаблоны в нескольких таблицах без сложного синтаксиса JOIN.

Чтобы визуализировать результаты, на панели "Результаты запроса" нажмите "График".

7. Визуализировать историю покупок Анжелики

Допустим, Cymbal Pets хочет получить подробную информацию о клиенте по имени Анжелика Рассел. Они хотят проанализировать товары, которые Анжелика купила за последние три месяца, и магазины, которые она посетила.

Хотя в этом конкретном сценарии можно было бы использовать традиционное реляционное объединение, мы используем 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 хочет рекомендовать товары Анжелике на основе ее недавних покупок. Мы можем использовать векторный поиск, чтобы найти товары с эмбеддингами, похожими на эмбеддинги ее прошлых покупок.

Запустите следующий SQL-скрипт в новой ячейке Colab. Этот скрипт:

1. Определяет товары, которые Анжелика недавно купила.
2. Использует VECTOR_SEARCH, чтобы найти четыре наиболее похожих товара из таблицы products.

Примечание. Предполагается, что вы уже выполнили функцию AI.GENERATE_EMBEDDINGS, чтобы создать столбец эмбеддингов в таблице товаров.

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;

Вы должны увидеть список рекомендуемых товаров, которые семантически похожи на то, что купила Анжелика.

9. Рекомендации на основе данных о товарах, которые покупают вместе, и коэффициента Жаккара

Ещё один эффективный метод – коллаборативная фильтрация, то есть рекомендация товаров, которые другие пользователи часто покупают вместе.

Чтобы найти эти товары, мы можем пройти по графу от клиента к купленным им товарам, затем к другим клиентам, которые купили эти товары, и, наконец, к другим товарам, которые купили эти клиенты.

Как преодолеть ошибку популярности с помощью коэффициента Жаккара

Необработанные данные о совместных покупках полезны, но могут быть смещены в сторону популярных товаров. Очень популярный товар может быть куплен со многими вещами просто случайно.

Коэффициент Жаккара позволяет улучшить рекомендации за счет нормализации количества совместных покупок. Он измеряет сходство между двумя наборами (в данном случае наборами заказов, содержащих каждый товар).

Формула для коэффициента Жаккара:

Где:

• A intersect B – количество заказов, содержащих и товар A, и товар B (количество совместных покупок).
• A – общее количество заказов, содержащих товар A.
• B – общее количество заказов, содержащих товар B.

В следующем примере набор A = {b,c,e,f,g}, набор B = {a,d,b,g}, их пересечение A⋂B = {b,g}, их объединение A⋃B = {a,b,c,d,e,f,g}, следовательно, сходство Жаккара между A и B равно 2 / 7 = 0,285714.

Создание и пересортировка кандидатов

В реальных рекомендательных системах, работающих с огромными наборами данных, часто нецелесообразно рассчитывать сложные показатели сходства (например, Жаккара) для всех возможных пар продуктов. Вместо этого обычно используется двухэтапный подход:

1. Генерация кандидатов. Используйте простой и быстрый показатель (например, количество совместных покупок), чтобы отфильтровать пространство поиска и найти управляемое количество кандидатов (например, 10 лучших).
2. Повторное ранжирование. Примените более точный, но требующий больших вычислительных ресурсов показатель (например, коэффициент Жаккара), чтобы ранжировать небольшой набор кандидатов и выбрать окончательные рекомендации.

В этой практической работе мы будем следовать следующему шаблону:

• Этап 1. Выполните запрос, чтобы найти 10 товаров, которые чаще всего покупают вместе с каждым товаром, на основе необработанного количества совместных покупок и сохраните их в таблице.
• Этап 2. Используйте запрос к графу, чтобы получить этих кандидатов, ранжируйте их по коэффициенту Жаккара и верните три лучших.

[!WARNING] Недостаток. Фильтрация по необработанному количеству на этапе 1 может привести к потере "полноты" очень специфических, но редких совместных покупок. Если товар очень похож на другой, но оба они редко покупаются, он может не попасть в топ-10 кандидатов и будет пропущен.

Выполните следующий запрос, чтобы рассчитать как необработанное количество совместных покупок, так и сходство Жаккара, и сохраните 10 лучших кандидатов по необработанному количеству:

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;

Выполните этот запрос, чтобы порекомендовать три лучших товара для каждой покупки Анжелики, напрямую связанной через ребро BoughtTogether, показывая как количество совместных покупок, так и сходство Жаккара:

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;

Этот запрос проходит по цепочке "Клиент -> Заказ -> Товар -> (Куплено вместе) -> Рекомендуемый товар", показывая рекомендации на основе коллективного покупательского поведения и извлекая оценки сходства.

10. Организовать

Чтобы избежать дальнейших списаний средств с вашего аккаунта Google Cloud, удалите ресурсы, созданные в ходе этой практической работы.

Удалите набор данных и все таблицы:

DROP SCHEMA IF EXISTS cymbal_pets_demo CASCADE;

Если вы создали новый проект для этой практической работы, вы также можете удалить его:

gcloud projects delete $PROJECT_ID

11. Поздравляю!

Поздравляем! Вы успешно создали представление Customer 360 и систему рекомендаций с помощью BigQuery Graph.

Что вы узнали

• Как создать граф свойств в BigQuery.
• Как загружать данные в узлы и ребра графа.
• Как запрашивать шаблоны графов с помощью GRAPH_TABLE и MATCH.
• Как объединить запросы к графу с векторным поиском для гибридных рекомендаций.

Дальнейшие действия

• Документация по BigQuery Graph
• Подробнее о векторном поиске в BigQuery…