ProHoster > Blog > Administration > Overvågning af ETL-processer i et lille datalager

Overvågning af ETL-processer i et lille datalager

Mange bruger specialiserede værktøjer til at oprette procedurer til udtrækning, transformation og indlæsning af data i relationelle databaser. Arbejdsprocessen for værktøjer logges, og fejl registreres.

I tilfælde af en fejl indeholder loggen oplysninger om, at værktøjet ikke kunne udføre opgaven, og hvilke moduler (ofte java) der stoppede. I de sidste linjer kan man finde databasefejlen, eksempelvis overtrædelse af en unik nøgle i tabellen.

For at besvare spørgsmålet om, hvilken rolle information om ETL-fejl spiller, har jeg klassificeret alle problemer, der er opstået i løbet af de sidste to år i et stort datalager.

Overvågning af ETL-processer i et lille datalager

Databasefejl omfatter sådanne som mangel på plads, afbrudt forbindelse, fastlåsning af sessioner osv.

Logiske fejl omfatter sådanne som overtrædelse af tabellens nøgler, ugyldige objekter, manglende adgang til objekter osv.
Planlæggeren kan blive kørt på et forkert tidspunkt, kan fryse osv.

Enkle fejl kræver ikke meget tid til at rette. De fleste af dem kan en god ETL håndtere selv.

Komplekse fejl medfører behov for at åbne og kontrollere procedurerne for databehandling, undersøge datakilder. Ofte fører de til behovet for at teste ændringer og implementering.

Således er halvdelen af alle problemer relateret til databasen. 48% af alle fejl er enkle fejl.
En tredjedel af alle problemer er relateret til ændringer af logik eller modellens lager, mere end halvdelen af disse fejl er komplekse.

Og mindre end en fjerdedel af alle problemer er relateret til opgaveplanlæggeren, 18% af dem er enkle fejl.

Generelt er 22% af alle opståede fejl komplekse, deres rettelse kræver mest opmærksomhed og tid. De opstår cirka en gang om ugen, mens enkle fejl sker næsten dagligt.

Det er tydeligt, at overvågning af ETL-processer vil være effektiv, når loggen præcist angiver fejlstedet og kræver minimal tid til at finde problemmets kilde.

Effektiv overvågning

Hvad ville jeg gerne se i overvågningsprocessen for ETL?

Overvågning af ETL-processer i et lille datalager
Start ved — hvornår det begyndte at fungere,
Kilde — datakilden,
Lag — hvilket niveau af lageret der indlæses,
ETL Job Name — en procedure for indlæsning, der består af mange små trin,
Step Number — nummeret på det udførte trin,
Affected Rows — hvor mange data der allerede er blevet behandlet,
Duration sec — hvor længe det tager,
Status — er alt i orden eller ej: OK, ERROR, RUNNING, HANGS
Message — den seneste succesfulde besked eller fejbeskrivelse.

Baseret på status for posterne kan der sendes en e-mail til andre deltagere. Hvis der ikke er nogen fejl, er e-mailen ikke nødvendig.

Derfor er det klart angivet, hvor fejlen opstod.

Nogle gange kan det ske, at selve overvågningsværktøjet ikke fungerer. I så fald er der mulighed for direkte at kalde visningen i databasen, som rapporten er baseret på.

ETL Overvågnings Tabel

For at implementere overvågning af ETL-processer kræves der kun én tabel og én visning.

For dette kan man vende tilbage til sit lille lager og oprette en prototype i sqlite-databasen.

DDL tabel

CREATE TABLE UTL_JOB_STATUS (
	/* Tabel til registrering af jobudførelseslog. Det er vigtigt, at jobbet har trinene ETL_START og ETL_END eller ETL_ERROR */
  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  SID               INTEGER NOT NULL DEFAULT -1, /* Session Identificator. Unik for hver kørsel af job */
  LOG_DT            INTEGER NOT NULL DEFAULT 0,  /* Dato tid */
  LOG_D             INTEGER NOT NULL DEFAULT 0,  /* Dato */
  JOB_NAME          TEXT NOT NULL DEFAULT 'N/A', /* Jobnavn som JOB_STG2DM_GEO */
  STEP_NAME         TEXT NOT NULL DEFAULT 'N/A', /* ETL_START, ... , ETL_END/ETL_ERROR */
  STEP_DESCR        TEXT,                        /* Beskrivelse af opgave eller fejlmeddelelse */
  UNIQUE (SID, JOB_NAME, STEP_NAME)
);
INSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);

DDL visning/rapport

OPRET VISNING HVIS IKKE EKSTERNE UTL_JOB_STATUS_V
AS 
/* Indhold: Pakkeeksekveringslog for de sidste 3 måneder. */
WITH SRC AS (
  SELECT LOG_D,
    LOG_DT,
    UTL_JOB_STATUS_ID,
    SID,
	CASE WHEN INSTR(JOB_NAME, 'FTP') THEN 'OVERFØRSEL' /* filoverførsel */
	     WHEN INSTR(JOB_NAME, 'STG') THEN 'STAGE' /* trin */
	     WHEN INSTR(JOB_NAME, 'CLS') THEN 'RENHOLD' /* rensning */
	     WHEN INSTR(JOB_NAME, 'DIM') THEN 'DIMENSION' /* dimension */
	     WHEN INSTR(JOB_NAME, 'FCT') THEN 'FAKTUM' /* faktum */
		 WHEN INSTR(JOB_NAME, 'ETL') THEN 'STAGE-MART' /* datamart */
	     WHEN INSTR(JOB_NAME, 'RPT') THEN 'RAPPORT' /* rapport */
	     ELSE 'N/A' END AS LAYER,
	CASE WHEN INSTR(JOB_NAME, 'ACCESS') THEN 'ADGANGSLOG' /* kilde */
	     WHEN INSTR(JOB_NAME, 'MASTER') THEN 'MASTERDATA' /* kilde */
	     WHEN INSTR(JOB_NAME, 'AD-HOC') THEN 'AD-HOC' /* kilde */
	     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 'FEJL'
  WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 AND strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 THEN 'HÆNGER' /* en halv time */
  WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 THEN 'KØRER'
  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 Tjek for at få nyt sessionnummer

SELECT SUM (
  CASE WHEN start_job.JOB_NAME IS NOT NULL AND end_job.JOB_NAME IS NULL /* eksisterende job afsluttet */
	    AND NOT ( 'y' = 'n' ) /* tvungen genstart 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' /* jobnavn PARAMETER */
	    AND STEP_NAME = 'ETL_START'
      GROUP BY JOB_NAME, SID
    ) start_job /* starter */
  ON d_job.dummy = start_job.dummy
  LEFT OUTER JOIN
    ( SELECT JOB_NAME, SID
      FROM UTL_JOB_STATUS
      WHERE JOB_NAME = 'RPT_ACCESS_LOG'  /* jobnavn PARAMETER */
	    AND STEP_NAME in ('ETL_END', 'ETL_ERROR') /* stop status */
      GROUP BY JOB_NAME, SID
    ) end_job /* slutter */
  ON start_job.JOB_NAME = end_job.JOB_NAME
     AND start_job.SID = end_job.SID

Tabelspecifikationer:

  • Begyndelsen og slutningen af databehandlingsproceduren skal ledsages af trin ETL_START og ETL_END
  • I tilfælde af fejl skal der oprettes et trin ETL_ERROR med en beskrivelse af fejlen
  • Antallet af behandlede data skal fremhæves, for eksempel med stjerner
  • Samtidig kan den samme procedure startes med parameteren force_restart=y; uden den gives sessionnummeret kun til den afsluttede procedure
  • I normal tilstand kan den samme databehandlingsprocedure ikke køres parallelt

De nødvendige operationer ved arbejde med tabellen er følgende:

  • Hentning af sessionnummeret for den kørende ETL-procedure
  • Indsættelse af en logpost i tabellen
  • Hentning af den seneste succesfulde post for ETL-proceduren

I databaser som for eksempel Oracle eller Postgres kan disse operationer implementeres med indbyggede funktioner. For sqlite er der brug for en ekstern mekanisme, og i dette tilfælde er den prototype på PHP.

Output

Dermed spiller fejlmeddelelser i databehandlingsværktøjer en kæmpe vigtig rolle. Men de kan være svære at kalde optimale for hurtig fejlfinding. Når antallet af procedurer nærmer sig hundrede, bliver overvågning af processerne et komplekst projekt.

Artiklen giver et eksempel på en mulig løsning af problemet i form af en prototype. Hele prototypen af det lille lager er tilgængelig i gitlab SQLite PHP ETL Utilities.

Kilde: habr.com

Køb pålidelig webhosting med DDoS-beskyttelse, VPS VDS-servere 🔥 Køb pålidelig webhosting med DDoS-beskyttelse, VPS VDS-servere | ProHoster