Помимо разделения таблицы на разделы для повышения производительности и затрат в BigQuery, существует еще один метод, называемый кластеризацией.

Небольшое предупреждение: кластеризация и разбиение не вытесняют друг друга. И в большинстве случаев имеет смысл использовать оба вместе.

Что такое кластерные таблицы?

Используя кластерные таблицы, BigQuery автоматически организует и размещает связанные данные на основе одного или нескольких выбранных столбцов. Здесь важно отметить, что порядок столбцов, который вы указываете для кластеризации, очень важен. Но об этом позже.

Поскольку BigQuery делает почти все автоматически - что касается кластеризации, - единственное, что вам нужно сделать, - это указать BigQuery, по каким столбцам и в каком порядке вы хотите, чтобы BigQuery кластеризовал ваши данные.

Затем BigQuery сохраняет данные в нескольких блоках во внутреннем хранилище BigQuery. Хранение данных в предварительно отсортированных блоках означает, что BigQuery может - в зависимости от предложения фильтра запроса - использовать эти блоки для предотвращения сканирования ненужных данных.

Согласно официальным документам, использование кластеризации значительно повысит производительность, когда отсканированная таблица - или раздел таблицы - превышает 1 ГБ.

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

Небольшой пример кластеризованной таблицы по сравнению с некластеризованной

На следующем изображении вы можете увидеть таблицу со случайно вставленными данными слева и кластеризованную таблицу столбцов name и event справа.

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

Взяв этот пример, вы уже можете представить себе возможное улучшение при запросе данных. В качестве примера предположим, что вы хотите запросить все значения, у которых name равно «1».

Без кластеризации BigQuery пришлось бы сканировать всю таблицу, потому что каждая строка могла бы содержать значение «1» в своем столбце name.

Благодаря кластеризации BigQuery знает, что этому условию фильтра соответствуют только первые три столбца. Это приводит к уменьшению количества сканируемых столбцов на 50%, поскольку все возможные результирующие строки располагаются друг под другом, а BigQuery может пропускать остальные 50%. Кроме того, уменьшение количества проверенных строк на 50% также означает снижение затрат на 50%, поскольку BigQuery взимает плату за считанные байты. Несмотря на то, что это может быть не на 100% точным, как мы увидим позже, этого достаточно, чтобы получить представление о кластеризации данных в таблицах.

Процесс пропуска определенных блоков при сканировании также называется обрезкой блоков. Даже несмотря на то, что BigQuery не обязательно создает один блок для каждого отдельного значения в кластеризованном столбце. Количество блоков, создаваемых BigQuery, сильно зависит от объема хранимых данных. Для 1 МБ данных, даже если он может содержать 100 различных значений в кластеризованном столбце, BigQuery не создаст 100 блоков и не сохранит 99% прочитанных байтов при фильтрации для одного конкретного значения. Но более (четкие) данные доступны внутри таблицы; тем эффективнее станет кластеризация. Как уже упоминалось выше, Google предложил минимальный размер таблицы 1 ГБ, чтобы увидеть значительные улучшения при использовании кластеризации. Но в следующем разделе мы увидим, что улучшения начнутся намного раньше.

Руки вверх

Теперь пришло время для практического программирования и увидеть несколько реальных примеров. Я разделил практическую часть на четыре небольших части.

  1. Простой скрипт для создания тестовых данных, которые мы можем импортировать и запрашивать.
  2. Импортируйте тестовые данные в BigQuery как обычную и как кластеризованную таблицу.
  3. Выполните один и тот же запрос к обеим таблицам и сравните результаты.
  4. Делаем предположение о размере блока кластеризации Google на реальном примере.

Создайте тестовые данные

Мы будем ориентироваться на первом изображении в этой статье для создания тестовых данных, где мы показали, как BigQuery организует кластеризованную таблицу по сравнению с обычной.

Это означает, что у нас будет два столбца. Один столбец представляет имя, другой - событие.



Оптимизация таблиц BigQuery с помощью секционирования: таблицы, секционированные по столбцам в единицах времени
Особенно при работе с большими данными затраты могут быстро возрасти, а производительность может быстро снизиться, когда данные начинают расти. … levelup.gitconnected.com



В отличие от предыдущей статьи (Оптимизация таблиц BigQuery с использованием секционирования: таблицы, секционированные по столбцам в единицах времени), мы будем динамически создавать данные с помощью сценария JavaScript и записывать их в файл CSV. Нам нужен минимальный объем данных - мы создадим 5 000 000 строк, в результате чего получится CSV-файл размером 28 МБ, чтобы мы могли увидеть некоторый эффект кластеризации.

Итак, давайте посмотрим на реальный код для генерации наших тестовых данных.

Как видите, мы используем модуль NPM csv-writer для записи файла CSV. Поэтому убедитесь, что вы установили его через npm i - save csv-writer перед запуском скрипта.

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

Когда вы используете мои значения по умолчанию, сценарий создаст CSV-файл размером 28 МБ, содержащий ровно 5 000 000 строк данных - плюс одну дополнительную для заголовка столбца - с 1000 различными именами и десятью различными событиями. Сценарий определяет имя как значение от 0 до 999 и событие как значение от 0 до 9.

Конечно, создаваемый CSV-файл будет содержать немного разных данных каждый раз, когда вы его запускаете. Но из-за большого количества вхождений можно ожидать, что каждое имя будет встречаться примерно 5.000 раз.

Теперь, когда тестовые данные готовы, пора отправить их в BigQuery.

Создайте новую таблицу с данными с помощью импорта CSV

Если вы посмотрите на следующий фрагмент, вы увидите много общего со сценарием импорта CSV из прошлой статьи (Оптимизация таблиц BigQuery с использованием секционирования: таблицы с разделением по столбцам в единицах времени). И правда, по большей части это один и тот же код. Основное отличие - это четвертый параметр метода importCSV (). Раньше мы использовали timePartitioning, теперь мы используем кластеризацию для определения полей, которые BigQuery должен кластеризовать.

В скрипте мы определяем поля кластеризации как [«name», «event»], что означает сначала кластеры BigQuery для name, а затем для события. столбец.

После успешного запуска сценария - может потребоваться немного времени, чтобы импортировать CSV-файл размером ~ 28 МБ в BigQuery и дождаться завершения кластеризации - мы можем проверить через интерфейс командной строки, правильно ли BigQuery добавила кластеризацию.

bq show - format = prettyjson

ВАШ_GCP_PROJECT: YOUR_DATASET_ID.my_clustered_table

Эта команда распечатает метаданные вашей таблицы BigQuery в CLI. Там вы должны найти запись для кластеризации следующего вида:

“clustering”: {
  “fields”: [
    “name”,
    “event”
  ]
},

Если вы выполните ту же команду для non_clustered_table, вы не увидите никаких записей для кластеризации.

Выполнение запросов к импортированным данным

Теперь, когда мы импортировали все данные в обычную и кластеризованную таблицы и убедились, что BigQuery правильно включил кластеризацию в нашей кластеризованной таблице, пришло время выполнить несколько запросов к ней, чтобы сравнить их статистику.

Опять же, мы используем довольно много кода из предыдущей статьи, потому что большая его часть не зависит от какой-либо кластеризации или разделения.

По сравнению с предыдущим, мы добавили некий printConfig в наш метод runQuery, чтобы определить, что он должен печатать - либо результат запроса, либо статистику задания, либо и то, и другое.

В нашем стандартном поисковом запросе - createQuery - мы фильтруем строки по определенному имени со значением «1». Теоретически это должно повлиять на около 5 000 строк, потому что мы создали 5 000 000 строк с 1 000 различными именами, что дает примерно 5 000 записей на имя.

Мы добавили один дополнительный запрос - createCountQuery - который мы запустим и распечатаем его результат. Мы запускаем этот запрос, чтобы проверить наше предположение, что около 5.000 строк должны быть затронуты нашим условием фильтра.

Результаты запроса

Когда вы запустите приведенный выше сценарий, вы должны получить результат, аналогичный следующему:

Поскольку у нас не так много данных, затраченное время одинаково в обоих запросах. Как объяснялось в начале, значительное улучшение производительности будет заметно при приближении к размеру таблицы 1 ГБ и более.

Но вы можете видеть, что количество прочитанных строк и обработанных байтов, за которые Google взимает плату, значительно уменьшилось.

С 5 000 000 прочитанных строк и обработанных 64 450 259 байт мы уменьшились до 384 853 прочитанных строк и обработанных 4 947 899 байт. Теоретическая экономия затрат составляет 92,3%! Просто потому, что мы использовали кластеризацию.

Вы также можете увидеть эту большую разницу на следующем графике:

Внимательная аудитория также заметит, что оплаченные байты не меньше на 92,3%, хотя этого можно было ожидать. И да, совершенно верно. Но это потому, что Google взимает минимум 10 МБ за запрос независимо от фактически обработанных байтов. Когда обработанные байты превышают 10 МБ, оплаченные байты почти всегда будут такими же, как обработанные байты.

Размер блока кластеризации Google, предположение

Помимо этого, вы также можете заметить, что количество прочитанных строк намного выше, чем 5000 строк, которые мы ожидаем сканировать только для нашего запроса. Это связано с некоторой внутренней конфигурацией BigQuery Google, в которой существует минимальный размер диапазона кластеров. Хотя «магия» Google заключается не только в простом размере размещенных данных.

Можно предположить, что при увеличении объема данных до определенной точки прочитанные строки должны иметь то же значение, что и затронутые строки. Поскольку мы видели выше, что, хотя должны быть затронуты только 5000 строк - запрос прочитал 384 853 строки - мы могли подумать, что где-то около этого значения могло быть какое-то «магическое число».

В качестве небольшого примера - я пока не хочу углубляться в эту тему - я изменил приведенный выше набор тестовых данных следующим образом:

  • Я увеличил общее количество записей до 10 000 000.
  • Я уменьшил количество разных имен до 20.

Теоретически эти изменения должны привести к появлению 20 различных имен, каждое из которых встречается в таблице примерно по 500 000. Благодаря этому у нас будет больше вхождений, чем в предыдущем запросе с прочитанными строками.

Итак, давайте проверим результат:

На первый взгляд результат может показаться несколько запутанным. Несмотря на то, что теперь у нас есть 500 000 записей, соответствующих критериям фильтра, BigQuery просто прочитал около 400 000 строк? И мы также проверили с помощью нашего createCountQuery, что действительно существует около 500 тысяч строк, соответствующих нашему фильтру.

Но ждать! Если вы помните, мы используем LIMIT 1 в нашем запросе, потому что нас никогда не интересовал фактический результат, а только смотрели статистику вакансий.

Давайте изменим этот LIMIT 1 на LIMIT 600000, чтобы мы извлекли все затронутые строки и снова посмотрим на результаты нашего скрипта:

Теперь мы ясно видим, что в задании было прочитано в общей сложности 799 675 человек. Это почти в два раза больше, чем количество прочитанных строк в случае LIMIT 1, что составляет 798 948. Там что-то может быть ...

Несмотря на то, что я не нашел никакой официальной документации, она может дать вам некоторые подсказки относительно возможного размера блока для кластеризованных таблиц. Но это пока чисто мое расследование, и наверняка есть и другие факторы. Я хочу продвинуться еще дальше, но я думаю, что это было бы слишком много для данной статьи, поскольку это должно быть некоторое введение в кластеризацию, а не глубокое погружение в ее внутренние структуры.

Ограничения кластеризации и распространенные ошибки, о которых следует знать

Максимальное количество кластеризованных столбцов - BigQuery поддерживает до четырех столбцов для кластеризации.

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

Порядок кластеризованных столбцов. Порядок, в котором вы определяете столбцы для кластеризации, важен для хорошей производительности. Если вы хотите воспользоваться механизмом кластеризации, необходимо использовать все кластеризованные столбцы или их подмножество в порядке сортировки слева направо в выражении фильтра. Если у вас есть кластерные столбцы A, B и C, вам придется отфильтровать все три из них, только A или A и B. Простая фильтрация для B и C не приведет к ожидаемому повышению производительности. Рекомендуется всегда сначала указывать наиболее часто фильтруемый или агрегированный столбец. Порядок в фактическом выражении фильтра SQL не влияет на производительность. Просто важно, по каким столбцам вы фильтруете.

об авторе

Паскаль Цвикирш - технический руководитель группы Usercentrics. Он управляет кросс-функциональной командой, состоящей из нескольких внешних и внутренних разработчиков, обеспечивая при этом беспрепятственную связь между техническим миром, менеджерами по продуктам и заинтересованными сторонами.

Паскаль в течение нескольких лет был разработчиком внешнего интерфейса, а теперь специализируется на внутренних сервисах, облачных архитектурах и принципах DevOps . Он подробно изучил оба мира. Этот опыт а сотрудничество с менеджерами по продуктам дало ему возможность создавать полноценное масштабируемое программное обеспечение.

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

→ Чтобы узнать больше, подпишитесь на меня в LinkedIn.