ProHoster > Blog > Bestjoer > Monitoring ETL prosessen yn in lyts data warehouse

Monitoring ETL prosessen yn in lyts data warehouse

In protte brûke spesjalisearre ark om prosedueres te meitsjen foar it ekstrahearjen, transformearjen en laden fan gegevens yn relationele databases. It proses fan wurkjende ark wurdt oanmeld, flaters wurde repareare.

Yn gefal fan in flater befettet it log ynformaasje dat it ark net slagge om de taak te foltôgjen en hokker modules (faak java) stoppe wêr. Yn de lêste rigels, kinne jo fine in databank flater, Bygelyks, in tabel unike kaai violation.

Om de fraach te beantwurdzjen fan hokker rol ETL-flaterynformaasje spilet, haw ik alle problemen yn 'e ôfrûne twa jier yn in frij grut repository klassifisearre.

Monitoring ETL prosessen yn in lyts data warehouse

Databankflaters omfetsje net genôch romte, ferlerne ferbining, sesje hong, ensfh.

Logyske flaters omfetsje lykas skending fan tabelkaaien, net-jildich objekten, gebrek oan tagong ta objekten, ensfh.
De planner kin miskien net op 'e tiid begjinne, it kin befrieze, ensfh.

Ienfâldige flaters nimme net lang om te reparearjen. In goede ETL kin de measte fan har op har eigen omgean.

Komplekse bugs meitsje it nedich om prosedueres te ûntdekken en te testen foar it wurkjen mei gegevens, om gegevensboarnen te ferkennen. Faak liede ta de needsaak foar feroaring testen en ynset.

Dat, de helte fan alle problemen is relatearre oan de databank. 48% fan alle flaters binne ienfâldige flaters.
In tredde fan alle problemen binne relatearre oan it feroarjen fan de opslachlogika of model, mear as de helte fan dizze flaters binne kompleks.

En minder as in kwart fan alle problemen binne relatearre oan de taakplanner, wêrfan 18% ienfâldige flaters binne.

Yn 't algemien binne 22% fan alle flaters dy't foarkomme kompleks, en har korreksje fereasket de measte oandacht en tiid. Se komme sawat ien kear yn 'e wike. Wylst ienfâldige flaters hast alle dagen barre.

It is fanselssprekkend dat it kontrolearjen fan ETL-prosessen effektyf sil wêze as de lokaasje fan 'e flater sa sekuer mooglik yn it log wurdt oanjûn en de minimale tiid is nedich om de boarne fan it probleem te finen.

Effektive tafersjoch

Wat woe ik sjen yn it ETL-monitoringsproses?

Monitoring ETL prosessen yn in lyts data warehouse
Begjin by - doe't hy begon te wurkjen,
Boarne - gegevensboarne,
Laach - hokker nivo fan opslach wurdt laden,
ETL Job Name - uploadproseduere, dy't bestiet út in protte lytse stappen,
Stapnûmer - it nûmer fan 'e stap dy't wurdt útfierd,
Beynfloede rigen - hoefolle gegevens is al ferwurke,
Duration sek - hoe lang it duorret,
Status - oft alles goed is of net: OK, ERROR, RUNNING, HANGS
Berjocht - Lêste suksesfolle berjocht as flaterbeskriuwing.

Op grûn fan 'e status fan' e records kinne jo in e-post stjoere. brief oan oare leden. As der gjin flaters binne, dan is de brief net nedich.

Sa wurdt by in flater de lokaasje fan it ynsidint dúdlik oanjûn.

Soms bart it dat it tafersjoch ark sels net wurket. Yn dit gefal is it mooglik om in werjefte (werjefte) direkt yn 'e databank te neamen, op basis wêrfan it rapport is boud.

ETL monitoring tabel

Om tafersjoch op ETL-prosessen út te fieren, binne ien tabel en ien werjefte genôch.

Om dit te dwaan, kinne jo weromgean nei dyn lytse opslach en meitsje prototype yn sqlite-database.

DDL tabellen

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

Besjoch / rapportearje 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 Kontrolearje oft it mooglik is om in nij sesjenûmer te krijen

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

Tabel funksjes:

  • it begjin en ein fan de gegevensferwurkingsproseduere moatte wurde folge troch de stappen ETL_START en ETL_END
  • yn gefal fan in flater moat de ETL_ERROR-stap mei syn beskriuwing oanmakke wurde
  • de hoemannichte ferwurke gegevens moat markearre wurde, bygelyks mei asterisken
  • deselde proseduere kin tagelyk wurde begon mei de parameter force_restart=y, sûnder it wurdt it sesjenûmer allinich útjûn foar de foltôge proseduere
  • yn normale modus, kinne jo net rinne deselde gegevens ferwurkjen proseduere parallel

De nedige operaasjes foar it wurkjen mei in tafel binne as folget:

  • it sesjenûmer krije fan 'e rinnende ETL-proseduere
  • ynfoegje log yngong yn tabel
  • it lêste suksesfolle rekord fan in ETL-proseduere krije

Yn databases lykas Oracle of Postgres kinne dizze operaasjes wurde ymplementearre as ynboude funksjes. sqlite fereasket in ekstern meganisme, en yn dit gefal it prototype yn PHP.

konklúzje

Sa spylje flaterberjochten yn ark foar gegevensferwurking in mega-wichtige rol. Mar it is lestich om se optimaal te neamen om de oarsaak fan it probleem fluch te finen. As it oantal prosedueres tichterby hûndert komt, dan feroaret prosesmonitoring yn in kompleks projekt.

It artikel jout in foarbyld fan in mooglike oplossing foar it probleem yn 'e foarm fan in prototype. It hiele lytse repository prototype is beskikber yn gitlab SQLite PHP ETL Utilities.

Boarne: www.habr.com

Add a comment