Базы данных, SQL и моделирование данных — краткий ускоренный курс

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

Это не должно вызывать удивления.

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

Кроме того, сейчас, когда в моде озера данных, кому вообще нужны хранилища, верно? (Это шутка!)

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

Мы рассмотрим три темы:

Давайте начинать!

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

Давайте посмотрим, как это выглядит более подробно:

Данные из операционных или транзакционных хранилищ данных (часто из баз данных 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 — могут легко визуально изучать данные и принимать решения, основанные на данных.

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

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

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

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

Каждый столбец таблицы должен содержать атомарные (неделимые) значения. То есть ни один столбец не должен содержать список или набор значений.

Например, представьте, что у вас есть таблица заказов клиентов. В каждой строке есть столбец для имени клиента и столбец для списка товаров, которые они заказали. В 1NF вы бы разбили этот список элементов на отдельные строки, чтобы каждая строка содержала информацию только об одном элементе. Это помогает предотвратить дублирование информации.

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

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

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

Теперь предположим, что у вас есть новая колонка для цены каждого заказанного товара. Если эта цена зависит от самого товара, а не от любого другого столбца в таблице, то вы уже в 3NF. Но если цена зависит от какого-либо другого столбца, например, от производителя товара, тогда вам нужно будет разбить таблицу на несколько таблиц, чтобы устранить эти переходные зависимости. Цель состоит в том, чтобы гарантировать, что каждый столбец зависит только от первичного ключа таблицы, а не от какого-либо другого неключевого столбца.

Существуют дополнительные уровни нормализации помимо 3NF, но они реже используются на практике.

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

Это системы управления данными, которые не применяют реляционную схему к данным. Нереляционные базы данных часто называют базой данных NoSQL, хотя некоторые поддерживают вариант языка SQL.

Обычно используются четыре распространённых типа нереляционных баз данных.

Аналитические модели позволяют структурировать данные для поддержки анализа.

Данные для аналитики переносятся из операционных систем в хранилища данных. Здесь моделируется полученная реляционная база данных, которая включает в себя её нормализацию для повышения эффективности и переработку в схему, подходящую для вашего бизнес-варианта использования.

Эти модели основаны на наличии связанных таблиц данных и определяют числовые значения, которые вы хотите проанализировать или сообщить (меры), и объекты, с помощью которых вы хотите их объединить (измерения).

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

Для дальнейшего повышения эффективности данные могут быть предварительно агрегированы в OLAP-модель или куб. Интуитивно эти модели образуют многомерные структуры, напоминающие куб, в том случае, если вы агрегируете данные ровно по 3 измерениям, например, выручку от продаж по клиенту, продукту и времени.

Затем аналитики данных и последующие пользователи получают информацию, обогащая данные по своему выбору (путём объединения таблиц) и выполняя интересующие их агрегации.

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

Я надеюсь, что вы нашли эту статью полезной!