Partitioned table (партиционирование) в Google BigQuery

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

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

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

Визуализируем процесс разделения таблицы на партиции:

Разделили большую таблицу по дате на несколько маленьких.

Таблицы BigQuery можно разбивать на разделы по следующим признакам:

Ingestion Time - таблицы разбиваются на разделы в зависимости от времени загрузки или времени поступления данных, которые содержат дополнительные зарезервированные поля _PARTITIONTIME, _PARTITIONDATE, хранящие дату создания записи.
Date/timestamp/datetime - таблицы разбиты на разделы на основе столбца с типом timestamp, date или datetime. Если таблица разделена по столбцу с типом DATE, вы можете создавать партиции с ежедневной, ежемесячной или ежегодной разбивкой. Каждый раздел содержит диапазон значений, где начало диапазона — это начало дня, месяца или года, а интервал диапазона составляет один день, месяц или год в зависимости от степени детализации разделения. Если таблица разделена по столбцам с типом TIMESTAMP или DATETIME, вы можете создавать разделы с любым типом гранулярности в единицах времени, включая HOUR.
Integer range - таблицы разделены по целочисленному столбцу. BigQuery позволяет разбивать таблицы на разделы на основе определенного столбца INTEGER с указанием значений начала, конца и интервала.

Как создать таблицу с партициями в Google BigQuery

1. Через интерфейс

Выводим название таблицы

Добавляем столбцы и их тип

Выбирает таблицу с разделителями или разделить по нашему полю из таблицы

Таблица с опцией "Partition by ingestion time " будет содержать дополнительный столбец _PARTITIONTIME с датой записи строки в таблицу. Именно по значению этого поля автоматически создаются партиции.

Название поля _PARTITIONTIME — зарезервировано. Поэтому обратиться к нему без присвоения псевдонима нельзя. Воспользуйтесь командой AS, чтобы получить значения данного поля.

Разбить можно по дню, часу, месяцу и году.

Таблица с разделением по полю, например, по date, которое есть у нас в таблице.

Таблица с опцией разделения таблицы по целочисленным значениям. То есть по значениям из поля "conversions", например? от 0 до 100 с интервалом 10. Таблица разделиться по значениям 0-10, 11-21, 22-33 и т.д.

Дальше нажимаем "Создать таблицу"

2. С помощью запроса
Таблица с разделением по полю, например, по date, аналогично второму варианту из создания таблиц через интерфейс.
Запрос ничем не отличается от обычного для созданию пустой таблицы, кроме последней строки. Используем оператор PARTITION BY по date


CREATE TABLE IF NOT EXISTS `united-strategy-279207.new_dataset.partition_table`
(
  date DATE, 
  source STRING, 
  medium STRING,
  conversions INT64
)
partition by (date)

Пустая партиционная таблица создана

Примеры работы с партиционными таблицами

1. Создание таблицы через интерфейс встроенным методом

Сегментирование выберем по часу

Обратимся к таблице, так как _PARTITIONTIME используется системой, то присвоим ему имя date


select _PARTITIONTIME as date,id, name,revenue
from `business-metric.new_dataset.partiton_table_test`

Так как при создании таблицы мы выбрали партицирование по часам, то и таблица разбита на сегменты по часам. И не имеет значения запись была добавлена в 11.40 или 11.05, все равно время будет 11:00:00 (по UTC).

Обратиться к конкретной партиции можно при помощи блока WHERE


select _PARTITIONTIME as date,id, name,revenue
from `business-metric.etnoshop.partiton_table_test`
WHERE _PARTITIONTIME = '2022-07-18 09:00:00 UTC'

2. Создание таблицы через запрос


CREATE TABLE IF NOT EXISTS `united-strategy-279207.new_dataset.partition_table`
(
  date DATE, 
  source STRING, 
  medium STRING,
  conversions INT64
)
partition by (date)

Результат

Обратиться к конкретной дате можно через запрос без _PARTITIONTIME


select * from `business-metric.etnoshop.partition_table`
where date = '2022-07-05'

Примеры

Искусственно созданные таблицы это интересно, но на практике все немного по другому, рассмотрим реальный пример с разделением таблиц по дате, например, из ежедневной выгрузки данных и GA4.

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

А если нам нужен промежуток или еще какой диапазон дат. На помощь приходит _TABLE_SUFFIX

Пишем его в блоке WHERE


SELECT event_date,event_timestamp,event_name
FROM `business-metric.analytics_319368526.events_*`
WHERE _TABLE_SUFFIX between '20220715' and '20220717' and event_name = '30sec_plus_50percent'

Или за два дня


SELECT event_date,event_timestamp,event_name
FROM `business-metric.analytics_319368526.events_*`
WHERE _TABLE_SUFFIX in ('20220715', '20220716') and event_name = '30sec_plus_50percent'

Важно

одна таблица может иметь не более 2500 разделов (партиций);
партицию можно обновлять не более 2000 раз в сутки;
частота обновлении партиции не более 50 обновлений в течении 10 секунд.
партици имеют срок действия (можно изменить)

ограничение для других пользователей, обращаться к партиции только через блок WHERE (включить/выключить)

Добавить настройки (срок действия и фильтрацию) можно при создании таблицы с помощью OPTIONS


CREATE TABLE IF NOT EXISTS `united-strategy-279207.new_dataset.partition_table_1`
( date DATE, 
  source STRING, 
  medium STRING,
  conversions INT64 )
partition by (date) options (partition_expiration_days=100, require_partition_filter=TRUE)

partition_expiration_days : BigQuery удаляет данные в разделе по истечении срока его действия. Это означает, что данные в разделах старше указанного здесь количества дней будут удалены.
require_partition_filter : пользователи не могут запрашивать без фильтрации (предложение WHERE) по ключу вашего раздела. TRUE - ограничения включены, FALSE - без ограничений

Чтобы изменить настройки уже в существующей таблице


ALTER TABLE
  `business-metric.etnoshop.partition_table`
SET
  OPTIONS(require_partition_filter=TRUE,partition_expiration_days=50)