Data warehouse как создать

Создание Data Lake и Warehouse на GCP

Эта статья не будет технически глубокой. Мы поговорим о Data Lake и Data Warehouse, важных принципах, которые следует учитывать, и о том, какие сервисы GCP можно использовать для создания такой системы. Мы коснёмся каждого из GCP сервисов и поймём почему они будут полезны при создании Data Lake и Warehouse.

Прежде чем перейти к своей версии Data Lake и Data Warehouse, я хотел бы привести несколько известных архитектур, с которыми вы, возможно, уже знакомы, если интересуетесь этой темой. Архитектура, которую я бы предложил, будет более общей, чем эти: Cloud Storage as a data lake и Architecture: Marketing Data Warehouse.

В своей более общей версии Data Lake и Data Warehouse я расскажу о таких сервисах GCP, как Data Transfer Service, Dataproc, Cloud Storage, Cloud Scheduler, BigQuery, и Cloud SQL.

Мы соберем «пазл» и посмотрим, какую пользу и как можно использовать перечисленные сервисы.

Основные соображения

Есть много важных моментов, о которых следует подумать во время проектирования собственного Data Lake и Warehouse. Тогда, когда функции Data Warehouse почти полностью покрываются BigQuery, функции Data Lake требуют более тщательного анализа и подхода к реализации. Все эти «модные слова», такие как отказоустойчивость, надежность, масштабируемость, в равной степени применяются и к Data Lake. Очень важно, чтобы данные были хорошего качества, когда они попадают в хранилище, чтобы обработка данных была быстрой, а интеграция новых источников данных было простым процессом, и многое другое. Все эти пункты можно разделить на три основные группы вопросов, которые касаются определенных областей системы. Я хочу упомянуть о них лишь вкратце, поскольку их обсуждение не является целью данной статьи.

Передача данных. В эту группу входят вопросы о самой передаче данных и их хранении. Необработанные данные должны передаваться из вашей основной системы в Data Lake без риска потери. Они должны храниться «как есть». Data Lake не должен давать сбоев при неожиданном изменении входящих данных. В случае неожиданного увеличения объема входящих данных система все равно должна быть в состоянии быстро его обработать. Новые интеграции источников данных должны быть делом нескольких часов (даже минут), а не дней. В случае простоев и сбоев ничего не должно теряться, и система должна восстанавливаться автоматически.
Организация данных. В этой группе вопросов вам нужно ответить, как ваши данные будут храниться и запрашиваться в Data Lake, и у этого есть свои проблемы. Структура входящих данных подвержена изменениям со временем. Может отсутствовать корреляция между временем поступления данных в Data Lake и бизнес-временем, связанным с объектом данных. Одни и те же данные могут поступать от нескольких поставщиков. Вам нужно будет понять как организовать партиции необработанных данных, чтобы поледующие выорки осуществлялись быстро и экономично. Вам также может потребоваться рассмотреть права доступа к данным и безопасность для разных ролей и пользователей, хотя на этом уровне это можно пропустить.
Обработка данных. Последняя группа связана с методами обработки данных. Прежде всего, вы хотите, чтобы обработка данных была достаточно простой и независимой от инструментов, чтобы ваша организация могла использовать лучшие инструменты для достижения целей. Написание новой логики обработки должно быть таким же простым, как написание нового SQL кода, когда вы знаете структуру данных. Логика обработки должна в равной степени иметь возможность обрабатывать как малые, так и большие объемы данных. Создание новых пайплайнов обработки данных должно быть быстрым, а развертывание — простым. Должно быть одинаково просто создать обработку в реальном времени или пакетную обработку, обработку по запросу или те, которые постоянно выполняются.

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

Выбор между Cloud Storage и BigQuery для Data Lake

Один из первых вопросов касается использования BigQuery или Cloud Storage для Data Lake. Если ваши данные имеют очень стабильную структуру и их схема меняется со временем очень медленно, и вы можете считать их постоянными в течение длительного периода, то BigQuery может стать для вас хорошим выбором. У вас могут быть внешние таблицы, которые будут читать файлы из Cloud Storage, и вы можете работать с такими таблицами почти так же, как и с обычными. Вы можете автоматизировать импорт таких данных в таблицы продуктов данных в хранилище данных. Те, очень редкие случаи, когда ваши данные меняют свою структуру, вы покрываете их наполовину вытоматическими процессами и не имеете проблем с поддержкой разных схем одновременно. Вы можете построить обработку данных с помощью SQL и Scheduled Queries в BigQuery и сохранить необходимые навыки, очень удобные для DBA. Для более глубокого погружения имеет смысл почитать: What is a data lake?

В тех случаях, когда структура данных меняется чаще, усилия по поддержке такого data Lake, использующего BigQuery, могут расти в геометрической прогрессии и становиться слишком высокими и слишком сложными, чтобы оставаться рентабельными для бизнеса. В таком случае Cloud Storage и процессы вокруг него могут быть лучшим решением. И это то, что мы выбрали для себя, потому что в нашем случае схема данных меняется от записи к записи, и у нас есть несколько конвейеров данных и поставщиков.

Выбор формата сырых данных: Parquet, Avro или JSON

Выбор между форматами данных имеет ту же основу, что и выбор между BigQuery и Cloud Storage для Data Lake. Форматы файлов Parquet и Avro требуют, чтобы в файлы была встроена определенная структура для поддержки функций, которые они предлагают. Например, Parquet чрезвычайно эффективен для запросов, потому что это столбцовое хранилище данных. Он также предлагает лучшее сжатие. Но он более требователен к ресурсам для операций записи, потому что требует больше процессора и оперативной памяти для работы. Avro выглядит лучшим выбором для случаев, когда операции записи должны быть быстрыми. Вы можете добавлять построчно к файлам Avro, но не можете делать это с файлами Parquet. Это делает Avro более предпочтительным в сценариях, где вы обрабатываете данные в потоковом режиме. Я нашел очень хорошее объяснение деталей этих форматов: Big Data File Formats и Storage size and generation time in popular file formats.

Таким образом, если ваши схемы развиваются медленно и вы можете гарантировать, что все записи будут иметь одинаковую структуру в одном файле, то Avro или Parquet — очень хороший выбор. Но когда структура часто меняется и вам нужно хранить в одном файле записи с разными схемами, это становится сложной задачей.

Parquet и Avro требуют, чтобы схема была встроена в файл. Если схема ваших записей данных меняется почти для каждой записи, вам нужно будет корректировать каждую запись, чтобы подогнать их на какую-то «целевую» схему, или создать множество файлов с немного различающейся схемой. Avro заявляет, что поддерживает эволюцию схемы как для добавления, так и для удаления столбцов, но наши исследования показывают, что для этого по-прежнему требуется, чтобы все записи имели одинаковую структуру в одном и том же файле. Решить можно, но усложняет систему и добавляет дополнительные точки отказа и накладные расходы на поддержку такой системы.

Напротив, формат JSON не имеет вышеупомянутых проблем и хорошо поддерживается многими платформами обработки. Единственная проблема, о которой вы можете подумать, — это размер текстовых файлов с данными JSON. Spark позволяет читать файлы с разными схемами. Когда вы указываете какую-то ожидаемую схему, Spark правильно загружает её, даже если записи имеют существенно разные схемы. Также вы можете хранить записи с разными схемами в одних и тех же файлах. В конце концов, после нескольких экспериментов, мы остановились на JSON.

Выбор правильной организации данных для Data Lake

Основная цель организации данных — определить единую структуру в Data Lake и помочь выполнять эффективные запросы к данным. То, как вы храните данные, как вы их организуете, партиционируете, все это влияет на производительность запросов. Очень хорошее руководство об этом тут: How to Organize your Data Lake. Основная идея заключается в том, что дата приема должна быть частью организации данных на 1-м уровне вашего Data Lake.

Второй уровень Data Lake может быть более адаптирован к потребностям бизнеса. Это даст возможность перестроить 2-й уровень из 1-го, если вы решите реорганизовать подготовленные данные.

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

GCP сервисы для Data Lake и Warehouse

Теперь я хотел бы поговорить о строительных блоках возможного Data Lake и Warehouse. Все компоненты являются сервисами GCP и полностью покрывают потребности конкретных областей: передача данных, обработка и запросы. Мы не будем настраивать какой-либо из сервисов, но я расскажу об их основных преимуществах и о том, почему они подходят для Data Lake и Warehouse. Схему системы, о которой я говорю, вы можете найти в начале статьи.

Data Transfer Service

Для приема данных в Data Lake вам необходимо установить надежную передачу данных из исходной системы. Data Transfer Service помогает передавать данные из AWS, Azure, Cloud Storage или on-premise в другое on-premise или Cloud Storage хранилище. Служба передает файлы, проверяя передачу и может возобновить её, если она была прервана. Агенты для on-premise можно масштабировать для повышения производительности передачи данных.

В нашем случае мы переносим данные из on-premise в Cloud Storage. Для настройки необходимо создать пул агентов в Data Transfer. Все агенты общаются друг с другом через службу Pub/Sub, чтобы координировать, какие файлы были отправлены и где возобновить работу в случае сбоя инфраструктуры. Например, если агент умирает или связь прерывается, другие агенты, которые еще могут работать, продолжат передачу. В конечном итоге все агенты работают совместно, что делает передачу данных отказоустойчивой и не пропускает ни одного файла. Поробнее тут: Manage transfer agents.

Dataproc

Dataproc — это управляемая Apache Spark и Apache Hadoop кластеры. Это ядро наших ETL процессов для сырых данных. Можно настроить автоматическое масштабирование, которое по умолчанию использует вытесняемые вторичные рабочие узлы и основано на метриках YARN. Можно создать минимальный кластер из 1 главного узла и двух рабочих узлов и установить в автомасштабировании максимальное количество вторичных рабочих узлов какое-то высокое значение. Подробнее тут: Autoscaling clusters. Отличие основных и второстепенных рабочих узлов в том, что последние не могут хранить на них данные. Также возможно использовать Dataproc в бессерверном режиме, отправляя пакетные задания. Мы постоянно запускаем несколько ETL каждые три минуты. Из-за этого наш кластер Dataproc работает постоянно.

Wildcard в файловом пути

Очень интересной особенностью Spark является то, как он работает с шаблонами путей для загрузки файлов. Например, это то, что можно указать в качестве источника для spark.read.json . Предположим, у вас есть следующие структуры папок в Data Lake: gs://object/year/month/day/hour/minute/instance.json . Почти любой сервис поддерживает указание звездочки в конце строки, например: gs://object/year/month/day/hour/* — если вы хотите загрузить данные за определенный час в определенный день. Но не все поддержат это: gs://object/year/month/*/*/minute/* — если вы хотите загрузить данные только за конкретную минуту за весь месяц. И ваша организация папок может быть немного сложнее, чем эта. Особенно на 2-м уровне Data Lake, где вы будете работать с подготовленными файлами данных, где организация хранения может включать бизнес-информацию. Например, вы можете захотеть загрузить данные только для определенного клиента за весь год.

SparkSQL

SparkSQL позволяет использовать хорошо известную DSL для работы с данными. Существует множество встроенных функций, которые очень ускоряют процесс обучения, особенно для DBA. Тут подробнее: Spark SQL, Built-in Functions.

Все ваши операции могут быть выполнены внутри оператора spark.sql :

spark.sql(""" CREATE OR REPLACE TEMPORARY VIEW my_view AS SELECT CAST(field AS FLOAT) AS mapped_field FROM another_view """)

Cloud Scheduler

Служба Cloud Scheduler позволяет активировать определенный URL-адрес или отправить сообщение в топик Pub/Sub по определенному расписанию. Мы используем его для запуска Data Transfer заданий и рабочих процессов Dataproc через URL-адрес. Планировщик использует GCP REST API для сервисов для запуска операций. Задания в Data Transfer не поддерживают расписания чаще одного раза в час. А задания в Dataproc вообще не имеют расписаний. Можно использовать Cloud Composer или Cloud Functions для запуска операций с Dataproc или Transfer Service, но это усложнит систему, добавляя больше компонентов и больше мест для отказа.

Cloud SQL

Этот компонент отсутствует в схеме нашего Data Lake и Warehouse, но мы его используем. Основная его цель — отслеживание операций и обеспечение обработки всех файлов, когда они поступают в Data Lake. Существуют Cloud Functions, которые срабатывают, когда новый файл поступает в Cloud Storage, и добавляют файлы в базу данных метаданных в Cloud SQL. Для преодоления временных ошибок для функций включена повторная попытка. При включенной повторной попытке функция будет пытаться добавить файл в течение следующих 7 дней и в конечном итоге гарантирует, что каждый файл будет добавлен в базу данных, независимо от временных ошибок. Мы используем PostgreSQL.

И последнее, но не менее важное

Остальные сервисы GCP не нуждаются в специальном рассказе, т.к. хорошо известны. Мы используем Cloud Storage для нашего Data Lake и BigQuery для Data Warehouse.

При организации Data Lake в Cloud Storage стоит настроить жизненный цикл для объектов данных. В одном и том же бакете могут быть объекты разных классов. Правила жизненного цикла могут помочь изменить класс объектов в зависимости от их возраста с Standard на Nearline и так далее. Даже если стоимость хранения данных невелика, когда вы имеете дело с огромным объемом данных и операций чтения, это будет генерировать заметную цену. Управление классами объектов несколько сократит расходы.

В BigQuery вы можете захотеть иметь несколько разделов для одного и того же продукта данных. Например, одна и та же история заказов может иметь разделение по клиентам и по местоположению. Таблицы BigQuery не поддерживают несколько партиций. Мы преодолеваем это, имея несколько таблиц с одинаковыми данными, но с разными разделами. Группа таблиц, принадлежащих одному и тому же продукту данных, генерируется одним и тем же ETL, поэтому поддерживать несколько таблиц несложно.

Послесловие

Проектирование Data Lake и Data Warehouse — интересный процесс со многих точек зрения: техническая архитектура и реализация, сотрудничество с людьми из разных сфер бизнеса и изучение расширенного использования систем, которые ранее использовались как «черный ящик». В дальнейших статьях я, возможно, подробно опишу использование службы Data Transfer, запуск Dataproc джобов через REST API и другие, если найду время между этими задачами.

Создаем аналитическое хранилище данных командой из 2-3 спецов

Цепочка создания ценности в процессе работы с данными (источник):

Ценность создается только на последнем шаге цепочки. Вы можете извлекать данные, обрабатывать их как угодно: чистить, обогащать, маршрутизировать по системам, накапливать в хранилищах, заливать в витрины и т.д. Все эти операции ценности никакой не приносят.
Ценность создается при принятии правильных решений. Поэтому, мне кажется, в начале пути нужно как можно быстрее научиться получать профит из данных и DWH, а затем постепенно улучшать модели данных и процессы разработки DWH.

Сейчас часто собирается всё что можно собрать в озеро, а на втором этапе начинается детальная проработка результирующих витрин. Считаю, что нужно мыслить от показателей к источникам данных, а не наоборот. Чем лучше вы проработаете показатели необходимые для анализа, разрезы в которых нужно анализировать показатели, тем меньше работы будет у дата инженеров (DE), а значит вы быстрее получите нужный результат.

Подробнее на эту тему: Six Things You Need to Know About Data Governance, также в этой статье есть хороший раздел “Продуктовый подход к DWH” о том, какие вопросы нужно задавать пользователям при создании витрин.

Сначала логика и архитектура, потом инструменты

Часто на презентациях я вижу картинки наподобие этой →

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

Поэтому один из моих базовых принципов — сначала логика и архитектура, а потом инструменты. Именно логике построения DWH посвящена статья.

Основы логики и архитектуры DWH

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

Поэтому, первая ключевая роль в создании DWH — архитектор, человек, который умеет думать и систематизировать. Через единообразие кода и правил нужно стремиться упростить жизнь DE. Большую часть времени DE должен тратить на создание продукта из данных.

Подрезка (фильтрация) таблиц в источниках при получении инкремента.
Типы загрузки таргета: append, upsert, full (snapshot), scd2, recreate.
Типы таргетов: ods, mart, scd2, data vault сущности.

Четко определить, что такое Джоб.
Иметь возможность отлаживать любую джобу/расчет локально.
Если есть ресурсы хранить “сырые данные” и историю их изменений, это упрощает исторические перерасчеты.

Джоба — основной кирпич системы. Джоба должна быть четко определена и проста.

Как лучше не делать джобы

Большие джобы работают долго, а значит у вас меньше возможностей для маневров.
При падении джобы её придется перезапускать полностью.
Трудно дорабатывать джобу.
Трудно развивать DWH.

Частично или полностью недоступен Git: сравнение, слияние, версионность джобов и т.д.
Ограничения на автогенерацию.
Вендор-лок.

Первый принцип создания джоб (DAG’ов)

Одна джоба загружает один таргет (таблицу).

Преимущества:

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

Этот принцип позволяет создавать действительно масштабируемые DWH, позволяя постепенно увеличивать число таблиц, слоев и DE.
Cколько джобов нужно для получения результата, полезного для бизнеса, зависит от числа слоев вашего DWH.

Пример 1. Данные из источника попадают сразу в витрину DWH, т.е. для одного таргета нужна одна джоба. Дополнительные слои не дают ценности бизнесу, поэтому для начала я рекомендую делать именно так. Разработка такой джобы займет не больше дня.
Пример 2. Данные из источников сначала складируются в озере данных. Такой подход лучше масштабируется по данным и разработчикам, однако разработка пары джоб в такой модели займет уже 2-3 дня.
Пример 3. Если делать всё правильно по популярной методологии Data Vault 2.0, то первого полезного результата можно ждать несколько недель. Мощь Data Vault 2.0 в том, что он позволяет прозрачно масштабировать DWH на любое число таблиц и DE.

С возрастанием числа источников, таблиц в DWH и DE нужно двигаться от примера 1 к 3.

Второй принцип создания джоб (DAG’ов)

job.py – инструкции исполнителю.
getdata.sql – получение данных.
recreate.sql – пересоздание таргета.

Исполнитель джобы

Когда приходит очередь выполнить определенный джоб, запускается команда
launcher.run_job(job_name=’marts.crm_bill’, method=’increment’)
Исполнитель джобы читает файл job.py из папки marts.crm_bill. Получает объект класса JobDag, и дальше выполняет задачи из него. Поскольку одна джоба загружает один таргет, все джобы маленькие – от одного до пяти шагов.

Пример файла job.py с одной задачей:

from job_launcher_package import job_module def get_job_dag() -> job_module.JobDag(): j = job_module.JobDag() params = < "connection": "dwh_main", "get_data_sql": "get_data.sql", "recreate_target_sql": "recreate_target.sql", "target_table_name": "", "target_table_type": job_module.JobTargetTableType.MART, "increment": < # при получении данных в where есть , # движок его заменяет в зависимости настроек инкремент "target_load_type": job_module.JobTargetTableLoadType.UPSERT_ROWS_BY_PK, "target_pk_field": "crm_bill_id”, "target_checkpoint_name": "crm_bill_dttm", # MAX значение этого поля сохранится в системную таблицу "source_filtering_type": job_module.JobSourceFilteringType.MORE_THAN, "source_filtering_field": "crm_bil_dttm", "source_filtering_field_type": job_module.JobFieldType.TIMESTAMP>> j.add_task(id=1, order=1,# шаги выполняются по порядку, шаги с одинаковым step_order - параллельно func='vertica_elt.load_data_vertica_to_vertica', # вызов функции исполнителя джобов params=params) # параметры функции исполнителя джобов return j

DE использует готовые функции движка, просто задавая параметры. Например: «target_load_type»: job_module.JobTargetTableLoadType.UPSERT_ROWS_BY_PK -тип загрузки данных в таргет.
Функция ‘vertica_elt.load_data_vertica_to_vertica’ — обычная python функция, которая должна иметь следующие входные атрибуты:

@core.lakehouse_function def load_data_vertica_to_vertica( launcher: job_launcher.JobLauncher,# connection strings и общие методы исполнителя джобов job_log: job_log_module.JobLog,# объект для логирования шагов task: job_module.JobTask, # данные по таску из job.py run_params, # параметры запуска, например method=’increment’ all_results): # результаты выполнения других шагов

Таким образом, все ELT/ETL процессы у нас запускает один исполнитель джобов. В исполнителе есть несколько хорошо отлаженных, документированных функций, которые делают всю работу.

По сути DE создает джобу декларативно. Такой подход резко снижает барьер входа для создания джобов и в разы — вероятность возникновения ошибок.

Оркестрация джобов

От исполнителя одной джобы переходим к их оркестрации.
Вариант 1. Более правильный, но сложный в поддержке. Если на уровне метаданных для каждой джобы определить источники и таргет, то можно автоматически создавать граф. Допустим job_2 уже отработал, тогда если job_3 закончит работу быстрее, чем job_1, то оркестратор запустит job_5, иначе job_4.

Проблемы:

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

Вариант 2. Разделяй и властвуй. Джобы объединяются в группы, у группы есть group_order. Внутри группы у джобы есть job_order. Оркестратор в порядке group_order выбирает группы, группы с одинаковым group_order запускаются параллельно. На уровне джобов и тасков такая же логика.

В обоих вариантах есть глобальный параметр — Параллельность — количество джобов/тасков, которые можно выполнять одновременно.

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

Инструменты и технологии

Данная статье не про инструменты, поэтому скажу лишь пару мыслей.
Если у вас меньше 4 терабайт данных, то не переусложняйте. Достаточно на мощном компьютере поднять PostgreSQL или ClickHouse для DWH. Плюс BI решение: Superset, Metabase или PowerBI.

Если данных больше 4 терабайт, или объемы быстро растут, то необходимо заложить горизонтальное масштабирование. Лучше, чтобы объем данных и вычислительные ресурсы можно было масштабировать по отдельности.
Hadoop — очень сложно и дорого. Почти всегда дешевле и быстрее облачная MPP + S3-совместимый storage.

Облачная MPP/Lakehouse система — ядро аналитического решения. Посмотрите на Google BigQuery, Snowflake, Databricks.

Итоги

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

Сначала логика, потом инструменты – решите задачу на псевдокоде. Поймите и отработайте основные сценарии, а затем уже начинайте выбирать оркестраторы и всё остальное.

Джоба — основной кирпич системы. Джоба должна быть четко определена и проста.
Одна джоба загружает один таргет (таблицу).
Джоба — это не визуальная схема, а код (мини-проект).
job.py + унифицированный исполнитель джобов ускоряет разработку ELT/ETL процессов и снижает порог входа — инвестируйте в движок. Аналитики смогут делать джобы.

Приложение. Основные типы витрин.

Транзакционная витрина – данные в строках не меняются.
Витрина снимков – все данные на определенную дату.
Накопительная витрина – у каждой строки есть дата начала и конца действия.
Витрина на основе запроса.

Транзакционная витрина – данные в строках не меняются

Самая простая и популярная витрина. Записи из источника попадают в неё один раз и не меняются.
Преимущества этой витрины — по ней автоматически строятся правильные графики и считаются суммы, например, продажи товаров.

Витрина снимков – все данные на определенную дату

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

Накопительная витрина – у каждой записи есть интервал актуальности

У накопительной витрины есть 4 дополнительных поля:

start_dttm, end_dttm – время актуальности записи.
is_actual – признак актуальности записи.
is deleted – признак удаления из источника.

Данные из источника забираются раз в час или в день. После загрузки в метаданные хранилища записывается максимально значение поля update_dttm, в котором хранится время обновления записи. В следующий раз данные забираются из источника с фильтром where source_table.update_dttm > MAX(target_table.update_dttm), т.е. берутся только изменившиеся и новые записи.
Пример: Постепенное дополнение заказа в интернет магазине.

Если в новых данных из источника приходит запись, которая уже есть в таргет-таблице DWH, то у записи в DWH проставляется end_dttm = NOW(), is_actual = false и после добавляется новая актуальная запись.

Таким образом в DWH есть все изменения конкретной записи, что удобно для анализа.

Витрина на основе sql запроса к источнику

К источнику делается произвольный запрос с фильтром source_table.update_dttm > MAX(target_table.update_dttm)
Чтобы не было дублей, из таргет таблицы стираются строки с id, которые пришли из источника. Опционально также могут стираться записи, удаленные из источника. Затем делается вставка всех пришедших записей в таргет.

Итоги по витринам

Все запросы к источнику обычно делаются не к продакшен базе, а к её реплике.
На мой взгляд, на старте создания DWH, описанные 4 типа витрин закроют почти все нужды бизнеса.
По мере взросления DWH, лучше переходить сразу к Data Vault минуя 3NF. Для начала крутое видео.

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

Интересно мне сообщества:
Чем отличается ваш подход?
Какие вещи я раскрыл слабо?

dwh
etl-процессы
архитектура системы

Big Data
Хранилища данных
Data Engineering

Что такое data warehouse и как его построить

Компании получают данные из множества источников. Рекламная информация поступает из Google AdWords, сессии пользователей — из Google Analytics, данные продукта — из MySQL, MS Server или MongoDB. Информация о платежах — из 1C. Кроме этого, есть тикет-системы, чаты, CRMs и даже Excel-файлы.

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

Оксана Носенко, лектор курса «SQL для аналитики» в robot_dreams, Senior Product Analyst в Jooble, объясняет, чем data warehouse отличается от базы данных и как создать собственное хранилище.

Хранение данных в «облаке»

Data warehouse — это система, которая хранит данные из разных источников для аналитики и составления отчетов.

Схема хранилища данных

Хранилища отличаются от баз данных по ряду признаков:

данные в warehouse не обязательно должны поступать в реальном времени (если иное не предусмотрено бизнес-задачей);
данные могут иметь разную структуру (в зависимости от источников);
хранилище не обязательно должно работать быстро. Главное, чтобы скорости хватало для решения всех аналитических задач.

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

Как создать data warehouse

Для хранения данных чаще всего используют cloud-решения. Их плюсы:

поддержка и масштабируемость: не надо выделять комнату для серверов и подключать новые в случае роста нагрузки. Обычно облачное хранилище масштабируется автоматически;
производительность: облачные решения работают быстрее традиционных и автоматически перераспределяют нагрузку;
доступ к данным: чтобы попасть в облачное хранилище, не нужно устанавливать сервер на компьютер. Достаточно открыть браузер и войти в облако. SQL-запрос можно делать даже со смартфона.

Основные cloud warehouses — Amazon Redshift, Google BigQuery, Azure.

У них разная стоимость, производительность, экосистема, поддержка.

В продуктовых IT-компаниях, где я работала, мы выбирали Google Big Query по этим причинам:

хранилище в GBQ запустить можно быстро и без помощи администраторов баз данных. Нужно только создать аккаунт в Google Cloud;
формат pay-as-you-go: оплата по мере потребления только за те услуги, которые используем. Не нужно заранее выбирать самый оптимальный пакет;
готовые pipeline-решения. Многие платформы предоставляют услуги трансфера данных в GBQ. Поэтому warehouse запускали только аналитики, без разработчиков и админов.

Пример GBQ Data Warehouse

Основной минус Big Query — плата за запросы. Каждый SQL-запрос использует определенный объем памяти, за который нужно платить в конце месяца. Поэтому советую поставить ограничение на количество запросов в день.

Создавая хранилище данных, нужно учитывать все сложности. Основная проблема — конструирование пайплайнов. Нужно настроить трансфер данных из всех источников в единое хранилище. Во время него придется столкнуться с несовершенными API, ограничениями по выгрузке или парсингом данных. Можно писать все соединения самостоятельно или использовать готовые решения (например, Owox, Alooma, Blendo). Тем, кто работает с большим объемом информации, лучше написать свои скрипты, которые будут синхронизировать данные каждый день.

Маленькому бизнесу без администратора баз данных стоит использовать pipeline-платформу. Ее стоимость зависит от объема информации и количества соединений.

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

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

курсы по теме:

SQL для аналитики и разработки

Data Warehouse: с чего начать?

В последнее время в компьютерной прессе появилось немало публикаций, посвященных построению хранилищ данных (Data Warehouse — DW) и организации работы с ними. Основные темы этих публикаций:

— описание преимуществ, которые получит руководитель предприятия от создания хранилища данных;

— описание функциональных возможностей конкретных программных продуктов;

— описание технологии построения хранилищ данных для разработчиков.

Однако “за кадром” остается ряд вопросов и, в частности, один из самых главных: общий план построения хранилища данных, причем понятный руководителям. Отсутствие такой методологии было обусловлено кажущимся недостатком опыта отечественных разработчиков, занимающихся DW, хотя в той или иной форме попытки создавать хранилища данных предпринимались ими начиная с середины 80-х. Тогда, правда, это называлось по-другому, функции хранилища не были так четко определены, технология работы не была столь пунктуально регламентирована.

Кроме того, от построения DW отпугивали большой объем внутренних капиталовложений и варьирования временных рамок (от 3 месяцев до 3 лет в зависимости от масштабов проекта — данные приводятся по оценкам западных специалистов). Для российского рынка информационных услуг существенным тормозом в построении DW был незначительный (5 — 8 лет) период существования основной массы предприятий на рынке, ибо потребности этого периода хорошо охватываются так называемыми транзакционными системами.

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

Концепция DW была предложена в 1990 г. Б. Инмоном и стала одной из доминирующих в разработке информационных технологий обработки данных 90-х годов. На мой взгляд, появление этой концепции было следствием неявного осознания того факта, что существует два основных функционально различных класса систем обработки информации.

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

В российской печати термин Data Warehouse переводится двояко: как хранилище данных и как информационное хранилище. Однако термин Information warehouse был введен корпорацией IBM в начале 80-х годов и, по утверждению ее специалистов, означает нечто большее, чем DW по Инмону. Поэтому было бы целесообразно пользоваться уже примелькавшимся термином “хранилище данных”, хотя он несколько хуже передает суть концепции. Терминология, используемая сейчас в рамках концепции DW, приведена в глоссарии.

Что же такое DW?

Согласно классическому определению Б. Инмона, DW есть предметно ориентированный, интегрированный, неизменный, поддерживающий хронологию набор данных, предназначенный для поддержки принятия решений. Следует отметить, что в этом определении соединены две различные функции: а) сбор, организация и подготовка данных для анализа в виде постоянно наращиваемой базы данных; б) собственно анализ как элемент принятия решений.

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

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

Функциональное определение понятия Data Warehouse

Отметим, что наиболее уязвимым местом использования DW на предприятии, с точки зрения бизнеса, является корректность его данных, полученных из разных источников. Данные перед загрузкой в DW должны быть либо “очищены от шума”, либо обработаны методами нечеткой логики, допускающей наличие противоречивых фактов. Например, данные о предприятии-партнере могут быть получены от разных экспертов, чьи оценки порой бывают диаметрально противоположными.

Заметим также, что интеграция в определении DW понимается не как интеграция информации по всем источникам функциональной деятельности предприятия, а в смысле согласованного представления данных из разных источников по их типу, размерности и содержательному описанию. Это есть интеграция данных от бизнес-процессов, а не самих бизнес-процессов. Бизнес-процессы интегрируются в рамках корпоративной информационной системы (КИС) вашего предприятия.

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

Что может испортить хорошую затею

Допустим, решено делать DW. Определены задачи и поставлены цели. Что может помешать (если не брать в расчет отсутствие денег)? Как известно, хорошую затею могут испортить только хорошие люди. Потому что в создании любых ресурсоемких долгосрочных систем человеческий фактор решает если не все, то очень многое. По оценкам западных специалистов, при создании удачных проектов DW лишь половина финансовых средств тратилась на аппаратно-программные средства, а другая уходила на консультации. Таким образом, система с DW обходится вдвое дороже, чем привычные нам сейчас информационные системы.

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

— Единообразие технологической концепции. При создании КИС обычно используются технологии Internet/intranet, так называемая Groupwise или их разумная комбинация. Независимо от выбора СУБД в рамках этих технологий формируется различная корпоративная технология обработки данных, по-разному распределяются нагрузки на сервер и клиентскую части и т. д. Единообразие подхода в какой-то степени обеспечивает ритмичность проведения работ.

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

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

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

— Фактор рынка информационных услуг. Судя по тенденциям, в ближайшем будущем на рынке информационных услуг появятся готовые к применению DW, аккумулирующие данные внешних по отношению к вашему предприятию источников. Такие услуги в виде рекомендаций и аналитических обзоров уже оказывают некоторые консалтинговые фирмы — так почему бы не придать им форму специализированного DW, которое может быть вмонтировано в вашу КИС или использоваться автономно?

— Не объединять плохо совместимое. Например, не следует объединять в программах типа “Торговый дом” транзакционную и аналитическую части в рамках одной технологии разработки. Это будет долгий и трудный процесс, поскольку подходы к моделированию и проектированию этих частей существенно различаются. Реализовать их по отдельности и проще, и быстрее. Однако есть и другие примеры, когда для разработки используются концепции более высокого уровня абстракции. Удачны, например, решения в Baan или ROSS system, при разработке которых была отработана методология слияния транзакционной и аналитической частей в рамках единой КИС. Зато они и стоят дорого.

Где искать выгоду

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

— Планирование продаж, прогнозирование и управление.

— Забота о клиенте.

— Разработка схем лояльности.

— Проектирование и разработка новых видов продукции.

— Интеграция цепочки поставок.

— Интеллектуальные технологии в организации бизнеса.

— Распространение DW из области стратегического планирования на текущие операции (здесь уместно отметить российскую фирму Ally, которая, на мой взгляд, пошла обратным путем — от операций к стратегическому планированию).

Решить перечисленные выше задачи вам помогут монографии директора фирмы Data Warehouse Network (Ирландия) Ш. Келли (Sean Kelly), а также консультанты фирм Oracle и Informix, которые в течение последних лет поддерживают концепцию DW в своих продуктах.

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

Мистер Фикс, у вас есть план?

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

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

— Создать рабочую группу проекта и включить в ее состав специалистов во всех областях использования DW.

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

— Стандартизовать представление данных для анализа и проверить их на полноту и противоречивость, а также определить процедуры извлечения их из источников.

— Провести моделирование, разработать схему и построить DW.

Рассмотрим эти действия более подробно.

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

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

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

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

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

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

На этом этапе следует:

— Идентифицировать все данные, которые будут храниться в DW, по предметным областям, а также зафиксировать все источники этих данных в информационной системе. Такими объектами могут стать продажи, покупки, сотрудники и т. д. Здесь важно определить исходя из задач управления совокупность объектов, подвергаемых анализу.

— Провести стандартизацию данных для DW, то есть привести все используемые данные к единому представлению в DW. Это станет первой итерацией в создании метаданных DW.

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

— Документировать в метаданных тип, размер, описание, наименование всех атрибутов объектов DW, т. е. определить смысл сохраняемых данных.

— Реализовать программные механизмы и процедуры преобразования данных для их загрузки в DW.

На этом этапе будет получен прототип DW.

4. На стадии стандартизации и очистки данных нужно:

— Проверить и оценить все источники данных по всем элементам данных, установить действительное содержание этих элементов и определить их стандартную форму для предприятия.

— Стандартизовать представления данных в масштабах предприятия.

— Стандартизовать данные по всем основным объектам DW.

— Стандартизовать события, криптографические коды и состояния объектов в DW.

— Реализовать принятые стандарты в программном обеспечении DW.

5. База данных DW может быть не реляционной. В принципе для реализации типичной схемы DW “звезда” (см. глоссарий) может быть использован любой тип базы данных. Здесь все зависит от сложившегося информационного стереотипа организации и финансовых возможностей проекта. В любом случае нужно иметь в виду следующие задачи.

— Разработка и загрузка общих словарей данных элементов DW (метаданных) и нормативно-справочной базы. Здесь я конкретизирую часть действий по стандартизации информации в нормативной базе данных. Все равно нечто подобное придется разрабатывать, так как любая стандартизация требует эталонов и справочной информации.

— Разработка или адаптация стандартных программных средств для предобработки данных (процедуры очистки данных).

— Загрузка данных на SQL-сервер или какой-либо другой сервер.

— Оценка достигнутых результатов и внедрение технологии.

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

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

К Владимиру Туманову можно обратиться по адресу: tve@icp.ac.ru.

Основные поставщики ПО хранилищ данных