В частности, при работе с большими данными затраты могут быстро возрасти, а производительность может быстро снизиться, когда данные начнут накапливаться.
BigQuery предлагает возможность создавать многораздельные таблицы, что помогает предотвратить резкий рост затрат и снижение производительности.
Конечно, вариант использования должен соответствовать идее разделения, даже несмотря на то, что большинство вариантов использования больших данных должны подходить туда тем или иным образом.
Google предоставляет три различных способа разбиения таблиц BigQuery:
Время загрузки. Таблицы разбиты на разделы в зависимости от времени, в течение которого они были загружены.
Столбец единиц времени. Таблицы разбиты на разделы на основе столбца единиц времени. Допустимые значения: TIMESTAMP, DATE и DATETIME.
Целочисленный диапазон - таблицы разбиваются на разделы на основе целочисленного столбца.
Что такое разбиение на единицы времени и как оно работает?
Простое разбиение таблицы на разделы похоже на разбиение таблицы на несколько «под» таблиц. Каждая из этих секционированных таблиц имеет свой уникальный ключ для легкого и быстрого доступа.
При использовании секций вы можете запускать запросы только к определенному набору секционированных таблиц и, таким образом, сохранять много строк, и ваш запрос не должен повторяться для проверки ваших условий.
Использование разбиения «столбец единицы времени» в BigQuery работает следующим образом:
Вы указываете столбец таблицы с типом TIMESTAMP, DATE или DATETIME и степенью детализации разделов. Допустимые значения здесь - ежечасно, ежедневно, ежемесячно и ежегодно. Если вы выберете ежедневно, каждая секционированная таблица будет содержать все строки за определенный день, поэтому вы можете быстро получить все строки или отфильтровать строки за определенный день, не просматривая ВСЕ другие секционированные таблицы.
Предполагая, что вы пишете хотя бы одну запись в день в течение одного года (365 дней) и используете ежедневное секционирование, у вас будет 365 секционированных таблиц в конце года. Это потому, что каждый раздел отражает данные за один день.
Вы можете увидеть «обычную» несекционированную таблицу и сразу после нее ту же таблицу с ежедневным разделом в столбце «День» на следующих двух изображениях.
Имейте в виду, что при фактическом использовании разделения ваши запросы также должны будут это отражать. Например, не будет преимуществ, если таблица разделена по столбцу даты, но при этом не выполняется фильтрация по дате, что приводит к повторному сканированию всей таблицы.
Насколько это может улучшить затраты / производительность?
Теперь, когда мы рассмотрели основы, один из наиболее важных вопросов - «Насколько это может улучшить затраты / производительность?». Нет ответа, подходящего для всех вариантов использования, но давайте приведем пример, который вы можете использовать в качестве образца для своих расчетов.
Чтобы перейти к некоторым конкретным значениям, предположим следующее:
- Вы используете ежедневное разбиение, поскольку вам нужно запрашивать данные либо за один день, либо за диапазон дней.
- У вас уже есть данные за шесть лет, что дает ~ 2190 дней на многораздельные таблицы.
- Вы равномерно распределили свои данные по всем дням (конечно, маловероятно, но здесь мы должны сделать предположение).
- Общий размер ваших данных составляет 72 ТБ - ›12 ТБ в год -› 1 ТБ в месяц - ›~ 33 ГБ в день
Для несекционированных таблиц ваши запросы всегда должны будут выполнять полное сканирование таблицы, в результате чего будет обработано 7,2e + 13 байт (72 ТБ) и будет выставлен счет для каждого запроса.
Пример №1: выполнение запроса в один конкретный день
Запрос данных только для одной конкретной даты означает, что запрос может напрямую обращаться к необходимому разделу и обрабатывать этот один раздел, но не должен обрабатывать другой 2189.
Выше мы предполагали, что один день / раздел содержит около 3,3e + 10 (33 ГБ) данных. Это означает, что мы будем обрабатывать только ~ 0,046% (33 ГБ / 72 000 ГБ или 1/2190) от всего набора данных, что приведет к сокращению использования данных и, следовательно, затрат на 99,954%!
Пример №2: выполнение запроса для 30-дневного диапазона
Запрос 30-дневного диапазона аналогичен приведенному выше примеру и может быть упрощен до 30/2190 = ›1,37%. Несмотря на то, что это намного больше, чем в примере №1, это все равно уменьшение на 98,6% размера и, следовательно, затрат по сравнению с полным сканированием таблицы!
Фактическая стоимость примеров:
Теперь давайте посмотрим, сколько будет стоить каждый приведенный выше пример запроса по сравнению с несекционированной таблицей. Google взимает 5 долларов США за ТБ в соответствии с официальными документами о ценах, что дает следующие значения.
Несекционированная таблица: 5 долларов США * 72 ТБ = 360 долларов США.
Пример №1: 5,00 долларов США * 0,033 ТБ = 0,165 доллара США
Пример № 2: 5,00 долларов США * 0,99 ТБ = 4,95 доллара США
Разница огромная, правда? 😏
Конечно, разбиение на разделы увеличит производительность пропорционально снижению затрат. Когда запрос должен оценивать на 99% меньше строк, можно представить, что для его завершения требуется примерно на 99% меньше времени.
Почему вы экономите еще больше денег с помощью разметки
Несмотря на то, что приведенные выше примеры уже значительно сокращают затраты, они могут быть еще больше, когда мы думаем о втором драйвере затрат в BigQuery. Затраты на хранение.
Как уже упоминалось в первой статье, BigQuery имеет концепцию активного и длительного хранения. Хотя Google взимает только половину за долгосрочное хранение, никаких недостатков это не приносит. Единственный критерий - отсутствие изменений данных таблицы в течение как минимум 90 дней. Когда вы используете многораздельные таблицы, каждая секция рассматривается отдельно в отношении цены на долгосрочное хранилище.
Давайте возьмем наши предположения относительно размера данных (33 ГБ в день) и распределения (равномерно) и проверим стоимость их хранения. Кроме того, мы предполагаем, что в течение последних 90 дней мы добавляли некоторые данные каждый день.
Это предположение приводит к тому, что несекционированная таблица никогда не достигает статуса долгосрочного хранения, поскольку в таблице ежедневно вносятся изменения. С другой стороны, у многораздельной таблицы всегда всего 90 разделов в активной памяти, а оставшиеся 2100 разделов в долгосрочном.
За активное хранилище Google в настоящее время взимает 0,02 доллара США за ГБ для местоположения в США (многорегиональный).
Несекционированная таблица: 0,02 USD * 33 ГБ * 2190 = 1445,40 USD в месяц
Разделенная таблица: 0,02 USD * 33 ГБ * 90 дней + 0,01 USD * 33 ГБ * 2100 дней = 59,40 USD + 693,00 USD = 752,40 USD в месяц
Как видите, с помощью секционирования вы можете не только значительно сократить затраты на анализ по требованию, но и значительно снизить затраты на хранение.
Руки вверх
Теперь давайте познакомимся с реальной реализацией и проанализируем результаты запроса.
Поскольку мы уже занимались настройкой проекта BigQuery на Node.js в прошлой статье, мы не будем повторять это здесь. Я также ожидаю, что глобально инициализированная переменная под названием bigquery будет существовать в следующих фрагментах кода и что доступен набор данных BigQuery.
Сначала мы должны создать обе таблицы с одним и тем же набором данных для сравнения некоторой фактической, хотя и относительно небольшой, несекционированной таблицы с многораздельной таблицей.
Создание новых таблиц с данными из файла CSV
Для этого примера я подготовил файл CSV, содержащий ~ 72 КБ строк в следующем формате:
дата, имя, событие
2021–01–04 00: 00: 00.000000, Адама, 4
2021–01–01 00: 00: 00.000000, Тома, 2
2021–01–02 00: 00: 00.000000, Джей, 1
2021–01–03 00: 00: 00.000000, Джей, 3
….
Первая строка «дата» будет иметь тип DATETIME и, следовательно, в следующем формате: ГГГГ-ММ-ДД ЧЧ: ММ: СС [.SSSSSS]
Фактические значения не так важны; Я просто удостоверился, что у меня есть четыре разных даты, упомянутых там, поэтому BigQuery создает четыре раздела, которые я позже могу запросить соответствующим образом. Затем я назвал CSV-файл «dump.csv» и добавил его под своим файлом сценария «importCSV.js», содержание которого вы можете увидеть в следующем фрагменте кода.
Метод importCSV () в приведенном выше фрагменте похож на то, что вы должны знать из предыдущей статьи. Разница в том, что вместо вызова некоторого createTable (…) мы обращаемся к таблице (…), а затем загружаем в нее исходные данные вместе с определением схемы с помощью метода load (…).
«Специфическая для раздела» часть метода importCSV - это только переменная timePartitioning, переданная в функцию.
Когда вы проверяете объект, сценарий передает этот параметр; это довольно просто. Мы определяем тип как ДЕНЬ, сообщая BigQuery, что он должен создавать ежедневные многораздельные таблицы. Кроме того, с помощью свойства поле мы приказываем BigQuery использовать столбец с именем дата в качестве столбца раздела. Вы можете найти официальную документацию для объекта TimePartitioning здесь.
После того, как мы выполнили приведенный выше сценарий и создали две таблицы, теперь мы можем запускать и сравнивать наши запросы со следующим фрагментом.
Сценарий очень простой. В «runQueryWithStatistics ()» мы запускаем предоставленный запрос, а затем передаем завершенное задание какому-либо методу, который выводит статистику задания на консоль. Важным моментом здесь является то, что мы устанавливаем useQueryCache: false, чтобы BigQuery не использовал кеширование в фоновом режиме. Это важно, поскольку мы не можем сравнивать показатели, когда они обслуживаются кешем. Конечно, вы всегда должны держать этот флаг на «true» - значение по умолчанию - при работе в продакшене!
Метод printJobStatistics () еще проще. Он более или менее получает доступ только к определенным релевантным свойствам и выводит их на консоль. Об отдельных показателях мы поговорим чуть позже.
Перейдем к методу main (). Здесь определены переменные и вспомогательный метод для создания нашего запроса. «CreateQuery ()» принимает только «tableId» в качестве входного параметра для генерации того же запроса для несекционированной и многораздельной таблицы, который отличается только таблицей, на которую указывает ссылка.
После этого мы запускаем метод runQueryWithStatistics (). Один раз в таблице non_partitioned_table и один раз в таблице partitioned_table и запишите результаты.
Вы можете увидеть результат выполнения вышеуказанного сценария в следующем фрагменте.
Анализ статистики
Теперь, когда у нас есть статистика вакансий, мы должны понять и интерпретировать ее соответствующим образом, чтобы проверить наши предположения.
Попадание в кеш - это значение говорит нам только о том, что кеш не был атакован, и мы можем быть уверены, что не получили кешированный результат, который искажает наши показатели.
Поскольку мы явно отключили кеш, это всегда должно быть false. Если это кажется «правдой», что-то не так.
Затраченное время - определяет время, которое потребовалось для выполнения запроса от его начала до времени окончания.
Здесь мы видим, что секционированная таблица работает немного быстрее (~ 19%). Исходя из своего опыта, я могу сказать, что в нашем примере это не имеет смысла, поскольку мы все еще находимся в диапазоне миллисекунд и имеем только около 2 МБ данных. Здесь побочные эффекты занимают слишком много времени по сравнению с фактическим запросом.
Этот показатель становится значимым, если время обработки запроса достигает нескольких секунд или минут.
Обработано разделов - указывает, сколько разделов прочитал запрос. В случае несекционированных таблиц это всегда 0.
В выводе нашей многораздельной таблицы мы видим, что она считывает только один раздел, поскольку запрашивалась только одна конкретная дата, а не диапазон дат.
Прочитанные строки - сколько строк запрос должен был прочитать для завершения.
Здесь мы уже видим, что два варианта сильно различаются. У нас есть ~ 73 тыс. Строк в случае таблицы разделов и только ~ 12 тыс. Строк в таблице разделов. Количество прочитанных строк уже уменьшилось на ~ 83,6%, что отражается в следующей метрике «обработанные байты».
Обработано байт - количество байтов, которое должен был обработать запрос.
Как и в случае с «прочитанными строками», здесь мы видим явное уменьшение размера. В то время как несекционированный запрос должен сканировать полные ~ 1,6 МБ данных, секционированный запрос должен был обработать только ~ 0,26 МБ, что привело к сокращению ~ 83,4%.
Счет в байтах - количество байтов, которое Google взимает с вас за этот запрос.
Обычно теперь можно ожидать, что количество «выставленных байтов» также будет отличаться примерно на 83%. Но внимательный читатель заметит, что значения совпадают.
Это первое неожиданное поведение имеет относительно простое происхождение. На странице ценовой информации BigQuery вы найдете следующую цитату:
«… С минимум 10 МБ данных, обрабатываемых на запрос».
В нашем случае, когда мы обрабатываем только 1,6 МБ и 0,26 МБ в наших двух запросах, Google округляет затраты до минимально 10 МБ оплаченных байтов, отражая это в нашей статистике.
В случае фактических данных, когда у вас есть несколько ГБ, ТБ или даже ПБ, «оплаченные байты» будут значительно отличаться и вести себя пропорционально «обработанным байтам».
Разделение квот и распространенные ошибки, о которых следует знать
Максимальное количество разделов. При разделении таблиц BigQuery существует жесткое ограничение в 4000 разделов на разделенную таблицу.
Пример:
Когда вы используете ежедневное разбиение на свою таблицу, вы можете охватить не более 4000 дней (10,96 лет) данных. Запись данных за большее количество дней в таблицу приведет к ошибкам или отклонениям на стороне BigQuery.
Несмотря на то, что маловероятно, что вы столкнетесь с этим очень быстро при ежедневном разбиении, при использовании почасового разбиения вы столкнетесь с этим примерно через 5,47 месяца.
Потоковая передача данных в многораздельные таблицы. При потоковой передаче данных в многораздельные таблицы можно было ожидать, что BigQuery немедленно добавит данные в нужный раздел. К сожалению, это неправда. BigQuery сначала запишет потоковые данные во временный раздел под названием __UNPARTITIONED__, пока данные находятся в потоковом буфере. В настоящее время нет возможности очистить этот буфер вручную. Таким образом, вам нужно подождать, пока BigQuery автоматически очистит этот буфер после того, как станет доступен определенный объем неразмеченных данных или по прошествии определенного времени. Google не предоставляет точных цифр относительно указанных сумм.
Хотя при запуске команды BigQuery:
‘bq show - format = prettyjson YOUR_GCP_PROJECT: DATASET_ID.TABLE_ID’
Чтобы узнать, сколько байтов и строк находится в буфере потоковой передачи, вы можете проверить свойства «EstimatedBytes» и «EstimatedRows» объекта «streamingBuffer».
Требовать фильтр раздела. При работе с многораздельными таблицами рекомендуется всегда включать флаг «requirePartitionFilter» для многораздельной таблицы при ее создании. Имея этот флаг, BigQuery всегда будет требовать фильтр предиката для SQL-запроса, который пытается получить доступ к таблице. Этот флаг помогает предотвратить запросы, которые могут случайно запустить полное сканирование таблицы вместо доступа к определенному разделу или набору разделов и привести к высоким затратам.
Не используйте "ИЛИ" для своего фильтра раздела. Кто-то может подумать, что этого достаточно, чтобы разделенный столбец был в условии фильтрации его оператора SQL. Несмотря на то, что использование «ИЛИ» по-прежнему запускает полное сканирование таблицы, для соответствия фильтру разделов не требуется.
Пример:
Запустит полное сканирование таблицы:
ГДЕ (partition_column = «2021–01–01» ИЛИ f = «a»
Не запускает полное сканирование таблицы:
ГДЕ (partition_column = «2021–01–01» И f = «a»
Я всегда рекомендую изолировать условие фильтрации раздела, а затем выполнить «И» с фактическими фильтрами, которые вы хотите применить к строкам. Это также помогает предотвратить неправильные / дорогие запросы.
Пример:
ГДЕ partition_column = «2021–01–01» И (ALL_THE_OTHER_CONDITIONS)
Что дальше?
В следующих статьях мы узнаем, как создавать кластеризованные таблицы и работать с ними в BigQuery, чтобы еще больше повысить производительность.
об авторе
Паскаль Цвикирш - технический руководитель группы Usercentrics. Он управляет кросс-функциональной командой, состоящей из нескольких внешних и внутренних разработчиков, обеспечивая при этом беспрепятственную связь между техническим миром, менеджерами по продуктам и заинтересованными сторонами.
Паскаль в течение нескольких лет был разработчиком внешнего интерфейса, а теперь специализируется на внутренних службах, облачных архитектурах и принципах DevOps . Он подробно изучил оба мира. Этот опыт а сотрудничество с менеджерами по продуктам дало ему возможность создавать полноценное масштабируемое программное обеспечение.
Помимо работы в качестве руководителя группы разработчиков B2B, он любит делиться своими глубокими знаниями и идеями в статьях, которые он создает как независимый технический писатель.
→ Чтобы узнать больше, подпишитесь на меня в LinkedIn.
