Начало работы с показателями графа BigQuery

1. Обзор

Большинство ошибок SQL, которые не были обнаружены при проверке кода, связаны с агрегированием. Число верно в таблице, в которой оно находится, но при объединении оно копируется, и итоговое значение оказывается неверным.

BigQuery Graph позволяет описать связи между таблицами в виде графа свойств и затем запрашивать эти связи. Показатели диаграммы добавляют к этому описанию ещё один элемент – агрегирование. Вы объявляете MEASURE(SUM(streams)) в узле, которому принадлежит число, и каждый запрос, который считывает граф, получает правильную сумму на нужном уровне детализации – без хитростей с GROUP BY и без CTE, которые нужно запоминать.

В этой практической работе вы создадите каталог музыки из пяти таблиц, добавите в него один показатель и увидите, что на один и тот же вопрос можно получить два разных ответа: 12,6 млрд с помощью SUM и 5,1 млрд с помощью AGG. Пробел – это форма каталога. Если трек добавлен в четыре плейлиста, то после преобразования графика в плоский вид он будет представлен четырьмя строками, и SUM добавит все четыре. Затем вы узнаете правила, которые делают второе предложение правильным.

Место, где вы будете вводить текст

В этом занятии используются два места в следующем порядке:

	где	что вы там делаете;
1	Терминал Cloud Shell	несколько команд только на этапе Настройка;
2	Консоль BigQuery	все остальное: вставка запросов в редактор запросов, переход по элементам моделирования графиков, общение с агентом.

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

• 👉💻 Cloud Shell – введите эту команду в терминале Cloud Shell.
• 👉📋 Редактор запросов – вставьте этот код в редактор запросов BigQuery и нажмите Выполнить.
• 👉🌐 Консоль – нажмите на элементы в консоли BigQuery. Ничего не нужно вводить, если в инструкции не указано иное.
• 👀 – посмотреть результат.

Что вы узнаете

• Как объявить показатель в таблице узла в визуальном редакторе графов или в SQL
• Как функция GRAPH_EXPAND преобразует диаграмму в одну широкую таблицу
• Почему AGG – единственная функция, которая может считывать показатель, и что происходит при попытке использовать другие функции
• Шесть агрегатов, которые поддерживает показатель, и как выполнять арифметические операции с ними
• Как один показатель отвечает на вопрос, сгруппированный по-другому, без изменений
• Необязательно: как GQL и вкладка "Граф" отвечают на вопрос, на который не может ответить сумма, и как агент данных BigQuery использует ваши показатели, не получая о них информации

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

• Аккаунт Google с доступными платежными функциями и браузер. Вам не нужен проект Google Cloud – он будет создан в процессе настройки.
• Для необязательного шага с чатом: включенный Gemini Data Analytics API и роль Gemini Data Analytics Data Agent Creator.

На компьютер ничего устанавливать не нужно. Все выполняется в Cloud Shell и консоли BigQuery.

Статус и стоимость товара

BigQuery Graph теперь общедоступен. Показатели диаграмм, визуальный редактор диаграмм, визуализация диаграмм и использование диаграммы в качестве источника знаний для агента данных доступны в предварительной версии, на которую распространяются Условия использования предварительных предложений.

GRAPH_EXPAND и AGG работают с ценообразованием по запросу. Для запросов GQL (необязательный шаг) требуется резервирование с использованием версии Enterprise или Enterprise Plus, согласно документации, поэтому этот шаг является необязательным.

Набор данных занимает несколько килобайт. При использовании ценообразования по запросу каждый запрос оплачивается по минимальной цене за 10 МБ, поэтому стоимость всего этого руководства составит лишь небольшую часть цента.

Данные вымышленные. Cymbal Records, Northwind Audio, исполнители, песни и плейлисты не существуют. Цифры специально округлены, чтобы вы могли проверить каждый результат в уме.

2. Настройка

Это единственный шаг, на котором используется Cloud Shell. Он создает проект, включает в нем оплату и создает исходные таблицы. После этого вы сможете создавать графики, показатели и запросы в консоли BigQuery.

Однако в этом лабораторном задании вам не будет предоставлен проект Google Cloud с включенной оплатой. На шаге 1 вы создадите аккаунт, с которого можно оплатить подписку, а на шаге 4 превратите этот аккаунт в проект. Выполняйте их в указанном порядке, поскольку каждый шаг после четвертого считывает проект, созданный на шаге 4.

1. Создайте аккаунт с платежными данными (~1 мин)

👉🌐 Сделайте это в первую очередь. Войдите в аккаунт Google с включенными платежными функциями. Подойдет любой аккаунт. Если у вас его нет, оформите бесплатную пробную подписку. В этом случае вы получите платежный аккаунт, который необходим для выполнения шага 4.

Для BigQuery по-прежнему требуется платежный аккаунт. Набор данных music занимает несколько килобайт, и каждый запрос в этой лаборатории оплачивается по минимальной цене за 10 МБ по требованию. Однако BigQuery не будет выполнять запрос для проекта, с которым не связан платежный аккаунт.

2. Откройте Cloud Shell (~1 мин)

👉🌐 Консоль – нажмите Активировать Cloud Shell в верхней части консоли Google Cloud. Это значок в виде терминала в заголовке. Внизу страницы откроется терминал.

👉🌐 Консоль – если сегодня вам будет предложено авторизоваться, нажмите Авторизоваться.

👉💻 Cloud Shell – проверьте, в каком аккаунте выполнен вход в Cloud Shell:

gcloud auth list

👀 Ожидайте, что ваш аккаунт будет отмечен значком *. Это должен быть тот же аккаунт, в который вы вошли на шаге 1. Если нет, выполните команду gcloud auth login и выберите нужный вариант.

Хотите увеличить размер окна терминала? Нажмите Открыть редактор (значок открытой папки с карандашом), чтобы открыть редактор Cloud Shell, а затем откройте в нем терминал, выбрав Вид → Терминал. В этом практическом занятии редактор не понадобится, поскольку вы не будете редактировать файлы. Однако полноэкранный терминал удобнее для чтения выходных данных.

3. Получите доступ к лаборатории (около 1 мин.)

👉💻 Cloud Shell – клонируйте репозиторий:

cd ~
rm -rf ~/bq-graph-measures-lab
git clone https://github.com/cuppibla/bq-graph-measures-lab.git
cd ~/bq-graph-measures-lab

Почему именно rm -rf? Домашний каталог Cloud Shell сохраняется между сеансами, поэтому при повторном выполнении этой практической работы вы столкнетесь с ошибкой fatal: destination path ... already exists. Удаление папки позволяет безопасно запускать этот блок дважды. В ней нет ваших данных.

4. Создайте проект (~2 мин)

👉💻 Cloud Shell – один скрипт находит ваш платежный аккаунт, создает в нем проект и указывает Cloud Shell на этот проект:

cd ~/bq-graph-measures-lab
./setup_project.sh

👀 Ожидайте ✓ для выбранного платежного аккаунта, ✓ для созданного проекта и окончания, подобного следующему:

Project ready: bq-graph-4271
  Next:  ./setup.sh

bq-graph-4271 – это идентификатор вашего проекта. У вас он будет другим. В нем происходят четыре процесса:

оплата	выбирает самый новый аккаунт [YYYY-MM-DD] GDP Credit:, если он у вас есть, или любой другой открытый платежный аккаунт.
project	создает аккаунт bq-graph-XXXX и связывает с ним платежный аккаунт;
выбирает	записывает идентификатор в ~/project_id.txt и запускает gcloud config set project, поэтому после этого вам не нужно будет вводить идентификатор.
waits	опросы, пока новый проект не будет допущен к показу, в течение двух минут;

🌐 В консоли теперь есть тот же проект. Его идентификатор указан на карточке Project info (Информация о проекте) на главной странице:

Почему он ждет в конце? Облачный проект Google Cloud, созданный несколько секунд назад, ещё не может обслуживать запросы, поскольку правила IAM ещё не распространились, и вызовы возвращают ошибку 403 IAM_PERMISSION_DENIED. Это самая вероятная причина ошибки в этой конфигурации, и она не связана с платежами или разрешениями – строки выше уже доказали, что проект существует и платежный аккаунт связан. Скрипт поглощает это ожидание, поэтому ./setup.sh не попадает в него.

Можно запустить повторно. Если что-то пойдет не так, просто запустите ./setup_project.sh ещё раз. Сначала он считывает ~/project_id.txt и возобновляет работу с этим проектом, если он ещё существует, связывая с ним оплату, если она не была завершена. Таким образом, при повторном запуске не будет создан второй проект.

No open billing account? Скрипт останавливается, не внося никаких изменений, и выводит инструкции. В девяти случаях из десяти Cloud Shell входит в аккаунт, отличный от того, в котором настроена оплата. Сравните gcloud auth list с шагом 1.

Could not create a project? В некоторых аккаунтах, особенно корпоративных, нельзя создавать проекты. Кроме того, для каждого аккаунта действует квота на количество проектов. Скрипт выводит в консоль инструкции по созданию проекта вручную. После этого вставьте идентификатор в ~/project_id.txt и снова запустите ./setup_project.sh. Скрипт будет использовать этот проект.

У вас уже есть проект, который вы хотите использовать? Не нужно писать скрипт. Вместо этого запустите команду gcloud config set project YOUR_PROJECT_ID, проверьте, что она сработала, с помощью команды gcloud config get-value project и перейдите к шагу 5. Вам потребуется роль Администратор BigQuery.

5. Создайте набор данных (около 3 минут).

👉💻 Cloud Shell – ещё один скрипт для создания таблиц:

cd ~/bq-graph-measures-lab
./setup.sh

Скрипт выводит каждый шаг по мере выполнения. Порядок действий:

project	считывает проект из gcloud config и отказывается продолжать без него;
API	включает bigquery.googleapis.com
dataset	создает набор данных music в макрорегионе "США" и пять таблиц, в каждой из которых есть PRIMARY KEY ... NOT ENFORCED;
check	подсчитывает количество строк в каждой таблице и останавливается, если количество строк не совпадает;

👀 Ожидаемые результаты

BigQuery Graph Measures · setup
  ✓ project: bq-graph-4271
  ✓ BigQuery API enabled

Creating dataset music (US) and five tables...
  ✓ music.Labels              2 rows
  ✓ music.Artists             3 rows
  ✓ music.Songs               5 rows
  ✓ music.Playlists           4 rows
  ✓ music.PlaylistEntries    10 rows

🎉 ready — open BigQuery Studio: https://console.cloud.google.com/bigquery?project=bq-graph-4271

Вы завершили работу с Cloud Shell. Вы можете закрыть его.

Каждый запрос в этой лаборатории также является файлом в этом репозитории. sql/01_...–sql/09_..., нумерация по шагам. Если вы предпочитаете запускать команду из терминала, а не вставлять ее в консоль, используйте bq query --nouse_legacy_sql < sql/02_sum_vs_agg.sql.

6. Откройте BigQuery и редактор запросов

👉🌐 Консоль – нажмите на ссылку в конце вывода или откройте BigQuery в меню навигации. На панели Проводник слева разверните проект: набор данных music с пятью таблицами.

👉🌐 Консоль – вверху BigQuery Studio в строке Создать нажмите SQL-запрос. Откроется пустая вкладка редактора.

Оставить вкладку. Каждый блок 👉📋 из этой лаборатории нужно вставлять сюда: выберите все, что есть в редакторе, вставьте новый блок поверх, нажмите Выполнить. Результат появится на панели ниже.

Знакомство с таблицами

Позже мы рассмотрим два важных аспекта этих таблиц. В каждой таблице первичный ключ объявляется как NOT ENFORCED. BigQuery не проверяет его, но графу свойств, которую вы собираетесь создать, нужен явный ключ, чтобы знать, что идентифицирует строку. Последняя таблица, PlaylistEntries, – это связующая таблица: если песня есть в четырех плейлистах, в ней будет четыре строки. Именно в этой таблице и происходит все самое интересное.

Таблицы связаны следующим образом: лейбл подписывает исполнителей, исполнитель выпускает песни, песня добавляется в плейлисты через PlaylistEntries. Cymbal Records владеет правами на Ava Sterling и Mina Okafor, а через них – на четыре песни. Northwind Audio владеет правами на исполнителя Кая Накамуру и его песню Midnight Drive.

стол	строки	клавиша	что в нем хранится;
Ярлыки	2	label_id	название, маркетинговый бюджет;
Исполнители	3	artist_id	название, какой ярлык
Треки	5	song_id	название, исполнитель, количество прослушиваний;
Плейлисты	4	playlist_id	name, followers
PlaylistEntries	10	entry_id	какие треки входят в какие плейлисты;

✅ Контрольная точка: 🎉 ready в Cloud Shell, пять таблиц под music в проводнике и открытая пустая вкладка редактора запросов.

3. Создание графа – вариант А: визуальный конструктор

Граф свойств – это описание того, как связаны таблицы. Вы можете создать его в визуальном редакторе графиков (на этом шаге нужно будет нажимать на разные элементы) или написать в виде кода SQL (на следующем шаге нужно будет вставить код). Оба варианта создают один и тот же объект, поэтому используйте только один из них.

Уже знакомы с DDL? Перейдите к следующему шагу и вставьте его. Вы можете вернуться к моделированию позже. Моделировщик отображает любой график, независимо от того, как он был создан.

1. Как создать оболочку графика

👉🌐 Консоль – в BigQuery Studio в строке Создать откройте меню ИИ и знания и нажмите Граф.

👉🌐 Консоль – введите MusicGraph в качестве названия графика и music в качестве целевого графика в наборе данных. Регион заполняется на основе набора данных (США).

👉🌐 Консоль – в разделе Источники данных выберите пять таблиц: "Исполнители", "Лейблы", "Элементы плейлистов", "Плейлисты" и "Треки" – и нажмите Создать.

👀 На холсте открываются пять цветных узлов без линий между ними. На панели Подробности справа указано Узлы (5) и Ребра (0). У каждого узла уже есть первичный ключ таблицы и все ее столбцы в качестве свойств. Чтобы посмотреть их, нажмите на любой узел.

2. Нарисуйте четыре края

Ребро – это внешний ключ, нарисованный. Каждая из них ведет от таблицы, содержащей ключ, к таблице, на которую она указывает.

👉🌐 Консоль – нажмите Новый край. В диалоговом окне Выбрать таблицу выберите PlaylistEntries и нажмите Добавить. На панели Добавить границу:

1. В поле Название укажите EntryToSong.
2. Нажмите Добавить основной ключ и примите условия entry_id.
3. В разделе Источник задайте для параметра Исходный узел значение PlaylistEntries, а затем с помощью параметров Добавить столбцы, на которые ссылается узел и Добавить столбцы, на которые ссылается ребро выберите entry_id для обоих.
4. В разделе Цель задайте для параметра Целевой узел значение "Треки", а затем выберите song_id для параметров Столбцы, на которые ссылается узел и Столбцы, на которые ссылается ребро.
5. Нажмите Готово.

👉🌐 Консоль (повторите ещё три раза):

название кромки	стол	исходный узел → целевой узел	столбец, по которому будет выполняться выборка с обеих сторон;
EntryToPlaylist	PlaylistEntries	PlaylistEntries → Playlists	источник entry_id, цель playlist_id
SongToArtist	Треки	Треки → Исполнители	источник song_id, цель artist_id
ArtistToLabel	Исполнители	Исполнители → Лейблы	источник artist_id, цель label_id

Не добавляйте к краям дополнительные свойства.

👀 На панели "Свойства" теперь указано Связи (4), а на холсте показана цепочка: "Ярлыки ← Исполнители ← Треки ← Элементы плейлиста → Плейлисты".

3. Добавьте меру

👉🌐 Консоль – нажмите на узел Треки, а затем на значок изменения (карандаш) в верхней части панели "Сведения". В разделе Свойства нажмите Добавить свойство > Добавить поле.

👉🌐 Консоль – введите total_streams в поле Название поля и введите следующее в поле Выражение:

MEASURE(SUM(streams))

👉🌐 Консоль – нажмите Добавить, а затем Готово.

4. Прочитайте код, а затем опубликуйте его.

👉🌐 Консоль – откройте вкладку Код на панели справа. Модельер уже написал для вас CREATE PROPERTY GRAPH. Прокрутите до узла "Треки". Список его свойств теперь заканчивается на

MEASURE(SUM(streams)) AS `total_streams`

Вот и все, что нужно знать об этой функции. Все, что происходит после этого шага, связано с тем, что делает эта строка.

👉🌐 Консоль – нажмите Опубликовать.

Вариант Б включает больше показателей: количество треков, бюджеты, количество подписчиков и места размещения. На шаге Все агрегаты, которые поддерживает показатель график воссоздается со всеми агрегатами в SQL, поэтому добавлять их вручную не нужно.

✅ Контрольная точка. В Проводнике в папке music теперь есть папка Graphs, в которой находится MusicGraph. Нажмите на него: пять узлов, четыре ребра и строка MEASURE(SUM(streams)) на вкладке "Код". Пропустите следующий шаг и перейдите к разделу Суммирование и агрегирование рядом.

4. Создание графика – вариант Б: SQL

Пропустите этот шаг, если вы создали график в моделировщике.

👉📋 Редактор запросов – вставьте запрос и нажмите Выполнить:

CREATE OR REPLACE PROPERTY GRAPH music.MusicGraph
  NODE TABLES (
    music.Labels KEY (label_id)
      PROPERTIES (label_id, label_name, marketing_budget,
        MEASURE(SUM(marketing_budget)) AS total_budget),
    music.Artists KEY (artist_id)
      PROPERTIES (artist_id, artist_name, label_id,
        MEASURE(COUNT(artist_id)) AS artist_count),
    music.Songs KEY (song_id)
      PROPERTIES (song_id, title, artist_id, streams,
        MEASURE(SUM(streams))   AS total_streams,
        MEASURE(COUNT(song_id)) AS song_count),
    music.Playlists KEY (playlist_id)
      PROPERTIES (playlist_id, playlist_name, followers,
        MEASURE(SUM(followers)) AS total_followers),
    music.PlaylistEntries KEY (entry_id)
      PROPERTIES (entry_id, playlist_id, song_id,
        MEASURE(COUNT(entry_id)) AS placement_count)
  )
  EDGE TABLES (
    music.PlaylistEntries AS EntryToSong
      SOURCE KEY (entry_id) REFERENCES PlaylistEntries(entry_id)
      DESTINATION KEY (song_id) REFERENCES Songs(song_id) NO PROPERTIES,
    music.PlaylistEntries AS EntryToPlaylist
      SOURCE KEY (entry_id) REFERENCES PlaylistEntries(entry_id)
      DESTINATION KEY (playlist_id) REFERENCES Playlists(playlist_id) NO PROPERTIES,
    music.Songs AS SongToArtist
      SOURCE KEY (song_id) REFERENCES Songs(song_id)
      DESTINATION KEY (artist_id) REFERENCES Artists(artist_id) NO PROPERTIES,
    music.Artists AS ArtistToLabel
      SOURCE KEY (artist_id) REFERENCES Artists(artist_id)
      DESTINATION KEY (label_id) REFERENCES Labels(label_id) NO PROPERTIES
  )

👀 На панели результатов указано, что граф ресурса создан.

Прочитайте его один раз.

На графике пять таблиц узлов и четыре таблицы ребер. В каждой таблице узла указано его KEY и перечислены PROPERTIES. Строки, начинающиеся с MEASURE, – это новый раздел. В каждой из них поддерживаемый агрегат применяется к свойству и получает псевдоним (это обязательно).

Каждая таблица ребер является внешним ключом, который обозначается следующим образом: SOURCE KEY в таблице, содержащей ключ, и DESTINATION KEY в таблице, на которую он указывает. NO PROPERTIES означает, что у края нет собственных данных.

Обратите внимание, что PlaylistEntries появляется трижды: один раз как таблица узлов и ещё два раза как физическая таблица за ребрами EntryToSong и EntryToPlaylist. Это сделано намеренно, и позже мы объясним почему.

Посмотрите, что у вас получилось

👉🌐 Консоль – в панели "Проводник" разверните music, затем Графики и нажмите MusicGraph. Откроется страница с пятью узлами и четырьмя ребрами. Нажмите на узел, чтобы посмотреть его свойства. Показатели будут перечислены вместе с агрегированными значениями. Чтобы посмотреть код, откройте вкладку Код.

✅ Контрольная точка. В папке Graphs в music есть файл MusicGraph, а на вкладке Code (Код) показан код MEASURE(SUM(streams)) AS total_streams.

5. Функции SUM и AGG рядом

Это вся практическая работа в одном запросе. Оба столбца содержат ответы на один и тот же вопрос – общее количество стримов для лейбла – и данные из одной и той же таблицы.

Именно эту таблицу создает GRAPH_EXPAND. Она сглаживает граф: начинается с одной таблицы и следует по каждому ребру наружу как по LEFT JOIN, пока все связанные таблицы не окажутся рядом в одном широком результате. Одна строка на место размещения, а не на песню, и каждый столбец с названием TableLabel_property. Посмотрите на него, прежде чем отправлять запрос.

👉📋 Редактор запросов – вставьте запрос и нажмите Выполнить:

SELECT Labels_label_name, Songs_title, Songs_streams, Playlists_playlist_name
FROM GRAPH_EXPAND("music.MusicGraph")
ORDER BY Songs_title, Playlists_playlist_name

👀 Ожидайте 10 строк – по одной для каждого места размещения в PlaylistEntries, что соответствует загруженным данным. Четыре из них занимает Neon Summer (по одному на каждый плейлист), и в каждом из них указано Songs_streams. С этими строками все в порядке. В плейлистах действительно по четыре видео. Но столбец, относящийся к песне, теперь повторяется в строках, относящихся к местам размещения, и это проблема, которую измеряет следующий запрос.

Теперь сравним.

👉📋 Редактор запросов – вставьте запрос и нажмите Выполнить:

SELECT
  Labels_label_name,
  SUM(Songs_streams)       AS with_sum,
  AGG(Songs_total_streams) AS with_agg
FROM GRAPH_EXPAND("music.MusicGraph")
GROUP BY 1 ORDER BY 1

👀 Результат:

Labels_label_name	with_sum	with_agg
Cymbal Records	12 600 000 000	5 100 000 000
Northwind Audio	9 000 000 000	4 500 000 000

Проверьте вручную. Четыре песни Cymbal набрали 2,0 млрд, 1,5 млрд, 1,2 млрд и 0,4 млрд прослушиваний. Это 5,1 миллиарда. AGG – правильный ответ. SUM в два с половиной раза больше, а у Northwind одна песня удвоилась.

Три новых элемента синтаксиса

• GRAPH_EXPAND("music.MusicGraph") – это функция, возвращающая табличное значение, которая преобразует граф в одну широкую таблицу, следуя каждому ребру как LEFT JOIN. Каждый столбец называется TableLabel_property, поэтому Songs_streams – это исходный столбец из таблицы "Песни", а Songs_total_streams – это объявленный вами показатель.
• SUM(Songs_streams) – это обычный SQL-запрос к широкой таблице, который наследует все дубликаты, созданные при выравнивании.
• Функция AGG(Songs_total_streams) оценивает показатель так, как он был объявлен: SUM(streams) один раз для каждого song_id, независимо от того, сколько строк занимает трек в широкой таблице.

Кэшированные результаты не учитывают изменения в графике. Результаты запросов, вызывающих GRAPH_EXPAND, кешируются так же, как и любые другие, и изменение графа не делает кеш недействительным. Каждый раз, когда вы повторно выполняете запрос после изменения диаграммы, открывайте Ещё → Настройки запроса в редакторе запросов и снимайте флажок Использовать кешированные результаты.

✅ Контрольная точка. Два ярлыка, два столбца и числа with_agg соответствуют количеству пальцев.

6. Почему функция SUM считает неправильно

Возьмите одну песню и посмотрите, что произойдет с ней после применения этих переходов.

👉📋 Редактор запросов – вставьте запрос и нажмите Выполнить:

SELECT 'BEFORE - the Songs table' AS step, title, streams, CAST(NULL AS STRING) AS playlist
FROM music.Songs WHERE title = 'Neon Summer'
UNION ALL
SELECT 'AFTER - joined to playlists', s.title, s.streams, pl.playlist_name
FROM music.Songs s
LEFT JOIN music.PlaylistEntries e ON e.song_id = s.song_id
LEFT JOIN music.Playlists pl ON pl.playlist_id = e.playlist_id
WHERE s.title = 'Neon Summer'
ORDER BY step DESC, playlist

👀 Результат:

шаг	название	прослушивания	плейлист
BEFORE – таблица "Треки"	Неоновое лето	2 000 000 000	null
AFTER - joined to playlists	Неоновое лето	2 000 000 000	Gym Pump
AFTER - joined to playlists	Неоновое лето	2 000 000 000	Популярные сейчас
AFTER - joined to playlists	Неоновое лето	2 000 000 000	Популярная музыка
AFTER - joined to playlists	Неоновое лето	2 000 000 000	Путешествие на автомобиле

Одна строка превратилась в четыре. Количество потоков не изменилось – оно было скопировано. Суммируем количество копий, которые есть в плейлистах Cymbal Records: Neon Summer 2.0 млрд в четырех плейлистах – 8.0, Paper Crown 1.5 млрд в двух плейлистах – 3.0, Double Shot 1.2 млрд в одном плейлисте, Slow Arrow 0.4 млрд в одном плейлисте. Это 12,6 млрд – число, которое вернула функция SUM. Трек Northwind's Midnight Drive был добавлен в два плейлиста, поэтому 4,5 млрд превратились в 9 млрд.

Это веерная рассылка, а не ошибка при объединении. Строки плейлистов реальны. Ошибка заключается в том, что число, относящееся к треку, суммируется по строкам, относящимся к местам размещения.

Показатель исправляет это, поскольку он был объявлен в узле "Треки" вместе с KEY (song_id). Главное – зернистость. Когда функция AGG оценивает total_streams, она агрегирует данные один раз для каждого ключа песни, а затем объединяет их в соответствии с выбранной вами группировкой.

Что изменилось

Вы писали	С показателем
SUM(streams) в каждом запросе, где это необходимо.	MEASURE(SUM(streams)) один раз на диаграмме.
SUM(...), чтобы прочитать его.	AGG(...), и только AGG.
Три JOIN	GRAPH_EXPAND(), одна простая таблица
Обобщенное табличное выражение, которое агрегирует данные перед объединением	Переход произойдет автоматически. KEY определяет зернистость

✅ Контрольная точка. Пять строк – одна ДО и четыре ПОСЛЕ – и вы можете восстановить 12, 6 млрд.

7. Правило: показатель читает только AGG

У показателей есть собственный тип – MEASURE, и его принимает только одна функция. Четыре запроса ниже намеренно не работают. Выполните каждый из них и прочитайте сообщение об ошибке красным цветом. Это четыре способа, которые все пробуют в первую очередь, и знание того, что они говорят, экономит время.

👉📋 Редактор запросов – AGG в обычном столбце:

SELECT AGG(Songs_streams) FROM GRAPH_EXPAND("music.MusicGraph")

👀 No matching signature for aggregate function AGG ... Argument 1: expected MEASURE, found INT64

👉📋 Редактор запросов – столбец показателя без AGG:

SELECT Songs_total_streams FROM GRAPH_EXPAND("music.MusicGraph") LIMIT 1

👀 Returning expressions of type MEASURE is not allowed

👉📋 Редактор запросов – показатель, на который ссылается GQL, но который никогда не видит показателей (это запрос GQL – см. примечание к версии в обзоре):

GRAPH music.MusicGraph
MATCH (s:Songs)
RETURN s.total_streams
LIMIT 1

👀 total_streams is not exposed by element type GRAPH_NODE(...)

👉📋 Редактор запросов – SUM над столбцом показателя, который вас интересует:

SELECT Labels_label_name, SUM(Songs_total_streams)
FROM GRAPH_EXPAND("music.MusicGraph")
GROUP BY 1

👀 MEASURE-typed arguments are only permitted in the AGG function

Таким образом, система типов обеспечивает соблюдение правила. Показатель виден только в GRAPH_EXPAND, а его значение можно получить только в AGG. Пересчитанные 12,6 млрд из предыдущего примера могут быть получены только путем суммирования значений в столбце "Исходные данные", но не в результате неправильной обработки показателя.

✅ Контрольная точка. Четыре красные ошибки, и вы можете сказать, какое правило нарушено в каждом случае.

8. Все агрегатные функции, поддерживаемые показателем

MEASURE() включает один из шести агрегатов: SUM, AVG, MIN, MAX, COUNT и COUNT(DISTINCT). Воссоздайте граф, добавив все шесть песен в узел "Песни". Края остаются NO PROPERTIES, что соответствует рекомендациям для случаев, когда край и узел находятся в одной физической таблице.

👉📋 Редактор запросов – вставьте запрос и нажмите Выполнить:

CREATE OR REPLACE PROPERTY GRAPH music.MusicGraph
  NODE TABLES (
    music.Labels KEY (label_id)
      PROPERTIES (label_id, label_name, marketing_budget,
        MEASURE(SUM(marketing_budget)) AS total_budget),
    music.Artists KEY (artist_id)
      PROPERTIES (artist_id, artist_name, label_id,
        MEASURE(COUNT(artist_id)) AS artist_count),
    music.Songs KEY (song_id)
      PROPERTIES (song_id, title, artist_id, streams,
        MEASURE(SUM(streams))              AS total_streams,
        MEASURE(AVG(streams))              AS avg_streams,
        MEASURE(MIN(streams))              AS min_streams,
        MEASURE(MAX(streams))              AS max_streams,
        MEASURE(COUNT(song_id))            AS song_count,
        MEASURE(COUNT(DISTINCT artist_id)) AS distinct_artists),
    music.Playlists KEY (playlist_id)
      PROPERTIES (playlist_id, playlist_name, followers,
        MEASURE(SUM(followers)) AS total_followers),
    music.PlaylistEntries KEY (entry_id)
      PROPERTIES (entry_id, playlist_id, song_id,
        MEASURE(COUNT(entry_id)) AS placement_count)
  )
  EDGE TABLES (
    music.PlaylistEntries AS EntryToSong
      SOURCE KEY (entry_id) REFERENCES PlaylistEntries(entry_id)
      DESTINATION KEY (song_id) REFERENCES Songs(song_id) NO PROPERTIES,
    music.PlaylistEntries AS EntryToPlaylist
      SOURCE KEY (entry_id) REFERENCES PlaylistEntries(entry_id)
      DESTINATION KEY (playlist_id) REFERENCES Playlists(playlist_id) NO PROPERTIES,
    music.Songs AS SongToArtist
      SOURCE KEY (song_id) REFERENCES Songs(song_id)
      DESTINATION KEY (artist_id) REFERENCES Artists(artist_id) NO PROPERTIES,
    music.Artists AS ArtistToLabel
      SOURCE KEY (artist_id) REFERENCES Artists(artist_id)
      DESTINATION KEY (label_id) REFERENCES Labels(label_id) NO PROPERTIES
  )

Теперь прочитай их все. Каждое значение проходит через AGG, и в последнем столбце показано, что результаты AGG – это обычные значения, с которыми можно выполнять арифметические действия. График только что изменился, поэтому сначала снимите флажок Использовать кешированные результаты (Ещё → Настройки запроса).

👉📋 Редактор запросов – вставьте запрос и нажмите Выполнить:

SELECT
  Labels_label_name,
  AGG(Songs_total_streams)    AS total_streams,
  AGG(Songs_avg_streams)      AS avg_streams,
  AGG(Songs_min_streams)      AS min_streams,
  AGG(Songs_max_streams)      AS max_streams,
  AGG(Songs_song_count)       AS songs,
  AGG(Songs_distinct_artists) AS artists,
  AGG(Songs_total_streams) / AGG(Songs_song_count) AS streams_per_song
FROM GRAPH_EXPAND("music.MusicGraph")
GROUP BY 1
ORDER BY 1

👀 Результат:

Labels_label_name	total_streams	avg_streams	min_streams	max_streams	– треков	исполнители	streams_per_song
Cymbal Records	5 100 000 000	1 275 000 000	400 000 000	2 000 000 000	4	2	1 275 000 000
Northwind Audio	4 500 000 000	4 500 000 000	4 500 000 000	4 500 000 000	1	1	4 500 000 000

Каждое число рассчитывается на основе четырех песен для Cymbal и одной для Northwind, а не на основе десяти строк с местами размещения. AVG, в частности, был бы неправильным в обратном случае: усреднение необработанного столбца по строкам мест размещения дает 1,575 млрд для Cymbal, поскольку 2 млрд Neon Summer учитываются четыре раза.

Таким образом, "заменить SUM на AGG" – это два решения. Когда вы объявляете показатель, вы выбираете, к какому из шести агрегатов он относится. Когда вы считываете его, функция всегда равна AGG.

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

✅ Контрольная точка. В каждой строке должно быть по семь чисел, а значение streams_per_song должно быть равно значению avg_streams.

9. Измените группировку, а не показатель

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

👉📋 Редактор запросов – вставьте запрос и нажмите Выполнить:

SELECT
  Playlists_playlist_name,
  AGG(Songs_total_streams) AS streams,
  AGG(Songs_song_count)    AS songs
FROM GRAPH_EXPAND("music.MusicGraph")
GROUP BY 1
ORDER BY 2 DESC

👀 Результат:

Playlists_playlist_name	прослушивания	– треков
Путешествие на автомобиле	6 900 000 000	3
Gym Pump	6 500 000 000	2
Популярные сейчас	4 700 000 000	3
Популярная музыка	3 500 000 000	2

Две строки AGG являются байт-в-байт строками из SUM и AGG, расположенных рядом. Перемещен только объект GROUP BY. В плейлист Road Trip входят Neon Summer, Midnight Drive и Slow Arrow, и каждая песня учитывается в нем один раз. Именно это и означает "стримы, попавшие в этот плейлист".

Автор показателя задал его зернистость один раз. Все последующие запросы, отправленные любым пользователем, наследуют его.

✅ Контрольная точка. На первом месте Road Trip с 6,9 млрд просмотров.

10. Как использовать функцию GRAPH_EXPAND

Чтобы узнать форму GRAPH_EXPAND, не нужно бегать вокруг него. Системная процедура BQ.SHOW_GRAPH_EXPAND_SCHEMA записывает список столбцов в переменную.

👉📋 Редактор запросов – вставьте все три строки и нажмите Выполнить:

DECLARE schema STRING DEFAULT '';
CALL BQ.SHOW_GRAPH_EXPAND_SCHEMA('music.MusicGraph', schema);
SELECT schema;

👀 Результат представляет собой строку JSON. У каждого поля есть название, тип и режим, а каждый столбец с показателями отмечен значком "is_measure": true. Для созданного вами графа доступны следующие функции:

name	тип	is_measure
Songs_title	STRING
Songs_streams	INTEGER
Songs_total_streams	INTEGER	true
Songs_avg_streams	FLOAT	true
Songs_distinct_artists	INTEGER	true
PlaylistEntries_placement_count	INTEGER	true

Здесь также показываются описания и синонимы, заданные для ресурса. На необязательном этапе чата можно посмотреть, кто прочитал эти сообщения.

✅ Контрольная точка. Строка JSON с is_measure в каждом столбце, объявленном с помощью MEASURE.

11. Требования к форме графика для функции GRAPH_EXPAND

На этом шаге ничего не нужно запускать – просто прочитайте информацию. GRAPH_EXPAND создает широкую таблицу с помощью цепочки из LEFT JOIN, начиная с одной таблицы и двигаясь по краям наружу. Из этого следует три правила, и MusicGraph соответствует всем трем.

Только одна таблица корневого узла. Корневая таблица – это таблица узлов, ключ которой не встречается ни в одной другой таблице. На нее не указывает ни одно ребро. В MusicGraph это PlaylistEntries – источник двух ребер и не конечная точка ни одного из них. В широкой таблице 10 строк, по одной на каждое место размещения.

Каждое ребро имеет тип "многие к одному" (от источника к пункту назначения). Каждая запись содержит одну песню и один плейлист, у каждой песни один исполнитель, у каждого исполнителя один лейбл. Поэтому широкая таблица никогда не выходит за пределы строк корневой таблицы. Данные, к которым у корневого элемента нет доступа, не отображаются. Например, если у песни нет мест размещения, она не будет показана в широкой таблице.

Отношения "многие ко многим" становятся узлом. Между песнями и плейлистами установлены отношения "многие ко многим". Если вы объявили таблицу связей как одно ребро от песен к плейлистам, GRAPH_EXPAND проигнорирует ее как неоднозначную. Поскольку других ребер не останется, произойдет сбой. Преобразование PlaylistEntries в таблицу узлов с двумя ребрами типа "многие к одному" – это описанный в документации шаблон, который вы использовали при создании графа.

Две рекомендации из документации: определите узел и его исходящие ребра в одной и той же физической таблице, как это сделано в этом графе; и сохраняйте эти ребра NO PROPERTIES, если только ребро не содержит информацию, которой нет в узле.

12. Пути с GQL (необязательно)

На все вопросы до этого момента нужно было ответить числом. В этом случае нет. Где слушатели находят стримы Cymbal? – это вопрос о маршрутах на графике, и сумма не может на него ответить, поскольку после суммирования маршрут исчезает.

Для этого шага требуется бронирование. Для запросов GQL требуется резервирование в версии Enterprise или Enterprise Plus. Если в вашем проекте используется тарифный план с оплатой по требованию, прочитайте приведенные ниже результаты и перейдите к следующему шагу.

1. Спросите, куда направляются потоки

👉📋 Редактор запросов – вставьте запрос и нажмите Выполнить:

GRAPH music.MusicGraph
MATCH (l:Labels)<-[:ArtistToLabel]-(a:Artists)<-[:SongToArtist]-(s:Songs)
      <-[:EntryToSong]-(e:PlaylistEntries)-[:EntryToPlaylist]->(pl:Playlists)
WHERE l.label_name = 'Cymbal Records'
RETURN pl.playlist_name, pl.followers,
       SUM(s.streams) AS streams_reaching_this_playlist,
       STRING_AGG(s.title, ', ') AS songs
GROUP BY pl.playlist_name, pl.followers
ORDER BY streams_reaching_this_playlist DESC

👀 Ожидайте четыре плейлиста:

pl.playlist_name	pl.followers	streams_reaching_this_playlist	– треков
Популярные сейчас	35 000 000	4 700 000 000	Double Shot, Neon Summer, Paper Crown
Популярная музыка	2 000 000	3 500 000 000	Неоновое лето, бумажная корона
Путешествие на автомобиле	5 000 000	2 400 000 000	Neon Summer, Slow Arrow
Gym Pump	3 000 000	2 000 000 000	Неоновое лето

Прочитайте строку MATCH как предложение: начните с лейбла, вернитесь к его исполнителям, затем к их песням, затем к записям плейлистов, в которых находятся эти песни, а затем перейдите к плейлистам. Стрелки указывают, как были объявлены ребра. <- перемещает на один шаг назад.

Теперь сложите значения в третьем столбце. 12,6 млрд – число, которое SUM указал на четвертом шаге и которое было неправильным.

Здесь все правильно. Тогда вопрос был в том, сколько раз трек прослушали на Cymbal, и песня считалась один раз независимо от того, в скольких плейлистах она была. Именно здесь мы понимаем, сколько стримов было на самом деле, и что трек, попавший в четыре плейлиста, действительно достиг четырех аудиторий. Дублирование GRAPH_EXPAND приводит к ошибке для одного вопроса и ответа на другой. AGG никогда не был против дубликатов – он просто хотел, чтобы ответ соответствовал вопросу.

2. Возвращать сами пути

Отмените агрегирование и запросите маршруты.

👉📋 Редактор запросов – вставьте запрос и нажмите Выполнить:

GRAPH music.MusicGraph
MATCH p = (l:Labels)<-[:ArtistToLabel]-(a:Artists)<-[:SongToArtist]-(s:Songs)
          <-[:EntryToSong]-(e:PlaylistEntries)-[:EntryToPlaylist]->(pl:Playlists)
WHERE l.label_name = 'Cymbal Records'
RETURN TO_JSON(p) AS path

p = – название всего совпадения, а TO_JSON – его визуализация.

👀 Ожидайте 8 строк и один столбец path. Каждая ячейка представляет собой массив JSON, содержащий узлы и ребра одного маршрута. Каждый узел содержит значения labels и properties.

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

3. Как открыть вкладку "График"

👉🌐 Консоль – над результатами нажмите График.

Эта вкладка появляется, только если в результатах поиска есть элементы диаграммы. Предыдущий запрос возвращал числа, поэтому вкладки "Диаграмма" не было. Этот запрос возвращает пути, поэтому вкладка есть.

Угол графика Cymbal будет представлен в виде макета с силой, а также будет показано количество узлов и ребер в представлении и чип для каждой метки. Перетащите узел, чтобы развернуть макет, или нажмите на него, чтобы посмотреть его свойства.

✅ Контрольная точка. Третий столбец первого запроса в сумме дает 12,6 млрд, второй запрос возвращает восемь путей, которые отображаются на вкладке "График".

13. Как общаться с графиком (необязательно)

На этом шаге SQL не используется. Агенты данных BigQuery могут использовать граф в качестве источника знаний, а в документации указано, что они используют показатели для многоуровневой агрегации. Давайте проверим, так ли это.

Прежде чем начать: для этого шага необходимо включить Gemini Data Analytics API в проекте и назначить роль "Создатель агента данных Gemini Data Analytics". Запросы, которые выполняет агент, оплачиваются по тарифам BigQuery на вычислительные ресурсы, как и любые другие запросы.

1. Как создать агента

👉🌐 Консоль – в меню навигации BigQuery слева нажмите Агенты, затем Каталог агентов и Создать агента.

👉🌐 Консоль – введите Music graph agent в поле Название агента и следующий текст в поле Описание агента:

Ask questions about labels, artists, songs and playlists in the music property graph.

👉🌐 Консоль – в разделе Источники знаний нажмите Добавить источник. В диалоговом окне Выбрать источники разверните проект, разверните music, разверните Графики, установите флажок MusicGraph и нажмите Подтвердить.

👀 Кнопка Добавить источник станет неактивной. Агент может содержать не более одного графика и не может сочетать графики с таблицами.

👉🌐 Консоль – оставьте в поле Регион значение, заданное на графике. Нажмите Сохранить > Опубликовать. На панели Каналы публикации нажмите Опубликовать агента и закройте подтверждение.

2. Задайте вопрос

👉🌐 Консоль – нажмите Создать чат. Откроется страница чата. Введите в поле Задайте вопрос внизу экрана и нажмите Ввод:

For each label, show total streams and how many playlists their songs are on

👀 Агент возвращает таблицу и краткое описание в одно предложение:

label_name	total_streams	playlist_placements
Cymbal Records	5 100 000 000	8
Northwind Audio	4 500 000 000	2

5,1 млрд Агент не был уведомлен о веерной рассылке и не попал в нее.

3. Прочитайте запрос, который создал ИИ.

👉🌐 Консоль – в меню навигации нажмите Исследователь заданий и выберите последнее задание. Его ярлыки включают ca-bq-job: true и data-agent-id с идентификатором вашего агента, а запрос выглядит так:

SELECT
  Labels_label_name AS label_name,
  AGG(Songs_total_streams) AS total_streams,
  AGG(PlaylistEntries_placement_count) AS playlist_placements
FROM GRAPH_EXPAND("music.MusicGraph")
GROUP BY label_name
ORDER BY total_streams DESC

Он использовал GRAPH_EXPAND и AGG самостоятельно, поскольку показатели были в схеме, которую он прочитал. В описании агента они не упоминаются.

✅ Контрольная точка. В ответе чата должно быть указано 5,1 млрд, а в SQL-запросе задания – AGG(...).

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

👉💻 Cloud Shell – удалите набор данных, его таблицы и граф свойств:

cd ~/bq-graph-measures-lab
./cleanup.sh

👀 Ожидайте ✓ dropped YOUR_PROJECT_ID.music (tables and graph).

Если вы создали необязательного агента данных, удалите его в консоли в разделе Агенты → Каталог агентов. Если ./setup_project.sh создал проект специально для этой лабораторной работы, лучше всего закрыть весь проект. Его идентификатор указан в ~/project_id.txt.

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

Вы объявили одну строку, MEASURE(SUM(streams)), и она изменила 12,6 млрд на 5,1 млрд – для вас и для каждого запроса, который кто-либо напишет к этому графику после этого, включая запросы, написанные агентом.

Что вы узнали

• Показатель – это агрегированное значение, объявленное в узле рядом с элементом KEY, который задает его детализацию.
• GRAPH_EXPAND преобразует граф в одну широкую таблицу с TableLabel_property столбцами.
• AGG – единственная функция, которая считывает показатель, и система типов обеспечивает это четырьмя разными способами.
• Доступно шесть агрегированных значений, а результаты AGG – это обычные значения, которые можно делить.
• Изменение GROUP BY позволяет задать вопрос повторно, не меняя показатель.
• GRAPH_EXPAND хочет, чтобы в графе был один корень, несколько ребер типа "многие к одному" и узлы, полученные из соединений.

Таблица решений

Symptom	Причина	Исправить
Слишком большая общая сумма	SUM над развернутым столбцом	объявить такт и прочитать его с помощью AGG;
"expected MEASURE, found INT64" (ожидалось значение MEASURE, найдено INT64).	AGG в обычном столбце	используйте SUM или укажите единицу измерения;
"Возвращать выражения типа MEASURE запрещено"	столбец с единицами измерения без атрибута "единица измерения" AGG	обернуть его: AGG(...);
"не поддерживается для этого типа элемента"	показатель, на который ссылается GQL;	отслеживает только трансляции с задержкой GRAPH_EXPAND;
GRAPH_EXPAND игнорирует край или не работает	перекресток, представленный в виде ребра;	преобразовать соединительную таблицу в узел;
Старое число после изменения графика.	кешированный результат	снимите флажок Использовать кешированные результаты;

Что мы не рассматривали (намеренно)

Описания и синонимы в свойствах, проверенные запросы и глоссарии для агентов данных, GQL для более чем одного MATCH, показатели для более чем одного графа. Каждая из них – отдельная лаборатория.

Подробнее…

• Как работать с показателями
• Как создать и запросить граф свойств
• Обзор разговорной аналитики
• Репозиторий с лабораторной работой – все запросы из этой практической работы, пронумерованные по шагам.