Monitorimi i proceseve ETL në një depo të vogël të të dhënave

Shumë njerëz përdorin mjete të specializuara për të krijuar procedura për nxjerrjen, transformimin dhe ngarkimin e të dhënave në bazat e dhënave marrëse. Procesi i punës së mjeteve dokumentohet dhe gabimet regjistrohen.

Në rast gabimi, logu përmban informacion në lidhje me faktin se mjeti nuk arriti të përfundojë detyrën dhe cilat module (shpesh është java) ndaluan. Në rreshtat e fundit, mund të gjeni një gabim në bazën e të dhënave, për shembull, shkelje të çelësit unik të tabelës.

Për të përgjigjur pyetjes se cili është roli i informacionit mbi gabimet ETL, kam klasifikuar të gjitha problemet që ndodhën gjatë dy viteve të fundit në një depo të konsiderueshme.

Gabimet e bazës së të dhënave përfshijnë ato si mungesa e hapësirës, ndërprerja e lidhjes, ngrirja e sesionit etj.

Gabimet logjike përfshijnë ato si shkelje të çelësave të tabelave, objekte të pavlefshme, mungesë qasjeje në objekte etj.
Planifikuesi mund të nisët vonë, mund të ngecë etj.

Gabimet e thjeshta nuk kërkojnë shumë kohë për t'u rregulluar. Me shumicën e tyre, një ETL i mirë di të përballojë vetë.

Gabimet e komplikuara kërkojnë që të hapen dhe të kontrollohen procedurat e punës me të dhënat, të studiohen burimet e të dhënave. Shpesh çojnë në nevojën për testimin e ndryshimeve dhe implementimin.

Pra, gjysma e të gjitha problemeve ka të bëjë me bazën e të dhënave. 48% e të gjitha gabimeve janë gabime të thjeshta.
Një e treta e të gjitha problemeve ka të bëjë me ndryshimin e logjikës ose modelit të depozitës, dhe më shumë se gjysma e këtyre gabimeve janë të komplikuara.

Dhe më pak se një e katërta e të gjitha problemeve është e lidhur me planifikuesin e detyrave, 18% e të cilave janë gabime të thjeshta.

Në përgjithësi, 22% e të gjitha gabimeve janë të komplikuara, dhe rregullimi i tyre kërkon më shumë vëmendje dhe kohë. Ato ndodhin rreth një herë në javë. Ndërsa gabimet e thjeshta ndodhin thuajse çdo ditë.

Evident është se monitorimi i proceseve ETL është efektiv kur logu tregon sa më saktë vendin e gabimit dhe kërkon kohën minimale për të gjetur burimin e problemit.

Monitorim efektiv

Çfarë doja të shihja në procesin e monitorimit ETL?

Start at — kur filloi puna,
Source — burimi i të dhënave,
Layer — cili nivel depozite po ngarkohet,
ETL Job Name — procedura e ngarkimit që përbëhet nga shumë hapa të vegjël,
Step Number — numri i hapit që po kryhet,
Affected Rows — sa të dhëna janë përpunuar tashmë,
Duration sec — sa gjatë po kryhet,
Status — a është gjithçka në rregull apo jo: OK, ERROR, RUNNING, HANGS
Message — mesazhi më i fundit i suksesshëm ose përshkrimi i gabimit.

Në bazë të statusit të regjistrimeve, mund të dërgohet një email te pjesëmarrësit e tjerë. Nëse nuk ka gabimeve, atëherë emaili nuk është i domosdoshëm.

Pra, në rast gabimi, vendi i incidentit është shprehur qartë.

Ndonjëherë ndodh që mjeti i monitorimit nuk funksionon. Në këtë rast, ka mundësi që në bazën e të dhënave të thirret një pamje (view), mbi të cilën është ndërtuar raporti.

Tabela e monitorimit ETL

Për të realizuar monitorimin e proceseve ETL, mjafton një tabelë dhe një pamje.

Për këtë, mund të kthehemi në depon tonë të vogël dhe të krijojmë një prototip në databazën sqlite.

DDL tabela

CREATE TABLE UTL_JOB_STATUS (
/* Tabela për regjistrimin e logut të ekzekutimit të punës. E rëndësishme që puna të ketë hapat ETL_START dhe ETL_END ose ETL_ERROR */
  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  SID               INTEGER NOT NULL DEFAULT -1, /* Identifikatori i sesionit. Unik për çdo ekzekutim të punës */
  LOG_DT            INTEGER NOT NULL DEFAULT 0,  /* Data dhe ora */
  LOG_D             INTEGER NOT NULL DEFAULT 0,  /* Data */
  JOB_NAME          TEXT NOT NULL DEFAULT 'N/A', /* Emri i punës si JOB_STG2DM_GEO */
  STEP_NAME         TEXT NOT NULL DEFAULT 'N/A', /* ETL_START, ... , ETL_END/ETL_ERROR */
  STEP_DESCR        TEXT,                        /* Përshkrimi i detyrës ose mesazhi i gabimit */
  UNIQUE (SID, JOB_NAME, STEP_NAME)
);
INSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);

DDL pamjeje/raporti

KRIJO PAMJE NËSE NUK EKZISTON UTL_JOB_STATUS_V
AS /* Përmbajtja: Logu i Ekzekutimit të Paketës për 3 muajt e fundit. */
ME SRC AS (
  ZGJIDH LOG_D,
    LOG_DT,
    UTL_JOB_STATUS_ID,
    SID,
	RASTI KUR INSTR(JOB_NAME, 'FTP') ATËHERË 'TRANSFER' /* transferimi i skedarëve */
	     RASTI KUR INSTR(JOB_NAME, 'STG') ATËHERË 'STAGE' /* faza */
	     RASTI KUR INSTR(JOB_NAME, 'CLS') ATËHERË 'PASTRIM' /* pastrimi */
	     RASTI KUR INSTR(JOB_NAME, 'DIM') ATËHERË 'DIMENSION' /* dimensioni */
	     RASTI KUR INSTR(JOB_NAME, 'FCT') ATËHERË 'FARË' /* fakte */
		 RASTI KUR INSTR(JOB_NAME, 'ETL') ATËHERË 'STAGE-MART' /* mart i të dhënave */
	     RASTI KUR INSTR(JOB_NAME, 'RPT') ATËHERË 'REPORT' /* raporti */
	     TË TJERAT 'N/A' MERR SI LËVIZJE,
	RASTI KUR INSTR(JOB_NAME, 'ACCESS') ATËHERË 'LOG i QASJES' /* burimi */
	     RASTI KUR INSTR(JOB_NAME, 'MASTER') ATËHERË 'TË DHËNAT MASTER' /* burimi */
	     RASTI KUR INSTR(JOB_NAME, 'AD-HOC') ATËHERË 'AD-HOC' /* burimi */
	     TË TJERAT 'N/A' SI BURIM,
    JOB_NAME,
    STEP_NAME,
    RASTI KUR STEP_NAME='ETL_START' ATËHERË 1 TË TJERAT 0 SI START_FLAG,
    RASTI KUR STEP_NAME='ETL_END' ATËHERË 1 TË TJERAT 0 SI END_FLAG,
    RASTI KUR STEP_NAME='ETL_ERROR' ATËHERË 1 TË TJERAT 0 SI ERROR_FLAG,
    STEP_NAME || ' : ' || STEP_DESCR SI STEP_LOG,
	SUBSTR( SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), 1, INSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), '***')-2 ) SI AFFECTED_ROWS
  FROM UTL_JOB_STATUS
  WHERE datetime(LOG_D, 'unixepoch') >= date('now', 'start of month', '-3 month')
)
ZGJIDH JB.SID,
  JB.MIN_LOG_DT AS START_DT,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) SI LOG_DT,
  JB.SOURCE,
  JB.LAYER,
  JB.JOB_NAME,
  RASTI
  KUR JB.ERROR_FLAG = 1 ATËHERË 'ERROR'
  KUR JB.ERROR_FLAG = 0 DHE JB.END_FLAG = 0 DHE strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 ATËHERË 'HANGS' /* gjysmë ore */
  KUR JB.ERROR_FLAG = 0 DHE JB.END_FLAG = 0 ATËHERË 'RUNNING'
  TË TJERAT 'OK'
  SI STATUS,
  ERR.STEP_LOG     SI STEP_LOG,
  JB.CNT           SI STEP_CNT,
  JB.AFFECTED_ROWS SI AFFECTED_ROWS,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) SI JOB_START_DT,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MAX_LOG_DT, 'unixepoch')) SI JOB_END_DT,
  JB.MAX_LOG_DT - JB.MIN_LOG_DT SI JOB_DURATION_SEC
FROM
  ( ZGJIDH SID, SOURCE, LAYER, JOB_NAME,
           MAX(UTL_JOB_STATUS_ID) SI UTL_JOB_STATUS_ID,
           MAX(START_FLAG)       SI START_FLAG,
           MAX(END_FLAG)         SI END_FLAG,
           MAX(ERROR_FLAG)       SI ERROR_FLAG,
           MIN(LOG_DT)           SI MIN_LOG_DT,
           MAX(LOG_DT)           SI MAX_LOG_DT,
           SUM(1)                SI CNT,
           SUM(IFNULL(AFFECTED_ROWS, 0)) SI AFFECTED_ROWS
    FROM SRC
    GRUPI NGA SID, SOURCE, LAYER, JOB_NAME
  ) JB,
  ( ZGJIDH UTL_JOB_STATUS_ID, SID, JOB_NAME, STEP_LOG
    FROM SRC
    KU 1 = 1
  ) ERR
KU 1 = 1
  DHE JB.SID = ERR.SID
  DHE JB.JOB_NAME = ERR.JOB_NAME
  DHE JB.UTL_JOB_STATUS_ID = ERR.UTL_JOB_STATUS_ID
POROSIA ZGJIDH JB.MIN_LOG_DT DESC, JB.SID DESC, JB.SOURCE;

Kontrolli SQL i mundësisë për të marrë një numër të ri sesioni

ZGJIDH SUM (
  RASTI KUR start_job.JOB_NAME NUK ËSHTË NULL DHE end_job.JOB_NAME ËSHTË NULL /* puna eksiste përfundoi */
	    DHE JO ( 'y' = 'n' ) /* forcimi i rinisjes PARAMETRI */
       ATËHERË 1 TË TJERAT 0
  END ) SI IS_RUNNING
  NGA
    ( ZGJIDH 1 SI dummy NGA UTL_JOB_STATUS KU sid = -1) d_job
  LEFT OUTER JOIN
    ( ZGJIDH JOB_NAME, SID, 1 SI dummy
      NGA UTL_JOB_STATUS
      KU JOB_NAME = 'RPT_ACCESS_LOG' /* emri i punës PARAMETRI */
	    DHE STEP_NAME = 'ETL_START'
      GRUPI NGA JOB_NAME, SID
    ) start_job /* fillon */
  ON d_job.dummy = start_job.dummy
  LEFT OUTER JOIN
    ( ZGJIDH JOB_NAME, SID
      NGA UTL_JOB_STATUS
      KU JOB_NAME = 'RPT_ACCESS_LOG'  /* emri i punës PARAMETRI */
	    DHE STEP_NAME në ('ETL_END', 'ETL_ERROR') /* statusi i ndaljes */
      GRUPI NGA JOB_NAME, SID
    ) end_job /* përfundon */
  ON start_job.JOB_NAME = end_job.JOB_NAME
     DHE start_job.SID = end_job.SID

Karakteristikat e tabelës:

• fillimi dhe përfundimi i procedurës së përpunimit të të dhënave duhet të shoqërohet me hapat ETL_START dhe ETL_END
• në rast gabimi duhet të krijohet hapi ETL_ERROR me përshkrimin e saj
• numri i të dhënave të përpunuara duhet të evidentohet, për shembull, me yje
• në të njëjtën kohë e njëjta procedurë mund të iniciohet me parametrin force_restart=y, pa të, numri i sesionit jepet vetëm për procedurën e përfunduar
• në mënyrë normale nuk mund të iniciohet paralelisht e njëjta procedurë e përpunimit të të dhënave

Operacionet e nevojshme për punën me tabelën janë si më poshtë:

• marrja e numrit të sesionit për procedurën ETL të nisur
• shtimi i shënimit të logut në tabelë
• marrja e shënimit të fundit të suksesshëm për procedurën ETL

Në baza të dhënash të tilla si Oracle ose Postgres këto operacione mund të implementohen me funksione të ndërtuara. Për sqlite nevojitet një mekanizëm i jashtëm dhe në këtë rast ai është prototipizuar në PHP.

Përfundim

Pra, mesazhet e gabimeve në mjetet e përpunimit të të dhënave luajnë një rol shumë të rëndësishëm. Por të qenët optimale për gjetjen e shpejtë të shkakut të problemeve, ato nuk janë të lehta. Kur numri i procedurave afrohet në njëqind, monitorimi i proceseve bëhet një projekt i ndërlikuar.

Ky artikull ofron një shembull të mundshëm të zgjidhjes së problemit në formën e një prototipi. E gjithë prototipi i një depoje të vogël është i disponueshëm në gitlab SQLite PHP ETL Utilities.

Burimi: habr.com