{"id":37701,"date":"2019-10-31T22:19:10","date_gmt":"2019-10-31T19:19:10","guid":{"rendered":"https:\/\/prohoster.info\/blog\/monitoring-etl-protsessov-v-malenkom-hranilishhe-dannyh\/"},"modified":"2019-10-31T22:19:10","modified_gmt":"2019-10-31T19:19:10","slug":"monitoring-etl-protsessov-v-malenkom-hranilishhe-dannyh","status":"publish","type":"post","link":"https:\/\/prohoster.info\/pl\/blog\/administrirovanie\/monitoring-etl-protsessov-v-malenkom-hranilishhe-dannyh","title":{"rendered":"Monitorowanie proces\u00f3w ETL w ma\u0142ym magazynie danych","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Wielu u\u017cytkownik\u00f3w korzysta z wyspecjalizowanych narz\u0119dzi do tworzenia procedur ekstrakcji, transformacji i \u0142adowania danych do relacyjnych baz danych. Proces dzia\u0142ania narz\u0119dzi jest logowany, a b\u0142\u0119dy s\u0105 dokumentowane.<\/p>\n

W przypadku wyst\u0105pienia b\u0142\u0119du w logu znajduje si\u0119 informacja o tym, \u017ce narz\u0119dzie nie mog\u0142o wykona\u0107 zadania oraz kt\u00f3re modu\u0142y (cz\u0119sto jest to java) si\u0119 zatrzyma\u0142y. W ostatnich linijkach mo\u017cna znale\u017a\u0107 b\u0142\u0105d bazy danych, na przyk\u0142ad naruszenie unikalnego klucza tabeli.<\/p>\n

Aby odpowiedzie\u0107 na pytanie, jak\u0105 rol\u0119 odgrywaj\u0105 informacje o b\u0142\u0119dach ETL, sklasyfikowa\u0142em wszystkie problemy, kt\u00f3re wyst\u0105pi\u0142y przez ostatnie dwa lata w du\u017cym zbiorze danych. <\/p>\n

\"Monitorowanie<\/a><\/noindex><\/p>\n

Do b\u0142\u0119d\u00f3w bazy danych zalicza si\u0119 takie jak brak miejsca, przerwanie po\u0142\u0105czenia, zawieszenie sesji itp.<\/p>\n

Do b\u0142\u0119d\u00f3w logicznych zalicza si\u0119 takie jak naruszenie kluczy tabeli, niewa\u017cne obiekty, brak dost\u0119pu do obiekt\u00f3w itp.
\nPlanista mo\u017ce by\u0107 uruchomiony nie w por\u0119, mo\u017ce tak\u017ce zawiesi\u0107 si\u0119 itp.<\/p>\n

Proste b\u0142\u0119dy nie wymagaj\u0105 du\u017co czasu na napraw\u0119. G\u0142\u00f3wnie dobre ETL potrafi sobie z nimi samodzielnie poradzi\u0107.<\/p>\n

Z\u0142o\u017cone b\u0142\u0119dy wymagaj\u0105 otwarcia i sprawdzenia procedur pracy z danymi, oraz badania \u017ar\u00f3de\u0142 danych. Cz\u0119sto prowadz\u0105 do konieczno\u015bci testowania zmian i wdro\u017cenia.<\/p>\n

Tak wi\u0119c po\u0142owa wszystkich problem\u00f3w dotyczy bazy danych. 48% wszystkich b\u0142\u0119d\u00f3w to proste b\u0142\u0119dy.
\nJedna trzecia wszystkich problem\u00f3w zwi\u0105zana jest ze zmian\u0105 logiki lub modelu zbioru danych, a wi\u0119kszo\u015b\u0107 tych b\u0142\u0119d\u00f3w jest z\u0142o\u017cona.<\/p>\n

I mniej ni\u017c jedna czwarta wszystkich problem\u00f3w zwi\u0105zana jest z planist\u0105 zada\u0144, z czego 18% to proste b\u0142\u0119dy.<\/p>\n

Og\u00f3lnie, 22% wszystkich wyst\u0119puj\u0105cych b\u0142\u0119d\u00f3w jest z\u0142o\u017conych, ich naprawa wymaga najwi\u0119kszej uwagi i czasu. Wyst\u0119puj\u0105 one mniej wi\u0119cej raz w tygodniu, podczas gdy proste b\u0142\u0119dy zdarzaj\u0105 si\u0119 prawie ka\u017cdego dnia.<\/p>\n

Jasne jest, \u017ce monitorowanie proces\u00f3w ETL b\u0119dzie efektywne wtedy, gdy w logu maksymalnie dok\u0142adnie wskazane jest miejsce b\u0142\u0119du i wymagane jest minimalne czas na znalezienie \u017ar\u00f3d\u0142a problemu.<\/p>\n

Efektywne monitorowanie<\/h4>\n

\nCzego chcia\u0142bym widzie\u0107 w procesie monitorowania ETL?<\/p>\n

\"Monitorowanie
\nStart at \u2014 kiedy rozpocz\u0119to prac\u0119,
\nSource \u2014 \u017ar\u00f3d\u0142o danych,
\nLayer \u2014 jaki poziom zbioru danych jest \u0142adowany,
\nETL Job Name \u2014 procedura \u0142adowania, sk\u0142adaj\u0105ca si\u0119 z wielu drobnych krok\u00f3w,
\nStep Number \u2014 numer wykonywanego kroku,
\nLiczba przetworzonych wierszy \u2014 ile danych zosta\u0142o ju\u017c przetworzonych,
\nCzas trwania (s) \u2014 jak d\u0142ugo trwa wykonanie,
\nStatus \u2014 czy wszystko jest w porz\u0105dku, czy nie: OK, B\u0141\u0104D, W TRAKCIE, ZAWIESZONE
\nWiadomo\u015b\u0107 \u2014 ostatnia udana wiadomo\u015b\u0107 lub opis b\u0142\u0119du.<\/p>\n

Na podstawie statusu zapis\u00f3w mo\u017cna wys\u0142a\u0107 e-mail do innych uczestnik\u00f3w. Je\u015bli nie ma b\u0142\u0119d\u00f3w, wiadomo\u015b\u0107 e-mail r\u00f3wnie\u017c nie jest konieczna.<\/p>\n

W ten spos\u00f3b w przypadku b\u0142\u0119du jest jasno okre\u015blone miejsce wyst\u0105pienia problemu.<\/p>\n

Czasami zdarza si\u0119, \u017ce sam narz\u0119dzie do monitorowania nie dzia\u0142a. W takim przypadku mo\u017cna bezpo\u015brednio w bazie danych wywo\u0142a\u0107 widok, na podstawie kt\u00f3rego stworzono raport.<\/p>\n

Tabela monitorowania ETL<\/h4>\n

\nAby zrealizowa\u0107 monitoring proces\u00f3w ETL, wystarczy jedna tabela i jeden widok.<\/p>\n

W tym celu mo\u017cna wr\u00f3ci\u0107 do swojego ma\u0142ego repozytorium<\/a><\/noindex> i stworzy\u0107 prototyp w bazie danych SQLite.<\/p>\n

DDL tabeli<\/b><\/p>\n

CREATE TABLE UTL_JOB_STATUS (\n\n\/* Tabela do logowania wykonania zada\u0144. To wa\u017cne, aby zadanie mia\u0142o kroki ETL_START i ETL_END lub ETL_ERROR *\/\n  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n  SID               INTEGER NOT NULL DEFAULT -1, \/* Identyfikator sesji. Unikalny dla ka\u017cdego uruchomienia zadania *\/\n  LOG_DT            INTEGER NOT NULL DEFAULT 0,  \/* Data i czas *\/\n  LOG_D             INTEGER NOT NULL DEFAULT 0,  \/* Data *\/\n  JOB_NAME          TEXT NOT NULL DEFAULT 'N\/A', \/* Nazwa zadania, np. JOB_STG2DM_GEO *\/\n  STEP_NAME         TEXT NOT NULL DEFAULT 'N\/A', \/* ETL_START, ... , ETL_END\/ETL_ERROR *\/\n  STEP_DESCR        TEXT,                        \/* Opis zadania lub wiadomo\u015bci o b\u0142\u0119dzie *\/\n  UNIQUE (SID, JOB_NAME, STEP_NAME)\n);\nINSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);<\/code><\/pre>\n
\nDDL widoku\/raportu<\/b><\/p>\n
UTW\u00d3RZ WIDOK, JE\u017bELI NIE ISTNIEJE UTL_JOB_STATUS_V\nJAKO \n\/* Tre\u015b\u0107: Dziennik wykonania pakietu za ostatnie 3 miesi\u0105ce. *\/\nZ SRC JAKO (\n  WYBIERZ LOG_D,\n    LOG_DT,\n    UTL_JOB_STATUS_ID,\n    SID,\n\tCASE WHEN INSTR(JOB_NAME, 'FTP') THEN 'TRANSFER' \/* przesy\u0142 plik\u00f3w *\/\n\t     WHEN INSTR(JOB_NAME, 'STG') THEN 'ETAP' \/* etap *\/\n\t     WHEN INSTR(JOB_NAME, 'CLS') THEN 'OCZYSZCZANIE' \/* oczyszczanie *\/\n\t     WHEN INSTR(JOB_NAME, 'DIM') THEN 'WYMIAR' \/* wymiar *\/\n\t     WHEN INSTR(JOB_NAME, 'FCT') THEN 'FAKT' \/* fakt *\/\n\t\t WHEN INSTR(JOB_NAME, 'ETL') THEN 'ETAP-MART' \/* magazyn danych *\/\n\t     WHEN INSTR(JOB_NAME, 'RPT') THEN 'RAPORT' \/* raport *\/\n\t     ELSE 'N\/A' END AS WARSTWA,\n\tCASE WHEN INSTR(JOB_NAME, 'ACCESS') THEN 'LOG DOST\u0118PU' \/* \u017ar\u00f3d\u0142o *\/\n\t     WHEN INSTR(JOB_NAME, 'MASTER') THEN 'DANE MASTER' \/* \u017ar\u00f3d\u0142o *\/\n\t     WHEN INSTR(JOB_NAME, 'AD-HOC') THEN 'AD-HOC' \/* \u017ar\u00f3d\u0142o *\/\n\t     ELSE 'N\/A' END AS \u0179R\u00d3D\u0141O,\n    JOB_NAME,\n    STEP_NAME,\n    CASE WHEN STEP_NAME='ETL_START' THEN 1 ELSE 0 END AS FLAG_START,\n    CASE WHEN STEP_NAME='ETL_END' THEN 1 ELSE 0 END AS FLAG_END,\n    CASE WHEN STEP_NAME='ETL_ERROR' THEN 1 ELSE 0 END AS FLAG_B\u0141\u0118D\u00d3W,\n    STEP_NAME || ' : ' || STEP_DESCR AS LOG_ETAPU,\n\tSUBSTR( SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), 1, INSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), '***')-2 ) AS ZMIENIONE_WIERSZE\n  Z UTL_JOB_STATUS\n  GDZIE datetime(LOG_D, 'unixepoch') >= date('now', 'start of month', '-3 month')\n)\nWYBIERZ JB.SID,\n  JB.MIN_LOG_DT AS DATA_START,\n  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS LOG_DT,\n  JB.\u0179R\u00d3D\u0141O,\n  JB.WARSTWA,\n  JB.JOB_NAME,\n  CASE\n  WHEN JB.FLAG_B\u0141\u0118D\u00d3W = 1 THEN 'B\u0141\u0104D'\n  WHEN JB.FLAG_B\u0141\u0118D\u00d3W = 0 AND JB.FLAG_END = 0 AND strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 THEN 'WISZY' \/* p\u00f3\u0142 godziny *\/\n  WHEN JB.FLAG_B\u0141\u0118D\u00d3W = 0 AND JB.FLAG_END = 0 THEN 'DZIA\u0141A'\n  ELSE 'OK'\n  END AS STATUS,\n  ERR.LOG_ETAPU     AS LOG_ETAPU,\n  JB.CNT           AS CNT_ETAPU,\n  JB.ZMIENIONE_WIERSZE AS ZMIENIONE_WIERSZE,\n  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS DATA_START_JOBU,\n  strftime('%d.%m.%Y %H:%M', datetime(JB.MAX_LOG_DT, 'unixepoch')) AS DATA_END_JOBU,\n  JB.MAX_LOG_DT - JB.MIN_LOG_DT AS CZAS_TRWANIA_JOBU_SEC\nZ\n  ( WYBIERZ SID, \u0179R\u00d3D\u0141O, WARSTWA, JOB_NAME,\n           MAX(UTL_JOB_STATUS_ID) AS UTL_JOB_STATUS_ID,\n           MAX(FLAG_START)       AS FLAG_START,\n           MAX(FLAG_END)         AS FLAG_END,\n           MAX(FLAG_B\u0141\u0118D\u00d3W)     AS FLAG_B\u0141\u0118D\u00d3W,\n           MIN(LOG_DT)           AS MIN_LOG_DT,\n           MAX(LOG_DT)           AS MAX_LOG_DT,\n           SUM(1)                AS CNT,\n           SUM(IFNULL(ZMIENIONE_WIERSZE, 0)) AS ZMIENIONE_WIERSZE\n    Z SRC\n    GROUP BY SID, \u0179R\u00d3D\u0141O, WARSTWA, JOB_NAME\n  ) JB,\n  ( WYBIERZ UTL_JOB_STATUS_ID, SID, JOB_NAME, LOG_ETAPU\n    Z SRC\n    GDZIE 1 = 1\n  ) ERR\nGDZIE 1 = 1\n  I JB.SID = ERR.SID\n  I JB.JOB_NAME = ERR.JOB_NAME\n  I JB.UTL_JOB_STATUS_ID = ERR.UTL_JOB_STATUS_ID\nZAM\u00d3WIONE PRZEZ JB.MIN_LOG_DT DESC, JB.SID DESC, JB.\u0179R\u00d3D\u0141O;<\/code><\/pre>\n
\nSQL Sprawdzenie mo\u017cliwo\u015bci uzyskania nowego numeru sesji<\/b><\/p>\n
WYBIERZ SUMA (\n  CASE WHEN start_job.JOB_NAME IS NOT NULL I end_job.JOB_NAME IS NULL \/* istniej\u0105ca praca zako\u0144czona *\/\n\t    I NIE ( 'y' = 'n' ) \/* wymu\u015b ponowne uruchomienie PARAMETRU *\/\n       TO 1 INNY 0\n  END ) AS IS_RUNNING\n  Z\n    ( WYBIERZ 1 AS dummy Z UTL_JOB_STATUS GDZIE sid = -1) d_job\n  LEWY ZJAZD DO\n    ( WYBIERZ JOB_NAME, SID, 1 AS dummy\n      Z UTL_JOB_STATUS\n      GDZIE JOB_NAME = 'RPT_ACCESS_LOG' \/* nazwa pracy PARAMETRU *\/\n\t    I STEP_NAME = 'ETL_START'\n      GRUPUJ PO JOB_NAME, SID\n    ) start_job \/* zaczyna *\/\n  NA d_job.dummy = start_job.dummy\n  LEWY ZJAZD DO\n    ( WYBIERZ JOB_NAME, SID\n      Z UTL_JOB_STATUS\n      GDZIE JOB_NAME = 'RPT_ACCESS_LOG'  \/* nazwa pracy PARAMETRU *\/\n\t    I STEP_NAME in ('ETL_END', 'ETL_ERROR') \/* status zatrzymania *\/\n      GRUPUJ PO JOB_NAME, SID\n    ) end_job \/* ko\u0144czy *\/\n  NA start_job.JOB_NAME = end_job.JOB_NAME\n     I start_job.SID = end_job.SID<\/code><\/pre>\n
\nCechy tabeli:<\/p>\n