🥇Monitoring van ETL-processen in een kleine gegevensopslag

Veel mensen gebruiken gespecialiseerde tools voor het creëren van extractie-, transformatie- en laadprocedures in relationele databases. Het werkproces van de tools wordt gelogd, fouten worden vastgelegd.

In geval van een fout bevat het log informatie over wat de tool niet kon uitvoeren en welke modules (vaak java) waar zijn stopgezet. In de laatste regels kan een databasefout worden gevonden, bijvoorbeeld een schending van de unieke sleutel van de tabel.

Om de rol van foutinformatie in ETL te beantwoorden, heb ik alle problemen die de afgelopen twee jaar zijn voorgekomen in een grote opslag geclassificeerd.

Databasefouten omvatten zaken zoals gebrek aan ruimte, verbroken verbinding, vastgelopen sessies, enz.

Logische fouten omvatten zaken zoals schending van tabelsleutels, ongeldige objecten, gebrek aan toegang tot objecten, enz.
De planner kan op een ongelegen moment worden gestart, kan vastlopen, enz.

Eenvoudige fouten vergen niet veel tijd om op te lossen. Met de meeste daarvan kan een goede ETL zelfstandig omgaan.

Complexe fouten vereisen het openen en controleren van gegevensprocedures, het onderzoeken van gegevensbronnen. Dit leidt vaak tot de noodzaak om veranderingen te testen en uit te rollen.

Dus de helft van alle problemen heeft te maken met de database. 48% van alle fouten zijn eenvoudige fouten.
Een derde van alle problemen heeft te maken met het wijzigen van de logica of het model van de opslag, meer dan de helft van deze fouten zijn complex.

En minder dan een kwart van alle problemen heeft te maken met de taakplanner, waarvan 18% eenvoudige fouten zijn.

Over het algemeen is 22% van alle opgetreden fouten complex, hun oplossing vergt de meeste aandacht en tijd. Deze komen ongeveer wekelijks voor. Terwijl eenvoudige fouten bijna elke dag voorkomen.

Het is duidelijk dat monitoring van ETL-processen effectief zal zijn wanneer in het logboek zo nauwkeurig mogelijk het foutlocatie is aangegeven en er minimale tijd nodig is om de bron van het probleem te vinden.

Effectieve monitoring

Wat wilde ik zien in het monitoringproces van ETL?

Start at — wanneer de werking begon,
Source — gegevensbron,
Layer — welk niveau van opslag wordt geladen,
ETL Job Name — de laadprocedure die uit veel kleine stappen bestaat,
Step Number — nummer van de uitgevoerde stap,
Benadeelde Rij — hoeveel gegevens zijn al verwerkt,
Duur sec — hoe lang het duurt om uit te voeren,
Status — is alles goed of niet: OK, ERROR, RUNNING, HANGS
Bericht — het laatste succesvolle bericht of een foutbeschrijving.

Op basis van de status van de records kan een e-mail naar andere deelnemers worden verzonden. Als er geen fouten zijn, is de e-mail ook niet verplicht.

Dus in het geval van een fout is de locatie van het voorval duidelijk aangegeven.

Soms komt het voor dat de monitoringtool zelf niet werkt. In dat geval is het mogelijk om direct in de database de weergave (view) aan te roepen, waarop het rapport is gebaseerd.

Monitoring tabel ETL

Om ETL-processen te monitoren, is slechts één tabel en één weergave nodig.

Daarvoor kun je teruggaan naar jouw kleine opslagplaats en een prototype in de sqlite-database maken.

DDL tabel

CREATE TABLE UTL_JOB_STATUS (

/* Tabel voor logging van jobuitvoeringslog. Belangrijk dat de job de stappen ETL_START en ETL_END of ETL_ERROR heeft */
  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  SID               INTEGER NOT NULL DEFAULT -1, /* Sessienaam. Uniek voor elke uitvoering van de job */
  LOG_DT            INTEGER NOT NULL DEFAULT 0,  /* Datum tijd */
  LOG_D             INTEGER NOT NULL DEFAULT 0,  /* Datum */
  JOB_NAME          TEXT NOT NULL DEFAULT 'N/A', /* Jobnaam zoals JOB_STG2DM_GEO */
  STEP_NAME         TEXT NOT NULL DEFAULT 'N/A', /* ETL_START, ... , ETL_END/ETL_ERROR */
  STEP_DESCR        TEXT,                        /* Beschrijving van de taak of foutmelding */
  UNIQUE (SID, JOB_NAME, STEP_NAME)
);
INSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);

DDL weergave/rapport

MAAK VIEW ALS DEZE NOG NIET BESTAAT UTL_JOB_STATUS_V
ALS 
/* Inhoud: Uitvoeringslogboek van pakketten voor de laatste 3 maanden. */
MET SRC AS (
  SELECT LOG_D,
    LOG_DT,
    UTL_JOB_STATUS_ID,
    SID,
	CASE WHEN INSTR(JOB_NAME, 'FTP') THEN 'OVERDRACHT' /* bestandsoverdracht */
	     WHEN INSTR(JOB_NAME, 'STG') THEN 'STADIA' /* stadium */
	     WHEN INSTR(JOB_NAME, 'CLS') THEN 'SCHOONMAKEN' /* schoonmaak */
	     WHEN INSTR(JOB_NAME, 'DIM') THEN 'DIMENSIE' /* dimensie */
	     WHEN INSTR(JOB_NAME, 'FCT') THEN 'FEIT' /* feit */
		 WHEN INSTR(JOB_NAME, 'ETL') THEN 'STAGE-MART' /* gegevensmart */
	     WHEN INSTR(JOB_NAME, 'RPT') THEN 'VERSLAG' /* verslag */
	     ELSE 'N/A' END AS LAYER,
	CASE WHEN INSTR(JOB_NAME, 'ACCESS') THEN 'TOEGANGSLOG' /* bron */
	     WHEN INSTR(JOB_NAME, 'MASTER') THEN 'MASTERGEGEVENS' /* bron */
	     WHEN INSTR(JOB_NAME, 'AD-HOC') THEN 'AD-HOC' /* bron */
	     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 'FOUT'
  WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 AND strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 THEN 'HANGT' /* een half uur */
  WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 THEN 'LOOPT'
  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 Controle op de mogelijkheid om een nieuw sessienummer te verkrijgen

SELECT SUM (
  CASE WHEN start_job.JOB_NAME IS NOT NULL AND end_job.JOB_NAME IS NULL /* bestaand job is beëindigd */
	    AND NOT ( 'y' = 'n' ) /* geforceerde herstart 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' /* jobnaam PARAMETER */
	    AND STEP_NAME = 'ETL_START'
      GROUP BY JOB_NAME, SID
    ) start_job /* begint */
  ON d_job.dummy = start_job.dummy
  LEFT OUTER JOIN
    ( SELECT JOB_NAME, SID
      FROM UTL_JOB_STATUS
      WHERE JOB_NAME = 'RPT_ACCESS_LOG'  /* jobnaam PARAMETER */
	    AND STEP_NAME in ('ETL_END', 'ETL_ERROR') /* stopstatus */
      GROUP BY JOB_NAME, SID
    ) end_job /* eindigt */
  ON start_job.JOB_NAME = end_job.JOB_NAME
     AND start_job.SID = end_job.SID

Kenmerken van de tabel:

Het begin en het einde van de gegevensverwerkingsprocedure moeten worden begeleid door de stappen ETL_START en ETL_END
In geval van een fout moet de stap ETL_ERROR met de beschrijving ervan worden aangemaakt
Het aantal verwerkte gegevens moet worden gemarkeerd, bijvoorbeeld met sterretjes
Tegelijkertijd kan dezelfde procedure worden gestart met de parameter force_restart=y; zonder deze wordt het sessienummer alleen gegeven voor de voltooide procedure
In de normale modus kan dezelfde gegevensverwerkingsprocedure niet parallel worden uitgevoerd

De noodzakelijke acties die met de tabel moeten worden uitgevoerd, zijn de volgende:

verhoog het sessienummer van de gestartte ETL-procedure
voeg een logrecord toe aan de tabel
haal de laatste succesvolle record van de ETL-procedure op

In databases zoals Oracle of Postgres kunnen deze acties worden geïmplementeerd met ingebouwde functies. Voor sqlite is een externe mechanisme vereist en in dit geval is het geprototyped in PHP.

Uitslag

Op die manier spelen foutmeldingen in gegevensverwerkingsinstrumenten een mega-belangrijke rol. Maar ze zijn niet optimaal voor het snel vinden van de oorzaak van het probleem. Wanneer het aantal procedures bijna honderd nadert, wordt het monitoren van processen een complex project.

In het artikel wordt een voorbeeld gegeven van een mogelijke oplossing voor het probleem in de vorm van een prototype. Het hele prototype van een kleine opslag is beschikbaar op gitlab SQLite PHP ETL Utilities.

Bron: habr.com

Monitoring van ETL-processen in een kleine databasemagazijn

Effectieve monitoring

Monitoring tabel ETL

Uitslag