Monitorà i prucessi ETL in un picculu magazzinu di dati

Parechje persone utilizanu strumenti specializati per creà rutine per estrazione, trasfurmazioni è carica di dati in basa di dati relazionale. U prucessu di l'arnesi hè registratu, l'errori sò registrati.

In casu d'errore, u logu cuntene l'infurmazioni chì l'uttellu hà fiascatu à compie u compitu è ​​chì moduli (spessu java) si fermanu induve. L'ultimi linii ponu cuntene un errore di basa di dati, cum'è una violazione di a chjave unica di una tabella.

Per risponde à a quistione di u rolu di l'informazione di l'errore ETL, aghju classificatu tutti i prublemi chì sò accaduti in l'ultimi dui anni in un repository abbastanza grande.

L'errore di a basa di dati includenu cum'è: ùn ci era micca abbastanza spaziu, a cunnessione hè stata persa, a sessione chjappata, etc.

L'errori lògichi includenu violazioni di e chjave di a tavula, oggetti invalidi, mancanza d'accessu à l'uggetti, etc.
U pianificatore ùn pò esse lanciatu à tempu, pò congelate, etc.

Errori simplici ùn piglianu micca assai tempu per correggerà. Un bonu ETL pò trattà a maiò parte di elli per sè stessu.

L'errori cumplessi facenu necessariu apre è cuntrollà e prucedure di gestione di dati è scopre fonti di dati. Spessu porta à a necessità di pruvà cambiamenti è implementà.

Dunque, a mità di tutti i prublemi sò ligati à a basa di dati. U 48% di tutti l'errori sò errori simplici.
Un terzu di tutti i prublemi sò ligati à i cambiamenti in a logica o mudellu di almacenamiento; più di a mità di questi errori sò cumplessi.

È menu di un quartu di tutti i prublemi sò ligati à u pianificatore di u travagliu, u 18% di i quali sò errori simplici.

In generale, u 22% di tutti l'errori chì si trovanu sò cumplessi è necessitanu a più attenzione è u tempu per curregà. Succedenu circa una volta à settimana. Mentri i sbagli simplici succede quasi ogni ghjornu.

Ovviamente, i prucessi di monitorizazione ETL seranu efficaci quandu u locu di l'errore hè indicatu in u logu u più precisamente pussibule è u tempu minimu hè necessariu per truvà a fonte di u prublema.

Monitoraghju efficace

Chì vulia vede in u prucessu di monitoraghju ETL?

Cumincià à - quandu aghju cuminciatu à travaglià,
Fonte - fonte di dati,
Layer - quale livellu di almacenamentu hè caricatu,
ETL Job Name hè una prucedura di carica chì si compone di parechji picculi passi,
Step Number - numeru di u passu chì hè eseguitu,
Righe affettate - quantu dati sò digià stati trattati,
Duration sec - quantu tempu ci vole à eseguisce,
Status - sì tuttu hè bonu o micca: OK, ERRORE, RUNNING, HANGS
Missaghju - ultimu missaghju successu o descrizzione di errore.

Basatu nantu à u statutu di i registri, pudete mandà un email. lettera à altri participanti. Se ùn ci sò micca errore, ùn hè micca necessariu una lettera.

Questu modu, in casu d'errore, u locu di l'incidentu hè chjaramente indicatu.

Calchì volta si succèri chì u strumentu surviglianza stessa ùn travaglia. In questu casu, hè pussibule chjamà a vista (vista) direttamente in a basa di dati, nantu à a basa di quale u rapportu hè custruitu.

Tavola di monitoraghju ETL

Per implementà u monitoraghju di i prucessi ETL, una tavola è una vista sò abbastanza.

Per fà questu, pudete vultà u vostru propiu pocu almacenamentu è creà un prototipu in a basa di dati sqlite.

tabelle DDL

CREATE TABLE UTL_JOB_STATUS (
/* Table for logging of job execution log. Important that the job has the steps ETL_START and ETL_END or ETL_ERROR */
  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  SID               INTEGER NOT NULL DEFAULT -1, /* Session Identificator. Unique for every Run of job */
  LOG_DT            INTEGER NOT NULL DEFAULT 0,  /* Date time */
  LOG_D             INTEGER NOT NULL DEFAULT 0,  /* Date */
  JOB_NAME          TEXT NOT NULL DEFAULT 'N/A', /* Job name like JOB_STG2DM_GEO */
  STEP_NAME         TEXT NOT NULL DEFAULT 'N/A', /* ETL_START, ... , ETL_END/ETL_ERROR */
  STEP_DESCR        TEXT,                        /* Description of task or error message */
  UNIQUE (SID, JOB_NAME, STEP_NAME)
);
INSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);

Vede / rapportu DDL

CREATE VIEW IF NOT EXISTS UTL_JOB_STATUS_V
AS /* Content: Package Execution Log for last 3 Months. */
WITH SRC AS (
  SELECT LOG_D,
    LOG_DT,
    UTL_JOB_STATUS_ID,
    SID,
	CASE WHEN INSTR(JOB_NAME, 'FTP') THEN 'TRANSFER' /* file transfer */
	     WHEN INSTR(JOB_NAME, 'STG') THEN 'STAGE' /* stage */
	     WHEN INSTR(JOB_NAME, 'CLS') THEN 'CLEANSING' /* cleansing */
	     WHEN INSTR(JOB_NAME, 'DIM') THEN 'DIMENSION' /* dimension */
	     WHEN INSTR(JOB_NAME, 'FCT') THEN 'FACT' /* fact */
		 WHEN INSTR(JOB_NAME, 'ETL') THEN 'STAGE-MART' /* data mart */
	     WHEN INSTR(JOB_NAME, 'RPT') THEN 'REPORT' /* report */
	     ELSE 'N/A' END AS LAYER,
	CASE WHEN INSTR(JOB_NAME, 'ACCESS') THEN 'ACCESS LOG' /* source */
	     WHEN INSTR(JOB_NAME, 'MASTER') THEN 'MASTER DATA' /* source */
	     WHEN INSTR(JOB_NAME, 'AD-HOC') THEN 'AD-HOC' /* source */
	     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 'ERROR'
  WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 AND strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 THEN 'HANGS' /* half an hour */
  WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 THEN 'RUNNING'
  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 Verificate a capacità di ottene un novu numeru di sessione

SELECT SUM (
  CASE WHEN start_job.JOB_NAME IS NOT NULL AND end_job.JOB_NAME IS NULL /* existed job finished */
	    AND NOT ( 'y' = 'n' ) /* force restart 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' /* job name PARAMETER */
	    AND STEP_NAME = 'ETL_START'
      GROUP BY JOB_NAME, SID
    ) start_job /* starts */
  ON d_job.dummy = start_job.dummy
  LEFT OUTER JOIN
    ( SELECT JOB_NAME, SID
      FROM UTL_JOB_STATUS
      WHERE JOB_NAME = 'RPT_ACCESS_LOG'  /* job name PARAMETER */
	    AND STEP_NAME in ('ETL_END', 'ETL_ERROR') /* stop status */
      GROUP BY JOB_NAME, SID
    ) end_job /* ends */
  ON start_job.JOB_NAME = end_job.JOB_NAME
     AND start_job.SID = end_job.SID

Caratteristiche di a tavola:

• l'iniziu è a fine di a prucedura di trattamentu di dati deve esse accumpagnata da i passi ETL_START è ETL_END
• in casu d'errore, un passu ETL_ERROR deve esse creatu cù a so descrizzione
• a quantità di dati trattati deve esse evidenziata, per esempiu, cù asterischi
• a listessa prucedura pò esse iniziata à u stessu tempu cù u paràmetru force_restart = y; senza ellu, u numeru di sessione hè emessu solu à a prucedura cumpleta.
• in modu normale, hè impussibile di eseguisce a stessa prucedura di trattamentu di dati in parallelu

L'operazioni necessarie per travaglià cù a tavola sò i seguenti:

• ottene u numeru di sessione di a prucedura ETL chì hè stata lanciata
• inserisce una entrata di log in una tavula
• ottene l'ultimu record successu di una prucedura ETL

In basa di dati cum'è Oracle o Postgres, queste operazioni ponu esse implementate cù funzioni integrate. sqlite richiede un mecanismu esternu è in questu casu prototipu in PHP.

cunchiusioni

Cusì, u rapportu d'errore in i strumenti di trattamentu di dati ghjoca un rolu mega-impurtante. Ma ùn ponu esse chjamati ottimali per truvà rapidamente a causa di u prublema. Quandu u numeru di prucedure s'avvicina à un centu, u cuntrollu di u prucessu si trasforma in un prughjettu cumplessu.

L'articulu furnisce un esempiu di una suluzione pussibule à u prublema in a forma di un prototipu. Tuttu u prototipu di u picculu repository hè dispunibule in gitlab SQLite PHP ETL Utilities.

Source: www.habr.com