Партиционирование таблиц в 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-ом. Если вы обнаружили здесь неактуальную информацию, напишите об этом в комментариях.
Комментарии:
Подборка ненаучных наблюдений и ситуаций, в которых бывал каждый из нас...
25 04 2024 15:29:45
Энн Смарти (Ann Smarty), пожалуй, самая известная публичная личность в мире контент-маркетинга и поискового продвижения. За несколько лет она прошла путь от SEO-блогера до главного редактора авторитетного Search Engine Journal и CEO одного из самых популя...
24 04 2024 11:54:43
Как сделать красиво. Алгоритм подготовки красивых и понятных презентаций, рекомендации и полезные ссылки, а также простые правила хорошего тона в дизайне Google Презентаций. Узнать больше и сделать свою презентацию эффективнее!...
23 04 2024 18:46:26
14 пунктов, которые помогут вам лучше изучить свой бизнес и подготовить эффективную SMM-стратегию...
22 04 2024 0:31:19
Как снизить цену за привлечение клиента на 50%, развивая только кампании для пользователей десктопов....
21 04 2024 20:23:59
Раскрутить телеграм-канал с нуля до тысячи — реально. Раскрываем пошаговый алгоритм и даем рекомендации для SMM-специалистов....
20 04 2024 8:15:43
Много интересных фактов о работе мозга и новейших исследованиях социологии и психологии....
19 04 2024 6:23:28
Крутые площадки для нового инструмента: контекстщикам на заметку!...
18 04 2024 15:31:57
VPN — технология анонимного сетевого подключения. Она позволяет организовать подсеть в уже существующем соединении. Поверхностная сеть закрыта от внешнего доступа, потому вмешаться в нее не смогут. Чтобы узнать больше, читайте дальше!...
17 04 2024 6:49:51
Профессиональный уровень SEO-специалиста в штатных комaндах редко определяется конкретной категорией (Junior, Midle, Senior). Но большинство считает себя мидлами. Узнать больше!...
16 04 2024 15:52:37
3 часто задаваемых вопроса о раскрутке сайтов об эффективной организации SEO своими силами, способах проверки оптимизации сайта и о том, как быстро можно увидеть эффект от SEO. Узнайте больше!...
15 04 2024 3:56:28
Case по продвижению в Instagram. В любой непонятной ситуации — масштабируйте рекламные инструменты...
14 04 2024 8:47:27
Благодаря внедрению интеpaктивной формы подписки увеличили количество ежемecячных подписок на 100%, доход — на 51%, а коэффициент транзакций — на 71%....
13 04 2024 1:39:40
Новая инструкция, которую можно применять к любым задачам в Google Таблицах....
12 04 2024 1:58:42
Как мы создаем BI-решение для департамента поискового продвижения — первые шаги...
11 04 2024 11:18:23
Лидеров определили открытым голосованием. В ТОП-списки вошли CPA-сети, которые набрали больше всего голосов суммарно и по отдельным критериям....
10 04 2024 8:44:33
Когда саппорт-отдел не справляется, подключайте к работе ПланФикс и Телеграм....
09 04 2024 18:18:45
Ярослав Легенчук поделился историей успешного «переезда» площадки IT-продукта из Рунета в страны ЕС...
08 04 2024 22:14:26
Происходит все большая автоматизация рекламы от Google. И это хорошо....
07 04 2024 1:44:29
Примеры странной, классной и непонятной рекламы в соцсетях из моей ленты за февраль 2021 года...
06 04 2024 19:22:45
Почему контент-план — не тетка, или о том, в какой последовательности выкладывать статьи...
05 04 2024 5:43:26
Как и кто создает новые услуги в Netpeak и что нужно знать, уметь, чтобы это делать? Рассказывает Елена Воскобойник, специалист, которая иногда разpaбатывает их почти с нуля. Читать!...
04 04 2024 13:18:20
03 04 2024 10:22:17
Разбираемся, как определить самые эффективные источники лидов....
02 04 2024 18:57:11
Google «выкатил» алгоритм Панда 2.2, как пару недель назад обещал Мэтт Каттс....
01 04 2024 9:13:14
Полезные советы для тех, кто делает контекст своими ручками :)...
31 03 2024 13:13:41
Система почтового маркетинга. Как удержать доверия подписчиков одним приветственным письмом...
30 03 2024 18:47:15
Простая инструкция для новичков, как легко создать анимированные баннеры для рекламных кампаний с помощью бесплатного инструмента Google Web Designer. При создании баннера сервис предложит создать файл с нуля либо использовать шаблон. Узнайте обо всех возможностях!...
29 03 2024 5:48:42
Человеку стало плохо, он теряет сознание. Те, кто рядом, хотят помочь, но не знают, как это сделать. Давайте разбираться....
28 03 2024 9:18:27
Стоит ли торопиться с переходом на новый формат объявлений? Эксперимент PPC-специалистов агентства Netpeak....
27 03 2024 11:24:51
Как правильно рассчитать окупаемость рекламных кампаний SaaS-продуктов, получить по ним четкую аналитику, и что делать дальше....
26 03 2024 20:49:40
Денис Бигус, Bihus.info: с 13 до 457 000 подписчиков на YouTube за четыре года....
25 03 2024 7:31:36
Определение частотности запросов — задача первоочередной важности для составления адекватного семантического ядра, наполнения качественным контентом и контекстной рекламой...
24 03 2024 6:59:15
Как делать большие деньги даже, когда случаются кассовые разрывы и каким образом строить процессы внутри компании, чтобы сотрудники горели своим (вашим) делом? Узнать!...
23 03 2024 15:10:21
Как Blizzard реализует тимбилдинг, мотивирует сотрудников и превращает офис в музей...
22 03 2024 11:17:29
В 2019 году в цикл зрелости вошли 28 технологий и инструментов...
21 03 2024 21:13:18
Alexa Rank учитывает как число страниц, так и количество просмотров страниц. Alexa Rank 1, что означает, что все сайты и сервисы Google посещают больше всего посетителей в интернете....
20 03 2024 13:19:26
Создаем фид с помощью выгрузки товаров в формате yml и специального скрипта, который сделает всю (почти) грязную работу за вас....
19 03 2024 20:45:26
Данные, приведенные в исследовании, помогут в составлении медиапланов по продвижению в интернете. Понимания стоимость конверсии и необходимое количество конверсий, вы можете прогнозировать, сколько денег на рекламу нужно выделить....
18 03 2024 10:32:22
Как считать конверсии из Facebook, когда они происходят в Jivosite и Битрикс24....
17 03 2024 3:47:21
Думаете, что почитать об онлайн-продвижении и диджитал-маркетинге? Вам сюда. Мы собрали топ интересных сайтов, блогов, каналов и пабликов...
16 03 2024 3:20:37
Гифка — это зациклиненное видеоизображение, рисунок-ролик, крохотный фильм, без которого трудно представить современный сайт. Как быстро создавать гифки и получать профит....
15 03 2024 19:22:10
Как развить в себе такой EQ, как хочется, и далее использовать его для достижения успеха. Рецензия на книгу Дэниела Гоулмана «Эмоциональный интеллект в бизнесе»....
14 03 2024 8:33:57
Узнаем расширенную информацию о посещениях сайта с помощью языка R — кейс Netpeak...
13 03 2024 4:45:42
На вопрос «как все успеть?» хочется ответить что-то вроде «никак». Особенно, если дело касается задач, связанных с творчеством. Но можно и не срывать дедлайны. Читать дальше....
12 03 2024 17:44:49
Продвижение казахстанского онлайн-издания, новостника informburo.kz....
11 03 2024 20:39:13
Ссылка с сайта с высоким тИЦ уже не считается качественной. Хороший донор вычисляется по комплексу факторов. Большую часть работы придется делать вручную. Важно найти отличный сайт, с огромным количеством интересного контента, оцененного по достоинству читателями, и гармонично туда вписать свою статью со ссылкой...
10 03 2024 17:56:26
Пример того, как составить маркетинговый контент-план, анализируя успешных конкурентов...
09 03 2024 12:40:58
Опрос экспертов. Как правильно искать подрядчиков, которые станут партнерами для вашего бизнеса? Как долго работать с партнерами и не испортить отношения? На что обращать внимание в первую очередь, а на что можно закрыть глаза?...
08 03 2024 21:49:47
Лихие девяностые и самые ужасные сайты интернета: образцы, примеры, оформление....
07 03 2024 10:49:12
Еще:
понять и запомнить -1 :: понять и запомнить -2 :: понять и запомнить -3 :: понять и запомнить -4 :: понять и запомнить -5 :: понять и запомнить -6 :: понять и запомнить -7 ::