🥇ETL-protsesside jälgimine väikeses andmete ladustamises

Paljud kasutavad spetsialiseeritud tööriistu andmete eraldamise, töötlemise ja laadimise protseduuride loomiseks relatsioonilistes andmebaasides. Tööriistade tööprotsess logitakse ja vead registreeritakse.

Veateate korral logis on teave selle kohta, et tööriist ei suutnud ülesannet täita ning millistes moodulites (tihti on need java) kus peatuti. Viimastest ridadest võib leida andmebaasi vea, näiteks unikaalse võtme rikkumise tabelis.

Küsimusele, millist rolli mängib ETL veateave, vastamiseks klassifitseerisin kõik probleemid, mis esinesid viimase kahe aasta jooksul märkimisväärses andmehoidlas.

Andmebaasi vead hõlmavad näiteks ruumi puudumist, ühenduse katkemist, sessiooni hangumist jne.

Loogilised vead hõlmavad näiteks võtmete rikkumisi tabelis, kehtetuks muutunud objekte, juurdepääsu puudumist objektidele jne.
Plaani täitmine võib olla ebaõige, see võib hanguda jne.

Lihtsad vead ei nõua palju aega parandamiseks. Enamik neist vead suudab hea ETL lahendada iseseisvalt.

Komplekssete vigade korral tuleb avada ja kontrollida andmete töötlemise protseduure, uurida andmeallikaid. Need toovad sageli kaasa vajaduse muudatuste testimise ja juurutamise järele.

Nii on pool kõigist probleemidest seotud andmebaasiga. 48% kõikidest vigadest on lihtsad vead.
Kolmandik kõigist probleemidest on seotud andmehoidla loogika või mudeli muutumisega, neist rohkem kui pooled vead on keerulised.

Ja vähem kui veerand kõigist probleemidest on seotud ülesannete planeerijaga, kus 18% on lihtsad vead.

Kokkuvõttes on 22% kõigist juhtunud vigadest keerulised, nende parandamiseks on vaja suurimat tähelepanu ja aega. Need esinevad umbes kord nädalas, samas kui lihtsad vead ilmnevad peaaegu iga päev.

On ilmne, et ETL-protsesside jälgimine on tõhus siis, kui logis on maksimaalselt täpselt näidatud vea asukoht ja probleemiallika leidmiseks kulub minimaalselt aega.

Tõhus jälgimine

Mida ma sooviksin näha ETL jälgimise protsessis?

Start at — millal töö algas,
Source — andmeallikas,
Layer — milline tasand andmehoidlas laaditakse,
ETL Job Name — laadimisprotseduur, mis koosneb paljusid väikestest sammudest,
Step Number — täidetava sammu number,
Mõjutatud read — kui palju andmeid on juba töödeldud,
Kestus sec — kui kaua see kestab,
Oleku — kas kõik on korras või mitte: OK, ERROR, RUNNING, HANGS
Sõnum — viimane edukas sõnum või veateade.

Failide staatuse põhjal on võimalik saata e-kiri teistele osalejatele. Kui vigu pole, siis pole ka kirja saatmine vajalik.

Nii on veateate korral selgelt näidatud, kus probleem esines.

Mõnikord juhtub, et monitorimisriist ise ei tööta. Sel juhul on võimalik otse andmebaasis kutsuda esile vaade (view), mille alusel raport on koostatud.

ETL-monitooringu tabel

ETL-protsesside jälgimiseks piisab ühest tabelist ja ühest vaatest.

Selleks võib naasta oma väikesse andmehoidlasse ja luua andmebaasis sqlite prototüüp.

DDL tabel

CREATE TABLE UTL_JOB_STATUS (

/* Tabel töö täitmise logimiseks. Oluline, et tööl on etapid ETL_START ja ETL_END või ETL_ERROR */
  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  SID               INTEGER NOT NULL DEFAULT -1, /* Sessiooni Identifikaator. Unikaalne iga töö käivitamise jaoks */
  LOG_DT            INTEGER NOT NULL DEFAULT 0,  /* Kuupäev ja kellaaeg */
  LOG_D             INTEGER NOT NULL DEFAULT 0,  /* Kuupäev */
  JOB_NAME          TEXT NOT NULL DEFAULT 'N/A', /* Töö nimi nagu JOB_STG2DM_GEO */
  STEP_NAME         TEXT NOT NULL DEFAULT 'N/A', /* ETL_START, ... , ETL_END/ETL_ERROR */
  STEP_DESCR        TEXT,                        /* Ülesande või veateate kirjeldus */
  UNIQUE (SID, JOB_NAME, STEP_NAME)
);
INSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);

DDL vaade/raport

LOO KAA KUILE KUILE UTL_JOB_STATUS_V
KUILE \* Sisu: Paketi täitmise logi viimase 3 kuu jooksul. \*
KOOS SRC KUIGA (
  VALI LOG_D,
    LOG_DT,
    UTL_JOB_STATUS_ID,
    SID,
	JUHUL KUI INSTR(JOB_NAME, 'FTP') SIIS 'ÜLEKANNE' \* failide edastamine \*
	     JUHUL KUI INSTR(JOB_NAME, 'STG') SIIS 'ETAPPIK' \* etapp \*
	     JUHUL KUI INSTR(JOB_NAME, 'CLS') SIIS 'PUHTUS' \* puhastamine \*
	     JUHUL KUI INSTR(JOB_NAME, 'DIM') SIIS 'DIMENSIOON' \* dimensioon \*
	     JUHUL KUI INSTR(JOB_NAME, 'FCT') SIIS 'TEGUR' \* tegur \*
		 JUHUL KUI INSTR(JOB_NAME, 'ETL') SIIS 'ETAPPIK-MART' \* andmete turustamise etapp \*
	     JUHUL KUI INSTR(JOB_NAME, 'RPT') SIIS 'ARUANNE' \* aruanne \*
	     MUUL JUHUL 'N/A' LõPUNAS,
	JUHUL KUI INSTR(JOB_NAME, 'ACCESS') SIIS 'ACCESS LOG' \* allikas \*
	     JUHUL KUI INSTR(JOB_NAME, 'MASTER') SIIS 'MEISTERSISU' \* allikas \*
	     JUHUL KUI INSTR(JOB_NAME, 'AD-HOC') SIIS 'AD-HOC' \* allikas \*
	     MUUL JUHUL 'N/A' LõPUNAS,
    JOB_NAME,
    STEP_NAME,
    JUHUL KUI STEP_NAME='ETL_START' SIIS 1 MUUL JUHUL 0 LõPUNAS,
    JUHUL KUI STEP_NAME='ETL_END' SIIS 1 MUUL JUHUL 0 LõPUNAS,
    JUHUL KUI STEP_NAME='ETL_ERROR' SIIS 1 MUUL JUHUL 0 LõPUNAS,
    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
  KUST UTL_JOB_STATUS
  KUHI datetime(LOG_D, 'unixepoch') >= date('now', 'start of month', '-3 month')
)
VALI 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,
  JUHUL
  KUI JB.ERROR_FLAG = 1 SIIS 'ERROR'
  KUI JB.ERROR_FLAG = 0 JA JB.END_FLAG = 0 JA strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 SIIS 'HANGS' \* pool tundi \*
  KUI JB.ERROR_FLAG = 0 JA JB.END_FLAG = 0 SIIS 'KÄIBES'
  MUUL JUHUL 'OK'
  LÕPUNA 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
KUST
  ( VALI 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
    KUST SRC
    GRUPPEERI SID, SOURCE, LAYER, JOB_NAME
  ) JB,
  ( VALI UTL_JOB_STATUS_ID, SID, JOB_NAME, STEP_LOG
    KUST SRC
    KUHI 1 = 1
  ) ERR
KUHI 1 = 1
  JA JB.SID = ERR.SID
  JA JB.JOB_NAME = ERR.JOB_NAME
  JA JB.UTL_JOB_STATUS_ID = ERR.UTL_JOB_STATUS_ID
KORRALDA JB.MIN_LOG_DT DESC, JB.SID DESC, JB.SOURCE;

SQL Kontrolli, kas on võimalik saada uut sessiooni numbrit

VALI SUM (
  JUHUL KUI start_job.JOB_NAME EI OLE NULL JA end_job.JOB_NAME ON NULL \* eksisteeriv töö lõppes \*
	    JA MITTE ( 'y' = 'n' ) \* sunniviisiline taaskäivitamine PARAMEETER \*
       SIIS 1 MUUL JUHUL 0
  LÕPUNA ) AS IS_RUNNING
  KUST
    ( VALI 1 AS dummy KUST UTL_JOB_STATUS KUHI sid = -1) d_job
  VASAKU VÄLJAVÕTT 
    ( VALI JOB_NAME, SID, 1 AS dummy
      KUST UTL_JOB_STATUS
      KUHI JOB_NAME = 'RPT_ACCESS_LOG' \* töö nime PARAMEETER \*
	    JA STEP_NAME = 'ETL_START'
      GRUPPEERI JOB_NAME, SID
    ) start_job \* algused \*
  ON d_job.dummy = start_job.dummy
  VASAKU VÄLJAVÕTT 
    ( VALI JOB_NAME, SID
      KUST UTL_JOB_STATUS
      KUHI JOB_NAME = 'RPT_ACCESS_LOG'  \* töö nime PARAMEETER \*
	    JA STEP_NAME in ('ETL_END', 'ETL_ERROR') \* peatamise olek \*
      GRUPPEERI JOB_NAME, SID
    ) end_job \* lõpeb \*
  ON start_job.JOB_NAME = end_job.JOB_NAME
     JA start_job.SID = end_job.SID

Tabeli omadused:

Andmete töötlemise protseduuri algus ja lõpp peaks olema märgistatud ETL_START ja ETL_END sammudega
Viga ilmnenud korral tuleb luua samm ETL_ERROR koos selle kirjeldusega
Töödeldud andmete arvu tuleb esile tuua, näiteks tähtedega
Ühe ja sama protseduuri saab samal ajal käivitada parameetriga force_restart=y; ilma selleta antakse seanssi number ainult lõpetatud protseduurile
Tavaliselt ei saa paralleelselt ühte ja sama andmete töötlemise protsessi käivitada

Tabeliga töötamiseks vajalikud toimingud on järgmised:

ETL protseduuri käivitamise seanssi numbri saamine
Logi kirje lisamine tabelisse
ETL protseduuri viimase eduka kirje saamine

Sellistes andmebaasides nagu Oracle või Postgres saab neid operatsioone rakendada sisseehitatud funktsioonide kaudu. SQLite puhul on vajalik väline mehhanism ja antud juhul on see prototüüpitud PHP-s.

Kokkuvõte

Seega mängivad andmete töötlemise tööriistades veateated äärmiselt olulist rolli. Kuid neid on keeruline nimetada optimaalseteks kiirete probleemide lahendamiseks. Kui protseduuride arv läheneb sajale, muutub protsesside jälgimine keeruliseks projektiks.

Artiklis on toodud näide võimalikust lahendusest prototüübi kaudu. Kogu väikese ladustamise prototüüp on saadaval GitLabis SQLite PHP ETL utiliidid.

Allikas: habr.com

ETL-protsesside jälgimine väikeses andmehoidlas

Tõhus jälgimine

ETL-monitooringu tabel

Kokkuvõte