1 Реляционное моделирование для экстремального масштабирования хранилищ данных Алексей Халяко Program Manager II

2 Темы Базовые понятия архитектуры хранилищ данных Staging/ODS/архивирование и моделирование Моделирование измерений Почему это моделирование предпочтительно и что плохого в 3NF Типы запросов Стратегии секционирования

3 Что реально имеет смысл. В зависимости от того, какого автора вы читали, они называют одну и ту же «сущность» разными именами Staging/ODS/Архив EDW/DW/Hub Витрина данных, Exploration Mart, уровень отчетов И т.д... до умопомрачения Можно остановиться и перестать заниматься ерундой. В наших диаграммах всегда присутствуют два важнейших объекта: Хранилище – данные находятся физически (на дисках) Трансформации – данные переносятся с одного хранилища на другое. Объект «хранилище» характеризуется «моделью» данных

4 Базис архитектуры– Staging Staging Source C Source C Source B Source A ODS Magic Memory Pipeline Source D Staged Tables Staged Tables Staged Tables Staged Tables

5 Staging/ODS/Архив (SODA) Две задачи Хранить данные, пришедшие от источников (хранилище исходных данных) Временно или «почти» временно? Физическое (диск) хранилище промежуточных наборов данных Иногда, несколько уровней хранилища исходных данных Несколько групп разработчиков часто будут называть каждый уровень по- новому, если другое имя уже используется (как пример: если ODS уже используется, то следующий уровень будет зваться Staging) ИТ обычно не терпит идею многих копий данных Однако: One Version of the Truth != одна модель для всех данных Промежуточные результаты: Сервер, как «расширенная» tempdb, которая «переживет» отказ системы...Больше сказать нечего – обычные преимущества промежуточного хранилища данных являются само собой разумеющимся для тех, кто строил проекты с огромными трансформациями данных Staging/ODS/Архив = SODA (Silly Org Driven Abyss*) * Безысходность, продиктованная неразумной организацией

6 Еще об данных из исходных хранилищ Данные, поступающие из хранилищ исходных данных могут быть временными или «полу-временными» Полу-временные данные имеют ряд больших преимуществ Решение о детализации данных всегда может быть изменено Источник всегда может «забыть» какие-то данные, но мы о них помним. Можно сымитировать источник исходных данных, если нужно изменить модель хранилища БЕЗ какого-то взаимодействия с самим источником исходных данных Долговременные промежуточные данные из источника защищают пользователей от неопределенностей Давайте говорить о моделировании, которое имеет смысл Договоримся о «старении» данных каждого хранилища исходных данных (но не переусердствовать)

7 Стоимость архитектуры SODA Хранилище может быть дешевым Использовать SATA или ленты для долговременного хранения промежуточных данных Решить как быстро «стареют» данные в источнике Один хорошо понятный и определенный тип доступа (откуда здесь появиться пользователям?) Данные просто распределены между серверами. Нет необходимости в «СуперБазе» Можно вообще ограничиться дешевыми DAS Да, но вы не сможете гарантировать эффективный и простой доступ к 3х годичным данным? Да никаких проблем – мы сможем хранить эти данные за Х $$$. И всегда можем их удалить, если Вы вдруг измените решение. Не попадайте в западню моделирования хранилища по образу и подобию исходного хранилища данных! Минимизируйте усилия- источники могут иметь «удивительную» структуру -. Так пусть ETL разбирается с этим. Не пытайтесь оптимизировать источники – просто используйте типы данных, которые гарантирую «прием» данных без ошибок. Экономьте ресурсы на перезагрузках данных из источников. Так и так придется данные загружать неоднократно. Гибкость в изменении модели и при росте данных.

8 Базисная архитектура – всё для пользователя! EDW Mart M1 Mart M2 M3 Mart M1 Mart M2 M3 Kimball Inmon SODA

9 EDW или не EDW? Не попадайте в западню Теория Инмона: Шаг 1: Сделайте планирование EDW проектом всей компании Шаг 2: Декларируйте: one version of the truth = одна база для управления всеми данными! Шаг 3: Оцените неисчисляемые требования к базе данных, которые защитят инвестиции в проект в долговременной перспективе, однако при ожидании компании роста в 100% в следующие 5 лет, затраты на «железо» будут раны нулю. Если тут вдруг включилось рациональное мышление, перейдите к шагу 2 Повторяйте шаги 2-3 до тех пор пока вас не уволят, или же пока вам не придется работать с идиотической, бесполезной, построенной на политическом влиянии и компромиссах модели.... Обычно этот подход продиктован страхом потери данных Напомню: нам не нужно бояться потерять данные SODA хранит копию для быстрой перезагрузки Эта копия также может хранить версию трансформации (откуда и когда она взялась) Если вдруг потребуется дополнить данные, мы перепишем и перезапустим ETL

10 Мини EDW Случаются ситуации, когда бывает полезно иметь физическую копию оговоренных «версий правды» Допустим, некоторые обще используемые Пример: измерения, особенно схожие представления исторических данных «Материализация» этих данных часто характеризуется более эффективностью ETL процессов и хранения данных Витрина данных (*) при любом EDW – может быть использована как «прототип», чтобы понять, какие типы данные являются обще используемыми Также полезно хранить «преднайденные»версии фактов.... * tactical data mart

11 Вместо того, чтобы думать об EDW … начните собирать требования бизнеса Данные должны быть доступны через … секунд Отчеты содержат активности пользователей за последний час В случае, если потребуются предоставить данные официальным органам, данные за последние..лет должны быть доступны Например, старые данные могут быть доступны на медленном хранилище В случае выхода системы из строя, последние 3 дня должны быть доступны в первую очередь после начала процесса восстановления. Требования могут идти дальше и быть сложнее Основной вывод: определитесь с основными требованию по доступности, устареванию и потере данных.

12 Что такое latency (задержка)? EDW Mart M1 Mart M2 M3 Mart M1 Mart M2 M3 Kimball Inmon SODA t1 t0 t2 t3 t4 T (data visible to end user) = t1 + t2 + t3 + t4 t0 t1 t2 T (data visible to end user) = t1 + t2

13 Бизнес запросы Определить запросы, которые пользователи выполняют ежедневно. Обычно: Отчет: Поведение подписчика за период времени (billing за прос по определенному сервису) Отчет: Тип поведения подписчика ( уточняющий запрос) Отчет: Поведение всех подписчиков за период времени ( подготовка данных для витрины) Отчет: все пользователи с определенным поведением ( фильтруем всех звонивших в другие сети) Отчет: все звонившие в определенной области/ switch

14 Стратегия секционирования логическая Три возможности Функциональное секционирование – секционирование по subject area Пример: разделить Call Detail Records и Customer Invoices Секционирование по дате – интервал времени. Пример: Разбить по годам 2010, 2009, 2008 Секционирование по ключу/пользователю – по какому-то признаку, который используется для «фильтрации» Пример: секционирование по коду региона или коду пользователя Эти критерии также являются и требованиями бизнеса

15 Секционирование Главная проблема: местонахождение данных Используются вместе = хранятся вместе Сетевой трафик очень дорого стоит Логическое секционирование должно аккуратно соотноситься с физическим Избегайте «нереально-идеальных» архитектур c = 300K km/s - это ограничение не оптимизировать Примеры: Задержка по I/O операциям: 1-5ms (в лучшем случае) Сетевая задержка : 1ms Доступ к памяти: 300ns

16 Секционирование в SQL Варианты: Секционирование таблиц на разных серверах (DPV) Секционирование по нескольким таблицам (PV) Секционирование таблицы (Partition schema/function) Что нужно учитывать: Кластеризация и индексирование для повышения скорости отклика Аггрегирование Загрузка данных Управляемость / Backup Хранилище/архивное хранилище

17 Внутри сервера Local Partitioned View За: Online switching Online Index Rebuild Меньше статистика Против: Поддержка представлений(views) Необходимо следить за constraints Ограниченное количество секций Mix: и то и другое Table Partitioning За: Меньше объектов в базе Больше секций (1000/15 000) Против: Невозможен online switch (SCH-M locks) Перестроение индекса online только по всей таблице Статистика только на всю таблицу (Фильтрованная статистика может помочь)

18 Секционирование по дате Секционирование по дате, сценарий «скользящее окно»

19 Пример применения: Telco сценарий Телекоммуникационные компании Загрузка ~1 TB данных в день Загружать необходимо параллельно: ограниченное окно загрузки и требования по доступности данных Обновлять данные в аналитических системах 4 раза в день «Архивные данные» доступны 3-5 лет. Большая часть данных используется отчетными и аналитическими системами Большие и долго выполняемые SELECT Некоторые ad-hoc запросы к «сегодняшним» данным Fraud detection запросы

20 Движение данных

21 Секционирование для доступности to INSERT / UPDATE MSCFact (View) MSCFact (View) ALTER VIEW + SWITCH ALTER VIEW + SWITCH

22 Создание двух уровневого секционирования Area Code: 150 Area Code: 151 Area Code: 152 Area Code: 153 CSV SELECT... FROM FactCDR WHERE PhoneNumber = AND ChargingDateTime = CREATE CLUSTERED INDEX CIX_Date ON MSCFY2009( ChargingDateTime, CarrierCode,PhoneNumber )

23 Example: Multi Level Partitoning Area Code: 150 Area Code: 151 Area Code: 152 Area Code: 153 Area Code: 150 Area Code: 151 Area Code: 152 Area Code: 153 FactMSC (view) ALTER TABLE dbo.MSCFY2009 ADD CONSTRAINT CK_DATE CHECK ( [ChargingDateTime] >= ' ' and [ChargingDateTime] =' and [ChargingDateTime]

24 Как тратится время при загрузке данных?

25 Что есть хороший ключ? ХарактеристикиОбоснование МаленькийПотому что тогда можно больше ключей загрузить в память и потратить на это меньше IO Это integerПотому что CPUs работает существенно быстрее с integer и это вряд ли изменится в будущем Единожды определив запись, не будет меняться уже никогда Потому что нам нужно в случае изменения записи избежать массивных изменений спровоцированных изменениями других записей, зависимых от изменяемой (аргумент против нормализации) Никогда не используется повторноПотому что мы не хотим, чтобы новые записи неким магическим образом унаследовали данные, которые мы вроде бы как удалили. Результат: достаточно большой спектр значений, чтобы избегать повторений Все, что описано вверху.. Ключ должен быть «тупым» – он не должен знать ничего о записи, к которой он привязан Потому что даже если значение в записи изменяется, ключ должен остаться прежним и ссылаться на те записи, на которые он ссылался. (исключение: время не меняется никогда!) Пользователи НЕ ДОЛЖНЫ помнить этот ключМы можем разрешить пользователю знать, что ключ существует, однако никогда не давать возможность оперировать ключом напрямую. Смысл ключа – объединения, выполняемые пользователями. Запрашивается – машинами.

26 Проблема хранилища исходных данных Нужные хорошие ключи, особенно если количество данных растет Хранилища исходных данных такие ключи не смогут предоставить никогда Потому что эти ключи обычно созданы программистами, а не специалистами по моделированию данных Потому что иногда наличие запоминающегося ключа может быть полезна хранилищу исходных данных Мы могли бы довериться источнику в предоставлении хороших данных Однако, это аналогично тому, что верить в то, что источник предоставляет «чистые» данные... А это, как известно, не происходит никогда. Не верьте, что источник предоставит хорошие ключи. Серьёзно – НИКОГДА!

27 Проблема суррогатных ключей Суррогатные ключи созданы для двух случаев: 1. Используются как компактные integer ключи 2. Выступают в роли history trackers А так как мы всегда можем изменить решение, как мы отслеживаем историю, то суррогатные ключи нам не подходят Наблюдение: имеет смысл только, когда мы показываем SCD второго типа конечным пользователям

28 От источника к пользователю Предположения: Источник никогда не предоставит «хороший ключ» Пользователю нужна модель с измерениями или нечто, что позволит отслеживать историю. На нужно: Связать ключ источника данных с «хорошим ключом» Имеет смысл хранить только «хорошие ключи» Связать «хороший ключ» с суррогатным ( который не «хороший») Не тратить много времени на поиске по ключу.

29 Жизнь таблицы фактов Order Lines Order Lines Order Headers Order Headers ColumnType ProductKeyCHAR(10) AmountDECIMAL(10,2) OrderKeyINT PriceDECIMAL(15,2) ColumnType CustomerKeyCHAR(10) OrderKeyINT DateDATETIME Internal_IDVARCHAR(20) Copy Lookup + Join + Project SODASource ColumnType ID_ProductINT ID_CustomerINT ID_DateINT SaleMONEY ID_Product Product ID_Product SK_Product ID_Product SK_Product Product History Product History ID_Customer Customer ID_Customer SK_Customer ID_Customer SK_Customer Customer History Customer History EDW Mart Reload Lookup + Agg ColumnType SK_ProductINT SK_CustomerINT SK_DateINT SaleMONEY Data Mart Sales Stage.Order Lines Stage.Order Headers

30 Как отслеживать/изменять историю ColumnType ProductKeyCHAR(10) AmountDECIMAL(10,2) OrderKeyINT PriceDECIMAL(15,2) ColumnType CustomerKeyCHAR(10) OrderKeyINT DateDATETIME Internal_IDVARCHAR(20) Lookup + Join + Projec SODA ColumnType ID_ProductINT ID_CustomerINT ID_DateINT SaleMONEY EDW Data Mart Sales Stage.Order Lines Stage.Order Headers Ключ может измениться Наше представление об истории может измениться. ColumnType ProductKeyCHAR(10) AmountDECIMAL(10,2) OrderKeyINT PriceDECIMAL(15,2) CustomerKeyCHAR(10) DateDATETIME Internal_IDVARCHAR(20) ColumnType ID_ProductINT ProductKeyCHAR(10) ColumnType ID_ProductINT ProductKeyCHAR(10) Prouct NameCHAR(10) ColumnType CustomerKeyCHAR(10) ID_CustomerINT ColumnType ID_CustomerINT CustomerKeyCHAR(10) CustomerNameCHAR(20) CustomerAddressCHAR(20) Date_BEGINDATE Date_EndDATE

31 BETWEEN двумя мирами Какой join нужно построить, чтобы получить результат? SELECT... FROM Sales S JOIN Product_History P ON S.ID_Product = P.ID_Product AND ID_Date BETWEEN P.Valid_From AND Valid_To ColumnType ID_ProductINT ID_CustomerINT ID_DateINT SaleMONEY ID_Product Product ID_Product SK_Product Valid_From Valid_To ID_Product SK_Product Valid_From Valid_To Product History Product History Sales ColumnType SK_ProductINT SK_CustomerINT SaleMONEY Как это повлияет на работу оптимизатора? Нет никаких статистик, которые могут помочь оценить эффективно BETWEEN Вы реально хотите, чтобы пользователи страдали от такой модели?

32 Высокоуровневая архитектура EDW может играть роль промежуточного хранилища agreed results Выполняем сколько угодно операционных задач (чистота данных) Полагаемся на SODA, чтобы «проиграть заново» данные Быстрый ETL «откат» не так сложно организовать! Не полагаться на ключи источника, оптимизировать для оптимальных типов данных Исходя из этого мы предполагаем, что: Все ключи integers Данные никогда не теряются = мы можем моделировать и «выкидывать» данные, которые нам не нужны. Оптимизация для наибольшей скорости доступности данных Данные joined по одному ключу Таблицы pre projected – мы работаем только со столбцами, которые нам нужны

33 Нормализовать или де- нормализовать? Нормализация Меньший объем хранилища Больше гибкости/управляемость Меньше влияние от изменения модели данных Можно упражняться в Joinах Проще управлять Проще загружать данные (ой ли?) Никогда не теряется история Хранилища в ответе за всё! т.е. Teradata/PDW/Hadoop etc.. Де-нормализация Быстрые запросы (JOIN) Не забываем о column store (оптимизированно) Понятно пользователям Меньше вероятность, что оптимизатор засбоит Ожидаемая производительность

34 Типичный подход групп IT Разве это выглядит как плохая модель? Does it look like a bad design? Customer измерение Product измерение Sales измерение SELECT ALL Customers from Geography = 'Country' WHERE PRODUCT = 'Product' and SalesAmount > '$100USD'

35 Магические JOIN!

36 Sizing Storage cache 4GB-512 GB 200K IOPS sec Up to 2 PB Server 8 CPU up to 8 cores each Up to 2TB memory Is this enough to build DW?

37 Sizing Prototype system Identify main system load through the set query types Scan queries balance vs look up queries Use the approach from Fast Track core calculator