Monitorado de ETL-procezoj en malgranda datumstokejo

Multaj homoj uzas specialajn ilojn por krei rutinojn por ĉerpi, transformi kaj ŝarĝi datumojn en interrilatajn datumbazojn. La procezo de la iloj estas registrita, eraroj estas registritaj.

En kazo de eraro, la protokolo enhavas informojn, ke la ilo ne plenumis la taskon kaj kiuj moduloj (ofte java) haltis kie. La lastaj linioj povas enhavi datumbazan eraron, kiel malobservon de unika ŝlosilo de tabelo.

Por respondi la demandon pri kia rolo ludas ETL-eraraj informoj, mi klasifikis ĉiujn problemojn, kiuj okazis dum la lastaj du jaroj, en sufiĉe granda deponejo.

Datumbazaj eraroj inkluzivas tiajn: ne estis sufiĉe da spaco, la konekto estis perdita, la sesio pendis, ktp.

Logikaj eraroj inkluzivas malobservojn de tabelŝlosiloj, nevalidajn objektojn, mankon de aliro al objektoj, ktp.
La planilo eble ne estas lanĉita ĝustatempe, eble frostiĝos, ktp.

Simplaj eraroj ne bezonas multe da tempo por korekti. Bona ETL povas trakti la plej multajn el ili memstare.

Kompleksaj eraroj faras necesa malfermi kaj kontroli procedurojn pri datumtraktado kaj esplori datumfontojn. Ofte kondukas al la bezono testi ŝanĝojn kaj deploji.

Do duono de ĉiuj problemoj rilatas al la datumbazo. 48% de ĉiuj eraroj estas simplaj eraroj.
Triono de ĉiuj problemoj rilatas al ŝanĝoj en la stokadlogiko aŭ modelo; pli ol duono de ĉi tiuj eraroj estas kompleksaj.

Kaj malpli ol kvarono de ĉiuj problemoj rilatas al la taskoplanilo, 18% el kiuj estas simplaj eraroj.

Ĝenerale, 22% de ĉiuj eraroj kiuj okazas estas kompleksaj kaj postulas la plej grandan atenton kaj tempon por korekti. Ili okazas proksimume unufoje semajne. Dum simplaj eraroj okazas preskaŭ ĉiutage.

Evidente, monitorado de ETL-procezoj estos efika kiam la loko de la eraro estas indikita en la protokolo kiel eble plej precize kaj necesas minimuma tempo por trovi la fonton de la problemo.

Efika monitorado

Kion mi volis vidi en la procezo de monitorado de ETL?

Komencu je - kiam mi eklaboris,
Fonto - datumfonto,
Tavolo - kiu stoka nivelo estas ŝarĝita,
ETL Job Name estas ŝarĝa proceduro kiu konsistas el multaj malgrandaj paŝoj,
Paŝo-Numero - nombro de la paŝo plenumata,
Trafitaj Vicoj - kiom da datumoj jam estis prilaboritaj,
Daŭro sek - kiom da tempo necesas por ekzekuti,
Statuso - ĉu ĉio estas bona aŭ ne: OK, ERARO, RUNNING, HANGS
Mesaĝo — lasta sukcesa mesaĝo aŭ erara priskribo.

Surbaze de la stato de la registroj, vi povas sendi retpoŝton. letero al aliaj partoprenantoj. Se ne estas eraroj, tiam letero ne estas necesa.

Tiel, en kazo de eraro, la loko de la okazaĵo estas klare indikita.

Kelkfoje okazas, ke la monitora ilo mem ne funkcias. En ĉi tiu kazo, eblas nomi la vido (vido) rekte en la datumbazo, surbaze de kiu la raporto estas konstruita.

ETL-monitora tablo

Por efektivigi monitoradon de ETL-procezoj, sufiĉas unu tablo kaj unu vido.

Por fari tion vi povas reveni al via propra eta stokado kaj krei prototipon en sqlite datumbazo.

DDL-tabeloj

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);

Vidi/raporti 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 Kontrolante la kapablon akiri novan sean numeron

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

Tabelaj Trajtoj:

• la komenco kaj fino de la proceduro pri datumtraktado devas esti akompanitaj de la paŝoj ETL_START kaj ETL_END
• en kazo de eraro, ETL_ERROR-paŝo devus esti kreita kun ĝia priskribo
• la kvanto de prilaboritaj datumoj devus esti reliefigita, ekzemple, per asteriskoj
• la sama proceduro povas esti komencita samtempe kun la parametro force_restart=y; sen ĝi, la seanca numero estas eldonita nur al la finita proceduro
• en normala reĝimo estas neeble ruli la saman datumtraktadproceduron paralele

La necesaj operacioj por labori kun la tablo estas la jenaj:

• ricevante la sean numeron de la ETL-proceduro lanĉita
• enmetante protokolan eniron en tabelon
• ricevante la lastan sukcesan rekordon de ETL-proceduro

En datumbazoj kiel Oracle aŭ Postgres, ĉi tiuj operacioj povas esti efektivigitaj kun enkonstruitaj funkcioj. sqlite postulas eksteran mekanismon kaj en ĉi tiu kazo ĝi prototipite en PHP.

konkludo

Tiel, erarraportado en datumtraktaj iloj ludas mega-gravan rolon. Sed ili apenaŭ povas esti nomataj optimumaj por rapide trovi la kaŭzon de la problemo. Kiam la nombro da proceduroj alproksimiĝas al cent, proceza monitorado fariĝas kompleksa projekto.

La artikolo donas ekzemplon de ebla solvo al la problemo en formo de prototipo. La tuta prototipo de la malgranda deponejo estas havebla en gitlab SQLite PHP ETL Utiloj.

fonto: www.habr.com