ProHoster > Blog > Administratioun > Iwwerwaachung vun ETL Prozesser an engem klengen Datelager

Iwwerwaachung vun ETL Prozesser an engem klengen Datelager

Vill benotze spezialiséiert Tools fir Prozeduren ze kreéieren fir Daten an relational Datenbanken ze extrahieren, ze transforméieren an ze lueden. De Prozess vun Aarbechtsinstrumenter gëtt protokolléiert, Feeler ginn fixéiert.

Am Fall vun engem Feeler enthält de Logbuch Informatiounen datt d'Tool net fäerdeg war d'Aufgab ze kompletéieren a wéi eng Moduler (dacks Java) gestoppt hunn wou. An de leschten Zeilen, kënnt Dir eng Datebank Feeler fannen, Zum Beispill, engem Dësch eenzegaarteg Schlëssel Violatioun.

Fir d'Fro ze beäntweren, wéi eng Roll ETL Feelerinformatioun spillt, hunn ech all d'Problemer, déi an de leschten zwee Joer opgetrueden sinn, an engem zimlech grousse Repository klasséiert.

Iwwerwaachung vun ETL Prozesser an engem klengen Datelager

Datebank Feeler enthalen net genuch Plaz, verluer Verbindung, Sessioun hänkt, etc.

Logesch Feeler enthalen wéi Verstouss géint Tabelleschlësselen, net valabel Objeten, Mangel un Zougang zu Objeten, asw.
De Scheduler kann net op Zäit ufänken, et kann afréieren, etc.

Einfach Feeler huelen net laang fir ze fixéieren. Eng gutt ETL kann déi meescht vun hinnen eleng handhaben.

Komplex Bugs maachen et néideg Prozeduren ze entdecken an ze testen fir mat Daten ze schaffen, Datenquellen z'entdecken. Féieren dacks zu der Bedierfnes fir Ännerungstesten an Deployment.

Also, d'Halschent vun alle Probleemer si mat der Datebank verbonnen. 48% vun alle Feeler sinn einfach Feeler.
En Drëttel vun alle Probleemer sinn am Zesummenhang mat der Ännerung vun der Späicherlogik oder Modell, méi wéi d'Halschent vun dëse Feeler si komplex.

A manner wéi e Véierel vun alle Probleemer si mam Taskplaner am Zesummenhang, 18% vun deenen einfache Feeler sinn.

Am Allgemengen sinn 22% vun alle Feeler, déi optrieden, komplex, an hir Korrektur erfuerdert déi meescht Opmierksamkeet an Zäit. Si geschéien ongeféier eemol d'Woch. Woubäi einfach Feeler bal all Dag geschéien.

Et ass offensichtlech datt d'Iwwerwaachung vun ETL Prozesser effektiv wäert sinn wann d'Location vum Feeler am Logbuch sou genau wéi méiglech uginn ass an d'Mindestzäit erfuerderlech ass fir d'Quell vum Problem ze fannen.

Effektiv Iwwerwaachung

Wat wollt ech am ETL Iwwerwaachungsprozess gesinn?

Iwwerwaachung vun ETL Prozesser an engem klengen Datelager
Start bei - wann hien ugefaang ze schaffen,
Quell - Daten Quell,
Layer - wéi en Niveau vun der Späichere gëtt gelueden,
ETL Job Numm - Upload Prozedur, déi aus ville klenge Schrëtt besteet,
Schrëtt Nummer - d'Zuel vun de Schrëtt, déi duerchgefouert gëtt,
Affektéiert Reihen - wéi vill Donnéeën scho veraarbecht goufen,
Dauer sec - wéi laang et dauert,
Status - ob alles gutt ass oder net: OK, ERROR, RUNNING, HANGS
Message - Last erfollegräich Message oder Feeler Beschreiwung.

Baséierend op de Status vun de records, kënnt Dir eng E-Mail schécken. Bréif un aner Memberen. Wann et keng Feeler ass, ass de Bréif net néideg.

Also, am Fall vun engem Feeler, ass d'Plaz vum Tëschefall kloer uginn.

Heiansdo geschitt et datt d'Iwwerwaachungsinstrument selwer net funktionnéiert. An dësem Fall ass et méiglech eng Vue (Vue) direkt an der Datebank ze ruffen, op Basis vun deem de Bericht gebaut ass.

ETL Iwwerwachung Dësch

Fir Iwwerwaachung vun ETL Prozesser ëmzesetzen, sinn een Dësch an eng Vue genuch.

Fir dëst ze maachen, kënnt Dir zréck op Är kleng Stockage a erstellt Prototyp an der sqlite Datebank.

DDL Dëscher

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

Kuckt / Rapport 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 Iwwerpréift ob et méiglech ass eng nei Sessiounsnummer ze kréien

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

Dësch Fonctiounen:

  • den Ufank an Enn vun der Dateveraarbechtungsprozedur musse vun de Schrëtt ETL_START an ETL_END gefollegt ginn
  • am Fall vun engem Feeler, soll de Schrëtt ETL_ERROR mat senger Beschreiwung geschaf ginn
  • d'Quantitéit u veraarbechten Donnéeën soll zum Beispill mat Asterisken markéiert ginn
  • déi selwecht Prozedur kann gläichzäiteg mat dem force_restart=y Parameter gestart ginn, ouni et gëtt d'Sessiounsnummer nëmmen un déi ofgeschloss Prozedur erausginn
  • am normalen Modus, Dir kënnt net déi selwecht Dateveraarbechtungsprozedur parallel lafen

Déi néideg Operatiounen fir mat engem Dësch ze schaffen sinn wéi follegt:

  • d'Sessiounsnummer vun der lafender ETL Prozedur ze kréien
  • setzen d'Logbuch Entrée an den Dësch
  • de leschten erfollegräiche Rekord vun enger ETL Prozedur ze kréien

An Datenbanken wéi Oracle oder Postgres kënnen dës Operatiounen als agebaute Funktiounen ëmgesat ginn. sqlite erfuerdert en externe Mechanismus, an an dësem Fall ass et Prototyp an PHP.

Konklusioun

Also, Fehlermeldungen an Datenveraarbechtungsinstrumenter spillen eng mega-wichteg Roll. Awer et ass schwéier se optimal ze nennen fir séier d'Ursaach vum Problem ze fannen. Wann d'Zuel vun de Prozeduren honnert ass, da gëtt d'Prozess Iwwerwachung an e komplexe Projet.

Den Artikel gëtt e Beispill vun enger méiglecher Léisung fir de Problem a Form vun engem Prototyp. De ganze klenge Repository Prototyp ass verfügbar a gitlab SQLite PHP ETL Utilities.

Source: will.com

Setzt e Commentaire