MPP — ключ к эффективной обработке больших данных

Я неоднократно упоминала в своих статьях аббревиатуру MPP, но не рассказывала, что же это такое. Даже если вы уже знакомы с этим термином, давайте всё равно освежим ваши знания.

MPP (Massive Parallel Processing) — это архитектурный подход к обработке данных, широко применяемый в хранилищах данных. Его суть заключается в распределении и параллельной обработке данных на нескольких серверах (узлах) одновременно, что обеспечивает высокую производительность и масштабируемость. В результате общее время выполнения операций сокращается в 10-100 раз по сравнению с традиционными СУБД.

Пример объяснения принципа MPP:
Представим ресторан с одним поваром, который готовит все поступающие заказы последовательно от начала до конца. При необходимости масштабирования и для улучшения производительности мы можем нанять дополнительных поваров с разными зонами ответственности, каждый из которых отвечает за свой этап приготовления блюд (нарезка, выпечка, варка, гриль и т.д). Точно также и в MPP каждый узел параллельно обрабатывает свою часть данных, ускоряя процесс и улучшая общую производительность системы.

Ключевые преимущества MPP:
— Высокая производительность: распределенная обработка данных на узлах кластера обеспечивает быстрое выполнение запросов.
— Масштабируемость: простота добавления новых узлов для обработки дополнительных объемов данных.
— Эффективное использование ресурсов: параллельная обработка данных на нескольких серверах повышает общую эффективность.

Важный момент. MPP-системы предназначены для обработки и анализа больших объемов данных, но не эффективны для обработки единичных OLTP -транзакций, таких как частые операции вставки, обновления и удаления отдельных строк данных.

#dwh

Ключи в базах данных: коротко о важном

При работе с БД важно понимать разницу между ключами.

Primary Key (PK) – уникальный идентификатор для каждой записи в таблице, гарантирует целостность данных. Эффективное использование этого ключа требует тщательного выбора типа данных. Использование слишком длинных строк (VARCHAR(MAX) и подобных) или сложных типов может существенно повлиять на производительность запросов при соединении по ключу.
Если нужен уникальный идентификатор, который может быть сгенерирован в любом месте без возможности конфликта, можно рассмотреть использование UUID или hash от строки (выбор конкретного метода зависит от целей и типа БД). Также PK поддерживает auto increment.

Foreign Key (FK) создает связь между двумя таблицами, ссылаясь на PK в другой таблице. FK обеспечивает целостность связей между таблицами и логически структурирует данные. Именование полей — это важный аспект проектирования БД, который способствует легкости в понимании структуры данных. Хороший тон в наименовании FK — использование стандартных сокращений и отражение связи с PK.

Unique Key (UK) – ограничение базы данных, которое гарантирует уникальность значений в столбце или группе столбцов. Это позволяет исключить дубликаты и обеспечить целостность данных. UK может содержать одно NULL значение.
При создании индексов, PK — это кластерный индекс, а UK — некластеризованный.

Резюме:
— PK: уникальный идентификатор для записей, обеспечивает единственность.
— UK: обеспечивает уникальность значений без строгой идентификации.
— FK: создает отношения между таблицами.
— В таблице может быть только 1 PK и несколько UK.
— Именование любых ключей должно быть осмысленным.

#databasedesign

Explicit is better than implicit: не используйте SELECT *

Один из моих любимых принципов в The Zen of Python (PEP-20) — это "Explicit is better than implicit" (явное — лучше неявного). Он актуален не только для питона, но и для любого кода, документации и в целом жизни. Сегодня я хочу поговорить о примере использования этого принципа.

Часто для сокращения sql-кода используется конструкция SELECT *, которая выгружает информацию сразу из всех колонок. Согласитесь, если у вас таблица с 200 столбцами, то вместо того, чтобы писать колбасу из перечислений, куда проще просто поставить *. Ведь мы на 100% уверены, что все столбцы нужны.

Почему так делать не нужно?

1. Структура данных: ничто не вечно
Даже если кажется, что исходная таблица никогда не изменится, через Х времени это произойдёт. Столбцы могут быть добавлены или удалены, и отлаженный процесс будет сломан. Но из-за отсутствия явного указания, найти и исправить ошибку будет не так просто.

2. Снижение производительности
SELECT * в запросах извлекает все столбцы из указанных объектов, включая те, которые не требуются. Это может значительно увеличить объем получаемых данных и снизить скорость выполнения запроса и его производительность (это особенно актуально для колоночного хранения).

3. Сложность в понимании логики запроса
Используя неявное указание, мы усложняем дальнейшую поддержку запроса другими людьми. Явное перечисление — это самодокументирующийся код с четким пониманием смыслов.

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

Для четкой понимании логики запроса, оптимизации запросов и упрощения рефакторинга всегда указывайте столбцы явно (разве что речь не идёт об ad-hoc запросах).

#sql

Принцип DRY при написании документации

В разработке принцип "Не повторяйся" (Don't Repeat Yourself, DRY) является одним из ключевых для эффективности и качества итогового продукта. Его же можно использовать и в подготовке документации.

Основной смысл: каждая часть знания должна иметь единственное, непротиворечивое и авторитетное представление в рамках системы.

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

Мои советы по использованию этого принципа:

1. Внедрение шаблонов
Большинство документов подлежит стандартизации. Используйте шаблоны не только для сокращения бесполезного труда, но и для систематизации. Хорошо структурированный документ всегда понятнее и чаще всего оставляет меньше простора для явных ошибок.

2. Использование ссылок
Вместо дублирования информации, старайтесь использовать ссылки на уже существующие материалы. Тогда и исправлять придётся не по всему пространству, и концы найти будет проще.

3. Контроль версий
Системы контроля версий актуальны не только для кода и разработки, но и для поддержания информации в актуальном виде — мы всегда можем "откатиться" к старой версии или понять кто, что и когда изменял (и пойти к этому человеку с вопросами). Сейчас контроль версий есть в большинстве систем для работы с информацией (Notion, Confluence, Google Docs, etc).

Ключ к хорошей документации — в постоянном стремлении к упрощению и оптимизации процесса её написания. В конце концов, некачественная документация столь же бесполезна, как и её полное отсутствие.

#документация

Группировка NULL-значений

Мы уже рассмотрели множество особенностей NULL, но это ещё не всё.
На очереди группировка данных, включающих NULL-значения.

Как думаете, будут ли NULL сгруппированы или каждый будет считаться самостоятельной единицей?

В этот раз, если вы читали мои предыдущие статьи, ответ может вас удивить. При использовании GROUP BY, строки с NULL в группирующем столбце объединяются в одну группу. Казалось бы NULL обозначает "неизвестное" значение и как мы можем его группировать? Но факт остаётся фактом, при агрегации все NULL считаются равными между собой и формируют единую группу.

Чтобы сделать это поведение более очевидным при аналитике, мы можем использовать функции COALESCE или CASE, чтобы заменить NULL на значение, которое ясно указывает на отсутствие данных, например, на 'Неизвестно'.

#sql #null

Ребята из Data Secrets выложили отличные карточки, рассказывающие про манипуляцию данными. Очень рекомендую ознакомиться, запомнить и не использовать визуальный обман при построении своих отчетов.
Ну и, конечно же, развивайте критическое мышление, чтобы и самим не попадаться на чужие уловки.

Традиционные методы загрузки данных всё ещё актуальны: полная, инкрементальная и дельта-загрузка в DWH

При организации работы хранилища данных важно выбрать оптимальный метод загрузки. Существует множество современных способов переноса данных из источников, например, Change Data Capture (CDC) или прямая передача данных без необходимости их временного хранения. Они предлагают продвинутые возможности для репликации данных в реальном времени с отслеживанием историчности. Но иногда данные нужно перенести быстро или в силу бизнес-требований нет необходимости использовать трудозатратные способы. Тогда мы выбираем традиционные методы репликации.

Полная загрузка — это перенос всех данных из источника (объекта) в хранилище за один раз. Каждый раз, когда нам нужно обновить данные, мы снова перезагружаем объект целиком. Этот метод прост и надежен, если объем данных невелик (н-р, это актуально для редко обновляемых справочников) или есть строгое требование к целостности данных (а других способов гарантировать её нет). Однако, с увеличением количества данных, полная загрузка становится всё более времязатратной и ресурсоемкой.

Инкрементальная загрузка предполагает добавление только новых записей (операция Insert) в хранилище без обновления или удаления уже существующих (если что-то из уже загруженных данных в источнике изменилось, данные в хранилище перестанут быть достоверными). Этот метод эффективнее, так как за один раз загружается меньший объем данных. Однако он несёт в себе риски утраты доверия к данным.

Дельта-загрузка, или загрузка с частичной перезагрузкой, — более продвинутый метод. Он включает в себя загрузку не только новых записей (Insert), но и обновление существующих данных (Update), а иногда и удаление устаревших записей (Delete). Дельта-загрузка требует сложной логики отслеживания изменений в источнике, что позволяет синхронизировать хранилище с актуальным состоянием данных с высокой точностью. Если, конечно, система-источник может предоставить необходимую информацию об изменениях.

Выбор между методами загрузки зависит от множества факторов: бизнес-задач, объема данных, частоты обновлений, требований к актуальности и доступных вычислительных ресурсов. И несмотря на то, что дельта-загрузка может показаться предпочтительнее, так как она обеспечивает баланс между эффективностью обработки и актуальностью данных, система-источник может ограничить нас в выборе и тогда придётся, например, использовать полную перезагрузку, но не целиком объекта, а за промежуток времени Х.

Примеры объектов и вариантов их репликации:
1. Справочник стран (обновляется редко, небольшой объем) — полная загрузка
2. Логи (старые данные не изменяются, только приходят новые) — инкрементальная загрузка
3. Текущее состояние заказа (данные в полях обновляются, есть отслеживание изменений) — дельта-загрузка

Мой совет: всегда ориентируйтесь именно на ваши бизнес-требования, внимательно изучайте источник, а не просто слепо следуйте "лучшим практикам". Гибкость в выборе метода загрузки — ваш ключ к эффективному управлению данными.

#dwh

Обзор традиционных методологий разработки в DWH: ищем свой путь в мире данных

Начнем с простого, Data Warehouse (DWH) — это специальные системы для хранения огромных объемов информации, собранной из различных источников. Она нужна для анализа и принятия обоснованных решений.

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

Методология Инмона
Уильям Х. Инмон считается одним из основателей концепции DWH. Он предложил создавать системы, где все данные будут храниться в одном месте, аккуратно организованы и легко доступны.

Основные принципы подхода Инмона:
— Централизованное хранилище: все данные собираются в одном месте, что обеспечивает единый источник правды для всей организации.
— Нормализованная модель: данные хранятся в нормализованной форме (3NF или выше), что минимизирует дублирование и обеспечивает высокую целостность данных.
— Историческая точность: хранилище содержит исторические данные, что позволяет анализировать изменения во времени.
— Больше времени и ресурсов на разработку: подход Инмона часто связан с большими затратами времени и ресурсов на начальном этапе из-за сложности интеграции и нормализации данных.

Методология Кимбалла
Ральф Кимбалл предложил более простой и понятный способ создания DWH, сосредоточенный на конкретных задачах бизнеса. Его идея в том, чтобы строить DWH по частям, используя схему "звезда" (поговорим об этом в отдельном посте). Этот подход позволяет быстрее запускать проекты и обеспечивает легкость внесения изменений.

Ключевые аспекты методологии Кимбалла:
— Моделирование "звезда": используется денормализованная модель данных (таблицы измерений и фактов), что упрощает запросы и анализ.
— Ориентация на бизнес-процессы: каждая схема строится вокруг конкретного бизнес-процесса, что облегчает разработку и понимание данных.
— Быстрая доставка: методология подразумевает итеративную разработку и доставку, позволяя бизнесу быстро получать ценность от данных.
— Гибкость в изменениях: Добавление новых данных или изменение существующих процессов проще в денормализованной среде.

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

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

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

#dwh

Используете USING? Тогда мы идём к вам

Ранее я писала про то, почему не стоит использовать SELECT *, а сегодня хочу поговорить об USING.

USING — ключевое слово, которое используется в SQL-запросах для упрощения синтаксиса объединения таблиц по одноимённым столбцам.

Например, вместо:

SELECT o.order_id, o.amount, c.customer_surname, c.customer_name
FROM orders o
JOIN customers c 
ON o.customer_id = c.customer_id

Можно написать:

SELECT o.order_id, o.amount, c.customer_surname, c.customer_name
FROM orders o
JOIN customers c 
USING (customer_id)

В этом случае USING объединяет таблицы по столбцам с одинаковыми именами, указанными в условии.

Почему так делать не нужно?
1. USING хоть и добавляет компактности, но убирает ясность в коде (это особенно заметно при множественном объединении таблиц), а следовательно снижает его поддерживаемость.

2. При использовании USING, столбец, по которому происходит объединение, возвращается в результирующем наборе только один раз. Какой из нужных? Звучит неоднозначно, особенно при использовании не только INNER JOIN. Это может создать сложности при необходимости разделения значений из каждой таблицы в дальнейшем анализе.

3. Изменения неизбежны в любой базе данных. Столбцы могут быть переименованы, добавлены или удалены. И при явном указании столбцов объединения, мы легко найдём ошибку. В случае же использования USING, могут возникнуть ситуации, когда будет переименовано несколько столбцов в разных таблицах и логика объединения нарушится, превратив результаты запроса в мусор.

4. Использование USING иногда может ограничить возможности оптимизатора выбирать наиболее эффективный план выполнения.

5. Особенности отдельны СУБД. Если JOIN ON работает предсказуемо, то использование USING может привнести сюрпризы.
Например, в Snowflake при определённых условиях множественные USING по одинаковым столбцам могут вообще игнорироваться. Совет: всегда изучайте документацию.

USING — это синтаксический сахар и его использование далеко не всегда оправдано.

Как я уже говорила ранее, помните, что явное всегда лучше неявного. Точные условия объединения с использованием ON делают зависимости между таблицами понятными, тем самым повышая стабильность, эффективность и надежность запросов, улучшая их масштабируемость.

#sql

Управление документацией: превращаем хаос в порядок

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

Ещё не так давно никто знать не знал о системах управления документацией и чаще всего все использовали Word (кучи файлов на сетевом диске) или Google Docs. Минус такого подхода очевиден: проблемы с поиском, навигацией и версионностью. Важна доступность, а не просто наличие документов.

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

Confluence, часть экосистемы Atlassian, идеален для технических команд, которым нужны мощные функции для создания сложной документации и управления знаниями. Он предлагает интеграцию с JIRA и Bitbucket, что упрощает трекинг задач и управление кодом.

Notion выделяется своей универсальностью, гибкостью по доступной цене (плюс есть бесплатные версии). Он позволяет комбинировать заметки, базы знаний, канбан-доски и документы в одном месте. Это отличный выбор для маленьких и средних команд, которым нужен один инструмент для всего.

Выбирайте инструмент, исходя из нужд команды, важности интеграции с другими сервисами и предпочтений в работе друг с другом. Confluence идеален для крупных проектов, Notion — для гибкой работы над разнообразными задачами в командах малого и среднего размеров. Google Docs я предпочитаю использовать в случаях крайней необходимости. Место для хранения знаний должно быть централизованным.

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

#документация

Немного об оконных функциях

Рассказывать о простом SQL можно много и долго, но давайте перейдем к более глубоким темам. Например, поговорим об оконных функциях. И перед тем как перейти к деталям, выясним, что же такое окошки?

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

Окно — набор строк, который указанным образом связан с текущей строкой.

Оконные функции обрабатывают данные в рамках окна, которое задается с помощью параметров:
1. Разделение на группы — PARTITION BY.
2. Упорядочивание данных внутри каждой группы — ORDER BY.
3. Определение диапазона строк для группы, над которыми будет производиться вычисление (ROWS BETWEEN) — поддерживается не всеми оконками.

Ниже показан расчет средней зарплаты по отделам. Каждая строка результата будет содержать данные о сотруднике, его отделе, зарплате и средней зарплате по его отделу.

SELECT
    employee_id,
    department,
    salary,
    AVG(salary) OVER(PARTITION BY department) as department_salary_avg
FROM employees;

Оконные функции предоставляют возможности для решения большого количества различных аналитических задач, например таких как:
— Анализ трендов и изменений
— Вычисление всевозможных рейтингов (ранжирование)
— Расчет скользящего среднего
— Сравнение со смещением
— Всевозможные агрегации
— Расчет кумулятивных сумм
— Определение первого/последнего значения в группе
— ...

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

Следите за каналом) в следующих статьях рассмотрим применение конкретных оконных функций под различные задачи.

#sql #оконные_функции

Противостояние CTE и подзапросов

В области анализа данных есть два классных инструмента — общие табличные выражения (CTE) и подзапросы, играющие важную роль в структурировании и обработке информации. Их правильное применение может значительно повысить эффективность ваших запросов.

CTE — это временные именованные наборы результатов, к которым можно обращаться внутри операторов SELECT, INSERT, UPDATE или DELETE. Они обеспечивают структурированный подход и делают код более читаемым и легким для анализа.

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

Минусы:
— Могут быть менее эффективны при неоптимальном использовании, особенно в больших базах данных из-за временного хранения результатов.

Пример:

WITH category_sales AS (
    SELECT p.category,
           SUM(s.quantity) AS total_quantity
      FROM sales s
      JOIN products p 
        ON s.product_id = p.product_id
     GROUP BY p.category
)
SELECT p.product_name,
       p.category,
       SUM(s.quantity) AS product_quantity,
       cs.total_quantity AS category_total_quantity
  FROM sales s
  JOIN products p ON s.product_id = p.product_id
  JOIN category_sales cs ON p.category = cs.category
 GROUP BY p.product_name, p.category, cs.total_quantity;

В этом примере в CTE category_sales вычисляется общее количество продаж товаров для каждой категории. Затем основной запрос использует эту информацию для вывода количества продаж по каждому продукту вместе с общим количеством продаж по его категории. Таким образом, мы можем увидеть как общие продажи по категориям, так и детальные продажи по каждому продукту. Это облегчает анализ эффективности продаж в разрезе категорий. Этот пример также можно реализовать с помощью оконных функций, но об этом в другой раз.
Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.

Подзапросы – это запросы, вложенные в другой запрос. Они предоставляют набор результатов для использования в основном запросе. Звучит сложно? На самом деле всё просто, сначала делается выборка по подзапросу, а результаты этой выборки используются для дальнейших вычислений.

Плюсы:
— Простота использования и понимания.
— Идеально подходят для извлечения данных в рамках ограниченного контекста.

Минусы:
— Ограниченные возможности повторного использования и рекурсии по сравнению с CTE.
— Могут привести к ухудшению читаемости кода при сложных вложениях.

Пример:

SELECT p.product_name, p.price
  FROM products p 
WHERE p.price > (
      SELECT AVG(price) -- Вычисление средней цены по категории
        FROM products
      WHERE category = p.category
);

В этом примере подзапрос в WHERE-части вычисляет среднюю цену по категориям. Затем основной запрос сравнивает цену товара и выводит только ту, чья цена выше средней.
Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.

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

#sql

Противостояние CTE и подзапросов
В статье выше я рассказываю про разницу между CTE и подзапросами.

Чтобы поэкспериментировать самостоятельно, используйте песочницу https://sqliteonline.com и мой код для создания таблиц из вложения.