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



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

Как мы вошли в топ Product Hunt — четкая инструкция от Serpstat

Подробный алгоритм успешного питча на Product Hunt на примере Serpstat...

08 06 2026 9:43:54

Что такое консалтинг от Netpeak и почему он нужен инхаус-комaндам

Что такое консалтинг от Netpeak и почему он нужен инхаус-комaндам Где найти лидера для своей инхаус-комaнды маркетологов...

07 06 2026 17:21:17

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

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

06 06 2026 1:24:55

Как настроить динамический ремаркетинг в Google Рекламе без Google Merchant Center

Как настроить динамический ремаркетинг в Google Рекламе без Google Merchant Center Создаем фид товаров, настравиваем код и списки динамического ремаркетинга для интернет-магазинов в странах, где отсутствует Google Merchant Center....

05 06 2026 9:38:15

Сколько стоил клик в Google Ads в первом квартале 2017

Сколько стоил клик в Google Ads в первом квартале 2017 Анализ цены за клик в 25 тематиках и 852 городах Украины, России, Казахстана, Болгарии...

04 06 2026 1:48:18

Как настраивать представления в Google ***ytics — подробное руководство

Как настраивать представления в Google ***ytics — подробное руководство Представление — это уровень доступа в аккаунте Google ***ytics. На уровне представления можно предоставить или ограничить доступ пользователей к отчетам и аналитическим инструментам...

03 06 2026 12:16:55

Как продвигаться в Instagram, если у вас B2B-бизнес

Как продвигаться в Instagram, если у вас B2B-бизнес Распространенные ошибки продвижения B2B-компаний + стратегии того, как наращивать число активных подписчиков и потенциальных клиентов....

02 06 2026 9:49:15

Как создать рассылку push-уведомлений. Чек-лист эффективной рассылки от Notix

В статье рассказываем, какие бывают push-уведомления, когда их используют, даем чек-лист и пошаговую инструкцию по созданию успешной push-рассылки....

01 06 2026 21:45:51

Отзыв про курсы продвижения сайтов от Netpeak

Отзыв про курсы продвижения сайтов от Netpeak Виктория Игнатьева рассказала про опыт обучения на наших курсах....

31 05 2026 1:33:46

Как найти PBN конкурента

Как найти PBN конкурента Как найти сетку сайтов конкурентов — рассказываем о популярных методах и секретных фишках. Бонус — инструкция по выявлению PBN конкурентов. Читать дальше!...

30 05 2026 17:49:30

Кейс по SEO-продвижению сайта в тематике «эко-товары и натуральная косметика»: ROMI 129%

SEO-продвижение сайта, торгующего натуральной косметикой...

29 05 2026 21:38:21

Как повысить органический трафик на 124% при полумиллионой аудитории — кейс тендерной площадки SmartTender

Как повысить органический трафик на 124% при полумиллионой аудитории — кейс тендерной площадки SmartTender Рассказываем, как комaнда Netpeak работала над ростом органики и повышением видимости приоритетных страниц сайта по релевантной семантике....

28 05 2026 3:17:17

«Демография» и «Технология». Сравнение отчетов в GA4 и Google Universal ***ytics

Решили перейти на Google ***ytics 4? Предлагаем разобраться, чем отличается инструмент от предыдущей версии и изучить новинки в отчетах «Демография» и «Технология»....

27 05 2026 8:25:40

Старт нового рейтинга зарплат Serpstat: как коронакризис повлиял на заработок интернет-маркетологов в 2020

Старт нового рейтинга зарплат Serpstat: как коронакризис повлиял на заработок интернет-маркетологов в 2020 Узнайте зарплату интернет-маркетологов с новым рейтингом от Serpstat....

26 05 2026 19:43:24

8 ненужных пунктов в SEO-аудите

Глоссарий глупых ошибок в аудите от топовых SEO-агентств...

25 05 2026 3:30:16

Сниппеты в выдаче Google –– что изменилось? Исследование Serpstat

Данные из контента страницы и основного заголовка h1 вместо метатега title. Что показало исследование Serpstat....

24 05 2026 20:53:49

Email-маркетинг, который приносит заявки, продажи, лояльность и всё на свете — версия Netpeak

Email-маркетинг, который приносит заявки, продажи, лояльность и всё на свете — версия Netpeak Email-маркетинг — хороший полицейский, спам — плохой....

23 05 2026 14:48:12

Как работать с автостратегиями Google Ads

Как работать с автостратегиями Google Ads И научиться делать свою рекламу прибыльной. Читайте про типы автостратегий и способы их оптимизации....

22 05 2026 7:28:32

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

13 неочевидных и пpaктически применимых SEO-фишек — круглый стол 8P 2017 Лучшие идеи круглого стола о SEO с участием Тараса Гущи, Сергея Карпенко, Алексея Чекушина, Дмитрия Шахова и других экспертов...

21 05 2026 13:29:52

Как провести аудит рекламных кампаний в Google Ads

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

20 05 2026 0:52:36

Как настроить автоматические правила в Facebook? Подробное руководство

Как настроить автоматические правила в Facebook? Подробное руководство Правила Facebook позволяют пользоваться автоматизированным контролем над эффективностью рекламы. Читайте, как уменьшить количество ручной работы с помощью автоматических правил...

19 05 2026 23:50:26

Сленг в Telegram — каналы-оборотни, приходы и закрепы

Сленг в Telegram — каналы-оборотни, приходы и закрепы Вам точно нужен ВП? Тогда утвердите сабж, чтобы умещался в три слова, скиньте линк и дескрипшн и распознайте канал-оборотень. Полная подборка сленговых слов, значение которых не всегда найдете в Google...

18 05 2026 8:19:52

Сколько заpaбатывают интернет-маркетологи

Сколько заpaбатывают интернет-маркетологи Результаты четвертого опроса среди IT-специалистов по уровню зарплат интернет-маркетологов с интересными итогами от количества участников до самой высокооплачиваемой должности и среднего опыта в рынке. Узнайте больше!...

17 05 2026 12:20:25

Как увеличить продажи из РСЯ — рост транзакций на 427% за месяц

Как увеличить продажи из РСЯ — рост транзакций на 427% за месяц Как увеличить количество транзакциий, сохранив прибыльность кампании в РСЯ?...

16 05 2026 11:15:19

Как готовить баннеры для мобильной рекламы

Как готовить баннеры для мобильной рекламы Как сделать креативный мобильный баннер. Зачем нужны особенные баннеры для мобильных? Что делать с картинкой? Что делать с текстом? Что делать с другими рекламными форматами? Дополнительные элементы. Обо всем этом детально и спримерами...

15 05 2026 14:48:36

Стратегия, позиционирование и бюджетирование: основные маркетинговые боли малого бизнеса Украины

Мы собрали и проанализировали ответы 270 владельцев и маркетологов...

14 05 2026 22:46:29

Как настроить рекламу в Facebook Messenger, Instagram Direct и даже WhatsApp

Как настроить рекламу в Facebook Messenger, Instagram Direct и даже WhatsApp Что нужно знать перед запуском рекламы в мессенджерах....

13 05 2026 12:59:25

Как работой гореть, но не сгорать — личный опыт

Как работой гореть, но не сгорать — личный опыт Первая реакция на перегорание — послать все к чертям собачьим. Очень заманчиво, но правильно ли убегать от всех проблем?...

12 05 2026 6:38:53

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

Партиционирование таблиц в Google BigQuery — понятная инструкция Как упростить работу с данными и сэкономить на оплате сервиса...

11 05 2026 9:22:27

Как проводить ежедневные встречи в режиме WFH — чек-лист Netpeak

Дейли — ежедневный комaндный митинг, на котором комaнда синхронизируется по ключевым моментам работы и по текущим задачам. Делимся чек-листом агентства Netpeak по организации дейли на удалёнке....

10 05 2026 10:49:10

Как повысить продажи брендовых украшений на 8-е марта. Кейс Pandora

Как повысить продажи брендовых украшений на 8-е марта. Кейс Pandora Стратегия продвижения, благодаря которой удалось снизить стоимость заявки от покупателей. Рассказываем об особенностях проведения акций в нише. Узнать больше!...

09 05 2026 15:36:48

SEO-продвижение интернет-магазина по продаже аксессуаров для путешествий: ROMI 743%

SEO-продвижение интернет-магазина по продаже аксессуаров для путешествий: ROMI 743% Результаты грамотного внедрения технического SEO-аудита...

08 05 2026 8:54:14

Бизнес-фреш: Дэвид и Кестрел Ли о «нешелковом» пути западных брендов к сердцам пользователей в Китае

Бизнес-фреш: Дэвид и Кестрел Ли о «нешелковом» пути западных брендов к сердцам пользователей в Китае Покупать в Китае. Как брендам покорить сердца и кошельки китайских пользователей...

07 05 2026 13:37:43

Проверка уникальности контента

Проверка уникальности контента Почему важно знать детали создания и защиты уникального контента? Потому что это позволит защитить ваш сайт от санкций поисковых систем из-за неуникального контента...

06 05 2026 5:27:23

Как стать менеджером проектов в Netpeak

Программа знаний и умений, чтобы стать Project Manager в агентстве интернет-маркетинга...

05 05 2026 13:30:34

Как перестать суетиться и начать контролировать свою продуктивность

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

04 05 2026 1:46:47

Кейс по таргетированной рекламе в тематике «бижутерия»: ROMI 90%

Кейс по таргетированной рекламе в тематике «бижутерия»: ROMI 90% Итоги одной из наших первых онлайн рекламных кампаний в Play...

03 05 2026 7:30:41

Google Ads — как работает планировщик ключевых слов

Подобрать нужные слова поможет проверка Google Рекламы....

02 05 2026 4:36:24

Google BigQuery — зачем нужна облачная база данных

Google BigQuery — зачем нужна облачная база данных Как бесплатно пользоваться преимуществами, особенностями и дополнительными инструментами сервиса...

01 05 2026 13:35:47

Какие данные о пользователях можно собирать сайтам, а какие — нет

Какие данные о пользователях можно собирать сайтам, а какие — нет General Data Protection Regulation (GDPR): ответы на вопросы....

30 04 2026 12:55:31

Анализ пути в Google ***ytics 4: как ускорить процесс покупки на сайте

Анализ пути в Google ***ytics 4: как ускорить процесс покупки на сайте Анализ пути — это как многокaнaльные последовательности в Universal ***ytics, только лучше....

29 04 2026 19:35:42

Как сохранить бизнес, если война. Реальная история бренда женской одежды Natali Bolgar

Как сохранить бизнес, если война. Реальная история бренда женской одежды Natali Bolgar Работать сейчас — не зазорно, а просто необходимо для страны...

28 04 2026 21:35:57

Как связать Microsoft Power BI и Google BigQuery

Как связать Microsoft Power BI и Google BigQuery Как визуализировать данные Google BigQuery в Power BI? Пошаговая инструкция от руководителя отдела веб-аналитики Netpeak Алексея Селезнева...

27 04 2026 14:10:43

Как определить ключевую метрику LTV перед запуском Apple Search Ads

Как определить ключевую метрику LTV перед запуском Apple Search Ads Расчет прогноза по LTV позволит более точно задать параметры рекламной кампании в Apple Search Ads на начальном этапе. Как это делают в RadASO, читайте в статье....

26 04 2026 23:24:17

Делаем первичный сбор семантики грамотно, экономя время на чистках

Делаем первичный сбор семантики грамотно, экономя время на чистках Как составить список, работа с которым займет меньше времени, чем обычно. На примере обработки запросов в ювелирной тематике....

25 04 2026 23:22:58

Управление репутацией в сети — особенности работы с отзовиками

Управление репутацией в сети — особенности работы с отзовиками Информация о любой организации распространяется в интернете очень быстро, особенно негативная. Поэтому необходимо учиться управлять своей репутацией....

24 04 2026 12:13:41

Закрытый софт Netpeak для проверки позиций под кодовым названием «Камасутра»

Закрытый софт Netpeak для проверки позиций под кодовым названием «Камасутра» Проверка позиций: бесценный онлайн софт для SEO-специалистов :)...

23 04 2026 16:53:17

Как привлечь дополнительный трафик с помощью SNDS-кампаний — кейсы Netpeak

Как привлечь дополнительный трафик с помощью SNDS-кампаний — кейсы Netpeak Как получить больше конверсионного трафика из поиска и не выйти за рамки KPI? Запустите SNDS-кампании...

22 04 2026 14:56:17

Как перестать платить за мусор в ссылочном индексе — исследование Serpstat

Как перестать платить за мусор в ссылочном индексе — исследование Serpstat При выборе SEO сервиса принято ориентироваться на объем ссылочной массы, но надежен ли такой подход? Большая база не всегда значит качество содержащихся в ней данных, а количество нередко обеспечивают спамные ссылки в отчетах....

21 04 2026 0:46:48

Интервью с Марком Цукербергом про Facebook Home, деньги и будущее коммуникаций

Интервью с Марком Цукербергом про Facebook Home, деньги и будущее коммуникаций Основатель фейсбука опубликовал на WIRED крутое интервью...

20 04 2026 10:45:56

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