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



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

SEO-продвижение для сайта сети кинотеатров в Украине — как за год увеличить приток органического трафика на 630%

SEO-продвижение для сайта сети кинотеатров в Украине — как за год увеличить приток органического трафика на 630% Перед нами стояла цель увеличить поисковый трафик и видимость, не охватывая новые города. Через 8 месяцев после старта сотрудничества сайт переносили на новый front-end. Узнать больше!...

08 06 2026 16:33:31

Самые распространенные SEO-ошибки

Самые распространенные SEO-ошибки В ТОПе ошибок — метатеги, разметка, заголовки h1-h6 и внутренние ссылочные факторы....

07 06 2026 20:58:18

«Цена? Ответили в direct». Почему магазины в Instagram скрывают цены?

«Цена? Ответили в direct». Почему магазины в Instagram скрывают цены? Пишите стоимость товара под постами. Но это не универсальный совет. Если ваша ниша с длинным циклом продаж, можно сделать исключение. Что ещё нужно знать про ответ в директ? Узнать!...

06 06 2026 0:33:45

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

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

05 06 2026 19:18:26

Как работать с Open Graph

Как работать с Open Graph Что нужно сделать, чтобы превью веб-страниц в социальных сетях стало магнитом для пользователей?...

04 06 2026 17:58:13

7 признаков некачественных онлайн-курсов

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

03 06 2026 12:53:32

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

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

02 06 2026 15:38:45

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

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

01 06 2026 5:40:38

Почему Google Merchant Center может заблокировать аккаунт

Почему Google Merchant Center может заблокировать аккаунт На что обращает внимание система и менеджеры Google при проверке сайта и аккаунта....

31 05 2026 16:45:54

Как запустить рекламную кампанию Call-Only для мобильных устройств

Как запустить рекламную кампанию Call-Only для мобильных устройств Учим правильно платить за верхние строчки показов объявления: настройка быстрых звонков из поисковых объявлений «Только номер телефона» Google Ads...

30 05 2026 0:30:37

Clubhouse — как создать комнату, клуб и удалить спящего

Clubhouse — как создать комнату, клуб и удалить спящего Возможности и перспективы Clubhouse, лайфхаки для пользователей и бонус в конце...

29 05 2026 5:28:40

Как снизить стоимость транзакций на 48% с помощью автоматических стратегий в Google Рекламе — кейс Citrus.ua

Как снизить стоимость транзакций на 48% с помощью автоматических стратегий в Google Рекламе — кейс Citrus.ua Каждый владелец знает, что залог успеха эффективного продвижения кампаний — качественная ежедневная оптимизация, а эксперименты — путь к новым горизонтам и еще более качественным результатам...

28 05 2026 17:31:42

Сколько стоил клик в Google Ads и Facebook в Украине в третьем квартале 2018 года

Сколько стоил клик в Google Ads и Facebook в Украине в третьем квартале 2018 года Данные по 43 миллионам кликов в 27 тематиках и 391 городах страны...

27 05 2026 12:34:33

Панель вебмастеров Google — анализируем данные без паники

Панель вебмастеров Google — анализируем данные без паники Как правильно читать отчеты в Панели вебмастеров Google — объясняем на примерах из пpaктики....

26 05 2026 12:24:43

Кейс по контекстной рекламе в тематике «детская обувь»: ROMI 110%

Кейс по контекстной рекламе в тематике «детская обувь»: ROMI 110% Магазин детской обуви: конкретные цифры в кейсе по продвижению....

25 05 2026 19:53:37

Как BuzzSumo заработал $2,5 миллиона за первый год — growth-кейс для SaaS

Стив Рейсон назвал 20 уроков, которые BuzzSumo усвоил на пути к топу самых популярных сервисов для контент-маркетологов....

24 05 2026 1:36:57

Global Tag Site — обзор фреймворка от Google

Global Tag Site — обзор фреймворка от Google Почему имеет смысл выбрать его, а не Google Tag Manager?...

23 05 2026 10:18:23

Как работать с товарными фидами в Google Рекламе и Яндекс.Директ

Как работать с товарными фидами в Google Рекламе и Яндекс.Директ Образец товарного фида можно использовать при запуске динамических объявлений в поисковой сети Яндекса и Google, в кампаниях со смарт-баннерами в Яндекс.Директ, в динамических медийных кампаниях Google Рекламы, в товарной рекламе — с помощью Google Merchant Center....

22 05 2026 0:55:54

Как провести тендер и выбрать подрядчика в сфере интернет-маркетинга — чек-лист

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

21 05 2026 16:30:55

Как провести интервью: инсайты и секреты Head of Content Inweb Наты Заяць

Как провести интервью: инсайты и секреты Head of Content Inweb Наты Заяць Как подготовиться и сделать из интервью полноценную историю, которая будет интересна аудитории...

20 05 2026 20:52:14

Обзор Netpeak Checker 2.1 — мультифункционального инструмента для массового анализа и сравнения сайтов

Обзор Netpeak Checker 2.1 — мультифункционального инструмента для массового анализа и сравнения сайтов Netpeak Checker – это мультифункциональный инструмент для массового анализа и сравнения сайтов, который помогает выполнять самые разнообразные задачи в области интернет-маркетинга....

19 05 2026 10:38:19

Кейс по продвижению интернет-магазина отопительного оборудования: ROMI 86%

Кейс по продвижению интернет-магазина отопительного оборудования: ROMI 86% Через тернии к кейсу. SEO-продвижение интернет-магазина отопительного оборудования с оплатой за трафик...

18 05 2026 18:33:39

Продвижение мобильного приложения: как вовлечь пользователей с помощью рекламы в Facebook?

Реклама Facebook для вовлечения пользователей помогает привлечь внимание к приложению и удержать пользователей в условиях перенасыщенного рынка....

17 05 2026 7:43:20

Продвижение мобильных приложений: анализируем данные в Search Console

Продвижение мобильных приложений: анализируем данные в Search Console Отслеживаем эффективность приложения в Search Console на примере OLX.KZ...

16 05 2026 9:29:51

Кейс по контекстной рекламе новостного сайта: качественный трафик за $0,02

Кейс по контекстной рекламе новостного сайта: качественный трафик за $0,02 В этом кейсе я не смогу похвастаться ROMI (возврат маркетинговых инвестиций), так как его герои — это новостные порталы. Основная задача — трафик. Но не просто трафик, а трафик по низкой стоимости....

15 05 2026 13:13:56

SEO-продвижение для СМИ — рост органического трафика на 569%, аудитории — в пять раз

SEO-продвижение для СМИ — рост органического трафика на 569%, аудитории — в пять раз Продвижение казахстанского онлайн-издания, новостника informburo.kz....

14 05 2026 1:51:50

Анонимный покупатель на страже роста продаж — опыт агентства Netpeak

Анонимный покупатель на страже роста продаж — опыт агентства Netpeak И эксперт, и агент. Анонимный покупатель — специальная проверка бизнес-процессов до и во время рекламной кампании...

13 05 2026 21:43:20

Экономим время с Impact Mapping — кейс разработки мобильного приложения

Экономим время с Impact Mapping — кейс разработки мобильного приложения Заказчик всегда приходит с идеей. В этой идее может быть все продумано до мелочей, а может быть только каркас. В любом случае мысли клиента следует систематизировать так, чтобы с ними было удобно работать....

12 05 2026 8:26:17

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

Крауд-маркетинг предполагает взаимодействие с целевой аудиторией с помощью рекомендательных комментариев на форумах, сайтах вопросов и ответов, в блогах, на других площадках...

11 05 2026 20:13:42

Как не сломать работу автостратегий Google, когда на сайте произошел сбой — используем «Исключение данных»

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

10 05 2026 1:30:57

Как мы внедрили BI-аналитику в отделе контекстной рекламы

Как мы внедрили BI-аналитику в отделе контекстной рекламы Зачем внедрять BI-решение для бизнеса, какие функции отдел может решить с помощью автоматизации? Кейс Netpeak....

09 05 2026 15:54:45

Как измерять ROI и зачем это делать предпринимателям

О том, как рассчитать коэффициент ROI (ROMI) для выявления эффективности и прибыльности рекламных кампаний...

08 05 2026 23:36:43

Дропшиппинг: как за два месяца снизить стоимость привлечения лида на 25%

Дропшиппинг: как за два месяца снизить стоимость привлечения лида на 25% Дропшип предполагает минимум затрат со стороны предпринимателя. Например, не нужно думать о логистике, держать склад. Можно минимизировать и траты на рекламу. Узнать как!...

07 05 2026 1:56:35

Как запустить R и R Studio в Google Cloud Platform

Как запустить R и R Studio в Google Cloud Platform Используем возможности Google Cloud Platform в работе с R...

06 05 2026 21:41:37

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

Как настроить Google Shopping — руководство для новичков Как создать аккаунт в Google Merchant Center, загрузить и изменить фид с информацией о товарах, — делимся тонкостями настройки кампаний шопинга...

05 05 2026 6:39:21

Digital стратег Даша Шигаева о работе, отчетах и киборгах

Digital стратег Даша Шигаева о работе, отчетах и киборгах Работа — рекламировать. Интервью с директором по стратегии агентства Smartica/Skykillers....

04 05 2026 19:25:34

SEO-анализ конкурентов: подробное руководство

SEO-анализ конкурентов: подробное руководство Как узнать особенности бизнеса в нише и что позаимствовать у конкурентов для SEO...

03 05 2026 1:58:21

Как сделать SPA-сайты SEO-Friendly?

Как сделать SPA-сайты SEO-Friendly? Всем, кто собирается перейти на Single Page Application, но переживает, что они поссорятся с поисковыми системами. Правильно переживаете!...

02 05 2026 15:59:21

MeDiConf — новая конференция по медицинскому маркетингу от Netpeak и Octopus Events

MeDiConf — новая конференция по медицинскому маркетингу от Netpeak и Octopus Events 29 ноября в Киеве в UNIT.City — конференция по медицинскому маркетингу MeDiConf...

01 05 2026 10:38:30

Сколько стоил клик в Google Ads в первом квартале 2016 — исследование Netpeak

Сколько стоил клик в Google Ads в первом квартале 2016 — исследование Netpeak Мы решили узнать, как дорого обходятся рекламодателям клики по объявлениям в разных странах и тематиках, чтобы читателям блога было удобнее планировать бюджет на рекламные кампании....

30 04 2026 20:52:57

Закон о языках и ваш сайт: как создать украинскую версию и где искать удачные кейсы

Закон о языках и ваш сайт: как создать украинскую версию и где искать удачные кейсы Рекомендации и мнения экспертов по одной из самых противоречивых тем украинского интернет-прострaнcтва....

29 04 2026 21:46:52

История успеха office-expert.kz: перенесли email-рассылки на новый сервис и получили ROMI 440%

История успеха office-expert.kz: перенесли email-рассылки на новый сервис и получили ROMI 440% В течение месяца нам удалось наладить рассылку с новой платформы....

28 04 2026 20:39:44

Как увеличить CTR и open rate в email-рассылках: получаем больше переходов на сайт

Как увеличить CTR и open rate в email-рассылках: получаем больше переходов на сайт Делимся советами по оптимизации рассылок. Важны новые форматы писем, предварительное тестирование, смена тем письма, ярче CTA. Все это в сочетании с понятностью и искренностью не заставит ждать хороший CTR...

26 04 2026 4:31:13

Как обучать комaнду по методу 70:20:10

Как эффективно распределять бюджет и ресурсы на обучение сотрудников. Статья будет полезной для собственников бизнеса и HR-специалистов....

25 04 2026 1:42:15

Где лучше догонять пользователей: в Facebook или Google Рекламе — кейс интернет-магазина

Где лучше догонять пользователей: в Facebook или Google Рекламе — кейс интернет-магазина При использовании похожих инструментов важно не переборщить с количеством рекламы...

24 04 2026 21:59:20

Кейс по email-маркетингу для интернет-магазина книг Book24 — рост дохода в 2 раза

Кейс по email-маркетингу для интернет-магазина книг Book24 — рост дохода в 2 раза Когда email-маркетинг становится точкой роста. Кейс интернет-магазина книг Book24....

23 04 2026 22:43:45

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

SEOmoz создал систему, которая определяет релевантность страницы поисковому запросу....

22 04 2026 13:31:56

Как использовать футпринты в SEO-продвижении

Как использовать футпринты в SEO-продвижении Футпринт — это признак, по которому можно спалить PBN-сетку сайтов. Это как родинки. Если взять двух парней с родинкой на щеке, то не факт, что они братья. Но если родинки совпадают на щеке, локте и колене, то тут что-то подозрительное...

21 04 2026 22:38:17

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

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

20 04 2026 15:24:15

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