Лабораторная работа 2 «curl, SQL JOIN и объединение данных из нескольких источников»
( командные curl-запросы, HTTP/FTP/SMTP, SQL JOIN, объединение файлов, баз данных и API, подготовка данных для BI. )
Цель работы
Освоить диагностические и тестовые HTTP-запросы из командной строки, получить данные минимум из трех разных источников и построить на Python и SQL единый аналитический набор данных.
Здесь главный учебный результат — студент должен понять, что получить данные и объединить данные — разные этапы инженерного конвейера.
Исходные данные
Собранные студентом данные:
organizations.csv
regions.db
и REST endpoint:
GET /api/transactions
Рекомендуемая структура данных:
organizations.csv
org_id
org_name
city_id
sector
API /api/transactions
transaction_id
org_id
date
amount
currency
SQLite regions.db, таблица cities
city_id
city_name
region_name
population
В данные следует намеренно добавить несколько проблемных ключей: организацию без операций, операцию с неизвестным org_id, город без организации и одну повторяющуюся строку. Тогда различие INNER/LEFT JOIN будет видно практически.
Задание студенту
Ваша задача — получить данные командными и программными средствами, объединить CSV + API + SQLite и построить аналитическую витрину.
Этап A. Диагностика curl.
В терминале выполните:
curl --version
Зафиксируйте версию программы и список протоколов, которые поддерживает конкретная установленная сборка.
curl поддерживает большое число протоколов, включая HTTP/HTTPS, FTP/FTPS и SMTP/SMTPS, но официальный manual уточняет, что конкретная сборка может поддерживать не все из них. [документация]
Далее средствами curl выполните запросы к учебному REST API:
GET списка ресурсов;
GET одного ресурса;
POST;
PUT или PATCH;
DELETE;
HEAD.
Для каждого варианта необходимо сохранить команду и существенную часть ответа.
Студент должен самостоятельно применить параметры для:
вывода response headers;
передачи query parameters;
отправки JSON;
добавления собственного HTTP-заголовка;
следования redirect;
записи ответа в файл.
Официальная документация curl описывает GET как стандартное получение ресурса, --head/-I для HEAD, --location/-L для следования HTTP redirect и --header/-H для собственных заголовков.
Этап B. Анализ протоколов.
Составьте сравнительную таблицу:
|
Протокол |
Типичная задача |
Шифрованный вариант |
Поддерживается вашей сборкой curl |
|
HTTP |
|
HTTPS |
|
|
FTP |
|
FTPS |
|
|
SMTP |
|
SMTPS |
|
Не требуется подключаться к произвольным FTP- или SMTP-серверам в интернете.
При наличии подготовленного локального учебного FTP/SMTP-сервиса студент выполняет один дополнительный curl-запрос по FTP или SMTP. Передавать реальные учетные данные в открытой командной строке не следует: документация curl отдельно предупреждает, что аргументы запущенной программы могут быть видны другим локальным пользователям.
Этап C. Загрузка источников в Python.
В Jupyter Notebook:
organizations = ...
transactions = ...
cities = ...
где:
-
organizations читается из CSV;
-
transactions получается через REST API;
-
cities извлекается SQL-запросом из SQLite.
Для каждого набора выведите:
shape
dtypes
head()
isna().sum()
число уникальных ключей
число дубликатов
Этап D. Очистка ключей.
Приведите org_id и city_id к одинаковым типам данных.
Проверьте:
дубли ключей;
NULL;
ключи, присутствующие только слева;
ключи, присутствующие только справа.
Нельзя сразу вызывать merge() и считать задачу законченной: студент должен сначала определить ожидаемую кардинальность связи:
1:1
1:N
N:1
N:N
Этап E. Объединение средствами pandas.
Постройте:
-
organizations INNER JOIN transactions;
-
organizations LEFT JOIN transactions;
-
полученный набор LEFT JOIN cities.
Используйте pandas.merge().
Для контроля объединения следует применить:
indicator=True
и там, где кардинальность известна:
validate="one_to_many"
# или
validate="many_to_one"
pandas.merge реализует database-style join, а параметры indicator и validate специально предназначены соответственно для анализа происхождения строк и проверки ожидаемого типа отношения между ключами.
Для каждого JOIN покажите:
число строк до;
число строк после;
число matched;
число left_only;
число right_only.
Этап F. SQL JOIN.
Загрузите подготовленные таблицы в SQLite и выполните аналогичное объединение SQL-запросом.
Обязательные конструкции:
INNER JOIN
LEFT JOIN
GROUP BY
COUNT(...)
SUM(...)
AVG(...)
SQLite определяет INNER JOIN, LEFT JOIN, RIGHT JOIN и FULL JOIN; для учебной переносимости студентам лучше использовать стандартные INNER JOIN ... ON ... и LEFT JOIN ... ON .... Для LEFT JOIN строки левой таблицы, которым не найдено соответствие справа, остаются в результате, а отсутствующие правые поля заполняются NULL.
Студент должен сравнить результат SQL JOIN и pandas.merge и объяснить, совпадают ли число строк и агрегаты.
Этап G. Построение витрины.
Создайте итоговый набор:
org_id
org_name
sector
city_name
region_name
transactions_count
total_amount
avg_amount
last_transaction_date
Сохраните:
data/processed/data_mart.csv
data/processed/data_mart.parquet
Этап H. Визуализация и BI.
На Python постройте минимум два графика:
общая сумма операций по регионам;
TOP организаций по общей сумме.
Поскольку рабочая программа отдельно предусматривает BI-инструменты, рекомендуется дополнительно импортировать data_mart.csv в Power BI, Tableau или Qlik и сформировать одну страницу дашборда.
Что студент должен сдать
lab_2.ipynb
src/loaders.py
src/transform.py
src/database.py
results/curl_commands.txt
data/processed/data_mart.csv
data/processed/data_mart.parquet
results/dashboard.png
README.md
Критерии приемки
Полностью выполненная работа должна демонстрировать цепочку:
curl
↓
получение источников
↓
проверка ключей
↓
pandas.merge
↓
SQL JOIN
↓
сравнение результатов
↓
аналитическая витрина
↓
визуализация / BI
На защите необходимо объяснить INNER JOIN, LEFT JOIN, связь 1:N, назначение ключа, причину возникновения дубликатов после JOIN и отличие curl от библиотеки Requests.