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

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

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

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

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

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

  • Рабочий процесс хранилища данных;
  • Нормализация базы данных;
  • Нереляционные базы данных.

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

1. Рабочий процесс хранилища данных

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

  1. Данные находятся в озере данных.
  2. Данные загружаются в хранилище данных.
  3. Создаётся модель данных.
  4. Аналитики используют данные.

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

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

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

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

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

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

Поскольку озёра данных обычно относятся к области больших данных, что предполагает распределенные вычисления и хранение с помощью таких платформ, как Apache Hadoop, обработка ETL выполняется инженерами по обработке данных, которые настраивают задания Hive или Spark, обрабатывающие большие объёмы данных параллельно с использованием многоузловых кластеров. Эти конвейеры включают как пакетную обработку статических данных, так и обработку потоковых данных в режиме реального времени.

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

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

Хранилища данных, таблицы, схемы и нормализация

Хорошо, двигаемся дальше! Время обогатить наши данные, находящиеся в озере.

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

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

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

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

Это то, что позволяет обогащать данные путём объединения таблиц вместе.

Например, в записи заказа на продажу можно ссылаться на первичный ключ клиента, чтобы указать, какой клиент разместил этот заказ:

-- Joining two tables using SQL
SELECT * FROM Customer C
JOIN Orders O
ON C.ID = O.Customer

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

Подробнее о нормализации мы расскажем позже.

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

Давайте подробнее поговорим о схемах баз данных.

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

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

Каждый объект представлен строкой с уникальным ключевым значением.

Остальные столбцы представляют атрибуты объекта — например, у продуктов есть названия и категории, а у клиентов – адреса и города. В большинстве аналитических моделей принято включать измерение времени, чтобы вы могли агрегировать числовые показатели, связанные с событиями во времени.

Итак, вот как всё это выглядит:

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

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

Чтобы быть более конкретным:

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

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

Вкратце:

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

-- Find total sales for each customer and product in 2022
SELECT c.name, p.name, s.sum(s.Revenue) FROM Sales s
JOIN Customer s
ON c.Key = s.CustomerKey
JOIN Product p
ON p.Key = s.ProductKey
JOIN Time t
ON t.Key = s.TimeKey
GROUP BY 1, 2
WHERE t.Year = '2023'

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

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

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

Аналитическая модель данных

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

Они называются моделями или кубамиоперативной аналитической обработки (OLAP).

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

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

Поскольку данные OLAP предварительно агрегированы, запросы для возврата содержащихся в них сводок могут выполняться быстро.

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

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

Довольно круто, не так ли?!

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

Важно! Хотя мы обычно называем аналитическую модель кубом, измерений может быть больше (или меньше) трёх — нам просто нелегко представить больше трёх!

Готов к употреблению!

Аналитики данных используют данные из этих аналитических моделей (шаг 3) – или непосредственно из хранилищ данных (Шаг 2) – или даже из ‘необработанных’ наборов данных, хранящихся в озере данных (Шаг 1), для изучения данных и создания информационных панелей, отчётов и визуализаций для получения информации.

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

Нетехнологичные специалисты в организации могут самостоятельно выполнять анализ данных и составлять отчёты по ним, особенно если они уже визуализированы в инструменте BI, таком как PowerBI или Tableau.

Эти визуализации, построенные на хороших аналитических моделях данных, показывают сравнения, тенденции и ключевые показатели эффективности (KPI) и могут принимать форму диаграмм, графических построений, отчётов, которые часто распространяются в документах и презентациях PowerPoint, веб-панелях мониторинга и интерактивных средах (например, PowerBI и Tableau), где последующие пользователи — даже C-Suite — могут легко визуально изучать данные и принимать решения, основанные на данных.

2. Нормализация базы данных

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

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

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

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

Первая нормальная форма (1NF)

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

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

Вторая нормальная форма (2NF)

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

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

Третья нормальная форма (3NF)

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

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

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

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

3. Нереляционные базы данных

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

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

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

  • Базы данных с ключами-значениями, в которых каждая запись состоит из уникального ключа и связанного значения, которые могут быть в любом формате.
  • Базы данных документов, представляющие собой особую форму базы данных ключ-значение, в которой значением является документ JSON, для анализа и запроса которого система оптимизирована.
  • Базы данных семейства столбцов, в которых хранятся табличные данные, состоящие из строк и столбцов, но вы можете разделить столбцы на группы, известные как семейства столбцов. Каждое семейство столбцов содержит набор столбцов, которые логически связаны друг с другом.
  • Графические базы данных, которые хранят объекты в виде узлов со ссылками для определения взаимосвязей между ними.
Базы данных, SQL и моделирование данных — краткий ускоренный курс

Краткое содержание

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

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

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

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

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

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

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

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

+1
0
+1
0
+1
0
+1
0
+1
0

Ответить

Ваш адрес email не будет опубликован. Обязательные поля помечены *