В рамках данной лабораторной работы необходимо:
- создать GitHub-репозиторий.
- произвести генерацию синтетических данных (одна таблица) с первым или вторым типом историчности, категориальными и количественными признаками (полей в выходящей таблице должно быть десять).
- сделать выгрузку таблицы в базу данных PostgreSQL, трансформировав данные от аномалий.
Детальное описание лабораторной работы:
- Поиск данных:
- Генерация синтетических данных: данные генерировать можно любым доступным способом. Можно прикрутить LLM (API, либо развернуть на мощности своей машины), так и сгенерировать любым другим способом. Как вариант: создать функцию get_dataset(...), в рамках которой будет проводиться генерация. Важно! Данные ОБЯЗАНЫ быть "сломанными" - чтобы их можно было почистить или структурировать далее.
- После генерации данных приступаем к загрузке ее в базу в формате "как есть" (например, с помощью Python или любого другого ETL-инструмента или языка программирования):
- Создать функцию, например, load_data_to_db(...) в рамках нее реализовать загрузку данных в базу в таблицу t_sql_source_unstructured.
- Создать схему s_psql_dds. Создать таблицы в этой схеме t_sql_source_unstructured (в этой таблице должны быть данные, которые вы загрузили в п.2) и t_sql_source_structured (то, куда вы загрузите очищенные и структурированные данные). Создать SQL-процедуру/SQL-функцию, которая будет убирать аномалии в данных и приводить данные в работоспособный вид (замечание: код избавления от аномалий должен быть написан исключительно на SQL! В качестве параметров функции/процедуры должны быть указаны) start_date и end_date - параметры с типом данных DATE.
- Например, можно создать PostgreSQL-функцию с названием fn_etl_data_load(start_date date, end_date date), в рамках которой будет проводиться трансформация данных и загрузка из загруженного в базу источника в предварительно подготовленную таблицу.
- Создать структуру репозитория: data-pipeline/"тут ваш проект на любом языке программирования, начиная с корневой папки (для питона: где main-py-файл - обычно, это корень)", sql/"dds(папки: table - Тут храниться будут ddl-таблиц, которые будут принимать участие в расчете, function - тут будут все функции, которые вы будете создавать для расчета, view - тут будут все представления") и выгрузить код в репозиторий.
- После написания SQL-функции создать функцию fill_structured_table(...) в приложении, которая будет просто запускать функцию fn_etl_data_load на PostgreSQL.
- Объединить функцию генерации датасета, функцию загрузки в неструктурированную таблицу и функцию запуска заполнения структурированной таблицы в одну верхнеуровневую функцию etl().
Пример структуры репозитория для пары Python - SQL (где написано init.txt - пустой файл, временное решение, постепенно будет наполняться кодом):
|-----------------------------------------------|
| data-pipeline/
|---- src/
|-------- get_dataset.py
|-------- load_data_to_db.py
|-------- fill_structured_table.py
|-------- etl.py
|-------- ...
|---- main.py
|---- config.py
|---- ...
| sql/
|---- dds/
|-------- s_sql_dds/
|------------ table/
|---------------- t_sql_source_unstructured.sql
|---------------- t_sql_source_structured.sql
|------------ function/
|---------------- fn_etl_data_load.sql
|------------ view/
|---------------- init.txt
|------------ trigger/
|---------------- init.txt
|------------ ...
|---- dm/
|-------- s_sql_dm/
|------------ table/
|---------------- init.txt
|------------ function/
|---------------- init.txt
|------------ view/
|---------------- init.txt
|------------ trigger/
|---------------- init.txt
|------------ ...
| .gitignore
| README.md
|-----------------------------------------------|
- Писать код маленькими буквами.
- Любой объект НАЧИНАТЬ называть следующим образом:
- для таблицы - t_: t_table
- для представления - v_: v_view
- для функции - fn_: fn_function
- для схемы - s_: s_schema
- Код приложения и SQL-код работают и выдают данные в целевой таблице.
- Параметры функции в виде временного периода применяются в расчете.
- Наличие верхнеуровневой функции etl() в приложении, которая состоит из:
- запуска функции get_dataset(...)
- запуска функции load_data_to_db(...)
- запуска функции fill_structured_table(...)
- функция etl() должна быть без входных параметров. Три функции, перечисленные выше, могут иметь входные параметры, если это необходимо.
Успеваемость и посещаемость: https://docs.google.com/spreadsheets/d/1P3DwKXF0awKqw1iAGq30P6qwpV52MXTC9HVRsWuuK8w/edit?usp=sharing
Основной сайт по работе с SQL (для лабораторных работ) – SQL-ex: https://sql-ex.ru/learn_exercises.php?LN=1
Начало работы с SQL и базами данных: https://sql-academy.org/ru/guide
В качестве первой лабораторной работы необходимо решить первые 25 заданий обучающего этапа на оператор SELECT сайта SQL-ex.
Ссылка на обучающий этап на сайте SQL-ex указана в разделе "Организационная информация"
Оформить каждую задачу в виде кода в google docs или другом онлайн-редакторе в формате Задание №N Формулировка и скрин БД (если она поменялась) + ниже решение в виде кода.
Дедлайн по прохождению курса: 14.09.2024.
- Онлайн-дока со скринами решения задач на тренажёре SQL-ex (25 заданий) с формулировкой задания и номером, а также скрином описания базы данных к каждому заданию
- Пройденный курс
Необходимо составить схему БД. Таблица-источник: https://clck.ru/3CsmXq
Затем необходимо с помощью Python или иных инструментов реализовать процесс ETL: Extract - Transform - Load с использованием СУБД PostgreSQL
На этапе Extract происходит выборка с помощью Python и загрузка таблицы-источника в созданную в СУБД таблицу в сыром виде.
На этапе Transform необходимо создать функцию/процедуру, которая будет обрабатывать/трансформировать данные на диалекте PostgreSQL (PG/SQL) (например, битые строки с нулями) и переносить "хорошие" (критерий "плохих" данных - все данные в строке = null или хотя бы одна отчетная дата (Report_date) = null, этот кейс надо обработать в первую очередь на этапе трансформации) данные в новую таблицу, имеющую такую же структуру, что и таблица-источник. Функция должна иметь параметр с периодом дат, за который необходимо перенести данные из таблицы-источника.
На этапе Load будет происходить вставка данных из временной таблицы в целевую с фильтром по дате. Как правило, на втором шаге уже происходит Load в целевую таблицу.
- Показать решение в DBeaver и схему в любом редакторе
- Запушить решение в GitHub в отдельный репозиторий в ветку под названием etl
- Сделать pull-request в master-ветку и назначить меня ревьювером (hecfi79)
- Оформить в отчет и запушить в репозиторий
В этой лабораторной работе необходимо вспомнить схему БД из прошлой. Надо реализовать процедуру, которая будет создавать таблицы с указанием ключей (PRIMARY KEY и FOREIGN KEY) и связями между таблицами.
Также на данном этапе необходимо добавить к каждому справочнику поле dtstart и dtend, по умолчанию равных 1900-01-01 и 9999-12-31 соответственно (на самом деле не только по умолчанию, но и в общем - даты должны быть в справочниках такие, чтобы их не заполнять лишний раз - можно просто добавить значение по умолчанию).
Особенность историчности таблицы фактов будет далее.
Необходимо написать процедуру/функцию на PostgreSQL, которая будет заполнять таблицы-справочники и таблицу фактов (собирать таблицу фактов необходимо из справочников с использованием языка SQL, не забывая про фильтр по дате: либо current_date::date between dtstart and dtend, либо Report_date between date1 and date2).
- Показать решение в DBeaver
- Оформить в отчет и запушить в репозиторий
В рамках данной лабораторной работы необходимо будет проделать то же самое, что и на PostgreSQL, но на любой другой СУБД на ваш выбор, а именно: создание хранимых процедур, аналогичных написанным, создание ddl для таблиц. Единственное уточнение: необходимо переложить данные из таблицы фактов на PostgreSQL в подготовленную таблицу на другой СУБД, а далее процесс тот же: из этой таблицы все раскидать по справочникам, а из справочников собрать новую таблицу фактов (переложенную таблицу использовать нельзя по причине того, что она является источником данных из другой СУБД, данные в ней по сути сырые и требуют обработки, аналогичной в PostgreSQL).
Предложенные СУБД: MySQL (проще), Microsoft SQL-Server (тяжко)
- Показать процесс на личном ПК
- Оформить в отчет и запушить в репозиторий
Есть набор данных: https://clck.ru/3F8U4X
Необходимо:
- Загрузить данные через Python в PostgreSQL
1.1. Код на Python должен быть оптимальным
1.2. Все должно быть разбито на модули, если требуется при реализации
- Выявить конечный набор атрибутов, который будет в итоговой витрине
2.1. В наборе данных есть 1 таблица-справочник - это горизонтальная витрина (ключ среди столбцов, значение в строках), в которой описано, через сколько умер конкретный рекорд и умер ли он в больнице. Если в данных -1, значит, что он жив до сих пор
2.2. Есть таблицы фактов - это вертикальная витрина (слева ключ, а справа в столбце значение): наблюдение за каждым рекордом на протяжении какого-то времени
2.3. Подсказка: итоговая витрина будет горизонтальной (сверху в столбцах ключ, а ниже для него значение)
- Оформление внутри СУБД:
3.1. Создание таблицы для логгирования
3.2. Создание процедуры/функции сбора итоговой витрины данных с параметрами (если понадобятся)
3.3. Использование временных таблиц. Не нужно здоровые запросы делать одним запросом, надо разбить их на несколько мелких
3.4. Добавление шагов в процедуру. С каждым действием шаг будет прибавлять к себе +x, где x - ваше значение по желанию (шаг - статическая переменная, автоматизировать ее не нужно, тут вы вольны в своих фантазиях)
3.5. Добавление каждого шага в таблицу логов. В таблице логов, естественно, будет название процедуры, которая выполняется и другие поля (какие - это надо подумать)
3.6. Как только собрали итоговую витрину в таблицу, необходимо создать VIEW из этой таблицы с перечислением полей ("select *" не допускается нигде по коду)
- Дальнейшая работа с датасетом
4.1. После создания представления из п. 3.6 необходимо будет на Python отдельным файлом в том же проекте (может быть, это будет Jupyter Notebook или VSCode, или же обычный .py файл) реализовать загрузку данных из представления PostgreSQL в Pandas.DataFrame и попробовать обучить на этих данных две и более модели, решив задачу регрессии и классификации.
- Реализован ETL-процесс посредством Python
- Получен итоговый набор полей, необходимых для задачи
- Реализована процедура/функция сбора витрины данных с шагами, временными таблицами и логгированием
- Решены задачи классификации и регрессии. Полученные результаты по точности не важны
- Оформить в отчет и запушить в репозиторий