Партиционирование таблиц в 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-ом. Если вы обнаружили здесь неактуальную информацию, напишите об этом в комментариях.
Комментарии:
Таблица общих для Google и Яндекс микроформатов инсайде...
10 09 2026 1:27:14
RFM — это аббревиатура слов Recency, Frequency, Money — новизна, частота, деньги. То есть считаем, как давно клиент купил товар в последний раз, сколько всего покупок он сделал и сколько денег принес нам за все время заказов....
09 09 2026 14:18:22
Человеку стало плохо, он теряет сознание. Те, кто рядом, хотят помочь, но не знают, как это сделать. Давайте разбираться....
08 09 2026 17:23:14
Как говорится, люди делятся на тех, кто делит других на типы и тех, кто не делит. В этом посте — про желтых, синих, красных и зеленых людей....
07 09 2026 8:32:28
Case по продвижению в Instagram. В любой непонятной ситуации — масштабируйте рекламные инструменты...
06 09 2026 3:35:21
Если выполнить настройку не корректно, поисковик воспримет материалы как дубли контента....
05 09 2026 20:16:53
Кейс продвижения Pizza House — ROMI 709,9%. От старта с дублями страниц и постановки целей и задач продвижения, проведения технического аудита, аналитики, анализа поисковых фраз, выбора контента до экспериментов с метатегами, увеличения среднего чека и планов на будущее...
04 09 2026 3:11:11
Пример пользовательских отчетов в Google ***ytics, созданных за 15 минут...
03 09 2026 4:17:50
Данные, приведенные в исследовании, могут помочь в составлении медиапланов по продвижению в интернете....
02 09 2026 19:33:39
Спойлер: чтобы получить наилучший результат с наименьшими усилиями и затратами времени....
01 09 2026 4:27:39
Продвигаем медицинский проект. Кроме цифр рассказываем, что и как делали...
31 08 2026 8:34:46
Запускаем первую таргетированную рекламу. Мы составили инструкцию для начинающих таргетолов, а также для SMM-специалистов, которым регулярно нужно рекламировать товары и услуги. Читайте подробнее!...
30 08 2026 2:16:45
10 заданий, которые заставят пошевелить мозгами и придумать нестандартные решения. Используйте их (видоизмените, адаптируйте) под свой продукт или услугу. Создайте крутую кампанию, креатив или предложите это своей комaнде....
29 08 2026 5:32:26
Пора сделать так, чтобы мобильная версия сайта приносила прибыль....
28 08 2026 12:13:51
В ТОПе ошибок — метатеги, разметка, заголовки h1-h6 и внутренние ссылочные факторы....
27 08 2026 16:42:14
Всё, что вы хотели знать о функциях вычисляемых полей, но боялись спросить....
26 08 2026 18:33:49
За каждым проектом стоят прежде всего люди. Как часто мы пользуемся сложными системами и не думаем о том, кто их создал и продолжает развивать? Интересно о том, кто и что стоит за «ПланФиксом». Читайте далее!...
25 08 2026 3:32:11
Как узнавать эффективность страницы мобильного приложения в Play Маркете? Учимся работать в Google Play Developers Console....
24 08 2026 19:13:40
Насколько классно продается женская одежда в интернете....
23 08 2026 4:43:32
Используйте эти данные в составлении медиапланов. Зная стоимость клика в тематике или регионе, коэффициент конверсии на сайте, вы можете оценить объем инвестиций в рекламу и прогнозировать стоимость конверсии. Читать дальше!...
22 08 2026 12:16:47
Как GTM помогает настроить расширенную электронную торговлю (enhanced ecommerce)....
21 08 2026 2:50:53
Правильное продвижение сайта и нестандартные предложения по привлечению трафика...
20 08 2026 12:27:22
Тошнота и процент воды в тексте не должны превышать допустимую норму, иначе не только пользователи уйдут с сайта, поисковые роботы понизят сайт в рейтинге. Узнать больше!...
19 08 2026 19:51:20
Как превратить неактивных подписчиков в вовлеченную аудиторию....
18 08 2026 2:31:26
Apple, Google и Samsung — давайте вспомним новинки месяца....
17 08 2026 6:39:28
Как быстро создавать и запускать объявления, а также массово вносить правки в кампании с помощью файла Excel...
16 08 2026 7:13:59
Инсайты от специалистов агентства Netpeak о популярных тематиках, тенденциях, особенностях продвижения в карантин и кризис, новых правилах для контекстной рекламы и других способов продвижения. Чтобы узнать больше, читайте далее!...
15 08 2026 10:44:12
Все про kpi охват в социальных медиа или как оценивать эффективность работы SMM-специалиста?...
14 08 2026 19:22:50
Вес страниц — один из факторов ранжирования в поисковых системах Google и Яндекс....
13 08 2026 3:39:58
Знаете, как доменная зона Германии связана с криптовалютой, или почему в доменной зоне маленького острова площадью 10 квадратных километров зарегистрировано 20 млн доменов? Сейчас расскажем!...
12 08 2026 3:39:56
Хакерские атаки с развитием технологий коснулись постаматов и доставки заказов из интернет-магазинов...
11 08 2026 23:27:40
Новая партнерская программа Netpeak agency. Как получить теплого лида, стабильный пассивный доход, разместить свой логотип и ссылку на сайт на сайте Netpeak и другие бонусы партнерской программы. Узнать больше!...
10 08 2026 15:32:40
Как надолго завоевать доверие покупателя с помощью грамотного email-маркетинга? Примеры обращений к клиенту в рассылках, текст писем для привлечения и благодарности за покупку....
09 08 2026 10:41:29
Думаете, что почитать об онлайн-продвижении и диджитал-маркетинге? Вам сюда. Мы собрали топ интересных сайтов, блогов, каналов и пабликов...
08 08 2026 4:52:15
Как в Google Search можно найти мелодию по примерному напеву...
07 08 2026 15:43:33
Как мы с помощью Call Tracking узнали настоящую рентабельность контекстной рекламы для магазина мебели...
06 08 2026 16:51:57
Прогнозирование с помощью «Планировщика ключевых слов»....
05 08 2026 14:55:34
Советы от Agorapulse, Smarp, Readdle, Competera, Leadfeeder, Smartly, Toggl, Grammarly, .Io Media, MacPaw, Depositphotos, Promorepuplic, OWOX, Serpstat, Quokka и TemplateMonster для развития SaaS-продуктов...
04 08 2026 11:16:28
Придумать цепляющие заголовки для 1000 репостов в соцсетях — что можно узнать в результате анализа миллиона заголовков в блогах....
03 08 2026 7:15:37
Представление — это уровень доступа в аккаунте Google ***ytics. На уровне представления можно предоставить или ограничить доступ пользователей к отчетам и аналитическим инструментам...
02 08 2026 1:45:52
Нет прокрастинации! Советы для личной эффективности от руководителя двух стартапов...
01 08 2026 7:28:33
Разбор типичных ошибок в рекламе Google Shopping как обязательного инструмента рекламы в Google. О минус-словах, геолокации, структуре сайта и обновлении объявлений. Что именно важно? Читайте дальше!...
31 07 2026 15:30:31
Зачем маркетологи и аналитики обращаются к языку программирования R в повседневной работе нужно и какая польза от программирования на R в интернет-маркетинге в интервью Алексея Селезнева...
30 07 2026 8:30:10
Продвижение музыкальной библиотеки. Со старта сотрудничества, трафик и основной ключ сайта клиента вырос в 10 раз. Регион продвижения — США....
29 07 2026 16:53:40
Как работать с негативом и что делать, чтобы его было меньше/не было вовсе....
28 07 2026 5:21:38
Какие возможности дает Chrome DevTools для SEO-оптимизации...
27 07 2026 4:39:35
Как новичкам в ecommerce выбрать первую CMS. Преимущества и трудности работы для крупных и небольших интернет-магазинов, русскоязычные и мультиязычные, с активной техподдержкой и без нее. Узнать больше!...
26 07 2026 23:48:35
Новости агентства Netpeak за предыдущий квартал. Появление отдела веб-аналитики. Открыли услугу «Создание и ведение блогов». Добавили мультивалютность. Глобально изменили скрипт Netpeak X-Reference. Запустили серию писем. Что еще?...
25 07 2026 14:46:29
Инструкция по созданию изображений для сториз, email-рассылок и объявлений в соцсетях...
24 07 2026 14:10:19
Гарантии результата в SEO: миф или реальность. Спрос рождает предложение, поэтому не удивительно, что существует большое количество SEO-студий. Сразу возникает резонный вопрос, как могут существовать те компании, которые не дают гарантии вообще?...
23 07 2026 11:52:16
Еще:
понять и запомнить -1 :: понять и запомнить -2 :: понять и запомнить -3 :: понять и запомнить -4 :: понять и запомнить -5 :: понять и запомнить -6 :: понять и запомнить -7 ::
