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



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

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

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

29 05 2024 12:54:21

Время, в которое мы живём — лучшее в истории. Давайте это замечать!

Время, в которое мы живём — лучшее в истории. Давайте это замечать! О ловушке медиа, Стивене Пинкере и почему в любой ситуации важно оставаться оптимистом....

28 05 2024 18:55:15

Сколько стоил клик в Яндекс.Директ в Казахстане в третьем квартале 2020 года — исследование Netpeak

Сколько стоил клик в Яндекс.Директ в Казахстане в третьем квартале 2020 года — исследование Netpeak Зная стоимость клика в вашей тематике или регионе, и коэффициент конверсии на вашем сайте, вы можете оценить объем необходимых инвестиций в рекламу и прогнозировать стоимость конверсии. Узнать больше!...

27 05 2024 10:17:27

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

Как быстро увеличить продажи — мнения руководителей отделов продаж Как повысить результаты продаж в режиме цейтнота. Советы новому руководителю от специалистов Netpeak Group. Больше подробностей — в посте...

26 05 2024 11:41:47

Эффект «Хода королевы» — как выросла популярность и доход шахматных приложений

Эффект «Хода королевы» — как выросла популярность и доход шахматных приложений Сериал «Ход королевы» взорвал интерес к шахматам, и вот насколько сильно...

25 05 2024 19:41:37

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

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

24 05 2024 22:58:15

10 ошибок молодого бизнеса в Instagram

10 ошибок молодого бизнеса в Instagram Как не нужно делать, запуская свой профиль и таргетированную рекламу в Instagram...

23 05 2024 8:44:47

Бизнес в сети — дайджест полезных статей о 8P и не только

Бизнес в сети — дайджест полезных статей о 8P и не только Как провести крутую конференцию по интернет-маркетингу, сделать трaнcляцию этой конференции и вместо сухих репортажей подготовить полезный контент для СМИ. Узнать больше....

22 05 2024 5:54:26

Исследование — сколько стоил клик в Яндекс.Директ в Казахстане во втором квартале 2021 года

Исследование — сколько стоил клик в Яндекс.Директ в Казахстане во втором квартале 2021 года Используйте эти данные в составлении медиапланов. Зная стоимость клика в тематике или регионе, коэффициент конверсии на сайте, вы можете оценить объем инвестиций в рекламу и прогнозировать стоимость конверсии. Читать дальше!...

21 05 2024 1:56:47

Эффективная работа с микроблогом Twitter: планирование твитов по времени

Эффективная работа с микроблогом Twitter: планирование твитов по времени Твиттереть нужно правильно. Интересная аналитики и планирование твитов по времени...

20 05 2024 0:16:33

Как настроить отправку писем с помощью языка R

Как настроить отправку писем с помощью языка R и как избежать подводных камней....

19 05 2024 18:36:23

Как найти фейковые 404 страницы с помощью Google Tag Manager

Как найти фейковые 404 страницы с помощью Google Tag Manager Как помешать фейковым 404 страницам испортить статистику вашего сайта?...

18 05 2024 22:58:25

Праздничные рассылки: примеры триггерных рассылок к праздникам

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

17 05 2024 2:38:54

Как мы снизили стоимость лида для клининга ковров в Эмиратах на 60%

Как мы снизили стоимость лида для клининга ковров в Эмиратах на 60% Услуга уровня элит-класса в конкурентной нише и не в самом простом для продвижения регионе. Рассказываем, как привели целевой трафик по минимальной цене. Узнать!...

16 05 2024 4:46:33

Как начать работу с BigData — загружаем данные в Google BigQuery

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

15 05 2024 6:47:12

15 примеров удачных маркетинговых онлайн-активностей к Новому году

15 примеров удачных маркетинговых онлайн-активностей к Новому году Адвент-календарь, конкурсы и викторины, праздничные линейки товаров, подборки подарков для родных и близких, опросы, тесты и другие интересности. Используйте, если нужны идеи и вдохновение....

14 05 2024 15:33:45

Чем отличается адвокат бренда от посла бренда?

Чем отличается адвокат бренда от посла бренда? Что такое посол бренда и чем он отличается от адвоката бренда?...

13 05 2024 5:50:20

White paper: что за маркетинговый зверь?

Хороший пост с примерами продающих «Белых книг»....

12 05 2024 9:42:23

Как рекламировать товары для HoReCa и выйти на окупаемость в первый месяц работ — кейс econom-service

Как рекламировать товары для HoReCa и выйти на окупаемость в первый месяц работ — кейс econom-service Ещё один проект, который продвигался во время карантина....

11 05 2024 10:37:17

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

Как растёт рынок электромобилей и почему экология здесь не главное: на примере Honda Почему популярность электромобилей растет и как это связан с продажей углеродных квот...

10 05 2024 15:18:19

Кейс по SMM в тематике «посуда»: фотоконкурс для стимуляции активности в сообществе

Кейс по SMM в тематике «посуда»: фотоконкурс для стимуляции активности в сообществе Фотоконкурс для стимуляции активности в сообществе, прироста фанов и поощрения лояльной аудитории....

09 05 2024 0:21:29

HTML5 объявления в Google Рекламе

HTML5 объявления в Google Рекламе Flash онлайн объявлений в формат HTML5: нововведение гугл рекламы...

08 05 2024 15:14:23

Почему трудоголики — не герои: ломаем стереотипы с «Rework»

Почему трудоголики — не герои: ломаем стереотипы с «Rework» «Rework» — одна из самых любимых книг нетпиковцев....

07 05 2024 23:58:31

Как обнаружить и снять лишнюю ссылочную массу

Как обнаружить и снять лишнюю ссылочную массу Ссылки нужно не только наращивать, но и постоянно посещать и мониторить качество уже существующих....

06 05 2024 19:59:19

Влияние ссылок из социалок на ранжирование в поисковиках

Влияние ссылок из социалок на ранжирование в поисковиках Выдержка из интервью Дэнни Салливаном с представителями компаний Google и Bing...

05 05 2024 9:30:47

Увольнение сотрудника. Алгоритм действий от Netpeak

Сотрудник не справляется? Значит с ним пора прощаться. Эмоции в сторону, действуем по алгоритму....

04 05 2024 16:25:33

Как правильно зарегистрировать сайт в поисковых системах Google, Яндекс, Bing, Mail.ru, Rambler и Yahoo

Как правильно зарегистрировать сайт в поисковых системах Google, Яндекс, Bing, Mail.ru, Rambler и Yahoo Бесплатная регистрация сайта в поисковых системах и каталогах – отличный способ увеличить объемы трафика и раскрутить собственный ресурс....

03 05 2024 10:58:16

Как оптимизировать YouTube-канал

Как оптимизировать YouTube-канал Инструкция по технической подготовке к продвижению...

02 05 2024 3:55:25

Карточка товара — как создать качественное описание

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

01 05 2024 14:55:10

Как запустить товарную рекламу, если у вас только YML-файл со списком товаров

Как запустить товарную рекламу, если у вас только YML-файл со списком товаров Создаем фид с помощью выгрузки товаров в формате yml и специального скрипта, который сделает всю (почти) грязную работу за вас....

30 04 2024 20:31:47

Как массово удалить в интернет-магазине страницы товаров, которые не приносят трафик

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

29 04 2024 10:31:46

Как нарисовать скелет сайта

Как нарисовать скелет сайта О создании структуры сайта на основе семантического ядра, работе с Xmind и таблицами онлайн...

28 04 2024 14:53:36

Big Data SEO — как заставить большие данные работать на ваш бизнес

Big Data SEO — как заставить большие данные работать на ваш бизнес Решение для поискового продвижения крупных проектов (от 300 000 страниц)....

27 04 2024 0:21:15

Группировка ключевых фраз для рекламных кампаний — подробный обзор методов

Группировка ключевых фраз для рекламных кампаний — подробный обзор методов Как правильно группировать ключевые фразы для релевантности рекламных кампаний...

26 04 2024 21:15:17

Визуализация данных с помощью DataDeck

Визуализация данных с помощью DataDeck Краш-тест сервиса визуализации данных: выбираем задачу, источники, создаём отчет с помощью DataDeck. Бонус в конце — лаконичные списки преимуществ и недостатков этого инструмента и приятный подарок для дочитавших...

25 04 2024 13:14:32

«Покажи свою работу»: Анастасия Здорикова, руководитель отдела маркетинга Netpeak Software

«Покажи свою работу»: Анастасия Здорикова, руководитель отдела маркетинга Netpeak Software Как научиться справляться со стрессом и находить в комaнду «тех самых» людей...

24 04 2024 19:58:30

Как работать с закладками в Power BI — подробное руководство

Как работать с закладками в Power BI — подробное руководство В этой статье я покажу пример визуальной работы с закладкой и пошагово разберу процесс их создания и применения для удобства визуализации данных...

23 04 2024 9:29:22

Что нужно понять перед запуском онлайн-рекламы — девять самых важных вопросов к себе и PPC-специалисту

Что нужно понять перед запуском онлайн-рекламы — девять самых важных вопросов к себе и PPC-специалисту Читайте, если не хотите «слить» бюджет. Определите конкретные цели и УТП, соберите аналитику, идите к специалисту без опыта работы в конкретной нише и другие рекомендации эксперта. Читайте подробнее в статье!...

22 04 2024 22:28:43

Кейс по контекстной рекламе сайта туристической компании: увеличение количества онлайн-бронирований на 100%

Кейс по контекстной рекламе сайта туристической компании: увеличение количества онлайн-бронирований на 100% Стратегия контекстной рекламы для лендинга турагенства: увеличение количества онлайн-бронирований на 100%...

21 04 2024 22:33:12

Подборка обучающих материалов для начинающего аналитика

Подборка обучающих материалов для начинающего аналитика Список курсов, книг, каналов и пабликов для работы с данными...

20 04 2024 15:53:44

7 уроков о всплывающих окнах для мобильных, которые мы выучили благодаря A/B-тестам

Выясняем, есть ли жизнь у мобильных всплывающих окон, и как их сделать правильно...

19 04 2024 18:45:53

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

Сколько стоил клик в Google Ads в третьем квартале 2016 — исследование Netpeak Алексей Селезнев проанализировал, как дорого обходятся рекламодателям клики по объявлениям в 25 тематиках и 92 странах....

18 04 2024 9:11:38

Как загрузить фото в Instagram с компьютера

В статье описан ручной и самый безопасный из существующих метод...

17 04 2024 18:32:18

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

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

16 04 2024 1:18:52

Недостатки популярных CMS интернет-магазинов: 1C-Битрикс, Magento, OpenCart, WooCommerce, CS-Cart

Недостатки популярных CMS интернет-магазинов: 1C-Битрикс, Magento, OpenCart, WooCommerce, CS-Cart Как новичкам в ecommerce выбрать первую CMS. Преимущества и трудности работы для крупных и небольших интернет-магазинов, русскоязычные и мультиязычные, с активной техподдержкой и без нее. Узнать больше!...

15 04 2024 10:42:14

Как добавить микроразметку с помощью Google Tag Manager

Как добавить микроразметку с помощью Google Tag Manager Стоит установить и освоить Google Tag Manager для расширения представления сайта в выдаче...

14 04 2024 2:27:14

TikTok, Banda, Rocket, I am IDEA и McCann Kyiv — идеи для видеоконтента

TikTok, Banda, Rocket, I am IDEA и McCann Kyiv — идеи для видеоконтента Инсайты Диви Конф 2021, онлайн-конференции о видеомаркетинге....

13 04 2024 2:31:38

Времена меняются: что будет с Википедией в 2030 году?

Времена меняются: что будет с Википедией в 2030 году? Терялись ли вы некогда в лабиринте Википедии из внутренних ссылок и побочных статей? Представьте, насколько непредсказуемым он может стать через несколько десятилетий....

12 04 2024 18:28:31

Гeймификация: игровые сценарии для развития бизнеса

Гeймификация: игровые сценарии для развития бизнеса О том, как сделать сайты интереснее и эффективнее. Гeймификация — применение игровых сценариев и элементов вне игровых контекстов. Это не про создание игр, это про поиск решений, которые помогут сделать любую работу интереснее. Читайте дальше!...

11 04 2024 6:21:54

Черная пятница 2021 в Европе и Украине. Как изменились традиционные даты распродаж и топы бестселлеров

Черная пятница 2021 в Европе и Украине. Как изменились традиционные даты распродаж и топы бестселлеров В этом году часть пользователей предпочла вернуться к привычным покупкам в оффлайн-магазинах....

10 04 2024 10:44:35

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