🥇Övervakning av ETL-processer i ett litet datalager

Många använder specialiserade verktyg för att skapa processer för extrahering, transformation och laddning av data i relationsdatabaser. Arbetsprocessen för verktygen loggas och fel registreras.

I händelse av ett fel innehåller loggen information om att verktyget misslyckades med att utföra uppgiften och vilka moduler (ofta Java) som stannade. I de sista raderna kan du hitta databaserelaterade fel, till exempel, överträdelse av den unika nyckeln i tabellen.

För att besvara frågan om vilken roll information om ETL-fel spelar, klassificerade jag alla problem som inträffade under de senaste två åren i ett stort datalager.

Databasfel inkluderar sådana som otillräckligt utrymme, avbrutet anslutning, hängande sessioner, etc.

Logiska fel inkluderar sådana som nyckelöverträdelse i tabeller, ogiltiga objekt, brist på åtkomst till objekt, etc.
Schemaläggaren kan startas vid fel tillfälle, kan hänga sig, etc.

Enkla fel kräver inte mycket tid för att åtgärda. De flesta av dem kan en bra ETL hantera på egen hand.

Komplexa fel kräver att man öppnar och kontrollerar procedurer för databehandling, undersöker datakällor. De leder ofta till tester av ändringar och installation.

Således är hälften av alla problem relaterade till databasen. 48% av alla fel är enkla fel.
En tredjedel av alla problem är relaterade till ändringar av logik eller lagermodell, mer än hälften av dessa fel är komplexa.

Och mindre än en fjärdedel av alla problem är relaterade till schemaläggaren, där 18% är enkla fel.

Övergripande utgör 22% av de inträffade felen komplexa fel, vars åtgärd kräver mest uppmärksamhet och tid. De inträffar cirka en gång i veckan, medan enkla fel inträffar nästan varje dag.

Det är uppenbart att övervakningen av ETL-processerna kommer att vara effektiv när loggen så exakt som möjligt anger felplatsen och minimal tid krävs för att hitta problemkällan.

Effektiv övervakning

Vad skulle jag vilja se i övervakningen av ETL?

Starta vid — när arbetet började,
Källa — datakälla,
Lager — vilket nivå av lagret som laddas,
ETL Job Name — laddningsproceduren som består av många små steg,
Stegnummer — nummer på det aktuella steget,
Påverkade rader — hur mycket data som redan har bearbetats,
Varaktighet sek — hur länge det tar att utföra,
Status — om allt är bra eller inte: OK, ERROR, RUNNING, HANGS
Meddelande — den senaste framgångsrika meddelandet eller beskrivningen av felet.

Baserat på statusen av poster kan ett e-postmeddelande skickas till andra deltagare. Om det inte finns några fel är inte e-postmeddelandet nödvändigt.

Således, i händelse av ett fel, anges tydligt platsen för händelsen.

Ibland händer det att själva övervakningsverktyget inte fungerar. I sådana fall finns det möjlighet att direkt i databasen anropa en vy, baserat på vilken rapporten har byggts.

ETL övervakningstabell

För att genomföra övervakning av ETL-processer krävs endast en tabell och en vy.

För detta kan man återvända till ditt lilla lagringsutrymme och skapa en prototyp i databasen sqlite.

DDL-tabell

CREATE TABLE UTL_JOB_STATUS (

/* Tabell för loggning av jobbets körlogg. Viktigt att jobbet har stegen ETL_START och ETL_END eller ETL_ERROR */
  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  SID               INTEGER NOT NULL DEFAULT -1, /* Sessionsidentifierare. Unik för varje körning av jobbet */
  LOG_DT            INTEGER NOT NULL DEFAULT 0,  /* Datum och tid */
  LOG_D             INTEGER NOT NULL DEFAULT 0,  /* Datum */
  JOB_NAME          TEXT NOT NULL DEFAULT 'N/A', /* Jobbnames som JOB_STG2DM_GEO */
  STEP_NAME         TEXT NOT NULL DEFAULT 'N/A', /* ETL_START, ..., ETL_END/ETL_ERROR */
  STEP_DESCR        TEXT,                        /* Beskrivning av uppgift eller felmeddelande */
  UNIQUE (SID, JOB_NAME, STEP_NAME)
);
INSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);

DDL vy/rapport

SKAPA VISNING OM DEN INTE FINNS UTL_JOB_STATUS_V
AS /* Innehåll: Paketexekveringslogg för de senaste 3 månaderna. */
MED SRC SOM (
  VÄLJ LOG_D,
    LOG_DT,
    UTL_JOB_STATUS_ID,
    SID,
	FALL NÄR INSTR(JOB_NAME, 'FTP') DÅ 'ÖVERFÖRING' /* filöverföring */
	     NÄR INSTR(JOB_NAME, 'STG') DÅ 'STEG' /* steg */
	     NÄR INSTR(JOB_NAME, 'CLS') DÅ 'RENSE' /* rengöring */
	     NÄR INSTR(JOB_NAME, 'DIM') DÅ 'DIMENSION' /* dimension */
	     NÄR INSTR(JOB_NAME, 'FCT') DÅ 'FAKT' /* fakta */
		 NÄR INSTR(JOB_NAME, 'ETL') DÅ 'STEG-MART' /* datamart */
	     NÄR INSTR(JOB_NAME, 'RPT') DÅ 'RAPPORT' /* rapport */
	     ANNARS 'N/A' SLUT SOM LAGER,
	FALL NÄR INSTR(JOB_NAME, 'ACCESS') DÅ 'ÅTKOMSTLOGG' /* källa */
	     NÄR INSTR(JOB_NAME, 'MASTER') DÅ 'MASTERDATA' /* källa */
	     NÄR INSTR(JOB_NAME, 'AD-HOC') DÅ 'AD-HOC' /* källa */
	     ANNARS 'N/A' SLUT SOM KÄLLA,
    JOB_NAME,
    STEP_NAME,
    FALL NÄR STEP_NAME='ETL_START' DÅ 1 ANNARS 0 SLUT SOM START_FLAG,
    FALL NÄR STEP_NAME='ETL_END' DÅ 1 ANNARS 0 SLUT SOM SLUT_FLAG,
    FALL NÄR STEP_NAME='ETL_ERROR' DÅ 1 ANNARS 0 SLUT SOM FEL_FLAG,
    STEP_NAME || ' : ' || STEP_DESCR SOM STEG_LOG,
	SUBSTR( SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), 1, INSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), '***')-2 ) SOM PÅVERKADE_RADER
  FRÅN UTL_JOB_STATUS
  DÄR datetime(LOG_D, 'unixepoch') >= date('now', 'start of month', '-3 month')
)
VÄLJ JB.SID,
  JB.MIN_LOG_DT SOM START_DT,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) SOM LOG_DT,
  JB.KÄLLA,
  JB.LAGER,
  JB.JOB_NAME,
  FALL
  NÄR JB.FEL_FLAG = 1 DÅ 'FEL'
  NÄR JB.FEL_FLAG = 0 OCH JB.SLUT_FLAG = 0 OCH strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 DÅ 'HÄNGER' /* en halv timme */
  NÄR JB.FEL_FLAG = 0 OCH JB.SLUT_FLAG = 0 DÅ 'KÖR'
  ANNARS 'OK'
  SLUT SOM STATUS,
  ERR.STEP_LOG     SOM STEG_LOG,
  JB.CNT           SOM STEG_CNT,
  JB.PÅVERKADE_RADER SOM PÅVERKADE_RADER,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) SOM JOB_START_DT,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MAX_LOG_DT, 'unixepoch')) SOM JOB_END_DT,
  JB.MAX_LOG_DT - JB.MIN_LOG_DT SOM JOB_DURATION_SEC
FRÅN
  ( VÄLJ SID, KÄLLA, LAGER, JOB_NAME,
           MAX(UTL_JOB_STATUS_ID) SOM UTL_JOB_STATUS_ID,
           MAX(START_FLAG)       SOM START_FLAG,
           MAX(END_FLAG)         SOM SLUT_FLAG,
           MAX(FEL_FLAG)       SOM FEL_FLAG,
           MIN(LOG_DT)           SOM MIN_LOG_DT,
           MAX(LOG_DT)           SOM MAX_LOG_DT,
           SUM(1)                SOM CNT,
           SUM(IFNULL(PÅVERKADE_RADER, 0)) SOM PÅVERKADE_RADER
    FRÅN SRC
    GRUPPERA EFTER SID, KÄLLA, LAGER, JOB_NAME
  ) JB,
  ( VÄLJ UTL_JOB_STATUS_ID, SID, JOB_NAME, STEG_LOG
    FRÅN SRC
    DÄR 1 = 1
  ) ERR
DÄR 1 = 1
  OCH JB.SID = ERR.SID
  OCH JB.JOB_NAME = ERR.JOB_NAME
  OCH JB.UTL_JOB_STATUS_ID = ERR.UTL_JOB_STATUS_ID
ORDNA EFTER JB.MIN_LOG_DT DESC, JB.SID DESC, JB.KÄLLA;

SQL Kontroll av möjligheten att få ett nytt sessionsnummer

VÄLJ SUM (
  FALL NÄR start_job.JOB_NAME ÄR INTE NULL OCH end_job.JOB_NAME ÄR NULL /* existerande jobb avslutat */
	    OCH INTE ( 'y' = 'n' ) /* tvinga omstart PARAMETER */
       DÅ 1 ANNARS 0
  SLUT ) SOM IS_RUNNING
  FRÅN
    ( VÄLJ 1 SOM dummy FRÅN UTL_JOB_STATUS DÄR sid = -1) d_job
  VÄNSTER YTTERJOIN
    ( VÄLJ JOB_NAME, SID, 1 SOM dummy
      FRÅN UTL_JOB_STATUS
      DÄR JOB_NAME = 'RPT_ACCESS_LOG' /* jobbnamn PARAMETER */
	    OCH STEP_NAME = 'ETL_START'
      GRUPPERA EFTER JOB_NAME, SID
    ) start_job /* startar */
  PÅ d_job.dummy = start_job.dummy
  VÄNSTER YTTERJOIN
    ( VÄLJ JOB_NAME, SID
      FRÅN UTL_JOB_STATUS
      DÄR JOB_NAME = 'RPT_ACCESS_LOG'  /* jobbnamn PARAMETER */
	    OCH STEP_NAME in ('ETL_END', 'ETL_ERROR') /* stoppstatus */
      GRUPPERA EFTER JOB_NAME, SID
    ) end_job /* avslutar */
  PÅ start_job.JOB_NAME = end_job.JOB_NAME
     OCH start_job.SID = end_job.SID

Egenskaper hos tabellen:

Början och slutet av databehandlingsproceduren måste åtföljas av stegen ETL_START och ETL_END
Vid ett fel ska ett steg ETL_ERROR skapas med dess beskrivning
Antalet behandlade data bör särmarkeras, till exempel med stjärnor
Samtidigt kan samma procedur startas med parametern force_restart=y; utan den ges sessionsnumret endast till den avslutade proceduren
I normalläge kan samma databehandlingsprocedur inte köras parallellt

Nödvändiga operationer för arbetet med tabellen är följande:

Hämta sessionsnumret för den startade proceduren ETL
Infoga en loggpost i tabellen
Hämta den senaste lyckade posten från proceduren ETL

I databaser som till exempel Oracle eller Postgres kan dessa operationer realiseras med inbyggda funktioner. För sqlite krävs en extern mekanism och i detta fall är den prototyperad i PHP.

Utdata

Således spelar felmeddelanden i databehandlingsverktyg en mega-viktig roll. Men de kan knappast kallas optimala för snabb felsökning av problem. När antalet procedurer närmar sig hundra blir övervakning av processer ett komplext projekt.

I artikeln ges ett exempel på en möjlig lösning på problemet i form av en prototyp. Hela prototypen av det lilla lagret finns tillgänglig på gitlab SQLite PHP ETL-verktyg.

Källa: habr.com