{"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\/de\/blog\/administrirovanie\/monitoring-etl-protsessov-v-malenkom-hranilishhe-dannyh","title":{"rendered":"\u00dcberwachung von ETL-Prozessen in einem kleinen Data Warehouse","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Viele verwenden spezialisierte Werkzeuge zur Erstellung von Prozessen zur Extraktion, Transformation und Laden von Daten in relationale Datenbanken. Der Arbeitsprozess der Werkzeuge wird protokolliert, Fehler werden erfasst.<\/p>\n

Im Falle eines Fehlers enth\u00e4lt das Protokoll Informationen dar\u00fcber, dass es dem Werkzeug nicht gelungen ist, die Aufgabe auszuf\u00fchren, und welche Module (h\u00e4ufig java) wo gestoppt sind. In den letzten Zeilen kann man den Datenbankfehler finden, zum Beispiel einen Versto\u00df gegen den einzigartigen Schl\u00fcssel der Tabelle.<\/p>\n

Um die Rolle der Informationen \u00fcber ETL-Fehler zu beantworten, habe ich alle Probleme klassifiziert, die in den letzten zwei Jahren in einem nicht kleinen Data Warehouse aufgetreten sind. <\/p>\n

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

Datenbankfehler umfassen Dinge wie unzureichenden Speicherplatz, unterbrochene Verbindungen, h\u00e4ngende Sitzungen usw.<\/p>\n

Logische Fehler umfassen Dinge wie Verst\u00f6\u00dfe gegen Tabellenschl\u00fcssel, ung\u00fcltige Objekte, fehlender Zugriff auf Objekte usw.
\nDer Scheduler kann zu einem falschen Zeitpunkt gestartet werden, h\u00e4ngen bleiben usw.<\/p>\n

Einfache Fehler erfordern nicht viel Zeit zur Behebung. Die meisten davon kann ein gutes ETL-System selbst bew\u00e4ltigen.<\/p>\n

Komplexe Fehler f\u00fchren dazu, dass man die Datenverarbeitungsverfahren \u00f6ffnen und \u00fcberpr\u00fcfen, die Datenquellen untersuchen muss. Dies f\u00fchrt oft zu Test\u00e4nderungen und Deployment.<\/p>\n

Somit h\u00e4ngen die H\u00e4lfte aller Probleme mit der Datenbank zusammen. 48 % aller Fehler sind einfache Fehler.
\nEin Drittel aller Probleme h\u00e4ngt mit \u00c4nderungen in der Logik oder dem Modell des Data Warehouses zusammen, mehr als die H\u00e4lfte dieser Fehler sind komplexe Fehler.<\/p>\n

Und weniger als ein Viertel aller Probleme h\u00e4ngt mit dem Aufgabenplaner zusammen, 18 % davon sind einfache Fehler.<\/p>\n

Insgesamt sind 22 % aller aufgetretenen Fehler komplex, ihre Behebung erfordert die gr\u00f6\u00dfte Aufmerksamkeit und Zeit. Sie treten ungef\u00e4hr einmal pro Woche auf. W\u00e4hrend einfache Fehler fast jeden Tag passieren.<\/p>\n

Offensichtlich ist die \u00dcberwachung von ETL-Prozessen dann effektiv, wenn im Protokoll der Fehlerort so genau wie m\u00f6glich angegeben ist und minimaler Aufwand erforderlich ist, um die Ursache des Problems zu finden.<\/p>\n

Effektive \u00dcberwachung<\/h4>\n

\nWas wollte ich im Prozess der ETL-\u00dcberwachung sehen?<\/p>\n

\"\u00dcberwachung
\nStartzeit - wann die Arbeit begann,
\nQuelle - Datenquelle,
\nSchicht - welche Ebene des Data Warehouses geladen wird,
\nETL Job Name \u2014 ein Ladeverfahren, das aus vielen kleinen Schritten besteht,
\nStep Number \u2014 die Nummer des ausgef\u00fchrten Schrittes,
\nAffected Rows \u2014 wie viele Daten bereits verarbeitet wurden,
\nDuration sec \u2014 wie lange es dauert,
\nStatus \u2014 ob alles gut ist oder nicht: OK, ERROR, RUNNING, HANGS
\nMessage \u2014 die letzte erfolgreiche Nachricht oder eine Fehlermeldung.<\/p>\n

Anhand des Status der Eintr\u00e4ge kann eine E-Mail an andere Teilnehmer gesendet werden. Wenn es keine Fehler gibt, ist die E-Mail nicht unbedingt erforderlich.<\/p>\n

Somit wird im Fehlerfall genau der Ort des Vorfalls angegeben.<\/p>\n

Manchmal kommt es vor, dass das \u00dcberwachungstool selbst nicht funktioniert. In diesem Fall besteht die M\u00f6glichkeit, direkt in der Datenbank die Sicht (View) aufzurufen, auf deren Grundlage der Bericht erstellt wurde.<\/p>\n

\u00dcberwachungstabelle ETL<\/h4>\n

\nUm die \u00dcberwachung der ETL-Prozesse zu realisieren, gen\u00fcgt eine Tabelle und eine Sicht.<\/p>\n

Daf\u00fcr kann man zu seinem kleinen Lager zur\u00fcckkehren<\/a><\/noindex> und ein Prototyp in der SQLite-Datenbank erstellen.<\/p>\n

DDL der Tabelle<\/b><\/p>\n

CREATE TABLE UTL_JOB_STATUS (\n\n\/* Tabelle zum Protokollieren der Ausf\u00fchrung des Jobs. Wichtig, dass der Job die Schritte ETL_START und ETL_END oder ETL_ERROR hat *\/\n  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n  SID               INTEGER NOT NULL DEFAULT -1, \/* Sitzungsidentifikator. Einzigartig f\u00fcr jeden Lauf des Jobs *\/\n  LOG_DT            INTEGER NOT NULL DEFAULT 0,  \/* Datum und Uhrzeit *\/\n  LOG_D             INTEGER NOT NULL DEFAULT 0,  \/* Datum *\/\n  JOB_NAME          TEXT NOT NULL DEFAULT 'N\/A', \/* Jobname wie JOB_STG2DM_GEO *\/\n  STEP_NAME         TEXT NOT NULL DEFAULT 'N\/A', \/* ETL_START, ... , ETL_END\/ETL_ERROR *\/\n  STEP_DESCR        TEXT,                        \/* Beschreibung der Aufgabe oder Fehlermeldung *\/\n  UNIQUE (SID, JOB_NAME, STEP_NAME)\n);\nINSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);<\/code><\/pre>\n
\nDDL der Sicht\/Berichts<\/b><\/p>\n
CREATE VIEW IF NOT EXISTS UTL_JOB_STATUS_V\nAS \n\/* Inhalt: Protokoll der Paketausf\u00fchrungen der letzten 3 Monate. *\/\nWITH SRC AS (\n  SELECT LOG_D,\n    LOG_DT,\n    UTL_JOB_STATUS_ID,\n    SID,\n\tCASE WHEN INSTR(JOB_NAME, 'FTP') THEN '\u00dcBERTRAGUNG' \/* Datei\u00fcbertragung *\/\n\t     WHEN INSTR(JOB_NAME, 'STG') THEN 'STUFEN' \/* Stufe *\/\n\t     WHEN INSTR(JOB_NAME, 'CLS') THEN 'REINIGUNG' \/* Reinigung *\/\n\t     WHEN INSTR(JOB_NAME, 'DIM') THEN 'DIMENSION' \/* Dimension *\/\n\t     WHEN INSTR(JOB_NAME, 'FCT') THEN 'FAKT' \/* Fakt *\/\n\t\t WHEN INSTR(JOB_NAME, 'ETL') THEN 'STUFE-MART' \/* Datenmarkt *\/\n\t     WHEN INSTR(JOB_NAME, 'RPT') THEN 'BERICHT' \/* Bericht *\/\n\t     ELSE 'N\/A' END AS LAYER,\n\tCASE WHEN INSTR(JOB_NAME, 'ACCESS') THEN 'ZUGRIFFSPROTOKOLL' \/* Quelle *\/\n\t     WHEN INSTR(JOB_NAME, 'MASTER') THEN 'STAMMDATEN' \/* Quelle *\/\n\t     WHEN INSTR(JOB_NAME, 'AD-HOC') THEN 'AD-HOC' \/* Quelle *\/\n\t     ELSE 'N\/A' END AS SOURCE,\n    JOB_NAME,\n    STEP_NAME,\n    CASE WHEN STEP_NAME='ETL_START' THEN 1 ELSE 0 END AS START_FLAG,\n    CASE WHEN STEP_NAME='ETL_END' THEN 1 ELSE 0 END AS END_FLAG,\n    CASE WHEN STEP_NAME='ETL_ERROR' THEN 1 ELSE 0 END AS ERROR_FLAG,\n    STEP_NAME || ' : ' || STEP_DESCR AS STEP_LOG,\n\tSUBSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), 1, INSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), '***')-2) AS AFFECTED_ROWS\n  FROM UTL_JOB_STATUS\n  WHERE datetime(LOG_D, 'unixepoch') >= date('now', 'start of month', '-3 month')\n)\nSELECT JB.SID,\n  JB.MIN_LOG_DT AS START_DT,\n  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS LOG_DT,\n  JB.SOURCE,\n  JB.LAYER,\n  JB.JOB_NAME,\n  CASE\n  WHEN JB.ERROR_FLAG = 1 THEN 'FEHLER'\n  WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 AND strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 THEN 'H\u00c4NGT' \/* eine halbe Stunde *\/\n  WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 THEN 'L\u00c4UFT'\n  ELSE 'OK'\n  END AS STATUS,\n  ERR.STEP_LOG     AS STEP_LOG,\n  JB.CNT           AS STEP_CNT,\n  JB.AFFECTED_ROWS AS AFFECTED_ROWS,\n  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS JOB_START_DT,\n  strftime('%d.%m.%Y %H:%M', datetime(JB.MAX_LOG_DT, 'unixepoch')) AS JOB_END_DT,\n  JB.MAX_LOG_DT - JB.MIN_LOG_DT AS JOB_DURATION_SEC\nFROM\n  ( SELECT SID, SOURCE, LAYER, JOB_NAME,\n           MAX(UTL_JOB_STATUS_ID) AS UTL_JOB_STATUS_ID,\n           MAX(START_FLAG)       AS START_FLAG,\n           MAX(END_FLAG)         AS END_FLAG,\n           MAX(ERROR_FLAG)       AS ERROR_FLAG,\n           MIN(LOG_DT)           AS MIN_LOG_DT,\n           MAX(LOG_DT)           AS MAX_LOG_DT,\n           SUM(1)                AS CNT,\n           SUM(IFNULL(AFFECTED_ROWS, 0)) AS AFFECTED_ROWS\n    FROM SRC\n    GROUP BY SID, SOURCE, LAYER, JOB_NAME\n  ) JB,\n  ( SELECT UTL_JOB_STATUS_ID, SID, JOB_NAME, STEP_LOG\n    FROM SRC\n    WHERE 1 = 1\n  ) ERR\nWHERE 1 = 1\n  AND JB.SID = ERR.SID\n  AND JB.JOB_NAME = ERR.JOB_NAME\n  AND JB.UTL_JOB_STATUS_ID = ERR.UTL_JOB_STATUS_ID\nORDER BY JB.MIN_LOG_DT DESC, JB.SID DESC, JB.SOURCE;<\/code><\/pre>\n
\nSQL \u00dcberpr\u00fcfung der M\u00f6glichkeit, eine neue Sitzungsnummer zu erhalten<\/b><\/p>\n
SELECT SUM (\n  CASE WHEN start_job.JOB_NAME IS NOT NULL AND end_job.JOB_NAME IS NULL \/* existierender Job abgeschlossen *\/\n\t    AND NOT ( 'y' = 'n' ) \/* Zwangsneustart PARAMETER *\/\n       THEN 1 ELSE 0\n  END ) AS IS_RUNNING\n  FROM\n    ( SELECT 1 AS dummy FROM UTL_JOB_STATUS WHERE sid = -1) d_job\n  LEFT OUTER JOIN\n    ( SELECT JOB_NAME, SID, 1 AS dummy\n      FROM UTL_JOB_STATUS\n      WHERE JOB_NAME = 'RPT_ACCESS_LOG' \/* Jobname PARAMETER *\/\n\t    AND STEP_NAME = 'ETL_START'\n      GROUP BY JOB_NAME, SID\n    ) start_job \/* beginnt *\/\n  ON d_job.dummy = start_job.dummy\n  LEFT OUTER JOIN\n    ( SELECT JOB_NAME, SID\n      FROM UTL_JOB_STATUS\n      WHERE JOB_NAME = 'RPT_ACCESS_LOG'  \/* Jobname PARAMETER *\/\n\t    AND STEP_NAME in ('ETL_END', 'ETL_ERROR') \/* Stoppstatus *\/\n      GROUP BY JOB_NAME, SID\n    ) end_job \/* endet *\/\n  ON start_job.JOB_NAME = end_job.JOB_NAME\n     AND start_job.SID = end_job.SID<\/code><\/pre>\n
\nBesonderheiten der Tabelle:<\/p>\n