Простая подготовка данных в BigQuery: руководство для тех, кто не тратит много времени на программирование

1. Введение

Аналитики данных часто сталкиваются с ценными данными, хранящимися в полуструктурированных форматах, таких как JSON. Извлечение и подготовка этих данных для анализа и машинного обучения традиционно представляли собой серьёзную техническую проблему, требующую сложных ETL-скриптов и вмешательства команды специалистов по обработке данных.

Эта практическая работа представляет собой техническую схему самостоятельного решения этой задачи для аналитиков данных. Она демонстрирует подход «с минимальным кодированием» к построению сквозного конвейера ИИ. Вы узнаете, как перейти от необработанного CSV-файла в Google Cloud Storage к реализации функции рекомендаций на основе ИИ, используя только инструменты, доступные в BigQuery Studio.

Основная цель — продемонстрировать надежный, быстрый и удобный для аналитиков рабочий процесс, который выходит за рамки сложных процессов с большим объемом кода и позволяет создавать реальную бизнес-ценность из ваших данных.

Предпосылки

• Базовое понимание Google Cloud Console
• Базовые навыки работы с интерфейсом командной строки и Google Cloud Shell

Чему вы научитесь

• Как загрузить и преобразовать CSV-файл непосредственно из Google Cloud Storage с помощью BigQuery Data Preparation.
• Как использовать преобразования без написания кода для анализа и выравнивания вложенных строк JSON в ваших данных.
• Как создать удаленную модель BigQuery ML, которая подключается к базовой модели Vertex AI для встраивания текста.
• Как использовать функцию ML.GENERATE_TEXT_EMBEDDING для преобразования текстовых данных в числовые векторы.
• Как использовать функцию ML.DISTANCE для вычисления косинусного сходства и поиска наиболее похожих элементов в наборе данных.

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

• Учетная запись Google Cloud и проект Google Cloud
• Веб-браузер, такой как Chrome

Ключевые концепции

• Подготовка данных BigQuery: инструмент в составе BigQuery Studio, предоставляющий интерактивный визуальный интерфейс для очистки и подготовки данных. Он предлагает варианты преобразования и позволяет пользователям создавать конвейеры данных с минимальным написанием кода.
• Удалённая модель BQML: объект BigQuery ML, выступающий в качестве прокси-сервера для модели, размещённой в Vertex AI (например, Gemini). Он позволяет вызывать мощные, предварительно обученные модели ИИ, используя знакомый синтаксис SQL.
• Векторное встраивание: числовое представление данных, таких как текст или изображения. В этой лабораторной работе мы преобразуем текстовые описания произведений искусства в векторы, где схожие описания приводят к векторам, которые «ближе» друг к другу в многомерном пространстве.
• Косинусное сходство: математическая мера, используемая для определения степени сходства двух векторов. Это основа логики нашей рекомендательной системы, используемая функцией ML.DISTANCE для поиска наиболее близких (наиболее похожих) произведений искусства.

2. Настройка и требования

Запустить Cloud Shell

Хотя Google Cloud можно управлять удаленно с вашего ноутбука, в этой лабораторной работе вы будете использовать Google Cloud Shell — среду командной строки, работающую в облаке.

В консоли Google Cloud Console нажмите значок Cloud Shell на верхней правой панели инструментов:

Подготовка и подключение к среде займёт всего несколько минут. После завершения вы увидите примерно следующее:

Эта виртуальная машина содержит все необходимые инструменты разработки. Она предоставляет постоянный домашний каталог объёмом 5 ГБ и работает в облаке Google Cloud, что значительно повышает производительность сети и аутентификацию. Всю работу в этой лабораторной работе можно выполнять в браузере. Вам не нужно ничего устанавливать.

Включите необходимые API и настройте среду

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

export PROJECT_ID=$(gcloud config get-value project)
gcloud config set project $PROJECT_ID
export LOCATION="US"
export GCS_BUCKET_NAME="met-artworks-source-${PROJECT_ID}" # Must be a globally unique name

gcloud services enable bigquery.googleapis.com \
                       storage.googleapis.com \
                       aiplatform.googleapis.com \
                       bigqueryconnection.googleapis.com

Создайте набор данных BigQuery и контейнер GCS

Создайте новый набор данных BigQuery для размещения наших таблиц и контейнер Google Cloud Storage для хранения нашего исходного CSV-файла.

# Create the BigQuery Dataset in the US multi-region
bq --location=$LOCATION mk --dataset $PROJECT_ID:met_art_dataset

# Create the GCS Bucket
gcloud storage buckets create gs://$GCS_BUCKET_NAME --project=$PROJECT_ID --location=$LOCATION

Подготовьте и загрузите образцы данных

Клонируйте репозиторий GitHub, содержащий пример CSV-файла, а затем загрузите его в контейнер GCS, который вы только что создали.

# Clone the repository
git clone https://github.com/GoogleCloudPlatform/devrel-demos.git

# Navigate to the correct directory
cd devrel-demos/data-analytics/dataprep

# Upload the CSV file to your GCS bucket
gsutil cp dataprep-met-bqml.csv gs://$GCS_BUCKET_NAME/

3. От GCS к BigQuery с подготовкой данных

В этом разделе мы воспользуемся визуальным интерфейсом без написания кода для приема нашего CSV-файла из GCS, очистки его и загрузки в новую таблицу BigQuery.

Запуск подготовки данных и подключение к источнику

1. В Google Cloud Console перейдите в BigQuery Studio.

2. На странице приветствия нажмите на карточку Подготовка данных, чтобы начать.

3. Если вы используете его впервые, вам может потребоваться включить необходимые API. Нажмите «Включить» для API Gemini for Google Cloud и для API BigQuery Unified. После включения можно закрыть эту панель.

4. В главном окне подготовки данных в разделе «Выбрать другие источники данных» выберите Google Cloud Storage. Справа откроется панель «Подготовка данных».

5. Нажмите кнопку «Обзор», чтобы выбрать исходный файл.

6. Перейдите к ранее созданному контейнеру GCS ( met-artworks-source-... ) и выберите файл dataprep-met-bqml.csv . Нажмите «Выбрать».

7. Далее необходимо настроить промежуточную таблицу.
8. В качестве набора данных выберите созданный вами met_art_dataset .
9. В поле Имя таблицы введите имя, например, temp .
10. Нажмите «Создать».

Преобразование и очистка данных

1. Подготовка данных BigQuery загрузит предварительный просмотр CSV-файла. Найдите столбец label_details_json , содержащий длинную строку JSON. Щёлкните по заголовку столбца, чтобы выбрать его.

2. На панели «Предложения» справа Gemini в BigQuery автоматически предложит соответствующие преобразования. Нажмите кнопку «Применить» на карточке «Выравнивание столбца label_details_json ». Это позволит извлечь вложенные поля ( description , score и т. д.) в отдельные столбцы верхнего уровня.

3. Щелкните столбец object_id и нажмите кнопку «Применить» в разделе «Преобразует столбец object_id из типа string в int64 .

Определите пункт назначения и запустите задание

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

2. Укажите данные пункта назначения:
3. Набор данных должен быть предварительно заполнен met_art_dataset .
4. Введите новое имя таблицы для вывода: met_art_flatten_table .
5. Нажмите «Сохранить».

3. Нажмите кнопку «Выполнить» и дождитесь завершения подготовки данных.

4. Вы можете отслеживать ход выполнения задания на вкладке «Выполнения» в нижней части страницы. Через несколько секунд задание будет завершено.

4. Генерация векторных вложений с помощью BQML

Теперь, когда наши данные очищены и структурированы, мы применим BigQuery ML для основной задачи ИИ: преобразования текстовых описаний произведений искусства в числовые векторные представления.

Создать соединение BigQuery

Чтобы разрешить BigQuery взаимодействовать со службами Vertex AI, необходимо сначала создать соединение BigQuery.

1. На панели проводника BigQuery Studio нажмите кнопку «+ Добавить данные».

2. В правой панели введите Vertex AI в строке поиска. Выберите его, а затем выберите BigQuery Federation в отфильтрованном списке.

3. Откроется форма «Внешний источник данных». Заполните следующие данные:

• Идентификатор подключения: введите идентификатор подключения (например, bqml-vertex-connection ).
• Тип местоположения: убедитесь, что выбран параметр «Мультирегион».
• Местоположение: выберите местоположение (например, US ).

4. После создания подключения появится диалоговое окно подтверждения. Нажмите «Перейти к подключению» или «Внешние подключения» на вкладке «Проводник». На странице сведений о подключении скопируйте полный идентификатор в буфер обмена. Это идентификатор учётной записи службы, который BigQuery будет использовать для вызова Vertex AI.

5. В меню навигации Google Cloud Console перейдите в раздел IAM и администрирование > IAM.

6. Нажмите кнопку «Предоставить доступ».
7. Вставьте учетную запись службы, скопированную на предыдущем шаге, в поле Новые субъекты.
8. В раскрывающемся списке «Роль» назначьте « Пользователя Vertex AI » и нажмите «Сохранить».

Этот важный шаг гарантирует, что BigQuery имеет необходимые полномочия для использования моделей Vertex AI от вашего имени.

Создать удаленную модель

В BigQuery Studio откройте новую вкладку редактора SQL. Здесь вы определите модель BQML для подключения к Gemini.

Этот оператор не обучает новую модель. Он просто создаёт ссылку в BigQuery, которая указывает на мощную, предварительно обученную модель gemini-embedding-001 использующую только что авторизованное вами соединение.

Скопируйте весь скрипт SQL ниже и вставьте его в редактор BigQuery.

CREATE OR REPLACE MODEL `met_art_dataset.embedding_model`
REMOTE WITH CONNECTION `US.bqml-vertex-connection`
OPTIONS (endpoint = 'gemini-embedding-001');

Генерировать вложения

Теперь мы воспользуемся нашей моделью BQML для генерации векторных вложений. Вместо того, чтобы просто преобразовывать одну текстовую метку для каждой строки, мы используем более сложный подход для создания более содержательного и содержательного «семантического резюме» для каждого изображения. Это обеспечит более высокое качество вложений и более точные рекомендации.

Этот запрос выполняет критический этап предварительной обработки:

• Сначала он использует предложение WITH для создания временной таблицы.
• Внутри него мы GROUP BY каждому object_id , чтобы объединить всю информацию об одном произведении искусства в одну строку.
• Мы используем функцию STRING_AGG для объединения всех отдельных текстовых описаний (например, «Портрет», «Женщина», «Масло на холсте») в одну полную текстовую строку, упорядочивая их по степени релевантности.

Этот объединенный текст дает ИИ гораздо более богатый контекст изображения, что приводит к более тонким и мощным векторным встраиваниям.

В новой вкладке редактора SQL вставьте и выполните следующий запрос:

CREATE OR REPLACE TABLE `met_art_dataset.artwork_embeddings` AS
WITH artwork_semantic_text AS (
  -- First, we group all text labels for each artwork into a single row.
  SELECT
    object_id,
    ANY_VALUE(title) AS title,
    ANY_VALUE(artist_display_name) AS artist_display_name,
    -- STRING_AGG combines all descriptions into one comma-separated string,
    -- ordering them by score to put the most relevant labels first.
    STRING_AGG(description, ', ' ORDER BY score DESC) AS aggregated_labels
  FROM
    `met_art_dataset.met_art_flatten_table`
  GROUP BY
    object_id
)
SELECT
  *
FROM ML.GENERATE_TEXT_EMBEDDING(
  MODEL `met_art_dataset.embedding_model`,
  (
    -- We pass the new, combined string as the content to be embedded.
    SELECT
      object_id,
      title,
      artist_display_name,
      aggregated_labels AS content
    FROM
      artwork_semantic_text
  )
);

Выполнение этого запроса займёт около 10 минут. После завершения запроса проверьте результаты. В панели «Проводник» найдите новую таблицу artwork_embeddings и щёлкните по ней. В окне просмотра схемы таблицы вы увидите object_id , новый столбец ml_generate_text_embedding_result , содержащий векторы, а также столбец aggregated_labels, который использовался в качестве исходного текста.

5. Поиск похожих произведений искусства с помощью SQL

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

1. Вставьте следующий запрос в новую вкладку редактора SQL. Этот запрос имитирует основную логику приложения-рекомендации:

• Сначала он выбирает вектор для одного конкретного произведения искусства (в данном случае «Кипарисы» Ван Гога, object_id которого равен 436535).
• Затем он вычисляет расстояние между этим вектором и всеми остальными векторами в таблице.
• Наконец, он сортирует результаты по расстоянию (меньшее расстояние означает большее сходство), чтобы найти 10 наиболее близких совпадений.

WITH selected_artwork AS (
  SELECT text_embedding
  FROM `met_art_dataset.artwork_embeddings`
  WHERE object_id = 436535
)
SELECT
  base.object_id,
  base.title,
  base.artist_display_name,
  -- ML.DISTANCE calculates the cosine distance between the two vectors.
  -- A smaller distance means the items are more similar.
  ML.DISTANCE(base.text_embedding, (SELECT text_embedding FROM selected_artwork), 'COSINE') AS similarity_distance
FROM
  `met_art_dataset.artwork_embeddings` AS base, selected_artwork
ORDER BY
  similarity_distance
LIMIT 10;

2. Выполните запрос. В результатах будут перечислены идентификаторы object_id , наиболее близкие совпадения будут вверху. Исходный объект будет отображаться первым с дистанцией 0. Это базовая логика, лежащая в основе рекомендательного механизма ИИ, и вы создали её полностью в BigQuery, используя только SQL.

6. (НЕОБЯЗАТЕЛЬНО) Запуск демо-версии в Cloud Shell

Чтобы воплотить концепции из этой лабораторной работы в жизнь, клонированный вами репозиторий включает простое веб-приложение. Эта дополнительная демонстрация использует созданную вами таблицу artwork_embeddings для работы визуальной поисковой системы, позволяя вам увидеть рекомендации ИИ в действии.

Чтобы запустить демо в Cloud Shell, выполните следующие действия:

1. Установка переменных среды: перед запуском приложения необходимо установить переменные среды PROJECT_ID и BIGQUERY_DATASET.

export PROJECT_ID=$(gcloud config get-value project)
export BIGQUERY_DATASET=met_art_dataset
export REGION='us-central1'
bq cp bigquery-public-data:the_met.images $PROJECT_ID:met_art_dataset.images

2. Установите зависимости и запустите внутренний сервер.

cd ~/devrel-demos/data-analytics/dataprep/backend/ && npm install
node server.js

3. Для запуска фронтенд-приложения вам понадобится вторая вкладка терминала. Нажмите значок «+», чтобы открыть новую вкладку Cloud Shell.

4. Теперь в новой вкладке выполните следующую команду для установки зависимостей и запуска сервера frontend.

cd ~/devrel-demos/data-analytics/dataprep/frontend/ && npm install
npm run dev

5. Предварительный просмотр приложения: на панели инструментов Cloud Shell нажмите значок «Веб-просмотр» и выберите «Предварительный просмотр на порту 5173». Откроется новая вкладка браузера с запущенным приложением. Теперь вы можете использовать приложение для поиска произведений искусства и увидеть, как работает поиск по сходству.

6. Чтобы связать эту наглядную демонстрацию с вашей работой в SQL-редакторе BigQuery, попробуйте ввести «Кипарисы» в строке поиска. Это тот же рисунок ( object_id=436535 ), который вы использовали в запросе ML.DISTANCE . Затем нажмите на изображение «Кипарисы», когда оно появится на левой панели. Результаты будут видны справа. Приложение отображает наиболее похожие рисунки, наглядно демонстрируя эффективность созданного вами поиска по векторному сходству.

7. Очистка окружающей среды

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

Выполните следующие команды в терминале Cloud Shell, чтобы удалить учетную запись службы, подключение BigQuery, GCS Bucket и набор данных BigQuery.

# Re-run these exports if your Cloud Shell session timed out
export PROJECT_ID=$(gcloud config get-value project)
export LOCATION="US"
export GCS_BUCKET_NAME="met-artworks-source-${PROJECT_ID}"
export BQ_CONNECTION_ID="bqml-vertex-connection"

Удалить подключение BigQuery и контейнер GCS

# Delete the BigQuery connection
bq rm --connection $LOCATION.$BQ_CONNECTION_ID

# Delete the GCS bucket and its contents
gcloud storage rm --recursive gs://$GCS_BUCKET_NAME

Удалить набор данных BigQuery

Наконец, удалите набор данных BigQuery. Эта команда необратима. Флаг -f (принудительно) удаляет набор данных и все его таблицы без запроса подтверждения.

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

# Manually type this command to confirm you are deleting the correct dataset
bq rm -r -f --dataset $PROJECT_ID:met_art_dataset

8. Поздравляем!

Вы успешно построили сквозной конвейер данных на базе искусственного интеллекта.

Вы начали с необработанного CSV-файла в контейнере GCS, использовали интерфейс BigQuery Data Prep с низким уровнем написания кода для приема и сжатия сложных данных JSON, создали мощную удаленную модель BQML для генерации высококачественных векторных вложений с помощью модели Gemini и выполнили запрос поиска схожести для нахождения связанных элементов.

Теперь вы вооружены базовой моделью для создания рабочих процессов с использованием ИИ в Google Cloud, которые преобразуют необработанные данные в интеллектуальные приложения быстро и просто.

Что дальше?

• Визуализируйте результаты в Looker Studio: подключите таблицу BigQuery artwork_embeddings напрямую к Looker Studio (это бесплатно!). Вы можете создать интерактивную панель инструментов, где пользователи смогут выбрать произведение искусства и просмотреть галерею наиболее похожих работ без написания кода на стороне клиента.
• Автоматизация с помощью запланированных запросов: вам не нужен сложный инструмент для оркестровки, чтобы поддерживать актуальность встроенных данных. Используйте встроенную функцию запланированных запросов BigQuery для автоматического повторного выполнения запроса ML.GENERATE_TEXT_EMBEDDING ежедневно или еженедельно.
• Создайте приложение с помощью Gemini CLI: используйте Gemini CLI для создания полноценного приложения, просто описав свои требования простым текстом. Это позволит вам быстро создать рабочий прототип для поиска по сходству без необходимости вручную писать код на Python.
• Прочтите документацию:
  • Введение в подготовку данных BigQuery
  • Пример кода на GitHub