Как использовать GoogleSQL в Crawlbaseзапрашивайте веб-данные с помощью SQL

Скрапинг даёт вам строки структурированных веб-данных, но сырые строки, это не анализ. Как только вы хотите задавать вопросы к этим данным: какие товары подешевели, в каких категориях больше всего отзывов, какой медианный рейтинг по бренду, вам нужен SQL. GoogleSQL, это диалект SQL, лежащий в основе Google Cloud SQL и BigQuery, и он отлично сочетается со скрапингом через Crawlbase: соберите данные один раз, загрузите в управляемую таблицу, а затем запрашивайте их обычным SQL вместо написания одноразовых скриптов-парсеров каждый раз.

Это руководство, пошаговое практическое описание такого конвейера. Вы получаете структурированные данные о товарах через Crawling API, сохраняете их в CSV, загружаете в таблицу Google Cloud SQL и затем пишете GoogleSQL-запросы: фильтры, агрегаты, ранжирование и несколько шаблонов, которые пригодятся на ваших собственных источниках. В примере скрапинга используются публичные результаты поиска Amazon, но SQL-часть применима к любому структурированному датасету.

Что вы создадите

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

• CSV со скрапленными записями по одной строке на товар: название, цена, валюта, рейтинг и количество отзывов.
• Таблицу Cloud SQL типизированную PostgreSQL-таблицу, хранящую импортированные строки.
• Набор запросов многократно используемые GoogleSQL-запросы для фильтрации, агрегации, ранжирования и обобщения данных.
• Воспроизводимый цикл шаблон, который можно применить к любому другому скрапленному источнику, просто заменив скрапер и схему таблицы.

Что такое GoogleSQL простыми словами

GoogleSQL, это название диалекта SQL, используемого в управляемых сервисах баз данных Google Cloud. В Cloud SQL это стандартный SQL, который вы пишете для управляемого экземпляра MySQL, PostgreSQL или SQL Server, где Google управляет оборудованием и большей частью операционной настройки за вас. В BigQuery, это диалект для работы с очень большими датасетами. В любом случае идея одна: вы не управляете собственными серверами, подключаетесь к управляемому движку, пишете запросы и позволяете Google заниматься бэкендом.

В этом руководстве мы используем Cloud SQL с PostgreSQL, поскольку это наиболее прямой путь от CSV со скрапленными данными к таблице, готовой к запросам. GoogleSQL здесь, обычный SQL: SELECT, WHERE, GROUP BY, ORDER BY и оконные функции. Ценность не в экзотическом синтаксисе, а в том, что ваши скрапленные веб-данные находятся в управляемой таблице, где эти запросы выполняются за миллисекунды.

Предварительные требования

Перед написанием кода нужно подготовить несколько вещей. На всё это уйдёт немного времени.

Аккаунт и токен Crawlbase. Зарегистрируйтесь, откройте дашборд и скопируйте токен обычных запросов со страницы документации аккаунта. Crawlbase включает до 20 000 бесплатных запросов для старта, чего вполне достаточно для этого руководства. Обращайтесь с токеном как с паролем и не включайте его в систему контроля версий.

Python 3 и библиотека requests. Проверьте версию командой python --version, затем установите единственную зависимость, нужную для скрапера. Модули json и csv входят в стандартную библиотеку.

Аккаунт Google Cloud. Вам нужен проект с включённой оплатой, чтобы создать экземпляр Cloud SQL. Google предоставляет бесплатные кредиты для новых аккаунтов, поэтому вы можете пройти руководство, не привязывая платёжные данные. Базовое знакомство с SQL полезно, но не обязательно, каждый запрос здесь разбирается построчно.

Настройка проекта

Создайте рабочий каталог и установите единственную библиотеку, используемую скрапером.

python --version

mkdir googlesql-crawlbase && cd googlesql-crawlbase

pip install requests

Это весь локальный setup. Шаг скрапинга выполняется в Python, а всё остальное происходит внутри Google Cloud SQL, где вы пишете GoogleSQL напрямую в консоли или через любой SQL-клиент на ваш выбор.

Шаг 1: Соберите структурированные данные в CSV

Первая задача, собрать данные, достаточно интересные для запросов. Скрипт ниже вызывает Crawlbase Crawling API со скрапером amazon-serp, который возвращает разобранный JSON для страницы результатов поиска Amazon, а затем преобразует товары в CSV. Сохраните его как crawl_data.py и замените заполнитель токена своим значением.

import csv
import json
import requests

API_TOKEN = "YOUR_CRAWLBASE_TOKEN"
API_ENDPOINT = "https://api.crawlbase.com/"

def crawl_data(search_url):
    params = {
        "token": API_TOKEN,
        "url": search_url,
        "scraper": "amazon-serp",
    }
    response = requests.get(API_ENDPOINT, params=params)
    response.raise_for_status()
    return response.text

FIELDNAMES = ["name", "price", "currency", "rating", "reviews"]

def save_to_csv(payload, filename="data.csv"):
    products = payload.get("body", {}).get("products", [])
    if not products:
        print("No products found")
        return
    with open(filename, "w", newline="", encoding="utf-8") as f:
        writer = csv.DictWriter(f, fieldnames=FIELDNAMES)
        writer.writeheader()
        for p in products:
            writer.writerow({
                "name": p.get("name", ""),
                "price": p.get("rawPrice", ""),
                "currency": p.get("currency", ""),
                "rating": p.get("customerReview", ""),
                "reviews": p.get("customerReviewCount", ""),
            })
    print(f"Saved {len(products)} products to {filename}")

if __name__ == "__main__":
    raw = crawl_data("https://www.amazon.com/s?k=wireless+headphones")
    save_to_csv(json.loads(raw), "data.csv")

Запустите командой python crawl_data.py. Crawling API выполняет парсинг за вас, поэтому вместо написания селекторов вы получаете чистый массив products и выбираете нужные поля. Скрипт сохраняет пять из них: name, price (взятое из числового rawPrice, чтобы импортироваться как число), currency, rating и reviews. Результат, data.csv, готовый к загрузке в Cloud SQL.

Шаг 2: Создайте экземпляр Cloud SQL и базу данных

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

• Откройте Google Cloud Console и создайте или выберите проект с включённой оплатой.
• Включите Cloud SQL Admin API на экране APIs and Services.
• Перейдите на страницу Cloud SQL Instances и нажмите Create instance, затем выберите PostgreSQL.
• Задайте идентификатор экземпляра, регион и пароль для пользователя по умолчанию, затем создайте экземпляр.
• Откройте новый экземпляр, перейдите на вкладку Databases и создайте базу данных с именем analytics_example_db.

Google выделяет и управляет движком за вас. Как только экземпляр запущен и база данных создана, вы можете открыть Cloud SQL Studio, встроенный редактор запросов, и выполнять GoogleSQL напрямую.

Шаг 3: Опишите таблицу

Откройте Cloud SQL Studio, войдите в analytics_example_db, создайте новую вкладку запроса и создайте таблицу, соответствующую столбцам CSV. Типы здесь важны: price и reviews объявлены числовыми, чтобы позже можно было суммировать, усреднять и сравнивать их, тогда как текстовые поля остаются TEXT или VARCHAR.

CREATE TABLE "public"."amazon_products" (
  "name" TEXT,
  "price" DECIMAL(10, 2),
  "currency" VARCHAR(10),
  "rating" DECIMAL(3, 2),
  "reviews" INTEGER
);

Это создаёт пустую таблицу amazon_products с пятью типизированными столбцами. Числовые типы, это суть всего упражнения: цена, хранящаяся как DECIMAL, может усредняться и ранжироваться, а цена как текст может только отображаться. Правильная схема здесь, это то, что делает запросы в шаге 5 возможными.

Шаг 4: Импортируйте CSV в таблицу

Когда таблица готова, загрузите скрапленные строки. Cloud SQL импортирует CSV-файлы из бакета Cloud Storage.

• Перейдите на страницу Cloud SQL Instances и откройте свой экземпляр.
• Выберите Import и укажите CSV как формат файла.
• Загрузите data.csv в бакет Cloud Storage и выберите его как исходный файл.
• Укажите analytics_example_db как целевую базу данных и amazon_products как таблицу.
• Нажмите Import.

После завершения импорта строки готовы к запросам. Всё дальнейшее, это GoogleSQL.

Шаг 5: Запросите данные с помощью GoogleSQL

Вот где структурированные данные окупаются. Каждый запрос ниже выполняется в Cloud SQL Studio или любом PostgreSQL-клиенте, подключённом к экземпляру. Начните с основ и постепенно усложняйте.

Прочитать всё. Самый простой запрос подтверждает, что импорт прошёл успешно, и показывает структуру данных.

SELECT * FROM amazon_products LIMIT 10;

Фильтровать и сортировать. Найдите хорошо оценённые товары ниже ценового порога, отсортировав по цене от наименьшей. Именно такой вопрос утомителен в таблице и тривиален в SQL.

SELECT name, price, rating, reviews
FROM amazon_products
WHERE price < 100
  AND reviews > 500
ORDER BY price ASC;

Агрегировать. Суммируйте весь результат в одной строке: сколько товаров, какова средняя цена и разброс цен.

SELECT
  COUNT(*) AS total_products,
  ROUND(AVG(price), 2) AS avg_price,
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive
FROM amazon_products;

Группировать и ранжировать. Когда в данных есть столбец категории, GROUP BY превращает плоскую таблицу в сравнение. Здесь пример группирует по валюте, но та же форма работает для бренда, категории или любого другого скрапленного столбца.

SELECT
  currency,
  COUNT(*) AS listings,
  ROUND(AVG(rating), 2) AS avg_rating
FROM amazon_products
GROUP BY currency
ORDER BY listings DESC;

Оконная функция. Ранжируйте товары по количеству отзывов, не сворачивая строки, так что каждый товар остаётся рядом со своим местом в наборе. RANK(), стандартный GoogleSQL и работает одинаково в Cloud SQL и BigQuery.

SELECT
  name,
  reviews,
  RANK() OVER (ORDER BY reviews DESC) AS popularity_rank
FROM amazon_products
ORDER BY popularity_rank
LIMIT 10;

Как выглядит результат

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

name,reviews,popularity_rank
Wireless Over-Ear Headphones,12840,1
Noise Cancelling Earbuds,9325,2
Bluetooth Sport Headphones,7710,3
Studio Monitor Headphones,4188,4
Budget Wireless Earbuds,3902,5

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

Адаптация под собственные источники

Ничто в SQL-части не привязано к Amazon. Чтобы запустить тот же конвейер для другого источника, меняются три вещи, остальное остаётся.

• Заменить скрапер. Укажите Scraper API на другой поддерживаемый источник или получите сырой HTML через Crawling API и разберите его самостоятельно. Желаемый результат, по-прежнему плоский CSV с согласованными столбцами.
• Согласовать схему. Отредактируйте оператор CREATE TABLE, чтобы имена и типы столбцов соответствовали новым полям. Всё, что вы будете агрегировать или сравнивать, сохраняйте как числовой тип.
• Переиспользовать запросы. Шаблоны фильтрации, агрегации, группировки и ранжирования применимы к любой таблице. Достаточно изменить имена столбцов, и они подходят к новому датасету без изменений.

Для крупных задач собирайте данные с помощью асинхронного Crawler, который ставит запросы в очередь и доставляет результаты через вебхук, а затем пакетно импортируйте их в Cloud SQL по расписанию. Если вас интересует подробное рассмотрение части загрузки и анализа, руководство по хранению и анализу скрапленных данных в SQL охватывает проектирование схемы и индексирование, а построение масштабируемого конвейера веб-данных рассматривает периодическое выполнение.

Ответственный скрапинг

SQL вы можете запускать свободно, но скрапинг, который его питает, должен следовать нескольким правилам. Собирайте только публичные данные: названия товаров, цены, рейтинги и количество отзывов, которые любой посетитель может видеть без авторизации, и не затрагивайте ничего за аутентификацией. Соблюдайте условия использования каждого источника и его robots.txt, и сохраняйте разумную частоту запросов, чтобы не создавать нагрузку на сайт. Когда источник публикует официальный API, предпочитайте его для массового или коммерческого использования. И если ваш датасет когда-либо будет включать персональные данные, обращайтесь с ними по соответствующим правилам конфиденциальности, таким как GDPR или CCPA: агрегируйте, не профилируйте людей и храните только то, что необходимо.

Часто задаваемые вопросы

В чём разница между GoogleSQL в Cloud SQL и в BigQuery?

GoogleSQL, общий диалект SQL, но движки под ним различаются. Cloud SQL запускает управляемые экземпляры MySQL, PostgreSQL или SQL Server и подходит для транзакционных датасетов среднего размера, таких как скрапленная таблица товаров. BigQuery, это бессерверное хранилище, созданное для очень больших аналитических запросов. Синтаксис запросов в этом руководстве, включая оконную функцию RANK(), работает в обоих. Начните с Cloud SQL для проекта такого размера и переходите на BigQuery только когда данные перерастут один экземпляр.

Нужно ли платить за использование Google Cloud SQL?

Для создания экземпляра Cloud SQL нужно включить оплату в проекте Google Cloud, однако новые аккаунты получают бесплатные кредиты, поэтому вы можете пройти это руководство без реальных затрат. Остановите или удалите экземпляр по завершении, чтобы избежать регулярных платежей. Сторона Crawlbase включает до 20 000 бесплатных запросов, что более чем достаточно для скрапинга в этом руководстве.

Подходит ли этот метод для источников, кроме Amazon?

Да. Конвейер не зависит от источника после скрапинга. Укажите Scraper API на другой поддерживаемый источник, или получите и разберите страницу через Crawling API, затем создайте CSV с согласованными столбцами. Обновите схему CREATE TABLE в соответствии с новыми полями, и те же шаги импорта и запросов применятся без изменений.

Что будет, если формат данных позже изменится?

Если источник добавляет поля или вы решаете собирать больше данных, вам не нужно начинать заново. Используйте оператор ALTER TABLE, чтобы добавить столбцы в существующую таблицу, затем импортируйте новые строки. Существующие данные остаются нетронутыми, и любой запрос, не ссылающийся на новые столбцы, продолжит работать точно так же.

Зачем собирать в CSV вместо прямой вставки строк?

CSV, простейший формат обмена, и Cloud SQL импортирует его нативно, что позволяет держать скрапинг и загрузку независимыми друг от друга. Вы можете проверить файл, повторно импортировать его или загрузить в другую систему, не запуская скрапинг заново. Для высокообъёмных или непрерывных конвейеров вы бы вставляли строки программно, но для периодических пакетных задач путь CSV-то-импорт требует наименьшего количества движущихся частей.

Работают ли эти запросы в стандартных SQL-клиентах?

Да. Как только экземпляр Cloud SQL запущен, вы можете подключить любой PostgreSQL-совместимый клиент: встроенный Cloud SQL Studio, psql или десктопный инструмент, и выполнять те же GoogleSQL-запросы. Запросы в этом руководстве используют стандартные возможности SQL, поэтому они переносимы между PostgreSQL-клиентами и не привязаны к конкретному редактору.

IK

Ian Kalvin

Инженер технической поддержки · Crawlbase

Инженер технической поддержки в Crawlbase, пишет с переднего края того, что на самом деле ломается в продакшене при скрейпинге и в прокси-конфигурациях.

NZ

Neil Zamora

Старший архитектор · Crawlbase

Старший архитектор в Crawlbase, сосредоточен на системах, стоящих за краулингом в больших масштабах: ротация прокси, устойчивость к anti-bot и API, скрывающие эту сложность.