Партиционирование таблиц в Google BigQuery — понятная инструкция
Знаете, как максимально продуктивно и экономно работать с данными в Google BigQuery с помощью разделения больших таблиц на партиции? В этой статье я расскажу, как создавать партиции и обращаться к конкретной партиции на обоих SQL-диалектах BigQuery.
Google BigQuery — это облачная база данных для хранения и быстрой обработки больших объемов информации. Как правило, стоимость использования облачной базы данных не превышает $5 в месяц даже при хранении и обработке достаточно больших объемов данных. Но при неправильном и неэффективном использовании инструментария оплата обходится в сумму в десять раз большую, чем вы ожидали.
Партиционирование — это разделение большой таблицы на части (партиции). Логически вы работаете с таблицей разбитой на партиции точно так же, как и с обычной, но физически эта таблица состоит из отдельных файлов (разделов) и в нужный момент вы получаете данные из тех разделов, в которых хранится запрашиваемая информация.
Такое разделение таблицы нужно для экономии ресурсов и ускорения работы запросов, а в случае с BigQuery — еще и для экономии денег за оплату обработанных данных.
Если вы не делите таблицу на партиции, то запрос будет сканировать все строки таблицы перед тем, как отдать вам результат. Если таблица разделена на партиции, и вам необходимо обработать данные из нескольких партиций, то запрос обратится за данными именно к этим частям таблицы, и объем данных для обработки будет значительно меньше.
Как создать таблицу с партициями в Google BigQuery
Создать таблицу из нескольких частей очень просто. Для этого в интерфейсе BigQuery при создании новой таблицы в опциях укажите формирование партиций по дням.
Таблица с такой опцией будет содержать дополнительный столбец _PARTITIONTIME с датой записи строки в таблицу. Именно по значению этого поля автоматически сформируются партиции.
Название поля _PARTITIONTIME — зарезервированное. Поэтому обратиться к нему без присвоения псевдонима нельзя. Воспользуйтесь комaндой AS, чтобы получить значения данного поля.
Пример запроса:
SELECT _PARTITIONTIME AS pt, id, value FROM Test_WF.part_testПример результата выполнения запроса:
По результату запроса видно, что столбец pt (это переименованный с помощью комaнды AS виртуальный столбец _PARTITIONTIME) содержит два значения: «2017-12-13 00:00:00 UTC» и «2017-12-14 00:00:00 UTC».
Таблица Test_WF.part_test на данный момент разделена на две партиции:
- строки с id 1-5 и значением «2017-12-13 00:00:00 UTC» — первая часть (эти данные загружены в таблицу 13 декабря 2017 года) ;
- строки с id 6-10 и значением «2017-12-14 00:00:00 UTC» входят во вторую партицию (данные загружены в таблицу 14 декабря 2017 года).
На первый взгляд, для пользователя это обычная таблица с физическим разделением (данные хранятся в разных файлах), но пользователь работает со всеми частями точно так же, как и работал бы с одной.
По умолчанию вся информация будет записываться в партицию, которая соответствует дате загрузки данных в таблицу. Для загрузки информации в партиции, сформированные в прошлом периоде, укажите партицию вместе с названием набора данных и таблицы через специальный знак разделитель $.
Например, для добавления записи в таблицу партиции от 1 декабря 2017 года запишем в Test_WF.part_test$20171201.
Как обращаться к партициям
BigQuery поддерживает два SQL-диалекта, поэтому рассмотрим, как обращаться к конкретной партиции на обоих диалектах.
В Legacy SQL укажите нужную партицию после имени таблицы через знак $.
Пример запроса:
SELECT *FROM Test_WF.part_test$20171213Пример результата выполнения запроса:
Этот запрос вернул все строки из партиции, в которую были загружены данные 13 декабря 2017 года.
В стандартном SQL вы можете обращаться к партициям в блоке WHERE.
Пример запроса:
SELECT * FROM Test_WF.part_test WHERE _PARTITIONTIME = \"2017-12-13\"Пример результата выполнения запроса:
Чтобы получить данные сразу из нескольких партиций, обратитесь к полю _PARTITIONTIME в блоке WHERE. Этот способ работает как в Standart ,так и в Legacy SQL.
SELECT * FROM TABLE_DATE_RANGE(Test_WF.my_table, TIMESTAMP(\"2017-12-13\"), TIMESTAMP(\"2017-12-14\"))Пример результата выполнения запроса:
Функции подстановки таблиц (Table Wildcard Function) поддерживаются только в Legacy SQL. Используйте виртуальное поле _TABLE_SUFFIX для обращения к отдельным партициям из таблицы, разбитой на разделы описанном в этом блоке способом.
Пример запроса:
SELECT * FROM `Test_WF.my_table*` WHERE _TABLE_SUFFIX BETWEEN \"20171213\" AND \"20171214\"Пример результата выполнения запроса:
Для перечисления любых суффиксов таблиц используйте поле _TABLE_SUFFIX и оператор IN.
Пример запроса:
SELECT * FROM `Test_WF.my_table*` WHERE _TABLE_SUFFIX IN (\"20171213\", \"20171214\")Пример результата выполнения запроса:
Название партиции удобно выводить в результате запроса.
Пример запроса:
SELECT *, _TABLE_SUFFIX as TableSuffix FROM `Test_WF.my_table*` WHERE _TABLE_SUFFIX BETWEEN \"20171213\" AND \"20171214\"Пример результата выполнения запроса:
Как пользоваться партиционированием таблиц
Партиции здорово помогают при составлении оперативной отчетности за фиксированный период, например, за последние 7 или 30 дней.
Для экономии ресурсов вы можете создать представление, которое будет возвращать данные за определенное количество дней, например за последние 7 или 30 дней.
Представление в SQL — это виртуальная таблица без данных с текстом SQL-запроса. Вы можете работать с результатом выполнения этого запроса, как с обычной таблицей базы данных.
Для создания представления в Google BigQuery напишите в редакторе запросов текст SQL-запроса и нажмите «Save View».
Укажите DataSet, в котором будет создано представление, и задайте имя представления.
Готово! Созданное вами представление появится в указанном наборе данных и вы сможете обращаться к нему, как обычной таблице.
Преимущество такого подхода очевидно: хранение данных в партициях, к которым вы не обращались более 90 дней, стоит в BigQuery на 50% дешевле. При этом счетчик времени сбрасывается при любом обращении к партиции: будь-то запрос, изменение, запись или копирование данных.
Для реализации подхода и получения данных за последние 7 дней воспользуйтесь примерами запросов для Legacy- и Standart-диалектов.
В Legacy SQL запрос будет выглядеть так:
SELECT * FROM Test_WF.part_test WHERE _PARTITIONTIME BETWEEN TIMESTAMP(UTC_USEC_TO_DAY(NOW() - 7 * 60 * 60 * 24 * 1000000)) AND TIMESTAMP(UTC_USEC_TO_DAY(CURRENT_TIMESTAMP()))В Standart SQL условие WHERE будет выглядеть немного иначе:
SELECT * FROM Test_WF.part_test WHERE _PARTITIONTIME BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 * 24 HOUR),DAY) AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(),DAY)Выводы
Партиционировать таблицы очень просто. Для этого в интерфейсе BigQuery при создании новой таблицы в опциях укажите формирование партиций по дням. Затем при запросе данных из таблицы с помощью соответствующего SQL-диалекта задайте нужную партицию после имени таблицы через символ $ или дату в формате ГГГГММДД.
При работе с партициями учитывайте ограничения:
- одна таблица может иметь не более 2500 разделов (партиций) ;
- партицию можно обновлять не более 2000 раз в сутки;
- частота обновлении партиции не более 50 обновлений в течении 10 секунд.
Партиционирование для таблиц с большим количеством строк сэкономит ваши деньги и повысит производительность выполнения запросов к данным. Стоимость хранения разделов, которые не обновлялись более 90 дней на 50% дешевле, чем хранение данных, которыми вы пользуетесь регулярно.
Этот пост впервые опубликован в 2018 году. Обновлен в 2021-ом. Если вы обнаружили здесь неактуальную информацию, напишите об этом в комментариях.
Комментарии:
Мы сменили CMS, не просев в трафике, и превратили каталог товаров в полноценный интернет-магазин....
06 02 2026 0:57:43
Идеи, достойные распространения. Какие доклады TED Talks повлияли на нетпиковцев....
05 02 2026 20:54:56
Как раскрутить виртуальный магазин женской одежды. Подробно рассказываем, что делали и чего достигли...
04 02 2026 8:18:24
SMM от Netpeak — это разработки стратегий продвижения, механики конкурсов, медиапланирование и постоянная интеpaктивная связь с клиентом с помощью «Личного кабинета»....
03 02 2026 17:32:55
Чтобы сделать что-то лучше, иногда надо довести это до абсурда. Так думают приверженцы теории «странных изделий» — chindogu...
02 02 2026 10:35:55
Инсайты от специалистов агентства Netpeak о популярных тематиках, тенденциях, особенностях продвижения в карантин и кризис, новых правилах для контекстной рекламы и других способов продвижения. Чтобы узнать больше, читайте далее!...
01 02 2026 19:22:23
Занимайтесь оптимизацией определенной категории товаров за полгода до скачка спроса....
31 01 2026 16:14:11
Раскрутить телеграм-канал с нуля до тысячи — реально. Раскрываем пошаговый алгоритм и даем рекомендации для SMM-специалистов....
30 01 2026 5:12:57
14 пунктов, которые помогут вам лучше изучить свой бизнес и подготовить эффективную SMM-стратегию...
29 01 2026 13:47:41
Нескучный инструктаж на примере @netpeak_ua о том, зачем компании корпоративный Twitter для продвижения контента, компании или персоны. А еще про хештеги, поиск читателей, аналитику, стиль ведения вашего микроблога, создание сообщества и акции...
28 01 2026 20:48:52
Рекламные кампании для брендинга, роста вовлечения пользователей, продаж, лояльности. Какой формат выбрать, чтобы получить необходимое целевое действие. Узнать!...
27 01 2026 20:15:23
Как правильно читать отчеты в Панели вебмастеров Google — объясняем на примерах из пpaктики....
26 01 2026 8:39:22
Что же такое эти authority и чем они лучше и полезнее, к примеру, чем Page Rank?...
25 01 2026 10:35:18
Все успешные люди немного сомневаются в своих достижениях и рефлексируют о большом количестве ошибок. Не ок, когда сомнения затягивают и разрушают самооценку, создают постоянное напряжение. Давайте разбираться с синдромом самозванца....
24 01 2026 4:35:18
Доминирование маркетплейсов и агрегаторов в Яндексе усилилось. Первый магазин появляется не раньше 5 позиции. Поэтому компания Alto подготовила пошаговое руководство - как обойти агрегаторы в выдаче....
23 01 2026 5:32:30
Как сэкономить время, ресурсы, и сделать онлайн-маркетинг эффективнее с помощью языка R...
22 01 2026 4:55:58
Счетчик Яндекс.Метрики: создание и установка кода, важные настройки....
21 01 2026 9:17:23
Украинские реалии того, в каких тематиках трафик из Yandex.Direct дороже Google Ads. Новое исследование Алексея Селезнева....
20 01 2026 22:32:13
Руководитель отдела HR, Ольга Пачесная, написала пост о главном — людях, которые выбрали работу в Netpeak....
19 01 2026 18:39:24
Современные платформы телефонии и коллтрекинга — это не только про звонки. С Ringostat вы узнаете, откуда пришел клиент, как менеджер ему ответил и какая реклама «принесла» деньги....
18 01 2026 21:21:34
Красивое креативное описание страницы «о нас» — важная составляющая коммуникации с потенциальным клиентом. Покажите свою комaнду, опишите ценности и миссию. Сделайте эту страницу продающей. Узнать больше!...
17 01 2026 23:41:57
О том, как добавить статью в Википедию и о правилах, которые нужно выполнить, чтобы ее не удалили: от добавления Интервики до ссылок на другие статьи, специальных терминов, добавления страниц в категории и выбора источников. Узнать больше!...
16 01 2026 8:14:18
Разбираем на примерах коллабораций, подрядчиков из регионов и тендендерных площадок...
15 01 2026 18:52:16
Структура того, чем обладают сотрудники департаментов работы с клиентами лучших компаний....
14 01 2026 23:18:26
Используйте и интерпретируйте карты знаний Netpeak Group, чтобы экономить свои силы и время на создании собственных карт с нуля. Узнать больше!...
13 01 2026 8:30:25
Сначала учитесь, а потом идите в бизнес и предпринимательство. Бизнес — это игра, в которую играют пару тысяч лет, и здесь не нужно придумывать велосипеды. Научитесь сначала просто ездить, потом уже будете думать про кастомы....
12 01 2026 2:32:38
Хорошая история состоит из узнаваемых образов, которые вызывают у пользователя ассоциации с личными переживаниями....
11 01 2026 13:51:29
Бухгалтерский мир Netpeak: цель, задачи, мотивация....
10 01 2026 11:59:51
Тошнота и процент воды в тексте не должны превышать допустимую норму, иначе не только пользователи уйдут с сайта, поисковые роботы понизят сайт в рейтинге. Узнать больше!...
09 01 2026 10:50:41
Гениальный математик создал систему, которая вполне может конкурировать с Google — Wolfram|Alpha. Или нет? Это не поисковик, тогда при чем здесь Гугл и теория вычислимости?...
08 01 2026 11:25:19
Знания основных принципов конверсионности сайта, этапов воронки продаж важны для того, чтобы наладить процессы, рост продаж в любом бизнесе. Узнать больше!...
07 01 2026 9:38:53
Лекции о ключевых этапах развития бизнеса в интернете: от создания сайта до подсчета ROMI (возврат маркетинговых инвестиций) рекламных каналов...
06 01 2026 10:53:28
В этом году часть пользователей предпочла вернуться к привычным покупкам в оффлайн-магазинах....
05 01 2026 11:35:54
Подбор правильного инструмента для визуализации данных может сэкономить время и ресурсы компании, а также повысить общую продуктивность маркетинга...
04 01 2026 22:35:22
Организаторам мероприятий, коллегам из PR-сферы и невестам о том, как делегировать важные мероприятия внешнему подрядчику...
03 01 2026 7:35:56
Создание канала бренда на видеоплатформе необходимо для увеличения притока трафика, улучшения репутации и поискового продвижения компании...
02 01 2026 20:33:43
Как понять, что проект нуждается в новых текстах и сделать читателя счастливым...
01 01 2026 19:52:44
Виктория Игнатьева рассказала про опыт обучения на наших курсах....
31 12 2025 9:12:48
Всё о крупной рыбе украинского интернет-маркетинга: компании, люди, кейсы в интервью с основателем Артёмом Бородатюком....
30 12 2025 5:15:32
Возможно, вы купили обувь Intertop именно после просмотра нашей рекламы....
29 12 2025 14:53:41
Как начать продавать и увеличить продажи, наращивая бесплатные ссылки...
28 12 2025 15:36:28
Разработка, вёрстка и оптимизация мобильных версий сайтов — популярные темы последнего времени. Мы расскажем, что стоит и что не стоит делать в мобильном SEO....
27 12 2025 4:12:38
Среди интернет-маркетологов существует мнение, что медийная реклама — не перформанс-инструмент. То есть не приносит прямые конверсии. Когда медийные кампании продают — кейс центра тюнинга автомобилей премиум класса....
26 12 2025 17:18:46
Кейс, в котором мы описываем работу с одним из наших старейших клиентов (с 2014 года) и как нам пришлось изменить устоявшийся формат работы и рекламного бюджета, чтобы соответствовать реалиям 2020 года....
25 12 2025 5:24:49
Образец рассылки от создателе Replyapp.io Олег Белозор, на которую ответили самые влиятельные эксперты в мире в2в продаж...
24 12 2025 10:34:10
Как прокачать свои знания и навыки использования GA, чтобы оптимизировать сайт и получать больше (намного больше, чем сейчас) трафика, конверсий, вовлечения. Больше!...
23 12 2025 3:57:45
Сайту нужны внешние ссылки на сторонних ресурсах. Они могут повлиять на ранжирование вашего ресурса и трафик, а один из методов их получить — аутрич....
22 12 2025 4:20:33
Часто копирайтеры пишут для поисковых роботов, не заботясь о качестве контента и читабельности. Рассмотрим метрики для оценки ридабилити....
21 12 2025 10:45:42
Как мы недооценили уровень конкуренции в нише, и как пришлось с этим бороться, чтобы принести пользу клиенту....
20 12 2025 20:17:34
По следам «Игры в кальмара». Небольшая подборка ностальгических комaндных игр, которые могут прижиться в вашем офисе....
19 12 2025 17:39:54
Еще:
понять и запомнить -1 :: понять и запомнить -2 :: понять и запомнить -3 :: понять и запомнить -4 :: понять и запомнить -5 :: понять и запомнить -6 :: понять и запомнить -7 ::
