Оптимизация запроса и запрос оптимизации
Как не грабить память, не пытать диск, не мучать кластер. Или делать все это всего одним запросом на Impala к Hadoop.
Среди задач аналитиков данных, в рамках которых необходимо иметь дело с большими объемами однотипных данных, выделяются задачи построения витрин данных, автоматизации процессов сбора и обработки данных. Многие аналитики используют различные реляционные базы данных, в таблицах которых хранятся огромные объемы информации, агрегация и доступ к которым может занимать долгое время, поэтому правильное составление и оптимизация запросов к этим таблицам становится критически необходимым фактором для работы аналитиков, инженеров данных и data scientist.
Epic battle
Автоматизация бизнес-процессов приносит много пользы, она позволяет
Упорядочить регулярные задачи.
Минимизировать человеческий фактор.
Четко разделить зоны ответственности внутри процессов.
Держать под контролем детали процесса.
Создать единую ИТ-инфраструктуру с различными правами доступа.
Экономить время и средства на управлении процессом.
В совокупности задачи автоматизации затрагивают экономию — одну из ключевых целей, причем экономиться могут как ресурсы так и время на выполнение задач. Для решения этих проблем не всегда нужно ломать существующие процессы и строить их заново, достаточно их оптимизировать. Так и в работе аналитика, обращающегося к базам данных, есть те инструменты, оптимизация которых принесет пользу, среди них — оптимизация запросов.
Steal = Spill
Пытки памяти
Моя история с пытками памяти на диске началась задолго до эры ChatGPT, это был код формирования витрины данных из далекого прошлого, еще тех времен, когда все ходили в офис и даже не мечтали об удаленной работе. Своими глазами я видел манускрипт с требованиями на автоматизацию, датированный 2020 годом нашей эры. Так началось мое знакомство с неоптимизированным скриптом на тысячу строк sql-кода.
Ничто не предвещало масштабной битвы за ресурсы и память. Запрос отрабатывал всего за час с небольшим… с небольшим спилом на диск в объеме 6 Терабайт!
Victim of a bad query
Доработки процесса шли своим чередом, шагая тернистой дорогой CI\CD. Чем ближе казалась цель, тем больше полей требовала витрина, тем больше новых источников использовалось в запросе: транзакции, балансы, заявки, справочники, договоры… Казалось им не будет конца.
Методы оптимизации запросов
Теперь пора поделиться тактикой борьбы с неоптимизированными запросами. Этих тактик несколько, приемы контрнаступления должны быть эффективными и решать две основные задачи:
Минимизация времени на выполнение запроса
Минимизация используемой памяти.
Реализовывать эти задачи приходится когда в результате запроса выдается ошибка »Out of memory» или сам запрос отрабатывает слишком долго. Иногда ошибка не является блокирующей при выполнении запроса — в таком случае Impala обращается к общему дисковому пространству, что может негативно сказываться на работе всего кластера. Сама необходимость оптимизации не всегда является очевидной. Поэтому при разработке скриптов рекомендуется использовать дополнительные инструменты для контроля и мониторинга выполнения запроса.
Cloudera Manager является компонентом платформы данных Cloudera CDP. Это комплексное приложение для управления кластерами, оно обеспечивает видимость и контроль каждой части кластера CDH, повышая производительность и качество сервисов. Именно в Cloudera Manager можно обнаружить необходимость в оптимизации, например, по времени выполнения запроса Duration или по используемой дисковой памяти — Memory Spilled.
Для достижения поставленных целей следует использовать следующие приемы:
Сбор статистики
После создания или изменения таблиц необходимо собирать статистику — compute stats table_name. Это поможет Impala строить план запроса эффективнее.
Минимизация подзапросов
Рекомендуется уменьшить количество подзапросов. Часто подзапрос можно заменить на join с условием или иными средствами.
Партицирование таблиц
Партицирование по некоторому полю (часто дате\периоду) позволяет обработывать данные несколькими независимыми и параллельно выполняющимися потоками, что в итоге ускоряет работу по чтению данных.
Ограничение выборки (where …)
Фильтрация данных является примером ограничения выборки, которая позволяет считывать не все данные, а только их часть. Условия на ограничение наиболее эффективны для полей партиционирования — в таком случае целые блоки таблиц не будут участвовать в запросе.
Материализация промежуточных результатов
Common Table Expression (CTE) —Обобщенное табличное выражение — результаты запроса, которые можно использовать множество раз в других запросах также как и подзапросы могут сильно влиять на производительность.
Минимизация затратных операций (group by, distinct, order by…)
Большое количество полей в группировке или сортировке — очень ресурсозатратно. Уменьшив число атрибутов, можно достичь меньшего потребления ресурсов.
Минимизация кол-ва join’ов в одном запросе
Чем больше таблиц участвует в запросе, тем дольше он отрабатывает. Разделение запроса на несколько частей с материализацией их результата и сбором статистики — это оптимальное решение, особенно если соединение по полям партицирования.
