Партиционирование таблиц в 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-ом. Если вы обнаружили здесь неактуальную информацию, напишите об этом в комментариях.
Комментарии:
Возможности для специалистов по рекламе в Facebook, о которых знают далеко не все. Подробности — тут....
15 10 2026 14:18:36
кейсы по seo интернет-магазина модной одежды в Болгарии: как мы сняли негативный эффект от Google Penguin и получили ROMI (возврат маркетинговых инвестиций) 1428%...
14 10 2026 0:48:56
Ошибка, которая может привести к полной потере контроля над вашим сайтом. Рассказываем, что это такое, какие могут быть причины поломки и как исправить. Узнать больше!...
13 10 2026 20:22:43
В этом кейсе я не смогу похвастаться ROMI (возврат маркетинговых инвестиций), так как его герои — это новостные порталы. Основная задача — трафик. Но не просто трафик, а трафик по низкой стоимости....
12 10 2026 20:46:15
Как снизить стоимость лидов путём использования новых технологий...
11 10 2026 4:29:13
Запускаем первую таргетированную рекламу. Мы составили инструкцию для начинающих таргетолов, а также для SMM-специалистов, которым регулярно нужно рекламировать товары и услуги. Читайте подробнее!...
10 10 2026 16:58:32
Используем маску ввода для оптимизации сбора телефонных номеров в формах на сайте: кейсы агентства Netpeak...
09 10 2026 7:28:46
Как перенести сайт с http на https и сохранить трафик, потратив минимум времени....
08 10 2026 16:17:14
Как продавать новую и б/у технику, ювелирные изделия в интернете. Эксперимент показал, что в Google Shopping можно продавать не только новые товары. Узнать больше!...
07 10 2026 8:35:10
Бесплатная регистрация сайта в поисковых системах и каталогах – отличный способ увеличить объемы трафика и раскрутить собственный ресурс....
06 10 2026 7:19:23
Относящиеся к низкочастотным long-tail запросы при правильном подходе могут стать эффективным инструментом для увеличения трафика. Рассказываем почему, а главное — как....
05 10 2026 0:52:19
Три способа парсинга данных с помощью Owox BI Pipeline, Mix Data BI Import, Owox BI Power Upload....
04 10 2026 9:44:46
Как украинский бизнес использует Черную пятницу, чтобы увеличить продажи и привлечь клиентов. Одежда, детские товары, курсы английского языка, экзотические фрукты, постельное белье, посуда, бытовая техника, SMM-агентство и многое другое...
03 10 2026 23:18:13
О важных для разметки сайта тегах — alternate, hreflang, media, — и как их грамотно использовать при продвижении....
02 10 2026 2:23:43
При наложении санкций на сайт его страницы заметно понижаются в выдаче. При наличии бана ресурс полностью исчезает из поиска, включая брендовые запросы...
01 10 2026 16:42:43
Как Netpeak продвигал Cifrus.ru, один из ведущих магазинов цифровой техники в Москве....
30 09 2026 10:11:32
Подобрать нужные слова поможет проверка Google Рекламы....
29 09 2026 23:17:38
Старт любых активностей в сфере email-маркетинга — разработка стратегии...
28 09 2026 7:40:31
Дублирование контента на разных сайтах, ориентированных на рынки разных стран, например, на публику Канады...
27 09 2026 3:19:18
Как бесплатно отключить смс уведомление. Плюс — подсказки для маркетологов: как снизить негативное отношение к бренду. То есть, как красиво предоставить возможность отписаться от смс клиентам, и не слить бюджет на ненужные сообщения. Узнать больше!...
26 09 2026 11:15:42
Используя для продвижения контекстную рекламу, большинство рекламодателей в Украине отдают предпочтение системе Google Ads. Директ однозначно не стоит недооценивать. Почему? Читайте в статье!...
25 09 2026 11:55:50
Так называемый Humankindex (доброжелательный стиль управления) способствует инновациям в компании....
24 09 2026 5:49:53
Как создать и внедрить баннеры об использовании куки-файлов с помощью Google Tag Manager и OneTrust...
23 09 2026 3:10:49
Если ваш товар или услуга будут хорошо выглядеть на фото или целевая аудитория точно находится в Инстаграм, площадка для будущего блога определена. Узнать больше!...
22 09 2026 5:35:18
Алгоритм технической подготовки IP-адреса к массовым рассылкам...
21 09 2026 15:11:12
Бывают случаи, когда код Google ***ytics перестает вообще собирать статистику. Чтобы выявить сбой вовремя, обязательно настройте эти оповещения....
20 09 2026 11:31:55
За 10 месяцев сотрудничества увеличили небрендовый на 99%, а общий трафик — в два раза. Рассказываем не только о результатах, но и подходе создания реалистичного прогноза SEO-продвижения сайта. Читать дальше!...
19 09 2026 7:49:55
Нужно подобрать красивый шрифт? Готический или народный? Стандартный или фирменный? Как выбрать — рассказывает профессионал...
18 09 2026 2:10:58
Как с помощью Google Tag Manager отслеживать исходящие ссылки на сайте и загрузки файлов с него...
17 09 2026 15:52:46
Украинцы хотят заниматься коммерцией. И иногда делают это очень нестандартно. Собрали для вас подборку бизнесменов с особенным мышлением....
16 09 2026 14:42:59
95% заполняемости коливингов для сервиса аренды жилья....
15 09 2026 13:16:47
С помощью этой шпаргалки вы легко распределите акценты и начнете последовательно внедрять маркетинговую стратегию по продвижению мобильного приложения...
14 09 2026 9:40:31
Импорт из Google ***ytics в Excel — manual по ***ytics Edge....
13 09 2026 11:12:13
Как зарегистрироваться в приложении Facebook Business Manager и грамотно сделать и настроить аккаунт...
12 09 2026 7:56:58
Давайте разбираться, в чем именно обвиняют техногиганта разработчики и действительно ли такую рекламу считают циничной....
11 09 2026 0:56:56
Эффективность контекстной рекламы в тематике «тюнинг автомобилей»....
10 09 2026 0:29:26
Создаем список тем, которые заинтересуют пользователя — для этого используем простые сервисы по типу Вордстат. Также изучаем деятельность конкурентов. Узнать больше!...
09 09 2026 11:11:23
Рассылка ежемecячно занимает второе место среди источников реферального трафика BuzzFeed. Уроки увеличения базы подписчиков от BuzzFeed от базы до метрик оценки. Принцип BuzzFeed — как можно быстрее внедрять в рассылку то, что нравится читателям....
08 09 2026 6:19:15
Рассказываем про маркетинговые техники продающих email рассылок...
07 09 2026 3:17:18
Шпаргалка по размерам креативов для всех, кто запускает рекламу в соцсетях...
06 09 2026 0:47:57
Данные по 34 миллионам кликов в 24 тематиках и 386 городах...
05 09 2026 17:25:21
Если в сетке сайтов есть интересный домен, его можно использовать для дополнительной монетизации, а не только для ссылок. В таком случае важно знать, как правильно отслеживать трафик таких доменов....
04 09 2026 16:18:47
Как делать большие деньги даже, когда случаются кассовые разрывы и каким образом строить процессы внутри компании, чтобы сотрудники горели своим (вашим) делом? Узнать!...
03 09 2026 13:27:44
Как контролировать множество сайтов — полезные сервисы и советы для вебмастеров. Одно дело — несколько десятков сайтов, а что если сотни или тысячи? Как раз это подробно и разберем....
02 09 2026 1:27:25
В конце октября Яндекс начал открытое бета-тестирование новой версии Коммaндера. Я решила уже сейчас проверить все плюсы и минусы новой версии сервиса...
01 09 2026 7:27:23
Каждый владелец знает, что залог успеха эффективного продвижения кампаний — качественная ежедневная оптимизация, а эксперименты — путь к новым горизонтам и еще более качественным результатам...
31 08 2026 22:34:58
12 идей для видеоконтента и расскажем, где их лучше размещать, чтобы зрители стали клиентами. Как использовать видео для повышения продаж? Длинный, но все равно неполный список форматов. Читайте дальше и предлагайте свои варианты!...
30 08 2026 1:59:24
Четыре важных шага для новичка...
29 08 2026 8:29:31
Дэн Гридин, ненавистник холодных звонков и эксперт в области системного B2B маркетинга для сложных рынков, рассказал читателям блога о принципах работы на рынке во время шторма....
28 08 2026 16:23:59
Интернет-маркетинг это не просто способ заработать. Про работу sео оптимизатора в Netpeak из первых уст...
27 08 2026 3:15:38
Еще:
понять и запомнить -1 :: понять и запомнить -2 :: понять и запомнить -3 :: понять и запомнить -4 :: понять и запомнить -5 :: понять и запомнить -6 :: понять и запомнить -7 ::
