Базы данных, SQL и моделирование данных — краткий ускоренный курс
За пять лет работы в корпоративной аналитике я наблюдал интересное количество специалистов по обработке данных, приступающих к работе с ограниченными знаниями о хранилищах данных и моделировании данных.
Это не должно вызывать удивления.
Специалисты по обработке данных имеют разный опыт работы, такой как математика, статистика, психология и программирование. Многие, вероятно, не стали бы углубляться в тонкости систем баз данных во время учёбы в университете.
Кроме того, сейчас, когда в моде озера данных, кому вообще нужны хранилища, верно? (Это шутка!)
Я написал эту статью как краткий ускоренный курс для всех, кто берётся за аналитическую работу без особых предварительных знаний о хранилищах данных и моделировании данных.
Мы рассмотрим три темы:
- Рабочий процесс хранилища данных;
- Нормализация базы данных;
- Нереляционные базы данных.
Давайте начинать!
1. Рабочий процесс хранилища данных
Конкретные детали могут различаться в разных решениях, но общая архитектура для аналитики корпоративного масштаба выглядит следующим образом:
- Данные находятся в озере данных.
- Данные загружаются в хранилище данных.
- Создаётся модель данных.
- Аналитики используют данные.
Давайте посмотрим, как это выглядит более подробно:
Аналитическая обработка данных
Данные из операционных или транзакционных хранилищ данных (часто из баз данных OLTP), файлов, потоков в реальном времени или других источников загружаются в централизованное хранилище данных.
Озера данных часто встречаются в сценариях крупномасштабной аналитической обработки данных, где необходимо собрать и проанализировать большой объём данных на основе файлов.
Эта операция загрузки из плоскости оперативных данных в плоскость аналитических данных обычно включает процесс извлечения, преобразования и загрузки (ETL) или процесс извлечения, загрузки и преобразования (ELT), в ходе которого данные очищаются, фильтруются и реструктурируются для анализа. Результирующая структура данных оптимизирована для аналитических запросов.
Поскольку озёра данных обычно относятся к области больших данных, что предполагает распределенные вычисления и хранение с помощью таких платформ, как Apache Hadoop, обработка ETL выполняется инженерами по обработке данных, которые настраивают задания Hive или Spark, которые обрабатывают большие объёмы данных параллельно с использованием многоузловых кластеров. Эти конвейеры включают как пакетную обработку статических данных, так и обработку потоковых данных в режиме реального времени.
Данные в data lake готовы к дальнейшему использованию в аналитике. Это включает в себя изучение, обобщение и моделирование специалистами по обработке данных или обработку аналитиками данных в целях создания отчётов и визуальных элементов.
Хранилища данных, таблицы, схемы и нормализация
Хорошо, двигаемся дальше! Время обогатить наши данные, находящиеся в озере.
Сначала данные копируются в хранилище данных , оптимизированное для операций чтения.
Хранилище данных - это реляционная база данных, в которой данные хранятся в схеме, оптимизированной для анализа данных, а не для транзакционных рабочих нагрузок. Хранилища по своей конструкции оптимизированы для операций чтения — в первую очередь запросов для поддержки бизнес-аналитики (BI), которая включает в себя создание отчетов, информационных панелей и визуализаций, которые под руководством хороших рассказчиков данных могут сообщать о ценности и влиять на принятие решений.
Реляционные базы данных обычно используются для хранения структурированных данных и запросов к ним. Данные хранятся в таблицах, которые представляют сущности, такие как клиенты, продукты или заказы на продажу. Каждому экземпляру объекта присваивается первичный ключ, который его однозначно идентифицирует, и эти ключи используются для ссылки на экземпляр объекта в других таблицах.
Это то, что позволяет обогащать данные путём объединения таблиц вместе.
Например, в записи заказа на продажу можно ссылаться на первичный ключ клиента, чтобы указать, какой клиент разместил этот заказ:
Использование ключей для ссылки на объекты данных позволяет нормализовать реляционную базу данных, что частично означает устранение повторяющихся значений данных, так что, например, сведения об отдельном клиенте сохраняются только один раз, а не для каждого заказа на продажу, который размещает клиент.
Подробнее о нормализации мы расскажем позже.
Таблицы управляются и запрашиваются с использованием языка структурированных запросов (SQL), который основан на стандарте ANSI, поэтому он похож во многих системах баз данных. Мы видели простой запрос, соединяющий две таблицы выше.
Давайте подробнее поговорим о схемах баз данных.
В то время как в хранилищах данных используется подход "Схема при чтении", который не требует определения предварительной схемы, хранилища используют более дорогостоящую в вычислительном отношении, но более организованную парадигму "Схема при записи", где схемы таблиц должны быть определены заранее.
Эти схемы разработаны разработчиками моделей данных и архитекторами решений в консультации с нижестоящими аналитиками и бизнес-пользователями.
На обычной практике разработчики моделей преобразуют данные из хранилища транзакций в схему, посредством которой числовые значения хранятся в центральных таблицах фактов.
Затем они связаны с одной или несколькими таблицами измерений, которые представляют сущности, с помощью которых вы хотите объединить эти числовые показатели — например, продукт или клиент.
Каждый объект представлен строкой с уникальным ключевым значением.
Остальные столбцы представляют атрибуты объекта — например, у продуктов есть названия и категории, а у клиентов - адреса и города. В большинстве аналитических моделей принято включать измерение времени, чтобы вы могли агрегировать числовые показатели, связанные с событиями во времени.
Итак, вот как всё это выглядит:
Мы можем видеть, что числовые показатели (например, доход), агрегированные по различным измерениям модели (Клиент, продукт, время), находятся в центральной таблице фактов, продажах.
Чтобы быть более конкретным:
Каждая строка в таблице фактов представляет записанное событие с прикреплёнными числовыми показателями. Здесь таблица продаж в нашей схеме представляет транзакции продаж по отдельным товарам и включает числовые значения для проданного количества и выручки.
Продажи могут быть агрегированы по клиенту, продукту, магазину и временным измерениям, что позволяет легко находить ежемесячную общую выручку от продаж по продуктам, например, для каждого магазина.
Именно здесь могут быть раскрыты идеи.
Вкратце:
Объединение таблиц с помощью ключей обогащает данные, а агрегирование затем обеспечивает понимание.
Схемы в виде звёздочек являются наиболее распространенным типом схем, которые могут быть расширены до схемы в виде снежинок путем добавления дополнительных таблиц, связанных с существующими таблицами измерений — часто для представления иерархий измерений. Например, продукты имеют свои собственные подкатегории.
В целом, хранилище данных - отличный выбор, когда у вас есть транзакционные данные, которые могут быть организованы в структурированную схему таблиц, и вы хотите использовать SQL для запроса к ним в высокопроизводительной среде.
Аналитическая модель данных
Хотя аналитики данных и специалисты по обработке данных могут работать с данными непосредственно в хранилище, обычно создаётся одна или несколько аналитических моделей данных, которые предварительно агрегируют данные, чтобы упростить создание отчетов, панелей мониторинга и интерактивных визуализаций.
Они называются моделями или кубамиоперативной аналитической обработки (OLAP).
Агрегирование данных осуществляется по измерениям на разных уровнях или иерархиях, что позволяет вам выполнять детализацию вверх / вниз для просмотра агрегированных данных на нескольких иерархических уровнях — например, для определения общего объёма продаж по регионам, городам или по отдельному адресу.
Поскольку данные OLAP предварительно агрегированы, запросы для возврата содержащихся в них сводок могут выполняться быстро.
Агрегированные числовые значения (меры) из таблиц фактов вычисляются для пересечений измерений из таблиц измерений. Концептуально это означает, что модель формирует многомерную структуру, в которой любая точка пересечения измерений представляет собой агрегированную меру для этих измерений.
Например, как предлагалось ранее, выручка от продаж может быть суммирована по дате, клиенту и продукту, что приводит к выводу запроса, который выглядит как декартова координата в кубе.
Довольно круто, не так ли?!
Важно! Хотя мы обычно называем аналитическую модель кубом, измерений может быть больше (или меньше) трёх — нам просто нелегко представить больше трёх!
Готов к употреблению!
Аналитики данных используют данные из этих аналитических моделей (шаг 3) - или непосредственно из хранилищ данных (Шаг 2) - или даже из ‘необработанных’ наборов данных, хранящихся в озере данных (Шаг 1), для изучения данных и создания информационных панелей, отчетов и визуализаций для получения информации.
Нетехнологичные специалисты в организации могут самостоятельно выполнять анализ данных и составлять отчёты по ним, особенно если они уже визуализированы в инструменте BI, таком как PowerBI или Tableau.
Эти визуализации, построенные на хороших аналитических моделях данных, показывают сравнения, тенденции и ключевые показатели эффективности (KPI) и могут принимать форму диаграмм, графических построений, отчётов, которые часто распространяются в документах и презентациях PowerPoint, веб-панелях мониторинга и интерактивных средах (например, PowerBI и Tableau), где последующие пользователи — даже C-Suite — могут легко визуально изучать данные и принимать решения, основанные на данных.
2. Нормализация базы данных
Теперь давайте углубимся в некоторые детали нормализации базы данных.
Это процесс организации данных в нашей реляционной базе данных для уменьшения избыточности и улучшения целостности данных. Он включает разбиение каждой таблицы на более мелкие таблицы и определение связей между ними для устранения дублирующих данных и несоответствий.
Целью нормализации является создание схемы базы данных, которая является более эффективной, гибкой и менее подверженной аномалиям данных. Мы начинаем с набора таблиц и часто заканчиваем большим набором ‘чистых’ таблиц.
Существует несколько уровней нормализации, каждому из которых необходимо следовать. Наиболее распространенными являются:
Первая нормальная форма (1NF)
Каждый столбец таблицы должен содержать атомарные (неделимые) значения. То есть ни один столбец не должен содержать список или набор значений.
Например, представьте, что у вас есть таблица заказов клиентов. В каждой строке есть столбец для имени клиента и столбец для списка товаров, которые они заказали. В 1NF вы бы разбили этот список элементов на отдельные строки, чтобы каждая строка содержала информацию только об одном элементе. Это помогает предотвратить дублирование информации.
Вторая нормальная форма (2NF)
Таблица находится в формате 2NF, если она находится в формате 1NF, и все неключевые столбцы полностью зависят от первичного ключа. Другими словами, не должно быть частичных зависимостей, когда неключевой столбец зависит только от части первичного ключа.
Продолжая пример с заказом клиента, предположим, у вас есть новый столбец для даты размещения заказа. Если эта дата зависит только от имени клиента, то в таблице могут получиться дубликаты. В 2NF вы бы разбили таблицу на две — одну таблицу для информации о клиентах и одну для информации о заказе. Это гарантирует, что каждая часть информации сохраняется только один раз, и предотвращает частичные зависимости.
Третья нормальная форма (3NF)
Таблица находится в формате 3NF, если она находится в формате 2NF, и все неключевые столбцы независимы друг от друга. Другими словами, не должно быть переходных зависимостей, когда неключевой столбец зависит от другого неключевого столбца.
Теперь предположим, что у вас есть новая колонка для цены каждого заказанного товара. Если эта цена зависит от самого товара, а не от любого другого столбца в таблице, то вы уже в 3NF. Но если цена зависит от какого-либо другого столбца, например, от производителя товара, тогда вам нужно будет разбить таблицу на несколько таблиц, чтобы устранить эти переходные зависимости. Цель состоит в том, чтобы гарантировать, что каждый столбец зависит только от первичного ключа таблицы, а не от какого-либо другого неключевого столбца.
Существуют дополнительные уровни нормализации помимо 3NF, но они реже используются на практике.
3. Нереляционные базы данных
В качестве отступления давайте быстро рассмотрим не реляционные базы данных.
Это системы управления данными, которые не применяют реляционную схему к данным. Нереляционные базы данных часто называют базой данных NoSQL, хотя некоторые поддерживают вариант языка SQL.
Обычно используются четыре распространённых типа нереляционных баз данных.
- Базы данных с ключами-значениями, в которых каждая запись состоит из уникального ключа и связанного значения, которые могут быть в любом формате.
- Базы данных документов, представляющие собой особую форму базы данных ключ-значение, в которой значением является документ JSON, для анализа и запроса которого система оптимизирована.
- Базы данных семейства столбцов, в которых хранятся табличные данные, состоящие из строк и столбцов, но вы можете разделить столбцы на группы, известные как семейства столбцов. Каждое семейство столбцов содержит набор столбцов, которые логически связаны друг с другом.
- Графические базы данных, которые хранят объекты в виде узлов со ссылками для определения взаимосвязей между ними.
Краткое содержание
Аналитические модели позволяют структурировать данные для поддержки анализа.
Данные для аналитики переносятся из операционных систем в хранилища данных. Здесь моделируется полученная реляционная база данных, которая включает в себя её нормализацию для повышения эффективности и переработку в схему, подходящую для вашего бизнес-варианта использования.
Эти модели основаны на наличии связанных таблиц данных и определяют числовые значения, которые вы хотите проанализировать или сообщить (меры), и объекты, с помощью которых вы хотите их объединить (измерения).
Существует три общих уровня нормализации, которые разбивают каждую таблицу на более мелкие, более специфические таблицы, которые связаны друг с другом более логичным и эффективным способом.
Для дальнейшего повышения эффективности данные могут быть предварительно агрегированы в OLAP-модель или куб. Интуитивно эти модели образуют многомерные структуры, напоминающие куб, в том случае, если вы агрегируете данные ровно по 3 измерениям, например, выручку от продаж по клиенту, продукту и времени.
Затем аналитики данных и последующие пользователи получают информацию, обогащая данные по своему выбору (путём объединения таблиц) и выполняя интересующие их агрегации.
С помощью убедительного повествования о данных аналитики и бизнес-профессионалы затем представляют свои выводы лицам, принимающим решения, которые могут действовать на основе данных.
Я надеюсь, что вы нашли эту статью полезной!