Wielu użytkowników korzysta z wyspecjalizowanych narzędzi do tworzenia procedur ekstrakcji, transformacji i ładowania danych do relacyjnych baz danych. Proces działania narzędzi jest logowany, a błędy są dokumentowane.
W przypadku wystąpienia błędu w logu znajduje się informacja o tym, że narzędzie nie mogło wykonać zadania oraz które moduły (często jest to java) się zatrzymały. W ostatnich linijkach można znaleźć błąd bazy danych, na przykład naruszenie unikalnego klucza tabeli.
Aby odpowiedzieć na pytanie, jaką rolę odgrywają informacje o błędach ETL, sklasyfikowałem wszystkie problemy, które wystąpiły przez ostatnie dwa lata w dużym zbiorze danych.
Do błędów bazy danych zalicza się takie jak brak miejsca, przerwanie połączenia, zawieszenie sesji itp.
Do błędów logicznych zalicza się takie jak naruszenie kluczy tabeli, nieważne obiekty, brak dostępu do obiektów itp.
Planista może być uruchomiony nie w porę, może także zawiesić się itp.
Proste błędy nie wymagają dużo czasu na naprawę. Głównie dobre ETL potrafi sobie z nimi samodzielnie poradzić.
Złożone błędy wymagają otwarcia i sprawdzenia procedur pracy z danymi, oraz badania źródeł danych. Często prowadzą do konieczności testowania zmian i wdrożenia.
Tak więc połowa wszystkich problemów dotyczy bazy danych. 48% wszystkich błędów to proste błędy.
Jedna trzecia wszystkich problemów związana jest ze zmianą logiki lub modelu zbioru danych, a większość tych błędów jest złożona.
I mniej niż jedna czwarta wszystkich problemów związana jest z planistą zadań, z czego 18% to proste błędy.
Ogólnie, 22% wszystkich występujących błędów jest złożonych, ich naprawa wymaga największej uwagi i czasu. Występują one mniej więcej raz w tygodniu, podczas gdy proste błędy zdarzają się prawie każdego dnia.
Jasne jest, że monitorowanie procesów ETL będzie efektywne wtedy, gdy w logu maksymalnie dokładnie wskazane jest miejsce błędu i wymagane jest minimalne czas na znalezienie źródła problemu.
Efektywne monitorowanie
Czego chciałbym widzieć w procesie monitorowania ETL?
Start at — kiedy rozpoczęto pracę,
Source — źródło danych,
Layer — jaki poziom zbioru danych jest ładowany,
ETL Job Name — procedura ładowania, składająca się z wielu drobnych kroków,
Step Number — numer wykonywanego kroku,
Liczba przetworzonych wierszy — ile danych zostało już przetworzonych,
Czas trwania (s) — jak długo trwa wykonanie,
Status — czy wszystko jest w porządku, czy nie: OK, BŁĄD, W TRAKCIE, ZAWIESZONE
Wiadomość — ostatnia udana wiadomość lub opis błędu.
Na podstawie statusu zapisów można wysłać e-mail do innych uczestników. Jeśli nie ma błędów, wiadomość e-mail również nie jest konieczna.
W ten sposób w przypadku błędu jest jasno określone miejsce wystąpienia problemu.
Czasami zdarza się, że sam narzędzie do monitorowania nie działa. W takim przypadku można bezpośrednio w bazie danych wywołać widok, na podstawie którego stworzono raport.
Tabela monitorowania ETL
Aby zrealizować monitoring procesów ETL, wystarczy jedna tabela i jeden widok.
W tym celu można wrócić do i stworzyć prototyp w bazie danych SQLite.
DDL tabeli
CREATE TABLE UTL_JOB_STATUS (
/* Tabela do logowania wykonania zadań. To ważne, aby zadanie miało kroki ETL_START i ETL_END lub ETL_ERROR */
UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
SID INTEGER NOT NULL DEFAULT -1, /* Identyfikator sesji. Unikalny dla każdego uruchomienia zadania */
LOG_DT INTEGER NOT NULL DEFAULT 0, /* Data i czas */
LOG_D INTEGER NOT NULL DEFAULT 0, /* Data */
JOB_NAME TEXT NOT NULL DEFAULT 'N/A', /* Nazwa zadania, np. JOB_STG2DM_GEO */
STEP_NAME TEXT NOT NULL DEFAULT 'N/A', /* ETL_START, ... , ETL_END/ETL_ERROR */
STEP_DESCR TEXT, /* Opis zadania lub wiadomości o błędzie */
UNIQUE (SID, JOB_NAME, STEP_NAME)
);
INSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);DDL widoku/raportu
UTWÓRZ WIDOK, JEŻELI NIE ISTNIEJE UTL_JOB_STATUS_V
JAKO
/* Treść: Dziennik wykonania pakietu za ostatnie 3 miesiące. */
Z SRC JAKO (
WYBIERZ LOG_D,
LOG_DT,
UTL_JOB_STATUS_ID,
SID,
CASE WHEN INSTR(JOB_NAME, 'FTP') THEN 'TRANSFER' /* przesył plików */
WHEN INSTR(JOB_NAME, 'STG') THEN 'ETAP' /* etap */
WHEN INSTR(JOB_NAME, 'CLS') THEN 'OCZYSZCZANIE' /* oczyszczanie */
WHEN INSTR(JOB_NAME, 'DIM') THEN 'WYMIAR' /* wymiar */
WHEN INSTR(JOB_NAME, 'FCT') THEN 'FAKT' /* fakt */
WHEN INSTR(JOB_NAME, 'ETL') THEN 'ETAP-MART' /* magazyn danych */
WHEN INSTR(JOB_NAME, 'RPT') THEN 'RAPORT' /* raport */
ELSE 'N/A' END AS WARSTWA,
CASE WHEN INSTR(JOB_NAME, 'ACCESS') THEN 'LOG DOSTĘPU' /* źródło */
WHEN INSTR(JOB_NAME, 'MASTER') THEN 'DANE MASTER' /* źródło */
WHEN INSTR(JOB_NAME, 'AD-HOC') THEN 'AD-HOC' /* źródło */
ELSE 'N/A' END AS ŹRÓDŁO,
JOB_NAME,
STEP_NAME,
CASE WHEN STEP_NAME='ETL_START' THEN 1 ELSE 0 END AS FLAG_START,
CASE WHEN STEP_NAME='ETL_END' THEN 1 ELSE 0 END AS FLAG_END,
CASE WHEN STEP_NAME='ETL_ERROR' THEN 1 ELSE 0 END AS FLAG_BŁĘDÓW,
STEP_NAME || ' : ' || STEP_DESCR AS LOG_ETAPU,
SUBSTR( SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), 1, INSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), '***')-2 ) AS ZMIENIONE_WIERSZE
Z UTL_JOB_STATUS
GDZIE datetime(LOG_D, 'unixepoch') >= date('now', 'start of month', '-3 month')
)
WYBIERZ JB.SID,
JB.MIN_LOG_DT AS DATA_START,
strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS LOG_DT,
JB.ŹRÓDŁO,
JB.WARSTWA,
JB.JOB_NAME,
CASE
WHEN JB.FLAG_BŁĘDÓW = 1 THEN 'BŁĄD'
WHEN JB.FLAG_BŁĘDÓW = 0 AND JB.FLAG_END = 0 AND strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 THEN 'WISZY' /* pół godziny */
WHEN JB.FLAG_BŁĘDÓW = 0 AND JB.FLAG_END = 0 THEN 'DZIAŁA'
ELSE 'OK'
END AS STATUS,
ERR.LOG_ETAPU AS LOG_ETAPU,
JB.CNT AS CNT_ETAPU,
JB.ZMIENIONE_WIERSZE AS ZMIENIONE_WIERSZE,
strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS DATA_START_JOBU,
strftime('%d.%m.%Y %H:%M', datetime(JB.MAX_LOG_DT, 'unixepoch')) AS DATA_END_JOBU,
JB.MAX_LOG_DT - JB.MIN_LOG_DT AS CZAS_TRWANIA_JOBU_SEC
Z
( WYBIERZ SID, ŹRÓDŁO, WARSTWA, JOB_NAME,
MAX(UTL_JOB_STATUS_ID) AS UTL_JOB_STATUS_ID,
MAX(FLAG_START) AS FLAG_START,
MAX(FLAG_END) AS FLAG_END,
MAX(FLAG_BŁĘDÓW) AS FLAG_BŁĘDÓW,
MIN(LOG_DT) AS MIN_LOG_DT,
MAX(LOG_DT) AS MAX_LOG_DT,
SUM(1) AS CNT,
SUM(IFNULL(ZMIENIONE_WIERSZE, 0)) AS ZMIENIONE_WIERSZE
Z SRC
GROUP BY SID, ŹRÓDŁO, WARSTWA, JOB_NAME
) JB,
( WYBIERZ UTL_JOB_STATUS_ID, SID, JOB_NAME, LOG_ETAPU
Z SRC
GDZIE 1 = 1
) ERR
GDZIE 1 = 1
I JB.SID = ERR.SID
I JB.JOB_NAME = ERR.JOB_NAME
I JB.UTL_JOB_STATUS_ID = ERR.UTL_JOB_STATUS_ID
ZAMÓWIONE PRZEZ JB.MIN_LOG_DT DESC, JB.SID DESC, JB.ŹRÓDŁO;SQL Sprawdzenie możliwości uzyskania nowego numeru sesji
WYBIERZ SUMA (
CASE WHEN start_job.JOB_NAME IS NOT NULL I end_job.JOB_NAME IS NULL /* istniejąca praca zakończona */
I NIE ( 'y' = 'n' ) /* wymuś ponowne uruchomienie PARAMETRU */
TO 1 INNY 0
END ) AS IS_RUNNING
Z
( WYBIERZ 1 AS dummy Z UTL_JOB_STATUS GDZIE sid = -1) d_job
LEWY ZJAZD DO
( WYBIERZ JOB_NAME, SID, 1 AS dummy
Z UTL_JOB_STATUS
GDZIE JOB_NAME = 'RPT_ACCESS_LOG' /* nazwa pracy PARAMETRU */
I STEP_NAME = 'ETL_START'
GRUPUJ PO JOB_NAME, SID
) start_job /* zaczyna */
NA d_job.dummy = start_job.dummy
LEWY ZJAZD DO
( WYBIERZ JOB_NAME, SID
Z UTL_JOB_STATUS
GDZIE JOB_NAME = 'RPT_ACCESS_LOG' /* nazwa pracy PARAMETRU */
I STEP_NAME in ('ETL_END', 'ETL_ERROR') /* status zatrzymania */
GRUPUJ PO JOB_NAME, SID
) end_job /* kończy */
NA start_job.JOB_NAME = end_job.JOB_NAME
I start_job.SID = end_job.SIDCechy tabeli:
- Początek i koniec procedury przetwarzania danych powinny być poprzedzone krokami ETL_START i ETL_END
- W przypadku błędu powinien być tworzony krok ETL_ERROR z jego opisem
- Liczba przetworzonych danych powinna być wyróżniona, na przykład gwiazdkami
- Jednocześnie tę samą procedurę można uruchomić z parametrem force_restart=y, bez niego numer sesji jest wydawany tylko dla zakończonej procedury
- W normalnym trybie nie można równocześnie uruchomić tej samej procedury przetwarzania danych
Niezbędnymi operacjami pracy z tabelą są następujące:
- Pobranie numeru sesji uruchamianej procedury ETL
- Wstawienie rekordu logu do tabeli
- Pobranie ostatniego udanego rekordu procedury ETL
W takich bazach danych, jak na przykład Oracle czy Postgres, te operacje można zrealizować wbudowanymi funkcjami. W przypadku sqlite potrzebny jest zewnętrzny mechanizm, a w tym przypadku .
Wnioski
W ten sposób komunikaty o błędach w narzędziach do przetwarzania danych odgrywają niezwykle ważną rolę. Jednak trudno je nazwać optymalnymi dla szybkiego znalezienia przyczyny problemu. Gdy liczba procedur zbliża się do stu, monitorowanie procesów staje się skomplikowanym projektem.
W artykule przedstawiono przykład możliwego rozwiązania problemu w postaci prototypu. Cały prototyp małego magazynu jest dostępny w gitlab .
Źródło: habr.com
