Физика DWH и MPP
Одна и та же логическая модель летает или ползёт в зависимости от физики: колоночного хранения, партиций, дистрибуции. Собес проверяет, проектируешь ли ты физику под реальный workload или расставляешь настройки на удачу.
Стержень: партиции и сортировка - по частым фильтрам, дистрибуция - по частым джойнам; физика без знания запросов - гадание.
// Формулировки: «зачем колоночное хранение?», «что такое partition pruning?», «как выбрать ключ дистрибуции в MPP?».
Колонки и партиции
Колоночное хранение - фундамент аналитики: запрос читает только нужные колонки, а однотипные данные в колонке сжимаются в разы лучше строчных. Строчное хранение - про точечные записи OLTP (online transaction processing), для сканов агрегатов оно проигрывает.
Партиционирование - крупная нарезка таблицы, обычно по дате: запрос с фильтром по дате читает только свои партиции (partition pruning), не трогая остальное. Партиция - ещё и единица дешёвого удаления и перезагрузки.
// Ловушка pruning: фильтр функцией по колонке партиции (date(ts) = ...) обычно отключает отсечение - движок не понимает, в какие партиции смотреть, и делает фулскан. Фильтруют по самой колонке партиции напрямую.
- колоночное хранение
- данные по колонкам: скан нужных полей + сжатие
- partition pruning
- чтение только партиций, попавших под фильтр
Сортировка и дистрибуция
Ключ сортировки/кластеризации внутри партиции это pruning второго уровня: движок пропускает блоки данных по их min/max статистикам (zone maps), если отсортированная колонка не попадает в фильтр.
В MPP-системах (massively parallel processing) строки раскладываются по нодам хешем ключа дистрибуции. Джойн по ключу дистрибуции локален (каждая нода джойнит своё), а джойн по другому ключу перегоняет данные по сети - shuffle или motion, самая дорогая операция.
// Отсюда правило перекоса: дистрибуция по колонке с неравномерным распределением (одно значение доминирует) грузит одну ноду за всех, и кластер работает как один сервер.
- sort/cluster key
- порядок строк для скипа блоков по min/max
- distribution key
- ключ раскладки строк по нодам MPP; джойн по нему локален
Файлы, витрины, статистика
Мелкие файлы - болезнь озёр данных: тысячи файлов по килобайтам убивают чтение чистыми накладными расходами на метаданные. Лечится регулярной компактизацией до файлов в сотни мегабайт; стриминг в озеро без компактизации деградирует за недели.
Материализованные агрегаты и витрины - плата хранением за скорость: предрасчёт тяжёлых группировок, но с ответственностью за их свежесть, иначе получишь быстрый, но вчерашний отчёт.
// Статистика таблиц - топливо оптимизатора: без ANALYZE после массовой загрузки план джойнов строится вслепую и может выбрать катастрофический порядок. Статистику обновляют после больших загрузок.
- compaction
- склейка мелких файлов в крупные ради чтения
- статистика (ANALYZE)
- распределения колонок - вход для планировщика
Как отвечать: «Как физически спроектировать таблицу под аналитику?»
Отталкиваюсь от workload - от того, как таблицу реально читают. Хранение колоночное, это база для сканов и сжатия. Партиционирую по колонке самого частого крупного фильтра, почти всегда по дате: тогда запросы читают только свои партиции, а перезагрузка и удаление идут партициями. Внутри партиции задаю ключ сортировки по следующему частому фильтру, чтобы движок скипал блоки по zone maps. В MPP выбираю ключ дистрибуции по самому частому джойну - тогда он локальный, без перегона по сети, и слежу, чтобы по этому ключу не было перекоса, иначе одна нода пашет за всех. Дальше слежу за гигиеной: компактизирую мелкие файлы, обновляю статистику после массовых загрузок, а тяжёлые агрегаты материализую с контролем свежести. Без знания частых фильтров и джойнов всё это - гадание, поэтому сначала смотрю запросы.
Почему это сильный ответ: физика выведена из workload (фильтры → партиции/сортировка, джойны → дистрибуция), учтён перекос, и названа операционная гигиена (компактизация, статистика) - инженерный, а не декларативный ответ.
На чём валят
- −Партиционировать по высококардинальному ключу (user_id) - миллионы партиций-огрызков.
- −Фильтр функцией по колонке партиции (date(ts) = ...) - pruning не сработал, фулскан.
- −Дистрибуция по колонке с перекосом - одна нода пашет за всех.
- −Стриминг мелкими файлами в озеро без компактизации - деградация чтения за недели.
- −Материализовать витрину и забыть про обновление - быстрый, но вчерашний отчёт.
Проверьте себя
Пять вопросов из банка по этой подтеме. Всего их 10, остальные разбираются в тренажёре.
- Чем инкрементальная загрузка витрины выгоднее полного пересчёта (full refresh)?A)Обрабатывает только новые/изменённые строки, а не всю историю — быстрее и дешевле, но требует ключа и отметки измененийB)Инкрементальная загрузка точнее полного пересчёта, потому что обрабатывает больше данных за разC)Полный пересчёт не получается сделать идемпотентным, поэтому инкремент — основной корректный способD)Инкрементальная загрузка не требует никакого признака изменений и просто берёт случайную часть строк
показать ответ и разбор
+A)Обрабатывает только новые/изменённые строки, а не всю историю — быстрее и дешевле, но требует ключа и отметки изменений// разбор: Full refresh каждый раз пересчитывает витрину с нуля по всей истории — просто и надёжно, но на больших объёмах дорого и долго. Инкрементальная загрузка берёт только дельту (новые и изменённые с прошлого запуска строки по watermark/updated_at или CDC) и применяет её через append или merge/upsert по ключу. Это кратно дешевле и быстрее, но сложнее: нужен надёжный признак изменения, обработка удалений и идемпотентность (повтор не должен задваивать). Компромисс — периодически делать full refresh для сверки.
- Что даёт таблице сортировка/кластеризация по часто фильтруемому столбцу (sort/cluster key)?A)Сортировка обеспечивает уникальность строк и работает как первичный ключ таблицы в хранилищеB)Физически упорядочивает данные, чтобы по статистике блоков (min/max) пропускать не подходящие под фильтр — меньше чтенийC)Кластеризация загружает таблицу в оперативную память, откуда и берётся ускорениеD)Сортировка по столбцу задаёт порядок вывода строк в SELECT без указания ORDER BY в запросе
показать ответ и разбор
+B)Физически упорядочивает данные, чтобы по статистике блоков (min/max) пропускать не подходящие под фильтр — меньше чтений// разбор: Если данные физически отсортированы (или кластеризованы) по столбцу, по которому часто фильтруют, то значения в соседних блоках близки, и по статистике блока (min/max) движок может целиком пропускать блоки, где искомого точно нет (data skipping). Это резко сокращает чтение на диапазонных и точечных фильтрах — тот же принцип, что ORDER BY в ClickHouse или Z-ordering в озере. На неотсортированных данных статистика блоков бесполезна (в каждом блоке весь диапазон значений). Цена — упорядочивание при загрузке.
- Что такое материализованное представление (агрегатная витрина) и в чём его главный трейдофф?A)Материализованное представление содержит актуальные данные без обновленияB)Это обычное (виртуальное) представление, которое пересчитывает запрос заново при каждом обращенииC)Заранее посчитанный и сохранённый результат запроса: чтение быстрое, но данные надо обновлять — есть риск устареванияD)Материализованное представление ускоряет запись в исходные таблицы, а на чтение никак не влияет
показать ответ и разбор
+C)Заранее посчитанный и сохранённый результат запроса: чтение быстрое, но данные надо обновлять — есть риск устаревания// разбор: Материализованное представление физически хранит результат тяжёлого агрегирующего запроса, поэтому обращения к нему быстрые — не пересчитывать каждый раз по сырью. Главный трейдофф: результат надо поддерживать в актуальности. Полное обновление дорого, инкрементальное сложно, а между обновлениями данные устаревают (staleness). Выбор — как часто обновлять: чаще — свежее, но дороже; реже — дешевле, но данные отстают. По сути тот же приём предрасчёта, что проекции/rollup-таблицы.
- Зачем в загрузке DWH данные сперва кладут в staging-таблицу, а не пишут прямо в core?A)Staging нужен чтобы архивировать сырые данные, к загрузке в core он не относитсяB)Прямая запись в core быстрее и надёжнее, а staging замедляет загрузку без пользыC)Staging-таблица физически обязана находиться в отдельной базе данных на другом сервере кластераD)Staging для валидации/очистки + атомарная публикация в core, рестарт безопасен
показать ответ и разбор
+D)Staging для валидации/очистки + атомарная публикация в core, рестарт безопасен// разбор: Staging (промежуточная зона) принимает сырьё из источника, где его валидируют, типизируют, дедуплицируют и трансформируют, не трогая боевой core. Только после успешной подготовки данные атомарно публикуются в core (вставкой/подменой партиции). Это даёт рестартуемость (упало на трансформации — core не испорчен, перезапустил), изоляцию боевых витрин от грязного ввода и возможность сверки перед публикацией. Прямая запись в core рискует оставить его в полусобранном состоянии при сбое посреди загрузки.
- Таблицу событий на миллиарды строк партиционировали по дате. Что это даёт запросам?A)запрос за неделю читает только партиции этой недели, а не всю таблицуB)строки внутри партиций автоматически дедуплицируютсяC)фильтры по остальным колонкам ускоряются так жеD)таблица начинает помещаться в оперативную память
показать ответ и разбор
+A)запрос за неделю читает только партиции этой недели, а не всю таблицу// разбор: Партиционирование режет таблицу на куски по ключу (обычно дате). Запрос с фильтром по этому ключу читает только нужные партиции — partition pruning; заодно дёшево удалять старое (drop партиции вместо DELETE по миллиардам строк). Фильтрам по другим колонкам партиции по дате не помогают — там работают индексы, сортировка, кластеризация.
дальше
Теорию прочитали. Навык ставится повторением
В Сеньорчике эта подтема идёт в ежедневных сессиях: движок возвращает её, пока ответы не станут уверенными, и ведёт прогресс отдельно по каждой подтеме. Теория внутри тоже бесплатна, лимит только на количество вопросов в день.