Wielu korzysta ze specjalistycznych narzędzi do tworzenia procedur wyodrębniania, przekształcania i ładowania danych do relacyjnych baz danych. Proces pracy narzędzi jest rejestrowany, błędy są naprawiane.
W przypadku błędu log zawiera informację, że narzędzie nie wykonało zadania oraz które moduły (często java) zatrzymały się w którym miejscu. W ostatnich wierszach można znaleźć błąd bazy danych, na przykład naruszenie klucza unikalnego tabeli.
Aby odpowiedzieć na pytanie, jaką rolę odgrywają informacje o błędach ETL, sklasyfikowałem wszystkie problemy, które wystąpiły w ciągu ostatnich dwóch lat w dość dużym repozytorium.
Błędy bazy danych obejmują za mało miejsca, utratę połączenia, zawieszenie sesji itp.
Błędy logiczne obejmują takie jak naruszenie kluczy tabeli, nieważne obiekty, brak dostępu do obiektów itp.
Harmonogram może nie zostać uruchomiony na czas, może się zawiesić itp.
Naprawienie prostych błędów nie zajmuje dużo czasu. Dobry ETL poradzi sobie z większością z nich samodzielnie.
Złożone błędy sprawiają, że konieczne jest odkrywanie i testowanie procedur pracy z danymi, eksploracja źródeł danych. Często prowadzą do konieczności testowania i wdrażania zmian.
Tak więc połowa wszystkich problemów jest związana z bazą danych. 48% wszystkich błędów to proste błędy.
Jedna trzecia wszystkich problemów jest związana ze zmianą logiki lub modelu pamięci masowej, ponad połowa z tych błędów jest złożona.
A mniej niż jedna czwarta wszystkich problemów jest związana z harmonogramem zadań, z czego 18% to proste błędy.
Ogólnie rzecz biorąc, 22% wszystkich występujących błędów to błędy złożone, a ich poprawianie wymaga najwięcej uwagi i czasu. Zdarzają się mniej więcej raz w tygodniu. Tymczasem proste błędy zdarzają się niemal codziennie.
Oczywiście monitorowanie procesów ETL będzie skuteczne, gdy lokalizacja błędu zostanie wskazana w logu jak najdokładniej, a znalezienie źródła problemu zajmie jak najmniej czasu.
Skuteczne monitorowanie
Co chciałem zobaczyć w procesie monitorowania ETL?
Zacznij od - kiedy zaczął pracę,
Źródło - źródło danych,
Warstwa - jaki poziom pamięci jest ładowany,
ETL Job Name - procedura uploadu, która składa się z wielu małych kroków,
Step Number - numer wykonywanego kroku,
Affected Rows - ile danych zostało już przetworzonych,
Duration sec - ile to trwa,
Status - czy wszystko jest w porządku, czy nie: OK, ERROR, RUNNING, HANGS
Wiadomość — ostatnia pomyślna wiadomość lub opis błędu.
Na podstawie statusu wpisów możesz wysłać wiadomość e-mail. list do innych członków. Jeśli nie ma błędów, list nie jest potrzebny.
Dzięki temu w przypadku pomyłki miejsce zdarzenia jest wyraźnie wskazane.
Czasami zdarza się, że samo narzędzie monitorujące nie działa. W takim przypadku istnieje możliwość wywołania widoku (widoku) bezpośrednio w bazie danych, na podstawie którego budowany jest raport.
Tabela monitorowania ETL
Do wdrożenia monitoringu procesów ETL wystarczy jedna tabela i jeden widok.
Aby to zrobić, możesz wrócić do i utwórz prototyp w bazie danych sqlite.
Tabele DDL
CREATE TABLE UTL_JOB_STATUS (
/* Table for logging of job execution log. Important that the job has the steps ETL_START and ETL_END or ETL_ERROR */
UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
SID INTEGER NOT NULL DEFAULT -1, /* Session Identificator. Unique for every Run of job */
LOG_DT INTEGER NOT NULL DEFAULT 0, /* Date time */
LOG_D INTEGER NOT NULL DEFAULT 0, /* Date */
JOB_NAME TEXT NOT NULL DEFAULT 'N/A', /* Job name like JOB_STG2DM_GEO */
STEP_NAME TEXT NOT NULL DEFAULT 'N/A', /* ETL_START, ... , ETL_END/ETL_ERROR */
STEP_DESCR TEXT, /* Description of task or error message */
UNIQUE (SID, JOB_NAME, STEP_NAME)
);
INSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);Wyświetl/zgłoś DDL
CREATE VIEW IF NOT EXISTS UTL_JOB_STATUS_V
AS /* Content: Package Execution Log for last 3 Months. */
WITH SRC AS (
SELECT LOG_D,
LOG_DT,
UTL_JOB_STATUS_ID,
SID,
CASE WHEN INSTR(JOB_NAME, 'FTP') THEN 'TRANSFER' /* file transfer */
WHEN INSTR(JOB_NAME, 'STG') THEN 'STAGE' /* stage */
WHEN INSTR(JOB_NAME, 'CLS') THEN 'CLEANSING' /* cleansing */
WHEN INSTR(JOB_NAME, 'DIM') THEN 'DIMENSION' /* dimension */
WHEN INSTR(JOB_NAME, 'FCT') THEN 'FACT' /* fact */
WHEN INSTR(JOB_NAME, 'ETL') THEN 'STAGE-MART' /* data mart */
WHEN INSTR(JOB_NAME, 'RPT') THEN 'REPORT' /* report */
ELSE 'N/A' END AS LAYER,
CASE WHEN INSTR(JOB_NAME, 'ACCESS') THEN 'ACCESS LOG' /* source */
WHEN INSTR(JOB_NAME, 'MASTER') THEN 'MASTER DATA' /* source */
WHEN INSTR(JOB_NAME, 'AD-HOC') THEN 'AD-HOC' /* source */
ELSE 'N/A' END AS SOURCE,
JOB_NAME,
STEP_NAME,
CASE WHEN STEP_NAME='ETL_START' THEN 1 ELSE 0 END AS START_FLAG,
CASE WHEN STEP_NAME='ETL_END' THEN 1 ELSE 0 END AS END_FLAG,
CASE WHEN STEP_NAME='ETL_ERROR' THEN 1 ELSE 0 END AS ERROR_FLAG,
STEP_NAME || ' : ' || STEP_DESCR AS STEP_LOG,
SUBSTR( SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), 1, INSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), '***')-2 ) AS AFFECTED_ROWS
FROM UTL_JOB_STATUS
WHERE datetime(LOG_D, 'unixepoch') >= date('now', 'start of month', '-3 month')
)
SELECT JB.SID,
JB.MIN_LOG_DT AS START_DT,
strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS LOG_DT,
JB.SOURCE,
JB.LAYER,
JB.JOB_NAME,
CASE
WHEN JB.ERROR_FLAG = 1 THEN 'ERROR'
WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 AND strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 THEN 'HANGS' /* half an hour */
WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 THEN 'RUNNING'
ELSE 'OK'
END AS STATUS,
ERR.STEP_LOG AS STEP_LOG,
JB.CNT AS STEP_CNT,
JB.AFFECTED_ROWS AS AFFECTED_ROWS,
strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS JOB_START_DT,
strftime('%d.%m.%Y %H:%M', datetime(JB.MAX_LOG_DT, 'unixepoch')) AS JOB_END_DT,
JB.MAX_LOG_DT - JB.MIN_LOG_DT AS JOB_DURATION_SEC
FROM
( SELECT SID, SOURCE, LAYER, JOB_NAME,
MAX(UTL_JOB_STATUS_ID) AS UTL_JOB_STATUS_ID,
MAX(START_FLAG) AS START_FLAG,
MAX(END_FLAG) AS END_FLAG,
MAX(ERROR_FLAG) AS ERROR_FLAG,
MIN(LOG_DT) AS MIN_LOG_DT,
MAX(LOG_DT) AS MAX_LOG_DT,
SUM(1) AS CNT,
SUM(IFNULL(AFFECTED_ROWS, 0)) AS AFFECTED_ROWS
FROM SRC
GROUP BY SID, SOURCE, LAYER, JOB_NAME
) JB,
( SELECT UTL_JOB_STATUS_ID, SID, JOB_NAME, STEP_LOG
FROM SRC
WHERE 1 = 1
) ERR
WHERE 1 = 1
AND JB.SID = ERR.SID
AND JB.JOB_NAME = ERR.JOB_NAME
AND JB.UTL_JOB_STATUS_ID = ERR.UTL_JOB_STATUS_ID
ORDER BY JB.MIN_LOG_DT DESC, JB.SID DESC, JB.SOURCE;SQL Sprawdzanie, czy możliwe jest uzyskanie nowego numeru sesji
SELECT SUM (
CASE WHEN start_job.JOB_NAME IS NOT NULL AND end_job.JOB_NAME IS NULL /* existed job finished */
AND NOT ( 'y' = 'n' ) /* force restart PARAMETER */
THEN 1 ELSE 0
END ) AS IS_RUNNING
FROM
( SELECT 1 AS dummy FROM UTL_JOB_STATUS WHERE sid = -1) d_job
LEFT OUTER JOIN
( SELECT JOB_NAME, SID, 1 AS dummy
FROM UTL_JOB_STATUS
WHERE JOB_NAME = 'RPT_ACCESS_LOG' /* job name PARAMETER */
AND STEP_NAME = 'ETL_START'
GROUP BY JOB_NAME, SID
) start_job /* starts */
ON d_job.dummy = start_job.dummy
LEFT OUTER JOIN
( SELECT JOB_NAME, SID
FROM UTL_JOB_STATUS
WHERE JOB_NAME = 'RPT_ACCESS_LOG' /* job name PARAMETER */
AND STEP_NAME in ('ETL_END', 'ETL_ERROR') /* stop status */
GROUP BY JOB_NAME, SID
) end_job /* ends */
ON start_job.JOB_NAME = end_job.JOB_NAME
AND start_job.SID = end_job.SIDCechy stołu:
- po rozpoczęciu i zakończeniu procedury przetwarzania danych muszą nastąpić kroki ETL_START i ETL_END
- w przypadku błędu należy utworzyć krok ETL_ERROR wraz z jego opisem
- ilość przetwarzanych danych powinna być wyróżniona np. gwiazdkami
- tę samą procedurę można uruchomić jednocześnie z parametrem force_restart=y, bez niego numer sesji nadawany jest tylko zakończonej procedurze
- w trybie normalnym nie można równolegle uruchomić tej samej procedury przetwarzania danych
Niezbędne operacje do pracy z tabelą są następujące:
- uzyskanie numeru sesji uruchomionej procedury ETL
- wstaw wpis dziennika do tabeli
- uzyskanie ostatniego udanego zapisu procedury ETL
W bazach danych takich jak Oracle czy Postgres operacje te można zaimplementować jako funkcje wbudowane. sqlite wymaga zewnętrznego mechanizmu, aw tym przypadku go .
Wniosek
Dlatego komunikaty o błędach w narzędziach do przetwarzania danych odgrywają mega ważną rolę. Trudno jednak nazwać je optymalnymi do szybkiego znalezienia przyczyny problemu. Kiedy liczba procedur zbliża się do setki, monitoring procesu zamienia się w złożony projekt.
W artykule podano przykład możliwego rozwiązania problemu w postaci prototypu. Cały prototyp małego repozytorium jest dostępny w gitlab .
Źródło: www.habr.com