Data analysis - DA SQL

Informatica PowerCenter Informatica PowerCenter — это ETL-инструмент, предназначенный для извлечения, преобразования и загрузки данных из разных источников в целевые хранилища, витрины и аналитические системы. Поддерживает очень много источников данных: реляционные СУБД, файлы, облачные сервисы, продукты Apache и т.д. Также поддерживает параллельную обработку, способен оптимизировать загрузку и работать с большими данными. В контексте метаданных можно создавать S2T Mapping, отслеживать происхождение данных с помощью Data Lineage (базово подойдет Metadata Manager, расширенно - Informatica Enterprise Data Catalog) и обеспечивать Data Quality (нужна интеграция с Informatica Data Quality). 🟠Организация ETL в PowerCenter имеет базово следующую логику: [Source] → [Transformation (опционально)] → [Target] Выглядит это так. Необходимо создать репозиторий в Repository Manager, подключить к нему источник данных и потом добавить нужные таблицы в категории Source и Target. В Informatica передадутся именно метаданные, а не сами данные. В Informatica PowerCenter Designer создается маппинг, куда потом просто добавляются метаданные из Source и Target. Сопоставление полей происходит автоматическим образом по принципу равенства их названий, но можно отключить Auto Link и создавать связи вручную или через Transformation. Существует много разных видов Transformation: Expression, Aggregator, Data Masking, Filter, Rank, Router и т.д. Остановимся на Expression и Aggregator: ▶️Expression работает по принципу построчной обработки: может делать CONCAT, IF, арифметические операции, SUBSTR, ROUND, DECODE и т.д. Для работы маппинга по такому принципу на вход Expression надо задать таблицу Source, на выход - таблицу Target, и связать выходные поля с полями целевой таблицы. ▶️Aggregator работает по принципу группировки наподобие GROUP BY. Как и в Expression, на вход идет Source-таблица. После этого можно делать преобразования по типу SUM, AVG, MAX, MIN, COUNT и т.п. После этого надо связать полученные значения с Target. Для дальнейшего переноса данных между БД по созданному маппингу в Informatica PowerCenter Workflow Manager нужно будет создать Workflow, в него добавить маппинг, создать связь между Workflow и Mapping и затем просто запустить Workflow. После завершения процесса данные из source-таблицы перенесутся в target-таблицу согласно маппингу. Для получения документации по S2T-Mapping можно использовать Metadata Manager. В нее могут включиться: Source, Target, связи между таблицами, источник данных, преобразования в Transformation (но внешние Python или SQL-скрипты не будут автоматически документированы, их нужно описывать вручную), частота обновления (через Workflow Manager), владелец данных (через Enterprise Data Catalog). 🟠Data Lineage осуществляется, например, через ранее упомянутый Metadata Manager. Сервис входит в состав PowerCenter и представляет собой веб-приложение для просмотра, анализа и управления метаданными, но может показать Data Lineage только в пределах PowerCenter, то есть по описанной ранее логике ETL: Source → Transformation → Target, а также связи, заданные вручную. При создании маппинга в PowerCenter Designer, метаданные сохраняются в репозитории. Metadata Manager анализирует эти метаданные и визуализирует их. Для Data Lineage за пределами PowerCenter используется отдельный продукт - Enterprise Data Catalog. Чтоб через него отследить происхождение данных в том числе и для PowerCenter - это уже отдельная история, но в двух словах для этого надо создать Connection для PowerCenter с указанием хоста, порта, логина и пароля. Затем добавить Scan Profile и выбрать объекты (маппинги, сессии, workflows). После этого запускается сканирование. В итоге получим полный путь от источника до цели с учетом преобразований и бизнес-атрибутов. 🟠Похожая ситуация и с Data Quality, для этого тоже нужен отдельный продукт - Informatica Data Quality, который подключается к PowerCenter, что позволит в рамках ETL-процессов обеспечить качество данных. Но простые проверки можно осуществлять и с помощью встроенных Transformation.

Source-to-Target (S2T) Mapping для миграции данных S2T-маппинг (Source-to-Target Mapping) — это сопоставление полей исходных данных (source) с целевыми полями (target) с описанием правил преобразования. Это важный этап ELT-процессов, который используется в контексте миграции данных (переносе данных, например, из одной СУБД в другую) и построении витрин данных. Это нужно для того, чтобы минимизировать вероятность использования некачественных данных и для того, чтобы все понимали, откуда берутся данные, по какой логике преобразовываются, что это за данные и т.д. Еще это может быть полезно для масштабируемости, то есть можно добавлять новые источники без изменения всей ETL-логики. Для целей такого маппинга создаются таблицы, которые могут быть как и просто обычной документацией в Word/Excel/PDF, так и быть, например, SQL-таблицами, с которыми можно взаимодействовать на уровне запросов. Создание таких таблиц - процесс достаточно творческий, они могут иметь разные атрибуты и содержание. В одном из достаточно типовых вариантов S2T таблица помимо атрибутов source и target может включать в себя: ▶️Правило преобразования (может быть SQL-функцией, Python-кодом или бизнес-логикой) ▶️Тип данных в атрибутах source и target ▶️Логику обработки NULL-значений ▶️Связи между таблицами ▶️Источник данных ▶️Частота обновления ▶️Владелец данных Логика заполнения этих таблиц следующая. Например, мы захотели перенести данные с oracle на postgre. Очевидная проблема - это разный SQL-синтаксис и разные типы данных в этих СУБД. При этом нужно еще учитывать существующие связи между таблицами. 1️⃣Создавая в этой ситуации таблицу маппинга, мы пропишем в ней атрибут Source (Oracle), который будет содержать данные о столбцах в источнике в формате "schema.table.column". Аналогично поступим и с атрибутом Target (Postgre), который будет содержать информацию о конечном назначении столбцов из источника. 2️⃣Далее пойдет атрибут преобразования, условно Transformation Rule. В него можно прописать любые самые разные используемые sql-запросы (и не только sql-запросы) на диалекте источника (в случае, если перенос данных происходит напрямую между СУБД), в данном случае на оракле, по типу UPPER(column), to_char(column), ROUND(column, n), CONCAT(a, b) и любую другую, в том числе намного более сложную, используемую логику. 3️⃣В атрибуте Data Type, как правило, одновременно прописываются типы данных как и source, так и target столбцов: Oracle: NUMBER(10) PG: INTEGER 4️⃣Что касаемо обработки null-значений, здесь всё те же самые разные sql-запросы на диалекте источника, как и в Transformation Rule, например, NVL(column, 'N/A'). 5️⃣Связи между таблицами обычно отражаются путем указания первичных (PK) и внешних (FK) ключей напротив столбцов с айдишниками. Например, есть у нас в оракле связанные между собой таблицы "category" и "sales", которые мы хотим перенести на постгре, предварительно создав там эти же таблицы. Пишем сначала про перенос category: source ▶️ category_id; target ▶️ category_id; тип связи ▶️ PK. Это означает, что category_id является первичным ключом в оракле, и что в постгре этот столбец тоже должен быть первичным ключом. Теперь перенос sales: source ▶️ sales_id; target ▶️ sales_id; тип связи ▶️ PK. source ▶️ category_id; target ▶️ category_id; тип связи ▶️ FK -> category_id. Это будет означать, что sales_id из sales можно связать по category_id c category (category_id есть как и в sales, так и в category). 6️⃣Ну и наконец в атрибуте "Источник данных" принято указывать базу данных, например, Oracle PROD_DB. Атрибуты "Частота обновления" и "Владелец данных" заполняются произвольно.

Qlik Sense Qlik Sense — это BI-инструмент для визуализации данных и аналитики. Как и множество других инструментов, Qlik Sense позволяет создавать интерактивные дашборды с диаграммами, таблицами и графиками, а также строить гистограммы, линейные графики, карты и т.д. Qlik Sense часто сравнивают, например, с Tableau, и не просто так: с его помощью точно так же можно подключаться к большому количеству самых разных источников данных (реляционные БД, Excel, CSV, веб-страницы, инструменты Apache, Teradata, Salesforce и т.п.). Так же поддерживается и питон с SQL, а еще есть возможность шерить друг с другом дашборды и отчеты. 🟠Но у Qlik Sense есть уникальная черта - это ассоциативная модель. Это движок, который автоматически связывает данные. Условно, если мы подключились к БД и вывели несколько таблиц, то Qlik Sense будет анализировать поля в таблицах и искать совпадающие значения. Найдя одни и те же поля в этих таблицах (например, client_id), Qlik Sense свяжет их в граф связей (грубо говоря, это как автоматически прописанные джоины, которые собирают, насколько это возможно, все таблицы из БД воедино). В рамках этого примера, если мы просто зададим какой-либо client_id, то сможем увидеть всю информацию по нему, которая прямо или косвенна раскидана по разным таблицам в БД. И вдобавок к этому, благодаря функционалу, мы еще сможем не только понять, как связаны данные, но и то, как они НЕ связаны (например, если клиент не покупает определенный товар). Очевидно, что в этом всем есть 2 проблемы: ▶️Нужно учитывать то, что для выявления связей нужны исключительно качественные данные, а именно такие, где все нужные столбцы названы идентично. ▶️И еще нужно учитывать то, что Qlik Sense может построить и лишние, не совсем логичные, связи. Для этого желательно грузить не всю БД целиком, а только нужные таблицы. Но если по какой-либо причине автоматическое построение связей работает некорректно, то можно настроить связи вручную. Это можно делать как и через интерфейс, так и через "Qlik-SQL" перед загрузкой данных. Во втором случае можно поступать по-разному: либо прописать джоины вручную: Sales: LOAD * FROM [ПУТЬ К ИСТОЧНИКУ 1]; Customers: LOAD * FROM [ПУТЬ К ИСТОЧНИКУ 2]; // Соединяем по CustomerID Join (Sales) LOAD CustomerID, CustomerName FROM [ПУТЬ К ИСТОЧНИКУ 2]; либо же создать мэппинг: // Создаём справочник CustomerMap: MAPPING LOAD CustomerID, CustomerName FROM [ПУТЬ К ИСТОЧНИКУ 1]; // Применяем к основной таблице Sales: LOAD OrderID, CustomerID, ApplyMap('CustomerMap', CustomerID, 'Unknown') as CustomerName FROM [ПУТЬ К ИСТОЧНИКУ 2]; (в этом примере для отображения CustomerName напротив CustomerID)

Триггеры в SQL Триггеры похожи на специальные хранимые процедуры, которые автоматически выполняются при наступлении определенных событий в базе данных. Могут срабатывать перед, после или вместо выполнения операции. Как и хранимая процедура, триггер хранится в базе данных и может вызываться многократно. Но при этом, в отличие от хранимой процедуры, триггер можно включать и отключать, но нельзя вызывать его явным образом. Можно задать триггеры, которые будут срабатывать при изменении данных (INSERT, UPDATE, DELETE), а также при изменении структуры БД и при возникновении системных событий. Общий вид запроса на создание триггеров: CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE} ON table_name [FOR EACH ROW] [WHEN (condition)] DECLARE -- объявление переменных BEGIN -- тело триггера EXCEPTION -- обработка исключений END; Посмотреть созданные триггеры можно в таблице user_triggers (подробнее про метаданные). Важная особенность триггеров - их нельзя создавать на объектах, принадлежащих SYS. Для этого нужно переключиться на менее значимое табличное пространство, например, через другого пользователя, у которого должно быть право на создание триггеров. Пример создания: CREATE OR REPLACE TRIGGER C##username.trg_check BEFORE INSERT OR UPDATE ON C##username.table FOR EACH ROW BEGIN IF :new.amount < 100 THEN RAISE_APPLICATION_ERROR(-20001, 'Маленькое значение'); END IF; END; Этот триггер создается в контексте отдельного пользователя (username), не затрагивая пространство SYS. Он действует перед вставкой или обновлением значений в таблице и предназначен для проверки поля amount (amount < 100) в таблице table. Иными словами, если мы захотим вставить в поле amount значение меньше 100, то сработает триггер и не даст нам этого сделать. 🟠Еще один пример триггера. На этот раз он запрещает пользователю удаление таблиц: CREATE OR REPLACE TRIGGER C##username.trg_prevent_table_drop BEFORE DROP ON SCHEMA BEGIN IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN RAISE_APPLICATION_ERROR(-20000, 'Удаление таблиц запрещено!'); END IF; END; Теперь при любом запросе по типу DROP TABLE table_name будет срабатывать триггер и специально вызывать ошибку. Отключить/включить триггеры можно так: ALTER TRIGGER trg_name DISABLE --или ENABLE, чтоб включить Проверить, включен ли триггер или нет, можно все в той же таблице user_triggers.

Как автоматизировать загрузку файлов в БД с помощью Python Иногда встречаются такие ситуации, когда необходимо загружать большое количество файлов в базу данных для их дальнейшего анализа. Например, у нас есть 1000 небольших excel-файлов (не факт, что с одинаковыми столбцами), которые надо загрузить в oracle db. Как ни удивительно, делается это достаточно просто. Сначала пройдемся по папке с файлами циклом и объединим их в 1 датафрейм: import pandas as pd import glob all_data = pd.DataFrame() for file in glob.glob(r"ПУТЬ К ПАПКЕ\*.xlsx"): df = pd.read_excel(file) all_data = pd.concat([all_data, df], ignore_index=True) После этого с помощью sqlalchemy подключимся к базе данных: from sqlalchemy import create_engine conn = "oracle+cx_oracle://user:password@localhost:port/?service_name=name&mode=mode" engine = create_engine(conn) Подробнее про подключение было здесь. Далее обработаем данные, а именно уберем все пропуски и для универсальности преобразуем всё в строки, чтобы избежать конфликта форматов при вставке: for col in all_data.columns: all_data[col] = all_data[col].astype(str) all_data[col] = all_data[col].replace('nan', None) В конце создадим новую таблицу и вставим ранее объединенный датафрейм в бд: all_data.to_sql( name='table_name', con=engine, if_exists='append', index=False ) На выходе получим таблицу в базе данных, которая будет содержать в себе все уникальные столбцы, которые встретились в этих файлах.

Aranadata Catalog и Data Lineage Arenadata Catalog — система управления корпоративными данными, которая помогает организациям внедрять процессы Data Governance. Объединяет в себе каталог метаданных, бизнес-глоссарий, поиск, проверку качества данных, построение Data Lineage и другое. Прежде всего для осуществления всего этого нужно собрать метаданные из разных источников. Делает ADC это по большей части автоматически из разного рода СУБД, BI-систем, ETL-инструментов. В случае с СУБД для этого используется прямое подключение через ODBC, в случае остальных источников метаданных может использоваться API или парсинг. Собираемые метаданные могут представлять собой: ▶️Структуры данных (таблицы, колонки, типы данных, ограничения...) ▶️SQL-запросы, представления (views), хранимые процедуры... ▶️ETL-процессы (например, из Airflow) ▶️BI-отчеты 🟠Одной из ключевых особенностей Arenadata Catalog является построение Data Lineage - визуализации происхождения данных. Это позволяет отслеживать путь данных от источника до конечного потребителя. Строится Data Lineage на основе собранных ранее метаданных из разных источников. Например, данные из CRM загружаются в DWH и потом визуализируются с помощью Bi-инструмента. Основная логика построенного Data Lineage будет выглядеть так: CRM-система > файлы > > ETL-инструмент (Airflow) > > DWH (Oracle) > BI-отчет (Tableau) Описываемый процесс полезен как и просто для понимания того, откуда берутся данные, какой путь проходят и где их искать, так и для анализа влияния изменений (например, если на каком-либо этапе изменить данные, то как это повлияет на промежуточный и конечный результат). Еще это помогает быстрее находить ошибки в контексте Data Quality, оптимизировать процессы путем исключения лишних этапов и даже может помочь условным финансовым аудиторам. 🟠Еще с помощью ADC можно создавать и поддерживать единый корпоративный словарь терминов, связывая их с метаданными. Это помогает стандартизировать язык общения между бизнесом и ИТ. Пример того, как может выглядеть термин в глоссарии ADC: Термин: «Выручка» Определение: «Сумма продаж без НДСы». Синонимы: «Revenue», «Оборот». Связанные объекты: - Таблица: `dwh.sales` (`revenue`). - Отчёт: Tableau «Фин рез 2024». - ETL: Task «Revenue calc» в Airflow.

Метаданные в SQL Метаданные - это "данные о данных", которые описывают структуру, свойства и контекст информации в базе данных. То есть это информация о таблицах, столбцах, индексах, представлениях, функциях, процедурах, пакетах, триггерах, зависимостях, правах доступа, типах данных, ограничениях и о многом другом. Хранятся метаданные в частности в таких представлениях, как (на примере pl/sql): ▶️USER_TABLES - таблицы текущего пользователя ▶️USER_TAB_COLUMNS - столбцы таблиц текущего пользователя ▶️USER_VIEWS - представления текущего пользователя ▶️USER_PROCEDURES - пользовательские процедуры и функции ▶️USER_OBJECTS - все объекты, принадлежащие текущему пользователю ▶️USER_DEPENDENCIES - зависимости между объектами ▶️USER_ARGUMENTS - аргументы процедур и функций ▶️USER_INDEXES - индексы 🟠Каждое из этих представлений имеет варианты: ▶️USER_* - объекты текущего пользователя ▶️ALL_* - объекты, доступные текущему пользователю ▶️DBA_* - все объекты в БД (но нужны привилегии) 🟠Метаданные нужны для оптимизации доступа к данным, для понимания структуры БД, для автоматизации процессов (например, получив метаданные из БД, можно с помощью питона загружать данные из excel-файлов во все таблицы, столбцы которых соответствуют столбцам в этих файлах) , а также для Data Lineage (о последнем расскажу чуть позже). Например, так осуществляется вывод полей и типа данных для таблицы CUSTOMERS: SELECT column_name, data_type FROM USER_TAB_COLUMNS WHERE table_name = 'CUSTOMERS' Запрос для отображения доступов к этой же таблице: SELECT grantee, privilege FROM USER_TAB_PRIVS WHERE table_name = 'CUSTOMERS' Проверка зависимостей у таблицы (например, перед изменением таблицы можно удостовериться, не является ли она базовой для какого-нибудь представления): SELECT name, type FROM USER_DEPENDENCIES WHERE referenced_name = 'CUSTOMERS' Вывод информации о параметрах процедуры: SELECT argument_name, data_type FROM all_arguments WHERE object_name = 'CALCULATE_SMTH'

Как обеспечить качество данных Выделяют такие критерии по Data Quality, как: (по сути чек-лист, по которому можно пробежаться, уменьшив вероятость использования некачественных данных) ▶️Полнота данных. Основная цель - удостовериться, что нет пропусков в важных полях. Проверка осуществляется как правило простым SQL-запросом с фильтрацией NULL-значений или пустот. Можно искать пропуски как точечно, так и посчитать их в процентом соотношении ко всем строкам в исследуемом наборе данных, чтоб в общих чертах понимать масштаб проблемы. Также подразумевает под собой в целом полноту набора данных: точечных пропусков может и не быть, но могут отсутствовать целые наборы строк. Или даже присутствовать лишние строки. Проверить можно обычным COUNT-ом, или группировкой с SUM по ключевым значениям, или создать Pivot-таблицу, зависит от ситуации. Главное - чтоб был эталон, с чем можно это все сверить. ▶️Точность и согласованность. Нужно удостовериться, что данные соответствуют реальности, или корректны, а также что одни и те же данные в разных источниках совпадают. Здесь под проверку может попадать что угодно: это может быть и наличие лишнего признака (например, данные могут принимать только значения А или Б, но внезапно присутствуют данные со значением С), и ошибки с датами (слишком давняя дата или будущая там, где это неуместно), и нарушение логики (например, дата заказа позже даты доставки), и несоответствие количественных значений (можно детально сверить выборку с первоисточником при помощи джоинов или вложенных запросов), и наличие аномальных статистических выбросов в выборке (можно проверить через STDDEV(amount)) и многое другое. ▶️Уникальность. Тут не должны встречаться дубликаты. Пример того, как может осуществляться такая проверка через группировку одного или нескольких столбцов с использованием HAVING COUNT: SELECT customer_email, COUNT(*) FROM customers GROUP BY customer_email HAVING COUNT(*) > 1 Также стоить отметить, что если вдруг по какой-нибудь бизнес-логике предусмотрено наличие полностью повторяющихся строк, то в этом случае необходимо создавать дополнительные ID, которые все равно будут обеспечивать уникальность данных. Еще при самой загрузке данных в таблицу можно вспомнить про UNIQUE INDEX чтобы избежать загрузки дублей. ▶️Актуальность. Данные должны обновляться вовремя. Обычно в DWH указывается дата обновления той или иной таблицы. И как вариант, чтобы это проверить - сравнить данные в столбце наподобие last_update с SYSDATE, учитывая некоторый временной интервал: SELECT * FROM products WHERE last_update > SYSDATE - INTERVAL '1' MONTH ▶️Соответствие формату. Данные должны иметь определенный вид (например, e-mail должен содержать @__.ru). Проверка осуществляется через LIKE, либо через REGEXP_LIKE: SELECT email FROM users WHERE NOT REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') P.S. Автоматизировать процесс DQ можно через создание процедур/триггеров, желательно на нескольких этапах: сначала проверять данные на этапе загрузки, а потом проводить проверку уже загруженных данных.

Материализованные и нематериализованные представления в SQL В дополнение к посту про представления (view). Если кратко: через CREATE VIEW можно сохранить SQL-запрос в базе данных и потом выполнять этот сохраненный запрос через SELECT * FROM view... Описанный ранее тип представлений является нематериализованным: то есть мы сохраняем запрос, потом его вызываем, и он начинает отрабатываться с самого начала, беря данные из тех таблиц, на основе которых он написан. Это, безусловно, экономит память, но занимает время на выполнение этого запроса. При этом можно не беспокоиться об актуальности данных при обращении к таким view, т.к. из-за того, что это SQL-запрос, данные в нематериализованном представлении по умолчанию соответствуют данным в "базисных" таблицах. Материализованные представления имеют другой принцип: результаты запроса сохраняются. Соответственно, тут все наоборот: доступ к данным получается быстрее, но занимается лишнее место в памяти и нужно беспокоиться о том, чтоб поддерживать данные в актуальном состоянии. Материализованные представления создаются таким образом: CREATE MATERIALIZED VIEW sales_by_region_mv AS SELECT * FROM sales WHERE region = 'South'; SELECT * FROM sales_by_region_mv После выполнения запроса из таблицы sales по условию region = 'South' выберутся данные и сохранятся в бд во вью sales_by_region_mv. В конце view непосредственно вызывается и выводится результат. Вью будет доступна даже после удаления базовой таблицы, но в таком случае актуализировать данные в ней уже не получится. Самая насущная проблема для материализованных представлений - это вопрос о поддержании актуальности данных. Обновление view (на примере oracle) происходит следующим образом: BEGIN DBMS_MVIEW.REFRESH('m_view_name'); END; Или так: EXEC DBMS_MVIEW.REFRESH('m_view_name'); То есть если в базовую таблицу были внесены данные, которые влияют на результат выполнения SQL-запроса для материализованной вью, то после REFRESH эти изменения будут учитываться во вьюхе.