Партиционирование таблиц в 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-ом. Если вы обнаружили здесь неактуальную информацию, напишите об этом в комментариях.
Комментарии:
Как сделать креативный мобильный баннер. Зачем нужны особенные баннеры для мобильных? Что делать с картинкой? Что делать с текстом? Что делать с другими рекламными форматами? Дополнительные элементы. Обо всем этом детально и спримерами...
07 06 2023 6:30:19
Клиенты, партнеры, потенциальные сотрудники — для каждой аудитории есть своя платформа. Какими нужно быть в соцсетях, чтобы вас заметили, читайте об опыте Reface....
06 06 2023 10:25:21
Новый тип рекламных кампаний, который помогает расширить целевую аудиторию. Потенциальных покупателей можно найти среди людей не знакомых с вашим брендом....
05 06 2023 0:10:17
Case по продвижению в Instagram. В любой непонятной ситуации — масштабируйте рекламные инструменты...
04 06 2023 15:39:49
Составим его вместе...
03 06 2023 16:27:34
Интеграция видео в маркетинговую кампанию – решение, которое повысит ценность каждого посетителя сайта....
02 06 2023 1:36:45
Как регулярно мониторить перспективные товары, по которым можно запускать контекстную рекламу...
01 06 2023 1:26:19
Миллионы решили купить книгу гении и аутсайдеры на Amazon: удивительное исследование канадского журналиста...
31 05 2023 2:46:59
Профессиональный уровень SEO-специалиста в штатных комaндах редко определяется конкретной категорией (Junior, Midle, Senior). Но большинство считает себя мидлами. Узнать больше!...
30 05 2023 4:38:21
Перевод колонки директора по маркетингу Ahrefs: об органическом поисковом трафике, высокопотенциальных темах для бизнеса, уникальности и качестве контента, его продвижении. С реальными рекомендациями и примерами. Узнайте больше!...
29 05 2023 17:21:35
Перевод статьи Никки Джиллиленд из Econsultancy об уроках для маркетологов про то, каким должно быть хорошее видео. Узнайте 4 правила эффективного видеоконтента прямо сейчас!...
28 05 2023 0:43:27
Как Bigl.ua внедрили AMP и остались очень довольны результатом....
27 05 2023 18:49:28
Современные платформы телефонии и коллтрекинга — это не только про звонки. С Ringostat вы узнаете, откуда пришел клиент, как менеджер ему ответил и какая реклама «принесла» деньги....
26 05 2023 3:54:42
Как бесплатно отключить смс уведомление. Плюс — подсказки для маркетологов: как снизить негативное отношение к бренду. То есть, как красиво предоставить возможность отписаться от смс клиентам, и не слить бюджет на ненужные сообщения. Узнать больше!...
25 05 2023 15:10:58
Как использовать игровые механики для вовлечения аудитории....
24 05 2023 8:56:45
Более строгие требования к таким ресурсам обуславливают свои подходы....
23 05 2023 9:33:32
Google «выкатил» алгоритм Панда 2.2, как пару недель назад обещал Мэтт Каттс....
22 05 2023 2:16:38
Под какие треки работают интернет-маркетологи, копирайтеры и другие специалисты digital-рынка....
21 05 2023 7:25:42
Данные по 22,4 миллионам кликов в 46 тематиках и 392 населенных пунктах страны — собрана статистика исключительно по проектам агентства Netpeak. Узнать больше!...
20 05 2023 13:19:40
Опрос экспертов. Как правильно искать подрядчиков, которые станут партнерами для вашего бизнеса? Как долго работать с партнерами и не испортить отношения? На что обращать внимание в первую очередь, а на что можно закрыть глаза?...
19 05 2023 22:27:15
Простой способ отслеживания как внутренних, так и внешних битых ссылок. Узнать больше!...
18 05 2023 17:37:40
База самых распространенных CMS и модулей для их SEO-оптимизации....
17 05 2023 16:53:54
Что нужно делать на старте работ с интернет-магазином и как минимизировать риски для бизнеса онлайн, используя такие инструмента как SEO, PPC и Email-маркетинг — советы от эксперта. Читать дальше!...
16 05 2023 17:16:27
Как найти сетку сайтов конкурентов — рассказываем о популярных методах и секретных фишках. Бонус — инструкция по выявлению PBN конкурентов. Читать дальше!...
15 05 2023 9:46:10
Быстро разогнать рекламные кампании и получить рост конверсий при повышении CPA до 100%. Как этого добиться — читайте в новом кейсе....
14 05 2023 4:25:36
Очередной красивый пост о продвижении платформы email и sms-рассылок — UniSender...
13 05 2023 11:48:20
Реклама Facebook для вовлечения пользователей помогает привлечь внимание к приложению и удержать пользователей в условиях перенасыщенного рынка....
12 05 2023 5:40:35
Алексей Селезнев проанализировал более 7 миллионов кликов и посчитал, во сколько обходятся рекламодателям клики по объявлениям в разных странах и тематиках....
11 05 2023 16:44:39
Брендовые или витальные запросы содержат только название компании или бренда, без дополнительных уточнений...
10 05 2023 3:20:13
Алгоритм E-A-T теперь следит за экспертностью, авторитетностью и достоверностью контента. Что это значит?...
09 05 2023 18:39:32
Для защиты конфиденциальной информации в организации вводят режим коммерческой тайны. Подписание NDA важно не только с сотрудниками, но и со сторонними компаниями, имеющими доступ к конфиденциальной информации...
08 05 2023 18:53:54
Оптимизация и оформление вордовских текстов, переводчик, совместное редактирование, возможность залить картинку... Эти и много других опций дают дополнения для Google Документов....
07 05 2023 2:23:45
Как продавать новую и б/у технику, ювелирные изделия в интернете. Эксперимент показал, что в Google Shopping можно продавать не только новые товары. Узнать больше!...
06 05 2023 5:10:30
Психологический бестселлер. Как достичь гармонии во всех делах и начать получать удовольствие от жизни, — об этом читайте в новой рецензии Константина Рябенко на книгу Михая Чиксентмихайи «Поток»....
05 05 2023 3:30:36
Создаём страницы, которые быстро загружаются на мобильных устройствах....
04 05 2023 2:23:50
Узнаем расширенную информацию о посещениях сайта с помощью языка R — кейс Netpeak...
03 05 2023 19:46:25
Заработок в четверть миллиона. Как многофункциональная SEO-платформа Serpstat из Netpeak Group стала самым продаваемым продуктом на AppSumo — пошаговая история-инструкция...
02 05 2023 1:48:34
Делимся фишками, которые облегчат работу как новичкам, так и специалистам....
01 05 2023 15:22:55
Одесские магазины, где продают вечерние и свадебные платья нуждаются в особом внимании интернет-маркетинга. Как настроить контекстную рекламу для малого и среднего бизнеса со скромными маркетинговыми бюджетами...
30 04 2023 4:44:45
Что нового, Netpeak? Новые стратегии контекстной рекламы и email-маркетинга, новая рубрика в блоге, а также о специфике работы отдела продаж Netpeak в карантин и о законах построения партнерской программы — всё читайте в материале!...
29 04 2023 9:54:29
Основные законы приняты. Что это значит?...
28 04 2023 0:43:37
Настроить контекстную рекламу для сети гипермаркетов бытовой техники и электроники Mechta.kz...
27 04 2023 0:35:14
Почему лендинги на Тильде выгодны бизнесу, какие задачи он решает, о форматах работы по услуге, детально о ее составе и спецпредложениях от Netpeak и наших партнеров по настройке контекстной рекламы, сервисов на лендинге и даже промокод на скидку Yagla...
26 04 2023 18:52:35
11 типов расширений и результат их внедрения на примере запущенных рекламных кампаний...
25 04 2023 5:57:23
Supplemental results, они же «сопли» или дополнительные результаты — страницы и документы сайта, которые не находятся в основном индексе Google...
24 04 2023 5:18:17
Десктопная версия, совместная работа (collabs), сбор средств и новые эффекты в Reels. Рассказываем, что дают новые функции. Узнать больше....
23 04 2023 0:28:41
Новая инструкция, которую можно применять к любым задачам в Google Таблицах....
22 04 2023 10:37:23
Футпринт — это признак, по которому можно спалить PBN-сетку сайтов. Это как родинки. Если взять двух парней с родинкой на щеке, то не факт, что они братья. Но если родинки совпадают на щеке, локте и колене, то тут что-то подозрительное...
21 04 2023 21:25:47
в поисках нового источника трафика расскажем о Pinterest — современной поисковой сети c акцентом на визуальном контенте...
20 04 2023 18:38:46
Денис Бигус, Bihus.info: с 13 до 457 000 подписчиков на YouTube за четыре года....
19 04 2023 13:14:52
Еще:
понять и запомнить -1 :: понять и запомнить -2 :: понять и запомнить -3 :: понять и запомнить -4 :: понять и запомнить -5 :: понять и запомнить -6 :: понять и запомнить -7 ::
