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.
Im Falle eines Fehlers enthält das Protokoll Informationen darüber, dass es dem Werkzeug nicht gelungen ist, die Aufgabe auszuführen, und welche Module (häufig java) wo gestoppt sind. In den letzten Zeilen kann man den Datenbankfehler finden, zum Beispiel einen Verstoß gegen den einzigartigen Schlüssel der Tabelle.
Um die Rolle der Informationen über ETL-Fehler zu beantworten, habe ich alle Probleme klassifiziert, die in den letzten zwei Jahren in einem nicht kleinen Data Warehouse aufgetreten sind.
Datenbankfehler umfassen Dinge wie unzureichenden Speicherplatz, unterbrochene Verbindungen, hängende Sitzungen usw.
Logische Fehler umfassen Dinge wie Verstöße gegen Tabellenschlüssel, ungültige Objekte, fehlender Zugriff auf Objekte usw.
Der Scheduler kann zu einem falschen Zeitpunkt gestartet werden, hängen bleiben usw.
Einfache Fehler erfordern nicht viel Zeit zur Behebung. Die meisten davon kann ein gutes ETL-System selbst bewältigen.
Komplexe Fehler führen dazu, dass man die Datenverarbeitungsverfahren öffnen und überprüfen, die Datenquellen untersuchen muss. Dies führt oft zu Teständerungen und Deployment.
Somit hängen die Hälfte aller Probleme mit der Datenbank zusammen. 48 % aller Fehler sind einfache Fehler.
Ein Drittel aller Probleme hängt mit Änderungen in der Logik oder dem Modell des Data Warehouses zusammen, mehr als die Hälfte dieser Fehler sind komplexe Fehler.
Und weniger als ein Viertel aller Probleme hängt mit dem Aufgabenplaner zusammen, 18 % davon sind einfache Fehler.
Insgesamt sind 22 % aller aufgetretenen Fehler komplex, ihre Behebung erfordert die größte Aufmerksamkeit und Zeit. Sie treten ungefähr einmal pro Woche auf. Während einfache Fehler fast jeden Tag passieren.
Offensichtlich ist die Überwachung von ETL-Prozessen dann effektiv, wenn im Protokoll der Fehlerort so genau wie möglich angegeben ist und minimaler Aufwand erforderlich ist, um die Ursache des Problems zu finden.
Effektive Überwachung
Was wollte ich im Prozess der ETL-Überwachung sehen?
Startzeit - wann die Arbeit begann,
Quelle - Datenquelle,
Schicht - welche Ebene des Data Warehouses geladen wird,
ETL Job Name — ein Ladeverfahren, das aus vielen kleinen Schritten besteht,
Step Number — die Nummer des ausgeführten Schrittes,
Affected Rows — wie viele Daten bereits verarbeitet wurden,
Duration sec — wie lange es dauert,
Status — ob alles gut ist oder nicht: OK, ERROR, RUNNING, HANGS
Message — die letzte erfolgreiche Nachricht oder eine Fehlermeldung.
Anhand des Status der Einträge kann eine E-Mail an andere Teilnehmer gesendet werden. Wenn es keine Fehler gibt, ist die E-Mail nicht unbedingt erforderlich.
Somit wird im Fehlerfall genau der Ort des Vorfalls angegeben.
Manchmal kommt es vor, dass das Überwachungstool selbst nicht funktioniert. In diesem Fall besteht die Möglichkeit, direkt in der Datenbank die Sicht (View) aufzurufen, auf deren Grundlage der Bericht erstellt wurde.
Überwachungstabelle ETL
Um die Überwachung der ETL-Prozesse zu realisieren, genügt eine Tabelle und eine Sicht.
Dafür kann man zu und ein Prototyp in der SQLite-Datenbank erstellen.
DDL der Tabelle
CREATE TABLE UTL_JOB_STATUS (
/* Tabelle zum Protokollieren der Ausführung des Jobs. Wichtig, dass der Job die Schritte ETL_START und ETL_END oder ETL_ERROR hat */
UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
SID INTEGER NOT NULL DEFAULT -1, /* Sitzungsidentifikator. Einzigartig für jeden Lauf des Jobs */
LOG_DT INTEGER NOT NULL DEFAULT 0, /* Datum und Uhrzeit */
LOG_D INTEGER NOT NULL DEFAULT 0, /* Datum */
JOB_NAME TEXT NOT NULL DEFAULT 'N/A', /* Jobname wie JOB_STG2DM_GEO */
STEP_NAME TEXT NOT NULL DEFAULT 'N/A', /* ETL_START, ... , ETL_END/ETL_ERROR */
STEP_DESCR TEXT, /* Beschreibung der Aufgabe oder Fehlermeldung */
UNIQUE (SID, JOB_NAME, STEP_NAME)
);
INSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);DDL der Sicht/Berichts
CREATE VIEW IF NOT EXISTS UTL_JOB_STATUS_V
AS
/* Inhalt: Protokoll der Paketausführungen der letzten 3 Monate. */
WITH SRC AS (
SELECT LOG_D,
LOG_DT,
UTL_JOB_STATUS_ID,
SID,
CASE WHEN INSTR(JOB_NAME, 'FTP') THEN 'ÜBERTRAGUNG' /* Dateiübertragung */
WHEN INSTR(JOB_NAME, 'STG') THEN 'STUFEN' /* Stufe */
WHEN INSTR(JOB_NAME, 'CLS') THEN 'REINIGUNG' /* Reinigung */
WHEN INSTR(JOB_NAME, 'DIM') THEN 'DIMENSION' /* Dimension */
WHEN INSTR(JOB_NAME, 'FCT') THEN 'FAKT' /* Fakt */
WHEN INSTR(JOB_NAME, 'ETL') THEN 'STUFE-MART' /* Datenmarkt */
WHEN INSTR(JOB_NAME, 'RPT') THEN 'BERICHT' /* Bericht */
ELSE 'N/A' END AS LAYER,
CASE WHEN INSTR(JOB_NAME, 'ACCESS') THEN 'ZUGRIFFSPROTOKOLL' /* Quelle */
WHEN INSTR(JOB_NAME, 'MASTER') THEN 'STAMMDATEN' /* Quelle */
WHEN INSTR(JOB_NAME, 'AD-HOC') THEN 'AD-HOC' /* Quelle */
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 'FEHLER'
WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 AND strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 THEN 'HÄNGT' /* eine halbe Stunde */
WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 THEN 'LÄUFT'
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 Überprüfung der Möglichkeit, eine neue Sitzungsnummer zu erhalten
SELECT SUM (
CASE WHEN start_job.JOB_NAME IS NOT NULL AND end_job.JOB_NAME IS NULL /* existierender Job abgeschlossen */
AND NOT ( 'y' = 'n' ) /* Zwangsneustart 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' /* Jobname PARAMETER */
AND STEP_NAME = 'ETL_START'
GROUP BY JOB_NAME, SID
) start_job /* beginnt */
ON d_job.dummy = start_job.dummy
LEFT OUTER JOIN
( SELECT JOB_NAME, SID
FROM UTL_JOB_STATUS
WHERE JOB_NAME = 'RPT_ACCESS_LOG' /* Jobname PARAMETER */
AND STEP_NAME in ('ETL_END', 'ETL_ERROR') /* Stoppstatus */
GROUP BY JOB_NAME, SID
) end_job /* endet */
ON start_job.JOB_NAME = end_job.JOB_NAME
AND start_job.SID = end_job.SIDBesonderheiten der Tabelle:
- Der Beginn und das Ende des Datenverarbeitungsprozesses müssen von den Schritten ETL_START und ETL_END begleitet werden.
- Im Falle eines Fehlers muss ein Schritt ETL_ERROR mit dessen Beschreibung erstellt werden.
- Die Anzahl der verarbeiteten Daten sollte hervorgehoben werden, zum Beispiel mit Sternchen.
- Gleichzeitig kann derselbe Prozess mit dem Parameter force_restart=y gestartet werden, ohne den wird die Sitzungsnummer nur für abgeschlossene Prozesse ausgegeben.
- Im regulären Modus kann derselbe Datenverarbeitungsprozess nicht parallel gestartet werden.
Die notwendigen Operationen beim Arbeiten mit der Tabelle sind folgende:
- Abruf der Sitzungsnummer des gestarteten ETL-Prozesses
- Einfügen eines Logeintrags in die Tabelle
- Abruf des letzten erfolgreichen Eintrags des ETL-Prozesses
In Datenbanken wie Oracle oder Postgres können diese Operationen mit eingebauten Funktionen realisiert werden. Für SQLite ist ein externes Mechanismus erforderlich, der in diesem Fall .
Ausgabe
Somit spielen Fehlermeldungen in Datenverarbeitungstools eine überaus wichtige Rolle. Doch für eine schnelle Ursachenfindung sind sie schwer als optimal zu bezeichnen. Wenn die Anzahl der Prozesse nahe der Hundert liegt, wird das Monitoring zu einem komplexen Projekt.
Im Artikel wird ein Beispiel für eine mögliche Lösung des Problems in Form eines Prototyps vorgestellt. Der gesamte Prototyp eines kleinen Speichers ist in GitLab verfügbar. .
Quelle: habr.com
