Анализ затрат на хранение данных и будущий прогноз в BigQuery

03 января, 2025

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

INFORMATION_SCHEMA включает множество схем, среди которых:

INFORMATION_SCHEMA.TABLES - предоставляет метаданные о всех таблицах и представлениях в указанном проекте или датасете, включая их имена, типы (TABLE, VIEW), даты создания и модификации, а также информацию о партиционировании и кластеризации;
INFORMATION_SCHEMA.COLUMNS - дает детальную информацию о столбцах в каждой таблице и представлении, такую как имя столбца, тип данных, возможность NULL значений, позиция в схеме и описание;
INFORMATION_SCHEMA.PARTITIONS - содержит информацию о логических партициях таблиц, включая даты партиций, количество строк и объем данных в каждой партиции. Это крайне полезно для понимания распределения данных и оптимизации запросов;
INFORMATION_SCHEMA.VIEWS - отображает определения (SQL-код) для всех представлений (views). Это позволяет легко проверить, как было построено то или иное представление;
INFORMATION_SCHEMA.ROUTINES - включает информацию о хранимых процедурах (stored procedures) и пользовательских функциях (user-defined functions - UDFs), включая их определения и параметры;
INFORMATION_SCHEMA.JOBS (или JOBS_BY_PROJECT, JOBS_BY_ORGANIZATION) - представления, которые предоставляют обширную информацию о выполненных запросах (jobs) BigQuery. Они включают детали о типе задания, время выполнения, объем обработанных данных, количество использованных слотов, статус (успех/неудача), информацию об ошибках, а также сам текст запроса. Варианты JOBS_BY_PROJECT и JOBS_BY_ORGANIZATION зависят от того, на каком уровне вы хотите агрегировать данные о заданиях.

Доступ к INFORMATION_SCHEMA осуществляется через SQL-запросы. Рассматривая таблицы в INFORMATION_SCHEMA.TABLES, можно отметить следующие:

INFORMATION_SCHEMA.TABLE_OPTIONS - представление для получения информации о различных опциях, заданных для таблиц и представлений. Например, оно может показать description таблицы, expiration_timestamp, labels, kms_key_name, require_partition_filter и другие настройки. Это позволяет программно проверять и управлять конфигурациями таблиц;
INFORMATION_SCHEMA.TABLE_CONSTRAINTS - предоставляет информацию об ограничениях, определенных на таблицах. На данный момент BigQuery поддерживает первичные ключи (PRIMARY KEY) и внешние ключи (FOREIGN KEY). Это представление позволяет вам видеть, какие ограничения существуют на ваших таблицах, что важно для обеспечения целостности данных;
INFORMATION_SCHEMA.TABLE_SNAPSHOTS - посвящено метаданным о снимках таблиц (table snapshots). Снимки таблиц — это статические, неизменяемые копии таблиц, которые BigQuery позволяет создавать для целей аудита, восстановления или анализа данных на определенный момент времени. Это представление поможет вам отслеживать ваши снимки;
INFORMATION_SCHEMA.TABLE_STORAGE - представление для анализа потребления хранилища на уровне проекта/датасета, показывающее логические, физические байты, данные для "путешествия во времени" (time travel) и т.д.;
INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION - расширенная версия TABLE_STORAGE, которая позволяет получить информацию о потреблении хранилища на уровне всей организации Google Cloud;
INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE - предоставляет ежедневную информацию о потреблении хранилища (включая логическое и физическое использование) для таблиц и материализованных представлений за последние 90 дней (или другой период, указанный в документации). Это помогает отслеживать тренды использования хранилища и прогнозировать затраты;
INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION - аналогично TABLE_STORAGE_BY_ORGANIZATION представление, которое агрегирует данные о временной шкале использования хранилища на уровне всей организации, давая комплексное представление о трендах потребления хранилища в масштабе предприятия.

Для анализа затрат на хранение данных и будущего прогноза нам потребуется INFORMATION_SCHEMA.TABLE_STORAGE. Доступ ко всем схемам INFORMATION_SCHEMA осуществляется через SQL-запросы (GoogleSQL).

Пример такого запроса:

SELECT
*
FROM
`region-REGION.INFORMATION_SCHEMA.TABLE_STORAGE`

SELECT

FROM

`region-REGION.INFORMATION_SCHEMA.TABLE_STORAGE`

Он выбирает все столбцы из указанной таблицы/представления. Таким образом, будут возвращены все доступные метаданные о хранении таблиц.

В этом запросе есть две особенности:

вы должны указать свой квалификатор региона данных region-REGION;
для его выполнения пользователю требуется роль BigQuery Metadata Viewer.

Чтобы назначить соответствующую роль, перейдите в службу IAM & Admin (см. приложение). Найдите пользователя, который будет выполнять запрос. Напротив него нажмите на иконку карандаша:

Редактирование ролей у пользователей

На открывшейся странице нажмите Add another role добавьте для этого пользователя роль BigQuery Metadata Viewer. Сохраните изменения.

BigQuery Metadata Viewer

После этого вы сможете выполнить данный запрос:

Результат выполнения запроса

Примечание:

INFORMATION_SCHEMA представления доступны только для чтения;
запросы к INFORMATION_SCHEMA оплачиваются как обычные запросы BigQuery (минимум 10 МБ за запрос);
результаты запросов к INFORMATION_SCHEMA не кэшируются, поэтому каждый раз при выполнении запроса вы будете платить.

INFORMATION_SCHEMA.TABLE_STORAGE вид имеет следующую схему:

project_id - ID проекта в Google Cloud, который содержит набор данных;
project_number - номер проекта, который содержит набор данных;
table_catalog - ID проекта, который содержит набор данных;
table_schema - название набора данных, который содержит таблицу или материализованное представление, также именуется как .datasetId;
table_name - название таблицы или материализованного представления, также именуемого .tableId;
creation_time - время создания таблицы;
total_rows - общее количество строк в таблице или материализованном представлении;
total_partitions - количество разделов, присутствующих в таблице или материализованном представлении. Неразделенные таблицы возвращают 0.
total_logical_bytes - общий размер данных в таблице в логических (несжатых) байтах. Это размер данных, как если бы они хранились без сжатия, и включает в себя все версии данных, доступные для запросов;
active_logical_bytes - количество логических байтов, которые считаются "активными" и обычно тарифицируются по стандартной ставке. В BigQuery это данные, которые хранятся менее 90 дней (или другой период, установленный пользователем);
long_term_logical_bytes - количество логических байтов, которые считаются "долгосрочными" и тарифицируются по сниженной ставке. В BigQuery это данные, которые хранятся 90 дней или дольше;
current_physical_bytes - текущий объем физического (сжатого) хранилища, используемого таблицей. Это фактический размер данных на диске после сжатия. Включает данные, доступные для запросов (активные и долгосрочные), а также любые данные для "путешествия во времени" (time travel);
total_physical_bytes - общий объем физического хранилища, используемого таблицей, включая все версии данных (например, для "путешествия во времени" и "безопасного отказа").
active_physical_bytes - количество физических (сжатых) байтов, которые считаются "активными" (меньше 90 дней);
long_term_physical_bytes - количество физических (сжатых) байтов, которые считаются "долгосрочными" (старше 90 дней);
time_travel_physical_bytes - объем физических байтов, используемых для функции "путешествия во времени" (Time Travel). BigQuery позволяет восстанавливать данные из предыдущих моментов времени в течение определенного периода. Это поле показывает объем хранилища, необходимого для поддержки этой функции;
storage_last_modified_time - дата последнего изменения данных;
deleted - показывает, была ли таблица удалена (true - если таблица удалена, false - нет);
table_type - тип таблицы (например, BASE TABLE, VIEW, EXTERNAL TABLE, MATERIALIZED VIEW);
fail_safe_physical_bytes - объем физических байтов, используемых для функции "безопасного отказа" (Fail-safe). Эта функция предоставляет дополнительный период хранения после окончания периода "путешествия во времени", позволяя восстановить данные в случае катастрофы;
last_metadata_index_refresh_time - время последнего обновления индекса метаданных таблицы.

Если вы желаете конкретизировать запрос, оставив только нужные столбцы для анализа затрат, вы можете выполнить следующий SQL:

SELECT
  project_id,
  table_schema,
  table_name,
  table_type,
  creation_time,
  storage_last_modified_time,
  total_logical_bytes,
  active_logical_bytes,
  long_term_logical_bytes,
  total_physical_bytes,
  active_physical_bytes,
  long_term_physical_bytes,
  time_travel_physical_bytes,
  fail_safe_physical_bytes,
  total_rows
FROM
  `region-eu.INFORMATION_SCHEMA.TABLE_STORAGE`
WHERE
  project_id = 'your-project-id' 
  AND table_schema = 'your-table-schema'
ORDER BY
  total_logical_bytes DESC;

SELECT

project_id,

table_schema,

table_name,

table_type,

creation_time,

storage_last_modified_time,

total_logical_bytes,

active_logical_bytes,

long_term_logical_bytes,

total_physical_bytes,

active_physical_bytes,

long_term_physical_bytes,

time_travel_physical_bytes,

fail_safe_physical_bytes,

total_rows

FROM

`region-eu.INFORMATION_SCHEMA.TABLE_STORAGE`

WHERE

project_id = 'your-project-id'

AND table_schema = 'your-table-schema'

ORDER BY

total_logical_bytes DESC;

, где в строках:

project_id - укажите ваш идентификатор проекта в Google Cloud;
table_schema - задайте ваш набор данных (датасет), например, для данных Google Analytics 4.

В BigQuery это будет выглядеть так:

project_id и table_schema

Запустив SQL, вы получите примерно такой результат:

Результат с конкретными столбцами

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

SELECT
  SUM(total_logical_bytes) AS total_logical_bytes
FROM
  `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE;

SELECT

SUM(total_logical_bytes) AS total_logical_bytes

FROM

`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE;

Не забудьте изменить квалификатор региона данных в region-REGION.

Результат в BigQuery:

Данные по total_logical_bytes

Теперь настало время выполнить SQL-запрос, который предоставляет подробный анализ использования хранилища BigQuery и прогнозирует затраты на основе заданных цен на следующие 30 дней, позволяя оптимизировать ваши расходы. Он взят из официальной документации Google Cloud:

DECLARE active_logical_gib_price FLOAT64 DEFAULT 0.02;
DECLARE long_term_logical_gib_price FLOAT64 DEFAULT 0.01;
DECLARE active_physical_gib_price FLOAT64 DEFAULT 0.04;
DECLARE long_term_physical_gib_price FLOAT64 DEFAULT 0.02;

WITH
 storage_sizes AS (
   SELECT
     table_schema AS dataset_name,
     -- Logical
     SUM(IF(deleted=false, active_logical_bytes, 0)) / power(1024, 3) AS active_logical_gib,
     SUM(IF(deleted=false, long_term_logical_bytes, 0)) / power(1024, 3) AS long_term_logical_gib,
     -- Physical
     SUM(active_physical_bytes) / power(1024, 3) AS active_physical_gib,
     SUM(active_physical_bytes - time_travel_physical_bytes) / power(1024, 3) AS active_no_tt_physical_gib,
     SUM(long_term_physical_bytes) / power(1024, 3) AS long_term_physical_gib,
     -- Restorable previously deleted physical
     SUM(time_travel_physical_bytes) / power(1024, 3) AS time_travel_physical_gib,
     SUM(fail_safe_physical_bytes) / power(1024, 3) AS fail_safe_physical_gib,
   FROM
     `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT
   WHERE total_physical_bytes + fail_safe_physical_bytes > 0
     AND table_type  = 'BASE TABLE'
     GROUP BY 1
 )
SELECT
  dataset_name,
  -- Logical
  ROUND(active_logical_gib, 2) AS active_logical_gib,
  ROUND(long_term_logical_gib, 2) AS long_term_logical_gib,
  -- Physical
  ROUND(active_physical_gib, 2) AS active_physical_gib,
  ROUND(long_term_physical_gib, 2) AS long_term_physical_gib,
  ROUND(time_travel_physical_gib, 2) AS time_travel_physical_gib,
  ROUND(fail_safe_physical_gib, 2) AS fail_safe_physical_gib,
  -- Compression ratio
  ROUND(SAFE_DIVIDE(active_logical_gib, active_no_tt_physical_gib), 2) AS active_compression_ratio,
  ROUND(SAFE_DIVIDE(long_term_logical_gib, long_term_physical_gib), 2) AS long_term_compression_ratio,
  -- Forecast costs logical
  ROUND(active_logical_gib * active_logical_gib_price, 2) AS forecast_active_logical_cost,
  ROUND(long_term_logical_gib * long_term_logical_gib_price, 2) AS forecast_long_term_logical_cost,
  -- Forecast costs physical
  ROUND((active_no_tt_physical_gib + time_travel_physical_gib + fail_safe_physical_gib) * active_physical_gib_price, 2) AS forecast_active_physical_cost,
  ROUND(long_term_physical_gib * long_term_physical_gib_price, 2) AS forecast_long_term_physical_cost,
  -- Forecast costs total
  ROUND(((active_logical_gib * active_logical_gib_price) + (long_term_logical_gib * long_term_logical_gib_price)) -
     (((active_no_tt_physical_gib + time_travel_physical_gib + fail_safe_physical_gib) * active_physical_gib_price) + (long_term_physical_gib * long_term_physical_gib_price)), 2) AS forecast_total_cost_difference
FROM
  storage_sizes
ORDER BY
  (forecast_active_logical_cost + forecast_active_physical_cost) DESC;

DECLARE active_logical_gib_price FLOAT64 DEFAULT 0.02;

DECLARE long_term_logical_gib_price FLOAT64 DEFAULT 0.01;

DECLARE active_physical_gib_price FLOAT64 DEFAULT 0.04;

DECLARE long_term_physical_gib_price FLOAT64 DEFAULT 0.02;

WITH

storage_sizes AS (

SELECT

table_schema AS dataset_name,

-- Logical

SUM(IF(deleted=false, active_logical_bytes, 0)) / power(1024, 3) AS active_logical_gib,

SUM(IF(deleted=false, long_term_logical_bytes, 0)) / power(1024, 3) AS long_term_logical_gib,

-- Physical

SUM(active_physical_bytes) / power(1024, 3) AS active_physical_gib,

SUM(active_physical_bytes - time_travel_physical_bytes) / power(1024, 3) AS active_no_tt_physical_gib,

SUM(long_term_physical_bytes) / power(1024, 3) AS long_term_physical_gib,

-- Restorable previously deleted physical

SUM(time_travel_physical_bytes) / power(1024, 3) AS time_travel_physical_gib,

SUM(fail_safe_physical_bytes) / power(1024, 3) AS fail_safe_physical_gib,

FROM

`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT

WHERE total_physical_bytes + fail_safe_physical_bytes > 0

AND table_type = 'BASE TABLE'

GROUP BY 1

)

SELECT

dataset_name,

-- Logical

ROUND(active_logical_gib, 2) AS active_logical_gib,

ROUND(long_term_logical_gib, 2) AS long_term_logical_gib,

-- Physical

ROUND(active_physical_gib, 2) AS active_physical_gib,

ROUND(long_term_physical_gib, 2) AS long_term_physical_gib,

ROUND(time_travel_physical_gib, 2) AS time_travel_physical_gib,

ROUND(fail_safe_physical_gib, 2) AS fail_safe_physical_gib,

-- Compression ratio

ROUND(SAFE_DIVIDE(active_logical_gib, active_no_tt_physical_gib), 2) AS active_compression_ratio,

ROUND(SAFE_DIVIDE(long_term_logical_gib, long_term_physical_gib), 2) AS long_term_compression_ratio,

-- Forecast costs logical

ROUND(active_logical_gib * active_logical_gib_price, 2) AS forecast_active_logical_cost,

ROUND(long_term_logical_gib * long_term_logical_gib_price, 2) AS forecast_long_term_logical_cost,

-- Forecast costs physical

ROUND((active_no_tt_physical_gib + time_travel_physical_gib + fail_safe_physical_gib) * active_physical_gib_price, 2) AS forecast_active_physical_cost,

ROUND(long_term_physical_gib * long_term_physical_gib_price, 2) AS forecast_long_term_physical_cost,

-- Forecast costs total

ROUND(((active_logical_gib * active_logical_gib_price) + (long_term_logical_gib * long_term_logical_gib_price)) -

(((active_no_tt_physical_gib + time_travel_physical_gib + fail_safe_physical_gib) * active_physical_gib_price) + (long_term_physical_gib * long_term_physical_gib_price)), 2) AS forecast_total_cost_difference

FROM

storage_sizes

ORDER BY

(forecast_active_logical_cost + forecast_active_physical_cost) DESC;

Цены, используемые в переменных ценообразования для этого запроса, указаны для региона us-central1. Если вы хотите выполнить этот запрос для другого региона, обновите переменные ценообразования согласно тарификации.

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

Скорректируйте эти значения согласно нужному региону в этих строках:

DECLARE active_logical_gib_price FLOAT64 DEFAULT 0.02;
DECLARE long_term_logical_gib_price FLOAT64 DEFAULT 0.01;
DECLARE active_physical_gib_price FLOAT64 DEFAULT 0.04;
DECLARE long_term_physical_gib_price FLOAT64 DEFAULT 0.02;

DECLARE active_logical_gib_price FLOAT64 DEFAULT 0.02;

DECLARE long_term_logical_gib_price FLOAT64 DEFAULT 0.01;

DECLARE active_physical_gib_price FLOAT64 DEFAULT 0.04;

DECLARE long_term_physical_gib_price FLOAT64 DEFAULT 0.02;

Не забудьте изменить квалификатор региона данных в region-REGION. Все остальное в запросе менять не нужно.

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

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

Результат выполнения SQL в BigQuery

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

Подробнее о том, из чего состоит тарификация в Google BigQuery, читайте в этой статье.

Метрики объема хранилища (в ГиБ):

dataset_name - имя набора данных (datasets);
active_logical_gib - общий объем "активных" логических (несжатых) данных в ГиБ для всех базовых таблиц в данном наборе данных. "Активные" данные - это те, которые хранятся менее 90 дней (по умолчанию в BigQuery) и тарифицируются по стандартной ставке;
long_term_logical_gib - общий объем "долгосрочных" логических (несжатых) данных в ГиБ для всех базовых таблиц в данном наборе данных. "Долгосрочные" данные - это те, которые хранятся 90 дней или дольше и тарифицируются по сниженной ставке;
active_physical_gib - общий объем "активных" физических (сжатых) данных в ГиБ для всех базовых таблиц в данном наборе данных. Это фактический размер данных на диске для активно используемых данных, включая данные для "путешествия во времени";
long_term_physical_gib - общий объем "долгосрочных" физических (сжатых) данных в ГиБ для всех базовых таблиц в данном наборе данных. Это фактический размер данных на диске для долгосрочно хранимых данных;
time_travel_physical_gib - общий объем физических (сжатых) данных в ГиБ, используемых для функции "путешествия во времени" (Time Travel) в данном наборе данных. Эта функция позволяет восстанавливать данные из предыдущих моментов времени;
fail_safe_physical_gib - общий объём физических (сжатых) данных в ГиБ, используемых для функции "безопасного отказа" (Fail-safe) в данном наборе данных. Эта функция предоставляет дополнительный период хранения для восстановления данных после окончания периода "путешествия во времени".

Коэффициенты сжатия:

active_compression_ratio - коэффициент сжатия для "активных" данных. Он показывает, насколько эффективно сжимаются активные логические данные в физические. Высокое значение указывает на хорошее сжатие;
long_term_compression_ratio - коэффициент сжатия для "долгосрочных" данных. Показывает, насколько эффективно сжимаются долгосрочные логические данные в физические.

Прогнозируемые затраты:

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

forecast_active_logical_cost - прогнозируемые затраты на "активное" хранение по логической модели ценообразования. Вычисляется как active_logical_gib умноженное на active_logical_gib_price (цену за 1 ГиБ активных логических данных), заданный в самом запросе;
forecast_long_term_logical_cost - прогнозируемые затраты на "долгосрочное" хранение по логической модели ценообразования. Вычисляется как long_term_logical_gib умноженное на long_term_logical_gib_price (цену за 1 ГиБ долгосрочных логических данных), заданный в самом запросе;
forecast_active_physical_cost - прогнозируемые затраты на "активное" хранение по физической модели ценообразования. Здесь учитываются физические объемы активных данных, данных для "путешествия во времени" и "безопасного отказа". Вычисляется как (active_no_tt_physical_gib + time_travel_physical_gib + fail_safe_physical_gib) умноженное на active_physical_gib_price (цену за 1 ГиБ активных физических данных), заданный в самом запросе;
forecast_long_term_physical_cost - прогнозируемые затраты на "долгосрочное" хранение по физической модели ценообразования. Вычисляется как long_term_physical_gib умноженное на long_term_physical_gib_price (цену за 1 ГиБ долгосрочных физических данных), заданный в самом запросе;
forecast_total_cost_difference - разница между общей прогнозируемой стоимостью по логической модели и общей прогнозируемой стоимостью по физической модели. Положительное значение означает, что логическая модель ценообразования будет дороже, а отрицательное — что физическая модель будет дороже. Это поле помогает определить, какая модель ценообразования является более экономичной для данного набора данных.

Метки: Google BigQuery, тарификация

Вход

Регистрация

Анализ затрат на хранение данных и будущий прогноз в BigQuery

Получайте бесплатные уроки и фишки

Еще статьи