Geekly articles weekly

Überwachung von ETL-Prozessen in einem kleinen Data Warehouse

Viele verwenden spezielle Tools, um Verfahren zum Extrahieren, Transformieren und Laden von Daten in relationale Datenbanken zu erstellen. Der Prozess der Arbeitstools wird protokolliert, Fehler werden aufgezeichnet.

Im Fehlerfall enthält das Protokoll Informationen darüber, dass das Tool die Aufgabe nicht abgeschlossen hat und welche Module (häufig Java) dort angehalten wurden. In den letzten Zeilen finden Sie einen Datenbankfehler, beispielsweise eine Verletzung eines eindeutigen Tabellenschlüssels.

Um die Frage zu beantworten, welche Rolle ETL-Fehlerinformationen spielen, habe ich alle Probleme klassifiziert, die in den letzten zwei Jahren im relativ großen Repository aufgetreten sind.

Bild

Merkmale des Lagers, in dem die Klassifizierung durchgeführt wurde:

  • 20 Datenquellen verbunden
  • Täglich werden 10,5 Milliarden Zeilen verarbeitet
  • die bis zu 50 Millionen Zeilen aggregiert sind,
  • Daten verarbeiten 140 Pakete in 700 Schritten (ein Schritt ist eine SQL-Anforderung)
  • Server - 4-Knoten-X5-Datenbank

Zu den Datenbankfehlern gehören Speicherplatzmangel, eine getrennte Verbindung, ein Hängen der Sitzung usw.

Zu den logischen Fehlern gehören Verstöße gegen Tabellenschlüssel, ungültige Objekte, mangelnder Zugriff auf Objekte usw.
Der Scheduler startet möglicherweise nicht zur richtigen Zeit, er hängt möglicherweise usw.

Einfache Fehler erfordern nicht viel Zeit, um sie zu beheben. Mit den meisten von ihnen kann eine gute ETL alleine fertig werden.

Komplizierte Fehler machen es erforderlich, Verfahren für die Arbeit mit Daten zu öffnen und zu überprüfen, um Datenquellen zu recherchieren. Dies führt häufig dazu, dass Änderungen und die Bereitstellung getestet werden müssen.

Die Hälfte aller Probleme hängt also mit der Datenbank zusammen. 48% aller Fehler sind einfache Fehler.
Der dritte Teil aller Probleme hängt mit einer Änderung der Logik oder des Modells des Repositorys zusammen. Mehr als die Hälfte dieser Fehler ist komplex.

Und weniger als ein Viertel aller Probleme hängt mit dem Taskplaner zusammen, von denen 18% einfache Fehler sind.

Im Allgemeinen sind 22% aller aufgetretenen Fehler komplex, und ihre Behebung erfordert die meiste 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 der Ort des Fehlers im Protokoll so genau wie möglich angegeben wird und eine minimale Zeit erforderlich ist, um die Ursache des Problems zu finden.

Effektive Überwachung


Was wollte ich bei der Überwachung von ETL sehen?

Bild
Beginnen Sie um - wenn gestartet
Quelle - eine Datenquelle
Ebene - Welche Speicherebene wird geladen?
ETL Job Name ist ein Ladevorgang, der aus vielen kleinen Schritten besteht.
Schrittnummer - die Nummer des auszuführenden Schritts,
Betroffene Zeilen - wie viele Daten wurden bereits verarbeitet?
Dauer sek - wie lange die Ausführung dauert,
Status - ob alles gut ist oder nicht: OK, FEHLER, LAUFEN, HÄNGT
Nachricht - Die letzte erfolgreiche Nachricht oder Fehlerbeschreibung.

Basierend auf dem Status der Einträge können Sie eine E-Mail senden. Brief an andere Teilnehmer. Wenn keine Fehler vorliegen, ist der Brief nicht erforderlich.

Somit wird im Fehlerfall der Ort des Vorfalls klar angegeben.

Manchmal funktioniert das Überwachungstool selbst nicht. In diesem Fall ist es möglich, eine Ansicht (Ansicht) in der Datenbank direkt aufzurufen, auf deren Grundlage der Bericht erstellt wird.

ETL-Überwachungstabelle


Um die Überwachung von ETL-Prozessen zu implementieren, reichen eine Tabelle und eine Ansicht aus.

Dazu können Sie zu Ihrem kleinen Repository zurückkehren und einen Prototyp in der SQLite-Datenbank erstellen.

DDL-Tabellen
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);

DDL-Einreichung / Bericht
 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 Überprüfen der Fähigkeit, eine neue Sitzungsnummer abzurufen
 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.SID

Merkmale der Tabelle:

  • Auf den Beginn und das Ende des Datenverarbeitungsvorgangs müssen die Schritte ETL_START und ETL_END folgen
  • Im Fehlerfall muss der Schritt ETL_ERROR mit seiner Beschreibung erstellt werden
  • Die Menge der verarbeiteten Daten sollte beispielsweise mit Sternchen hervorgehoben werden
  • Gleichzeitig kann dieselbe Prozedur mit dem Parameter force_restart = y gestartet werden. Ohne diesen Parameter wird die Sitzungsnummer nur für die abgeschlossene Prozedur ausgegeben
  • Im normalen Modus können Sie nicht denselben Datenverarbeitungsvorgang parallel ausführen

Die notwendigen Operationen zum Arbeiten mit der Tabelle sind wie folgt:

  • Abrufen der Sitzungsnummer der ETL-Prozedur zum Starten
  • Fügen Sie einen Protokolleintrag in eine Tabelle ein
  • Abrufen des letzten erfolgreichen ETL-Prozedurdatensatzes

In Datenbanken wie Oracle oder Postgres können diese Vorgänge mit integrierten Funktionen implementiert werden. Sqlite benötigt einen externen Mechanismus und ist in diesem Fall ein Prototyp in PHP .

Fazit


Daher spielen Fehlermeldungen in Datenverarbeitungswerkzeugen eine überaus wichtige Rolle. Es ist jedoch schwierig, sie als optimal für eine schnelle Suche nach den Ursachen des Problems zu bezeichnen. Wenn sich die Anzahl der Verfahren einhundert nähert, wird die Überwachung von Prozessen zu einem komplexen Projekt.

Der Artikel bietet ein Beispiel für eine mögliche Lösung des Problems in Form eines Prototyps. Der gesamte Prototyp des kleinen Repositorys ist in den gitlab SQLite PHP ETL-Dienstprogrammen verfügbar.

Source: https://habr.com/ru/post/de465637/

More articles:


All Articles