Партиционирование таблиц в Google BigQuery — понятная инструкция > NetPeak - Независимость и осознанность
NetPeak Biz Tech    


Партиционирование таблиц в Google BigQuery — понятная инструкция

Партиционирование таблиц в 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-ом. Если вы обнаружили здесь неактуальную информацию, напишите об этом в комментариях.



Комментарии:

Контекстная реклама недвижимости на Кипре — в 5 раз больше лидов за 3 месяца

Контекстная реклама недвижимости на Кипре — в 5 раз больше лидов за 3 месяца Почему в высококонкурентных тематиках важно оптимизировать поведенческие показатели...

17 04 2026 14:24:45

Epic vs Apple — первый раунд битвы

Epic vs Apple — первый раунд битвы Следим за развитием событий и анализируем решение суда. Читать дальше!...

16 04 2026 8:52:35

5 способов повысить продажи с помощью социальных доказательств

5 способов повысить продажи с помощью социальных доказательств Чтобы увеличить продажи и улучшить их эффективность в новых условиях необходимо применять «социальные доказательства»....

15 04 2026 13:31:55

Как увеличить трафик блога на 1136% и привлечь тысячи новых пользователей — кейс блога Ahrefs

Как увеличить трафик блога на 1136% и привлечь тысячи новых пользователей — кейс блога Ahrefs Перевод колонки директора по маркетингу Ahrefs: об органическом поисковом трафике, высокопотенциальных темах для бизнеса, уникальности и качестве контента, его продвижении. С реальными рекомендациями и примерами. Узнайте больше!...

14 04 2026 18:58:28

Контент-маркетинг на США: 51 публикация за 5 месяцев

Контент-маркетинг на США: 51 публикация за 5 месяцев Кейс: контент-маркетинг сервиса в США, или как SerpStat покорил англоязычный мир...

13 04 2026 16:53:42

Как быстро перенести кампании в Facebook из одного аккаунта в другой

Экспорт/импорт с помощью Excel-файла или таблиц Google позволит скопировать и перенести кампании, группы объявлений и объявления в Facebook вместе с креативами, текстами, ссылками и utm-метками...

12 04 2026 15:13:44

Как работать с перформанс-рекламой

Как работать с перформанс-рекламой И зачем топ-менеджерам разбираться в настройках кампаний, повышать свою техническую грамотность? Показываем на примерах и раскладываем по полочкам....

11 04 2026 12:22:10

Netpeak Group купила конференцию eCommerce от OWOX

Конференцию eCommerce будет развивать комaнда Octopus Events...

10 04 2026 6:42:57

Как увеличить базу подписчиков на 100% за год — кейс domcomfort.ua

Как увеличить базу подписчиков на 100% за год — кейс domcomfort.ua Почти 50 тыс. контактов за год принесли формы подписки и регистрации....

09 04 2026 9:36:21

Что такое трекер мобильного приложения и как его выбрать

Что такое трекер мобильного приложения и как его выбрать Трекер — система аналитики мобильного приложения с возможностью отслеживания источника установок. Рассмотрим бесплатные и платные решения для аналитики мобильных приложений....

08 04 2026 10:56:31

Как создать чат-бота для сайта

Чат-боты упрощают процесс покупки продуктов онлайн. Netflix, Adidas, British Airways и другие крупные компании уже внедрили ботов в свои маркетинговые системы. Так ли это эффективно, если компании начинают отказываться от операторов в пользу ботов?...

07 04 2026 23:14:41

Какие виды гeймификации можно использовать в рассылках

Какие виды гeймификации можно использовать в рассылках Игровой формат отлично работает в email-рассылке. Но какой вид гeймификации интересен любой целевой аудитории? Давайте рассмотрим примеры....

06 04 2026 16:42:10

Обзор трех новых типов микроразметки schema.org

Обзор трех новых типов микроразметки schema.org FAQ (frequently asked questions), HowTo и Q&A (questions and answers)....

05 04 2026 17:30:57

Как жить копирайтеру на фрилансе в этом жестоком мире: от принятия неизбежного до выбивания денег

Как жить копирайтеру на фрилансе в этом жестоком мире: от принятия неизбежного до выбивания денег Суперспособности фрилансера: как понять свою прокрастинацию, распознать мошенников и решать проблемы, связанные с удалёнкой...

04 04 2026 9:58:41

Как за два месяца увеличить число заявок для оконного бизнеса? Кейс о сотрудничестве Netpeak и Steko

Как за два месяца увеличить число заявок для оконного бизнеса? Кейс о сотрудничестве Netpeak и Steko Количество обращений выросло на 43,27%, а рынок перегрет — десятки компаний предлагают свою продукцию, услуги. Что делать, чтобы люди выбрали вас среди других игроков рынка (помимо качественного выполнения своей работы)? Читайте дальше!...

03 04 2026 8:40:52

13 фишек маркетинга и продвижения проектов за рубежом — круглый стол 8P 2017

13 фишек маркетинга и продвижения проектов за рубежом — круглый стол 8P 2017 «Нет, нам нужна раскрутка и точка!» Список маркетинговых пpaктически применимых фишек....

02 04 2026 23:23:25

NPS: что такое Net Promoter Score и как начать его считать

NPS: что такое Net Promoter Score и как начать его считать Цель этой статьи — предупредить от опасной ошибки в маркетинге: бездумного и неправильного внедрения NPS-опросника....

01 04 2026 17:28:32

Как проанализировать эффективность страницы бренда в Facebook

Как проанализировать эффективность страницы бренда в Facebook Бренду нужно зайти на Фейсбук? Несколько очень полезных советов об измерении эффективности SMM....

31 03 2026 9:52:25

Как делать вирусный контент без лишнего текста — правила жизни Coub

Как делать вирусный контент без лишнего текста — правила жизни Coub Пользователи могут смотреть вирусные ролики без единого слова...

30 03 2026 8:58:48

3 фишки про продвижение постов в Facebook

3 фишки про продвижение постов в Facebook Если маркетер хочет привлечь к бренд-контенту дополнительное внимание, стоит воспользоваться опцией Promote post....

29 03 2026 17:14:47

Как digital-специалистам сохранить конкурентоспособность в 2021 году: 5 важных навыков и рекомендации по самопрезентации

Как digital-специалистам сохранить конкурентоспособность в 2021 году: 5 важных навыков и рекомендации по самопрезентации Digital-маркетинг развивается и модернизируется: новые методы работы, инструменты и технологии. И стабильно растут требования к digital-специалистам. Чтобы оставаться в профессии, нужно мониторить тренды, отслеживать тенденции и повышать знания....

28 03 2026 19:50:47

Как работать с аудиториями Google Ads в наблюдении

Давайте больше не будем сливать бюджет, выбирая неправильный параметр....

27 03 2026 14:48:31

Как лучше презентовать себя — несложные советы, которые игнорируют 90% специалистов

Как лучше презентовать себя — несложные советы, которые игнорируют 90% специалистов Как правильно оформлять свое портфолио, писать сопроводительные письма и делать посты в соцсетях. С помощью простой человеческой адекватности можно выигрывать у 90% других подрядчиков. Как это использовать? Читайте дальше!...

26 03 2026 22:25:16

Как покорить Бразилию — советы Depositphotos

Как покорить Бразилию — советы Depositphotos Идем в Бразилию: опыт Depositphotos + полезные факты....

25 03 2026 13:17:35

Рейтинг директоров по маркетингу среди ecommerce-проектов Казахстана

Рейтинг директоров по маркетингу среди ecommerce-проектов Казахстана Помимо полезного и качественного продукта, услуги, компании нужен мощный маркетинг. И возможно это только благодаря хорошему директору по маркетингу. Только так это работает. Кто лучшие в этой нише в Казахстане? Узнать!...

24 03 2026 9:50:43

Как использовать социальное доказательство в email-маркетинге

Как использовать социальное доказательство в email-маркетинге Рассказываем о приемах, которые помогут вам заинтересовать читателей рассылки....

23 03 2026 3:12:21

Системные улучшения Netpeak — демо услуг PPC, чат любителей блога и сервис поиска ошибок рекламных кампаний в Cluster

Новости, доработки и обновления в услугах, блоге, Академии блога и Кластере....

22 03 2026 0:40:48

Апдейты поисковых алгоритмов Google, Яндекса и ручные санкции — мануал для вебмастеров

Апдейты поисковых алгоритмов Google, Яндекса и ручные санкции — мануал для вебмастеров Почему сайт упал в выдаче и какой апдейт алгоритма поисковика на это повлиял...

21 03 2026 22:27:38

13 пpaктически применимых фишек для интернет-магазинов — круглый стол 8P 2017

Как создать популярный и удобный магазин в интернете, какой должна быть его раскрутка и многое другое, прозвучавшее на круглом столе 8P 2017...

20 03 2026 7:18:37

Как добавить время работы в объявления Google Ads

Как добавить время работы в объявления Google Ads Google внедрил новую возможность выделить объявление — автоматическое добавление часов работы. Давайте разберемся, как добавить эту полезную информацию в расширения объявлений...

19 03 2026 16:30:39

Контекстная реклама для продажи квартир в Киеве — как увеличить количество конверсий на 75%

Контекстная реклама для продажи квартир в Киеве — как увеличить количество конверсий на 75% Исправляем ошибки в рекламных кампаниях, аналитике и бюджете...

18 03 2026 21:45:17

Результаты тестирования похожих аудиторий в торговых кампаниях (Google Shopping)

Результаты тестирования похожих аудиторий в торговых кампаниях (Google Shopping) Обновление от Google: похожие аудитории можно использовать не только для медийных, но и для поисковых и торговых кампаний...

17 03 2026 3:11:46

Что такое URL — как создать корректный адрес сайта

Что такое URL — как создать корректный адрес сайта Понятные людям и роботам URL. Это уникальный набор символов, который дает возможность идентифицировать всевозможные ресурсы в интернете (страницы сайтов, файлы, изображения, видео и так далее). Чтобы узнать больше, читайте далее!...

16 03 2026 14:22:45

Контекст-стратегия: купил и полетел

Чтобы привлечь представителей малого и среднего бизнеса, мы разработали стратегии по контекстной рекламе. Это открывает двери агентства Netpeak проектам с самым скромным бюджетом...

15 03 2026 20:49:11

Итоги Всемирного дня шопинга: рост продаж в 11 раз в Украине и самые популярные товары

Итоги Всемирного дня шопинга: рост продаж в 11 раз в Украине и самые популярные товары Впереди ещё черная пятница и новогодние праздники. И успех массовых распродаж можно повторить....

14 03 2026 20:36:25

DMCA-алгоритм Google: закон об авторском праве в цифровую эпоху

DMCA-алгоритм Google: закон об авторском праве в цифровую эпоху Что значит всемирное обновление DMCA-алгоритма и приведет ли оно к полноценной защите авторских прав?...

13 03 2026 21:18:44

Интервью с Юлией Куриловой

Интервью с Юлией Куриловой Увлекательные истории от специалиста по контекстной рекламе....

11 03 2026 12:57:25

Как не терять звонки даже с двумя сотрудниками в call-центре

Как не терять звонки даже с двумя сотрудниками в call-центре Минимизация пропущенных звонков и ускорение обработки заявок с наглядными примерами из сервиса...

10 03 2026 23:15:37

18 способов собрать базу подписчиков для email-рассылок

18 способов собрать базу подписчиков для email-рассылок Будьте полезны, будьте удобны и будьте везде. Далее подробно — 18 способов собрать базу для email-рассылок. О пользе бесплатного, привлечении внимания, возможных активностях для аудитории, социальных доказательствах, предсказуемости, работе с Facebook и многом другом...

09 03 2026 8:50:22

Как настроить Google ***ytics — инструкция для новичков

Как настроить Google ***ytics — инструкция для новичков Шаги, которые необходимо выполнить для правильного сбора и анализа данных сайта...

08 03 2026 5:45:42

Форматы рекламных объявлений в Facebook и Instagram

Форматы рекламных объявлений в Facebook и Instagram Гайд по рекламе для SMM-, PPC-специалистов, блогеров и владельцев бизнеса...

07 03 2026 3:16:26

Как использовать видео для увеличения онлайн-продаж

Как использовать видео для увеличения онлайн-продаж 12 идей для видеоконтента и расскажем, где их лучше размещать, чтобы зрители стали клиентами. Как использовать видео для повышения продаж? Длинный, но все равно неполный список форматов. Читайте дальше и предлагайте свои варианты!...

06 03 2026 21:18:55

Кейс по SERM для DOM.RIA. Как работать с репутацией и за четыре месяца убрать негатив в топ-10.

Кейс по SERM для DOM.RIA. Как работать с репутацией и за четыре месяца убрать негатив в топ-10. Крупным брендам не избежать негатива в сети. Но с ним можно и нужно работать. Читайте успешный кейс для DOM.RIA....

05 03 2026 4:10:47

Стратегия ведения контекстной рекламы по воронке продаж на примере SaaS: шаблон и инструкция

Стратегия ведения контекстной рекламы по воронке продаж на примере SaaS: шаблон и инструкция Как найти индивидуальный подход к аудитории на каждом этапе воронки продаж? В этом вам поможет правильно составленная стратегия контекстной рекламы....

04 03 2026 12:17:23

Трудности с рекламными инструментами Facebook — что может пойти не так

Трудности с рекламными инструментами Facebook — что может пойти не так Объявление или кабинет блокируют из-за эмоджи. Что делать?...

03 03 2026 6:28:28

Продвижение недвижимости: как снизить стоимость заявки на 46% — кейс

Продвижение недвижимости: как снизить стоимость заявки на 46% — кейс Работа с умными рекламными кампаниями с оплатой за конверсии в контекстно-медийной сети. Особенности настройки и оптимизации в кейсе продвижения недвижимости. Узнать больше!...

02 03 2026 17:46:36

Как быстро проверить индексацию сайта в Google и Яндекс

Как быстро проверить индексацию сайта в Google и Яндекс Быстрая индексация: как оперативно узнать, попала ли важная для вас страница в поисковые системы Google и Яндекс....

01 03 2026 10:56:57

Как оформлять Google Таблицы, чтобы с вами хотели работать

Как оформлять Google Таблицы, чтобы с вами хотели работать 10 базовых правил оформления онлайн-таблиц. Оформление — основа таблицы. Как работать со столбцами и ячейками, комментариями и примечаниями, делать корректные цветовые выделения. Читайте далее....

28 02 2026 8:11:58

Создаем спрос на бренд с помощью видеорекламы — кейс в тематике «металлопластиковые окна»

Создаем спрос на бренд с помощью видеорекламы — кейс в тематике «металлопластиковые окна» Как продвинуть бренд в конкурентной тематике? Читаем новый кейс по видеорекламе....

27 02 2026 13:57:54

Еще:
понять и запомнить -1 :: понять и запомнить -2 :: понять и запомнить -3 :: понять и запомнить -4 :: понять и запомнить -5 :: понять и запомнить -6 :: понять и запомнить -7 ::