День открытых дверей
Всё о курсе «Data Engineer»

Запуск PostgreSQL в Docker. Создание таблиц.. По ходу выполнения сохраняйте команды и вывод каждого шага — это и есть содержимое `README.md`, собирать всё в конце сложнее.

-----------
### Шаг 1. Поднять контейнер PostgreSQL

Запустите PostgreSQL через Docker:

```bash
docker run --name pg-shop \
-e POSTGRES_PASSWORD=secret \
-e POSTGRES_DB=shop \
-p 5432:5432 \
-d postgres:16
```

Убедитесь, что контейнер запустился: `docker ps`.

-----------
### Шаг 2. Подключиться к базе данных

Выберите удобный инструмент: DBeaver, pgAdmin или `psql`. Параметры подключения:
- хост: `localhost`
- порт: `5432`
- база: `shop`
- пользователь: `postgres`
- пароль: `secret`

-----------
### Шаг 3. Создать три таблицы

В файле `schema.sql` напишите и выполните DDL-скрипт:

```sql
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL
);

CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
price NUMERIC(10,2) NOT NULL CHECK (price >= 0)
);

CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customers(customer_id),
created_at TIMESTAMP NOT NULL DEFAULT now()
);
```

Требования к таблицам:
- у каждой таблицы — первичный ключ (`PRIMARY KEY`);
- `orders.customer_id` — внешний ключ (`FOREIGN KEY`) на `customers`;
- типы данных разумные: `NUMERIC` для цены, `TIMESTAMP` для даты.

-----------
### Шаг 4. Вставить тестовые данные

В файле `queries.sql` напишите `INSERT` для 3–5 строк в каждую таблицу. Соблюдайте порядок: сначала `customers` и `products`, потом `orders` (иначе нарушится ссылочная целостность).

Пример:

```sql
INSERT INTO customers (name, email) VALUES
('Иван Петров', 'ivan@example.com'),
('Мария Сидорова', 'maria@example.com'),
('Пётр Кузнецов', 'petr@example.com');

INSERT INTO products (title, price) VALUES
('Ноутбук', 89999.00),
('Мышь', 1499.00),
('Монитор', 24999.00);

INSERT INTO orders (customer_id) VALUES (1), (1), (2), (3);
```

-----------
### Шаг 5. Написать запросы

В том же файле `queries.sql` напишите:

1. Два-три `SELECT` с `WHERE` и `ORDER BY`, например:
- все товары дороже 2000 рублей, отсортированные по цене;
- все заказы одного клиента (`WHERE customer_id = ...`), отсортированные по дате.

2. Один `JOIN` — клиент вместе со своими заказами:

```sql
SELECT c.name, c.email, o.order_id, o.created_at
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name;
```

-----------
### Форма сдачи

GitHub-репозиторий с тремя файлами:
- `schema.sql` — DDL: `CREATE TABLE`;
- `queries.sql` — `INSERT` и `SELECT`;
- `README.md` — команда запуска контейнера, как подключиться, скриншот или текстовый вывод запросов.

-----------
### Куда сдать

Форма сдачи — в карточке занятия на сайте OTUS (личный кабинет).

-----------
### Куда задавать вопросы

Чат группы в VK или лично преподавателю: https://vk.com/rafraf

-----------
### Чем можно пользоваться

- Материалы лекции
- Документация PostgreSQL: https://www.postgresql.org/docs/
- Образ `postgres` на Docker Hub: https://hub.docker.com/_/postgres
- DBeaver: https://dbeaver.io/


-----------
-----------
-----------
## Бонусное ДЗ

На выбор (можно выполнить несколько):

**Вариант A — таблица `order_items` (M:N):**
Добавьте таблицу, связывающую заказы и товары:

```sql
CREATE TABLE order_items (
order_id INT NOT NULL REFERENCES orders(order_id),
product_id INT NOT NULL REFERENCES products(product_id),
qty INT NOT NULL CHECK (qty > 0),
PRIMARY KEY (order_id, product_id)
);
```

Вставьте несколько строк в `order_items` (иначе агрегат ниже вернёт пусто):

```sql
INSERT INTO order_items (order_id, product_id, qty) VALUES
(1, 1, 2), (1, 2, 1), (2, 3, 1);
```

Напишите `JOIN` через `order_items` и агрегат — сумму заказа:

```sql
SELECT o.order_id, c.name, SUM(p.price * oi.qty) AS total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
GROUP BY o.order_id, c.name
ORDER BY o.order_id;
```

**Вариант B — ограничения:**
Добавьте `UNIQUE` или `CHECK` к любой таблице и объясните в `README.md`, зачем это нужно.

**Вариант C — индекс и EXPLAIN:**
На таблицах из 3–5 строк работу индекса не увидеть, поэтому сгенерируйте таблицу побольше:

```sql
CREATE TABLE big_orders (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT now()
);

INSERT INTO big_orders (customer_id)
SELECT (random() * 10000)::int
FROM generate_series(1, 100000);
```

Посмотрите план запроса без индекса:

```sql
EXPLAIN SELECT * FROM big_orders WHERE customer_id = 42;
```

Создайте индекс и повторите тот же `EXPLAIN`:

```sql
CREATE INDEX idx_big_orders_customer_id ON big_orders (customer_id);
```

В `README.md` приложите оба плана и объясните разницу: какой способ доступа PostgreSQL выбрал до индекса и после (`Seq Scan` → `Bitmap Index Scan` / `Index Scan`) и почему.

Проектирование DWH по модели Data Vault (по другому набору таблиц). Вы работаете Data Engineer в компании «КиноДляВсех». Бизнес хочет построить
корпоративное хранилище данных для онлайн-кинотеатра.
В хранилище необходимо загружать следующую информацию:
О пользователях:
• ФИО;
• дата рождения;
• адрес электронной почты;
• номер телефона;
• дата регистрации;
• страна проживания;
• тип подписки (обычная, детская);
О фильмах:
• название фильма;
• дата выхода;
• жанр;
• возрастное ограничение;
• продолжительность фильма;
• страна производства;
• рейтинг фильма;
О просмотрах:
• дата и время начала просмотра;
• дата и время окончания просмотра;
• статус просмотра (завершен, прерван, просмотрено менее 50%);
• устройство просмотра (Web, Smart TV, Android, iOS);
Об оплатах подписки:
• дата оплаты;
• сумма платежа;
• способ оплаты;
• статус платежа;
• валюта платежа;
Необходимо:
1. Спроектировать модель Data Vault для данной предметной области.
2. Определить:
• Hub-таблицы;
• Link-таблицы;
• Satellite-таблицы.
3. Для каждой таблицы определить:
• Бизнес- и суррогатный ключи (при наличии);
• Описательные атрибуты (при наличии);
• Связи с другими таблицами;
По необходимости разрешено самостоятельно вводить ID. Например, ID
пользователя.
4. Изобразить итоговую схему Data Vault с указанием связей между
таблицами.

Анализ веб-логов с помощью ELK. https://github.com/Gorini4/elk_demo — в README репозитория содержится полная инструкция с подсказками.




1. Склонируйте репозиторий в директорию elk_demo.
2. Зайдите в эту директорию и разверните инфраструктуру, выполнив в терминале docker-compose up.
3. Отредактируйте файл clickstream.conf.
4. Загрузите данные веб-логов, выполнив команду ./load_data.sh.
5. Перейдите по адресу http://localhost:5601 и создайте отчет (dashboard), показывающий распределение запросов с разными кодами ответов (status_code) по времени.

Запуск Kafka в Docker. Самостоятельно запустить Kafka по предложенному алгоритму:

1. Скачать образ контепйнера Kafka с https://hub.docker.com/r/apache/kafka
2. Подготовить файл docker-compose.yml для запуска одного сервера.
3. Создать топик test.
4. Записать несколько сообщений в топик.
5. Прочитать сообщения из топика.

Задание со *: запустить кластер Kafka из 3-х серверов.

В материалах к этому занятию можете найти видеоинструкцию по сдаче ДЗ.

Подготовка и установка на расписание DAG выгрузки данных из источников. Часть из операций мы разбирали на занятии. При необходимости можно пересмотреть запись.




1) Создаем виртуальную машину с Apache Airflow 2.0 в Yandex Cloud.




2) Создаем «managed instance» PostgreSQL/ClickHouse/MySQL — по выбору в Yandex Cloud.
Создаем БД «analytics».




3) Добавляем наш psql в Connections через UI Airflow.




4) Выбираем один из 2-х API, с которым будем работать:
- Положение Международной Космической Станции на текущий момент времени (timestamp-latitude-longitude). Source:
http://api.open-notify.org/iss-now.json
- Курс BTC:
https://docs.coincap.io/#2a87f3d4-f61f-42d3-97e0-3a9afa41c73b

Тут нас интересует следующий endpoint: «api.coincap.io/v2/rates/bitcoin»




5) Создаем схему данных (таблицу в БД analytics) с названиями и типами полей, релевантными тому, что будем забирать из API.




6) Описываем DAG (программируем на Python) для получения данных с периодичностью 30 min.

Сам оператор для обращения к API можно выбрать любой.

Для простоты рекомендуется слать GET-запросы через python-библиотеку requests (`PythonOperator`), либо через bash (`BashOperator`) с помощью curl.




**Концептуальная схема DAG-a:** отправить запрос в API -> распарсить пришедший результат -> положить данные в БД (сделать insert в таблицу).




7) Кладем .py файл с DAG-ом в нужную директорию виртуалки с airflow.




8) Запускаем DAG тумблером в UI Airflow.




9) Отлаживаем DAG до работоспособного состояния.

В интерфейсе Airflow (облачный инстанс) есть информация о наборе успешно завершившихся DAG runs (темно-зеленые кружки) + в БД есть данные за >5 периодов времени.

В качестве проверки мы зайдем в ваш airflow webserver и отправим SQL-запрос в таблицу с данными.




10) Проверяем, что данные появились в БД.




11) Поздравляю, вы завершили ДЗ!

Сборка витрины на Spark. Инструкция:
1) Загрузить данные из [https://www.kaggle.com/datasets/AnalyzeBoston/crimes-in-boston](https://www.kaggle.com/datasets/AnalyzeBoston/crimes-in-boston).




2) Проверить данные на корректность, наличие дубликатов. Очистить.




3) Собрать витрину (агрегат по районам (поле district)) со следующими метриками:
- crimes_total — общее количество преступлений в этом районе;
- crimes_monthly — медиана числа преступлений в месяц в этом районе;
- frequent_crime_types — три самых частых crime_type за всю историю наблюдений в этом районе, объединенных через запятую с одним пробелом “, ” и расположенных в порядке убывания частоты;
- crime_type — первая часть NAME из таблицы offense_codes, разбитого по разделителю “-” (например, если NAME “BURGLARY-COMMERCIAL-ATTEMPT”, то crime_type “BURGLARY”);
- lat — широта координаты района, рассчитанная как среднее по всем широтам инцидентов;
- lng — долгота координаты района, рассчитанная как среднее по всем долготам инцидентов.




4) Сохранить витрину в один файл в формате .parquet в папке path/to/output_folder.

Подсказки:
- Функция percentile_approx может посчитать медиану.
- Конкретный месяц идентифицируется не только номером месяца, но и номером года.
- В справочнике кодов есть дубликаты. Нужно выбрать уникальные коды, взяв любое из названий.

Витрина + BI. 1. Установить Metabase
- Начало работы с интерфейсом командной строки
- Install: https://cloud.yandex.ru/docs/cli/quickstart#install
- Init profile: https://cloud.yandex.ru/docs/cli/quickstart#initialize




`yc config list` # check



- Подключиться к виртуальной машине Linux по SSH
- Создание пары ключей SSH: https://cloud.yandex.ru/docs/compute/operations/vm-connect/ssh#creating-ssh-keys






`ls ~/.ssh/id_rsa.pub # check`




- Создать виртуальную машину из публичного образа Linux https://cloud.yandex.ru/docs/compute/operations/vm-create/create-linux-vm
- проверьте путь до файла с публичным ключом




```
yc compute instance create \
--name airbyte-vm \
--ssh-key ~/.ssh/key.pub \
--create-boot-disk image-folder-id=standard-images,image-family=ubuntu-1804-lts,size=10,auto-delete=true \
--network-interface subnet-name=default-ru-central1-a,nat-ip-version=ipv4 \
--memory 2G \
--cores 2 \
--hostname airbyte-vm
```

1.


`yc compute instance list`




- Запишите публичный адрес ВМ – EXTERNAL IP

- Подключение к виртуальной машине
- https://cloud.yandex.ru/docs/compute/operations/vm-connect/ssh#vm-connect



ssh -L 3000:localhost:3000 -i ~/.ssh/key yc-user@178.154.202.230 <укажите свой EXTERNAL IP>





- If you get this:
- Permissions 0644 for '.../key.pub' are too open.
- It is required that your private key files are NOT accessible by others.
- Then change permissions: https://stackoverflow.com/questions/9270734/ssh-permissions-are-too-open-error




```
sudo chmod 600 ~/.ssh/key.pem
sudo chmod 600 ~/.ssh/key.pub
sudo chmod 755 ~/.ssh

```

- Install docker




```
sudo apt-get update
sudo apt-get install -y apt-transport-https ca-certificates curl gnupg2 software-properties-common
curl -fsSL https://download.docker.com/linux/debian/gpg | sudo apt-key add --
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/debian buster stable"
sudo apt-get update
sudo apt-get install -y docker-ce docker-ce-cli containerd.io
sudo usermod -a -G docker $USER

```

- Install docker-compose




```
sudo apt-get -y install wget
sudo wget https://github.com/docker/compose/releases/download/1.26.2/docker-compose-$(uname -s)-$(uname -m) -O /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose
docker-compose --version

```

- Install Metabase




```
docker run -d -p 3000:3000 --name metabase metabase/metabase

```


- Done
- Access at http://localhost:3000/





2. Подключиться к источнику данных

- ClickHouse Playground: [https://clickhouse.tech/docs/en/getting-started/playground](https://clickhouse.tech/docs/en/getting-started/playground)
- [Supermarket sales](https://www.kaggle.com/datasets/aungpyaeap/supermarket-sales)
- Любой другой



3. Создать дашборд

- Нескольо видов визуализации (таблицы, графики, числа)
- Фильтры
- Блок с комментариями



4. Выслать скриншоты своего дашборда с комментариями

Проектная работа. ## Курсовой проект. Постановка задачи и требования




### Что

В качестве курсового проекта необходимо придумать дизайн и архитектуру data-driven приложения в выбранной доменной области, а затем реализовать это приложение.
Работающее приложение должно быть представлено в качестве репозитория в GitHub.




### Как

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