ProHoster > Blog > Pangangasiwa > Pagsubaybay sa mga proseso ng ETL sa isang maliit na warehouse ng data

Pagsubaybay sa mga proseso ng ETL sa isang maliit na warehouse ng data

Maraming gumagamit ng mga espesyal na tool upang lumikha ng mga pamamaraan para sa pagkuha, pagbabago, at paglo-load ng data sa mga relational na database. Ang proseso ng mga tool sa pagtatrabaho ay naka-log, ang mga error ay naayos.

Sa kaso ng isang error, ang log ay naglalaman ng impormasyon na nabigo ang tool upang makumpleto ang gawain at kung aling mga module (madalas na java) ang huminto kung saan. Sa mga huling linya, makakahanap ka ng error sa database, halimbawa, isang paglabag sa natatanging key ng talahanayan.

Upang masagot ang tanong kung ano ang papel na ginagampanan ng impormasyon ng error sa ETL, inuri ko ang lahat ng mga problemang naganap sa nakalipas na dalawang taon sa isang medyo malaking repositoryo.

Pagsubaybay sa mga proseso ng ETL sa isang maliit na warehouse ng data

Kasama sa mga error sa database ang hindi sapat na espasyo, nawalang koneksyon, nakabitin ang session, atbp.

Kasama sa mga lohikal na error ang tulad ng paglabag sa mga key ng talahanayan, hindi wastong mga bagay, kawalan ng access sa mga bagay, atbp.
Maaaring hindi magsimula ang scheduler sa oras, maaaring mag-hang, atbp.

Ang mga simpleng pagkakamali ay hindi nagtatagal upang ayusin. Kakayanin ng isang mahusay na ETL ang karamihan sa mga ito nang mag-isa.

Ginagawa ng mga kumplikadong bug na matuklasan at subukan ang mga pamamaraan para sa pagtatrabaho sa data, upang galugarin ang mga mapagkukunan ng data. Kadalasan ay humahantong sa pangangailangan para sa pagsubok ng pagbabago at pag-deploy.

Kaya, kalahati ng lahat ng mga problema ay nauugnay sa database. 48% ng lahat ng pagkakamali ay mga simpleng pagkakamali.
Ang ikatlong bahagi ng lahat ng mga problema ay nauugnay sa pagbabago ng lohika o modelo ng imbakan, higit sa kalahati ng mga error na ito ay kumplikado.

At mas mababa sa isang-kapat ng lahat ng mga problema ay nauugnay sa scheduler ng gawain, 18% nito ay mga simpleng error.

Sa pangkalahatan, 22% ng lahat ng mga error na nagaganap ay kumplikado, at ang kanilang pagwawasto ay nangangailangan ng higit na pansin at oras. Nangyayari sila halos isang beses sa isang linggo. Samantalang ang mga simpleng pagkakamali ay nangyayari halos araw-araw.

Malinaw na ang pagsubaybay sa mga proseso ng ETL ay magiging epektibo kapag ang lokasyon ng error ay ipinahiwatig sa log nang tumpak hangga't maaari at ang pinakamababang oras ay kinakailangan upang mahanap ang pinagmulan ng problema.

Epektibong pagsubaybay

Ano ang gusto kong makita sa proseso ng pagsubaybay sa ETL?

Pagsubaybay sa mga proseso ng ETL sa isang maliit na warehouse ng data
Magsimula sa - noong nagsimula siyang magtrabaho,
Pinagmulan - pinagmumulan ng data,
Layer - kung anong antas ng imbakan ang nilo-load,
Pangalan ng Trabaho ng ETL - pamamaraan sa pag-upload, na binubuo ng maraming maliliit na hakbang,
Numero ng Hakbang - ang bilang ng hakbang na ginagawa,
Mga Apektadong Hanay - kung gaano karaming data ang naproseso na,
Tagal seg - gaano katagal,
Status - maayos man ang lahat o hindi: OK, ERROR, RUNNING, HANGS
Mensahe - Huling matagumpay na mensahe o paglalarawan ng error.

Batay sa katayuan ng mga entry, maaari kang magpadala ng email. sulat sa ibang miyembro. Kung walang mga pagkakamali, kung gayon ang liham ay hindi kinakailangan.

Kaya, sa kaganapan ng isang error, ang lokasyon ng insidente ay malinaw na ipinahiwatig.

Minsan nangyayari na ang tool sa pagsubaybay mismo ay hindi gumagana. Sa kasong ito, posible na tumawag ng isang view (view) nang direkta sa database, batay sa kung saan binuo ang ulat.

ETL monitoring table

Para ipatupad ang pagsubaybay sa mga proseso ng ETL, sapat na ang isang talahanayan at isang view.

Upang gawin ito, maaari kang bumalik sa ang iyong maliit na imbakan at lumikha ng prototype sa sqlite database.

Mga talahanayan ng 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);

Tingnan/Iulat ang 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 Checking kung posibleng makakuha ng bagong session number

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

Mga tampok ng talahanayan:

  • ang pagsisimula at pagtatapos ng pamamaraan sa pagpoproseso ng data ay dapat na sundan ng mga hakbang na ETL_START at ETL_END
  • sa kaso ng isang error, ang ETL_ERROR na hakbang kasama ang paglalarawan nito ay dapat gawin
  • ang dami ng naprosesong data ay dapat na naka-highlight, halimbawa, na may mga asterisk
  • ang parehong pamamaraan ay maaaring magsimula sa parehong oras gamit ang force_restart=y parameter, kung wala ito ang numero ng session ay ibinibigay lamang sa nakumpletong pamamaraan
  • sa normal na mode, hindi mo maaaring patakbuhin ang parehong pamamaraan ng pagproseso ng data nang magkatulad

Ang mga kinakailangang operasyon para sa pagtatrabaho sa isang talahanayan ay ang mga sumusunod:

  • pagkuha ng numero ng session ng tumatakbong pamamaraan ng ETL
  • ipasok ang log entry sa talahanayan
  • pagkuha ng huling matagumpay na record ng isang ETL procedure

Sa mga database tulad ng Oracle o Postgres, ang mga operasyong ito ay maaaring ipatupad bilang mga built-in na function. Ang sqlite ay nangangailangan ng isang panlabas na mekanismo, at sa kasong ito ito prototype sa PHP.

Pagbubuhos

Kaya, ang mga mensahe ng error sa mga tool sa pagpoproseso ng data ay may mahalagang papel. Ngunit mahirap tawagan silang pinakamainam para sa mabilis na paghahanap ng sanhi ng problema. Kapag ang bilang ng mga pamamaraan ay lumalapit sa isang daan, ang pagsubaybay sa proseso ay nagiging isang kumplikadong proyekto.

Ang artikulo ay nagbibigay ng isang halimbawa ng isang posibleng solusyon sa problema sa anyo ng isang prototype. Ang buong maliit na prototype ng repositoryo ay magagamit sa gitlab Mga Utility ng SQLite PHP ETL.

Pinagmulan: www.habr.com

Magdagdag ng komento