search

profcomff / dwh-pipelines

Графы работы с данными в Airflow
BSD 3-Clause "New" or "Revised" License
9 stars 0 forks source link

Pipeline работы с расписанием физфака #2

Closed dyakovri closed 2 years ago

dyakovri commented 2 years ago

Подгрузить расписание физфака в базу данных

Комментарии по отдельным задачам ниже

dyakovri commented 2 years ago

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

Особенности:

Указания к разработке

Хочется сделать код читаемым и поддерживаемым, поэтому существует этот раздел

Daiwery commented 2 years ago

Работа с интернетом - тема, которая для меня покрыта мраком и загадкой. Я просто напросто даже не представляю, как все это работает. Ты написал, что есть несколько версий этого парсера? Можешь, пожалуйста, их прислать? Ну или просто покидать туториалы по Beautiful Soup 4 и request. А то без примеров я не справлюсь.

dyakovri commented 2 years ago

Ты написал, что есть несколько версий этого парсера? Можешь, пожалуйста, их прислать?

https://github.com/dyakovri/phys-msu-ru-timetable-parser/blob/master/phys_msu_timetable_parser/__init__.py

Ну или просто покидать туториалы по Beautiful Soup 4 и request

В целом из requests тебе нужно:

user_agent = 'Mozilla/5.0 (Linux; Android 7.0; SM-G930V Build/NRD90M) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.125 Mobile Safari/537.36'
headers = {'User-Agent': user_agent}
response = requests.get(f'http://ras.phys.msu.ru/table/1/1/1.htm', headers=headers)
html_for_soup = response.test

Документация Beautiful Soup 4 https://www.crummy.com/software/BeautifulSoup/bs4/doc/

Прочитай, как работает HTML перед тем, как начать: теги, классы, id, селекторы

dyakovri commented 2 years ago

Как сделать хранение календаря, поэма в трех частях:

  1. У тебя сейчас есть таблица с расписанием, первым шагом надо оттуда достать кабинеты, преподов и т.п. Хорошо бы сделать универсальное и расширяемое решение, предлагаю такое:

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

import re

m = re.match(
    r'.*?>(?P<subject>.+)<nobr>(?P<place>.+)</nobr>(?P<teaher>.+)</.*', 
    '<td align="center" class="tditem1">СФЕРИЧЕСКАЯ АСТРОНОМИЯ<nobr>ГАИШ 48</nobr>проф. Жаров В. Е.</td>''',
    re.IGNORECASE | re.DOTALL
)
m.groupdict()

Даст результат 

{'subject': 'СФЕРИЧЕСКАЯ АСТРОНОМИЯ',
 'place': 'ГАИШ 48',
 'teaher': 'проф. Жаров В. Е.'}

Добавляя регулярку в базу можно будет без изменения кода получать интересные нам параметры. Надо только им еще приоритет будет добавить, следовательно 3 столбца в таблице: id, regex, priority.

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

  1. Надо полученную таблицу положить с сохранением истории. Это важно потому, что нам надо будет мониторить изменения для автоматической синхронизаций с аккаунтами. Зачастую мы будем делать проверку “равна ли прошлая неделя текущей” (на самом деле нас будет интересовать разница между предыдущим временем синхронизации и текущим временем), и разницу будем обновлять в аккаунтах.

Нашу таблицу, думаю, стоит построить следующим образом: id, (предмет, время, параметры), время изменения, флаг удаления

Заполняется она следующим образом:

  1. Делаем запрос, который отдаст актуальное расписание
  2. Пытаемся добавить новую строку: 2.1. Если среди строк актуального расписания нет таких предмет, время, параметры, добавляем новую строку с новым id, текущим временем изменения, флаг удаления = FALSE 2.2. Если среди строк актуального расписания есть предмет, время, параметры, ничего не добавляем 2.3. Если среди строк актуального расписания есть предмет, время, параметры, которого нет в обновляемом расписании, то устанавливается последний ID этого предмета, текущее время изменения и флаг удаления = TRUE

Там довольно сложный запрос есть для создания такой таблицы с историей, для чтения чуть проще, но тоже сложный. Я тебе скину пример. Это очень стандартный подход к работе с историчными данными

Вставка выглядит примерно так: 

INSERT INTO {ods_table} ({columns}, is_deleted, tech_load_ts)
WITH LAST_ODS_VERSION AS (
    SELECT *
    FROM (
        SELECT *
        FROM {ods_table} ods
        LIMIT 1 OVER (
            PARTITION BY {primary_keys}
            ORDER BY ods.tech_load_ts DESC
        )
    ) s
    WHERE is_deleted != 1
),
DIFFERENCE_NEW_ITEMS AS (
    SELECT {columns} FROM {stg_table}
    EXCEPT
    SELECT {columns} FROM LAST_ODS_VERSION
),
DIFFERENCE_DELETE_ITEMS AS (
    SELECT ods.*
    FROM LAST_ODS_VERSION ods
    LEFT JOIN {stg_table} stg
        ON {pk_compare}
    WHERE {pk_exists}
),
DIFFERENCE_ALL AS (
    SELECT
        {columns},
        0 AS is_deleted,
        STATEMENT_TIMESTAMP() AS tech_load_ts
    FROM DIFFERENCE_NEW_ITEMS
    UNION ALL
    SELECT
        {columns},
        1 AS is_deleted,
        STATEMENT_TIMESTAMP() AS tech_load_ts
    FROM DIFFERENCE_DELETE_ITEMS
)
SELECT {columns}, t.is_deleted, t.tech_load_ts
FROM DIFFERENCE_ALL t;

Соответственно 

SELECT *
FROM (
    SELECT *
    FROM {ods_table} ods
    LIMIT 1 OVER (
        PARTITION BY {primary_keys}
        ORDER BY ods.tech_load_ts DESC
    )
) s
WHERE is_deleted != 1

используется для получения последней (актуальной) версии таблицы

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

Надо будет из таблицы истории доставать изменения. Проверять, каких мероприятий коснулись эти изменения, логиниться и делать изменения в календарях пользователей. Добавлять в лог новые мероприятия, удалять старые и менять время последней синхронизации. Тут довольно просто, но до этого еще далеко