🥇ETL-protsesside jälgimine väikestes andmehoidlates

Paljuski kasutavad spetsialiseeritud tööriistu, et luua andmete väljavõtmise, transformatsiooni ja laadimise protseduure suhelistesse andmebaasidesse. Tööriistade tööprotsessi jälgitakse ja vead fikseeritakse.

Vea tekkimisel logis on teave selle kohta, et tööriist ei suutnud ülesannet täita ja millised moodulid (tihti java) kus peatunud on. Viimastest ridadest võib leida andmebaasi vea, näiteks unikaalse võti rikkumise tabelis.

Et vastata küsimusele, millist rolli mängib ETL veateave, olen klassifitseerinud kõik probleemid, mis on viimase kahe aasta jooksul juhtunud suures andmehoidlas.

Andmebaasi vead hõlmavad selliseid nagu vähese ruumi tõttu, ühenduse katkemine, seansi peatamine jne.

Loogilised vead hõlmavad selliseid nagu tabeli võtmete rikkumine, kehtetud objektid, juurdepääsu puudumine objektidele jne.
Planeerija võib käivituda ebasobival ajal, võib peatuda jne.

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

Kuna keerulised vead nõuavad andmepäringute menetlemise protseduuride avamist ja kontrollimist, nõuavad nad sageli muudatuste testimist ja juurutamist.

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

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

Kokku on 22% kõigist tõrgetest keerulised, nende parandamine nõuab kõige rohkem tähelepanu ja aega. Need juhtuvad umbes kord nädalas. Samas kui lihtsaid vigu esineb peaaegu iga päev.

Ilmselgelt on ETL-protsesside jälgimine tõhus siis, kui vealogis on võimalikult täpselt märgitud vea koht ja probleemide allika leidmiseks on minimaalne aeg vajalik.

Tõhus jälgimine

Mida sooviksin näha ETL jälgimise protsessis?

Start at — millal töö algas,
Source — andmeallikas,
Layer — milline andmehoidla tase laaditakse,
ETL Job Name — laadimisprotseduur, mis koosneb paljusid väikeseid samme,
Step Number — teostatava sammu number,
Affected Rows — kui palju andmeid on juba töödeldud,
Duration sec — kui kaua see kestab,
Status — kas kõik on hästi või mitte: OK, ERROR, RUNNING, HANGS
Message — viimane edukas sõnum või veakirjeldus.

Salvestatud kirjade staatuse põhjal saab saata e-kirja teistele osalistele. Kui vigu pole, siis ei ole kirja saatmine vajalik.

Seega on vigade korral selgelt märgitud juhtumikoht.

Mõnikord juhtub, et jälgimise tööriist ise ei tööta. Sellisel juhul on võimalik otse andmebaasis kutsuda esitus (view), mille alusel aruanne on koostat.

ETL jälgimise tabel

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

Selle jaoks saab naasta oma väikesesse andmehoidlasse ja luua andmebaasis sqlite prototüüp.

DDL tabel

CREATE TABLE UTL_JOB_STATUS (
/* Tabel tööülesannete täitmise logimise jaoks. Oluline on, et tööl on sammud 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, /* Seansi identifikaator. Ainulaadne 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 esitus/aruanne

Loo vaade, kui UTL_JOB_STATUS_V ei eksisteeri
AS /* Sisu: Paketi täitmise logi viimase 3 kuu kohta. */
WITH SRC AS (
  SELECT LOG_D,
    LOG_DT,
    UTL_JOB_STATUS_ID,
    SID,
	CASE WHEN INSTR(JOB_NAME, 'FTP') THEN 'ÜLEKANNE' /* failiedastus */
	     WHEN INSTR(JOB_NAME, 'STG') THEN 'ETAPPI' /* etapp */
	     WHEN INSTR(JOB_NAME, 'CLS') THEN 'PUHASTAMINE' /* puhastamine */
	     WHEN INSTR(JOB_NAME, 'DIM') THEN 'MÕÕTMINE' /* mõõtmine */
	     WHEN INSTR(JOB_NAME, 'FCT') THEN 'FAKT' /* fakt */
		 WHEN INSTR(JOB_NAME, 'ETL') THEN 'ETAPPI-RANDI' /* andmete rand */
	     WHEN INSTR(JOB_NAME, 'RPT') THEN 'ARUANNE' /* aruanne */
	     ELSE 'N/A' END AS KIHT,
	CASE WHEN INSTR(JOB_NAME, 'ACCESS') THEN 'JÕUDSUS LOGI' /* allikas */
	     WHEN INSTR(JOB_NAME, 'MASTER') THEN 'PEAMEETODI ANDMED' /* allikas */
	     WHEN INSTR(JOB_NAME, 'AD-HOC') THEN 'AD-HOC' /* allikas */
	     ELSE 'N/A' END AS ALLIKAS,
    JOB_NAME,
    STEP_NAME,
    CASE WHEN STEP_NAME='ETL_START' THEN 1 ELSE 0 END AS ALGUS_FLAG,
    CASE WHEN STEP_NAME='ETL_END' THEN 1 ELSE 0 END AS LÕPP_FLAG,
    CASE WHEN STEP_NAME='ETL_ERROR' THEN 1 ELSE 0 END AS VIGA_FLAG,
    STEP_NAME || ' : ' || STEP_DESCR AS ETAPI_LOG,
	SUBSTR( SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), 1, INSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), '***')-2 ) AS KÄSITLETUD_RRID
  FROM UTL_JOB_STATUS
  WHERE datetime(LOG_D, 'unixepoch') >= date('now', 'start of month', '-3 month')
)
SELECT JB.SID,
  JB.MIN_LOG_DT AS ALGUS_DT,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS LOG_DT,
  JB.ALLIKAS,
  JB.KIHT,
  JB.JOB_NAME,
  CASE
  WHEN JB.VIGA_FLAG = 1 THEN 'VIGA'
  WHEN JB.VIGA_FLAG = 0 AND JB.LÕPP_FLAG = 0 AND strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 THEN 'PEATUNUD' /* pool tundi */
  WHEN JB.VIGA_FLAG = 0 AND JB.LÕPP_FLAG = 0 THEN 'KÄIVITAMINE'
  ELSE 'HEA'
  END AS STAATUS,
  ERR.ETAPI_LOG     AS ETAPI_LOG,
  JB.CNT           AS ETAPI_CNT,
  JB.KÄSITLETUD_RRID AS KÄSITLETUD_RRID,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS TÖÖPROTSESSI_ALGUS_DT,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MAX_LOG_DT, 'unixepoch')) AS TÖÖPROTSESSI_LÕPP_DT,
  JB.MAX_LOG_DT - JB.MIN_LOG_DT AS TÖÖPROTSESSI_AEG_SEC
FROM
  ( SELECT SID, ALLIKAS, KIHT, JOB_NAME,
           MAX(UTL_JOB_STATUS_ID) AS UTL_JOB_STATUS_ID,
           MAX(ALGUS_FLAG)       AS ALGUS_FLAG,
           MAX(LÕPP_FLAG)         AS LÕPP_FLAG,
           MAX(VIGA_FLAG)       AS VIGA_FLAG,
           MIN(LOG_DT)           AS MIN_LOG_DT,
           MAX(LOG_DT)           AS MAX_LOG_DT,
           SUM(1)                AS CNT,
           SUM(IFNULL(KÄSITLETUD_RRID, 0)) AS KÄSITLETUD_RRID
    FROM SRC
    GROUP BY SID, ALLIKAS, KIHT, JOB_NAME
  ) JB,
  ( SELECT UTL_JOB_STATUS_ID, SID, JOB_NAME, ETAPI_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.ALLIKAS;

SQL Kontroll uue sessiooni numbri saamise võimaluse üle

SELECT SUM (
  CASE WHEN start_job.JOB_NAME IS NOT NULL AND end_job.JOB_NAME IS NULL /* eksisteerinud töö lõppenud */
	    AND NOT ( 'y' = 'n' ) /* jõudude taaskäivitamise 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' /* töö nime PARAMETER */
	    AND STEP_NAME = 'ETL_START'
      GROUP BY JOB_NAME, SID
    ) start_job /* algused */
  ON d_job.dummy = start_job.dummy
  LEFT OUTER JOIN
    ( SELECT JOB_NAME, SID
      FROM UTL_JOB_STATUS
      WHERE JOB_NAME = 'RPT_ACCESS_LOG'  /* töö nime PARAMETER */
	    AND STEP_NAME in ('ETL_END', 'ETL_ERROR') /* peatada olek */
      GROUP BY JOB_NAME, SID
    ) end_job /* lõpurajad */
  ON start_job.JOB_NAME = end_job.JOB_NAME
     AND start_job.SID = end_job.SID

Tabeli eripärad:

andmete töötlemise protsessi algus ja lõpp peaks olema ETL_START ja ETL_END etappidena
vea korral peaks olema ETL_ERROR etapp koos selle kirjeldusega
töödeldud andmete arvu peaks eraldama, näiteks tähekestega
samal ajal võib sama protseduuri käivitada parameetriga force_restart=y; ilma selleta antakse sessiooni number ainult lõpetatud protseduurile
tavalistes tingimustes ei saa sama andmete töötlemise protseduuri käivitada paralleelselt

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

ETL protsessi käivitusseansi numbri saamine
logi kirje lisamine tabelisse
viimase eduka ETL protsessi kirje saamine

Sellistes andmebaasides nagu Oracle või Postgres on neid toiminguid võimalik teostada sisseehitatud funktsioonide kaudu. SQLite puhul on vajalik väline mehhanism ja sel juhul on see prototüüpitud PHP abil.

Kokkuvõte

Seega mängivad vigade teated andmete töötlemise tööriistades üliolulist rolli. Kuid nende kiire leidmine probleemide põhjuse osas on keeruline. Kui protsesside arv läheneb sajale, muutub protsesside jälgimine keeruliseks projektiks.

Artiklis on toodud näide probleemide lahendamisest prototüübi vormis. Kogu väikese ladustamise prototüüp on saadaval gitlabis SQLite PHP ETL Utiliid.

Allikas: habr.com

ETL-protsesside jälgimine väikeses andmehuses

Tõhus jälgimine

ETL jälgimise tabel

Kokkuvõte