ProHoster > Blog > uprava > Praćenje ETL procesa u malom skladištu podataka

Praćenje ETL procesa u malom skladištu podataka

Mnogi koriste specijalizirane alate za izradu postupaka za izdvajanje, transformiranje i učitavanje podataka u relacijske baze podataka. Proces rada alata se bilježi, greške se popravljaju.

U slučaju pogreške, zapisnik sadrži informacije da alat nije uspio izvršiti zadatak i koji su moduli (često java) gdje stali. U posljednjim redcima možete pronaći pogrešku baze podataka, na primjer, kršenje jedinstvenog ključa tablice.

Kako bih odgovorio na pitanje kakvu ulogu imaju informacije o pogrešci ETL-a, klasificirao sam sve probleme koji su se dogodili u posljednje dvije godine u prilično veliko spremište.

Praćenje ETL procesa u malom skladištu podataka

Pogreške baze podataka uključuju nedovoljno prostora, izgubljenu vezu, prekid sesije itd.

Logičke pogreške uključuju kao što su kršenje ključeva tablice, nevažeći objekti, nedostatak pristupa objektima itd.
Planer se možda neće pokrenuti na vrijeme, može se zamrznuti itd.

Ispravljanje jednostavnih grešaka ne traje dugo. Dobar ETL može sam riješiti većinu njih.

Složeni bugovi zahtijevaju otkrivanje i testiranje postupaka za rad s podacima, istraživanje izvora podataka. Često dovode do potrebe za testiranjem promjena i implementacijom.

Dakle, polovica svih problema povezana je s bazom podataka. 48% svih pogrešaka su jednostavne pogreške.
Trećina svih problema povezana je s promjenom logike ili modela pohrane, više od polovice tih pogrešaka je složeno.

Manje od četvrtine svih problema povezano je s planerom zadataka, od čega su 18% jednostavne pogreške.

Općenito, 22% svih pogrešaka koje se pojave su složene, a njihovo ispravljanje zahtijeva najviše pažnje i vremena. Događaju se otprilike jednom tjedno. Dok se jednostavne pogreške događaju gotovo svaki dan.

Očito je da će praćenje ETL procesa biti učinkovito kada je mjesto pogreške naznačeno u zapisniku što je točnije moguće i potrebno je minimalno vrijeme da se pronađe izvor problema.

Učinkovit nadzor

Što sam želio vidjeti u procesu praćenja ETL-a?

Praćenje ETL procesa u malom skladištu podataka
Početi u - kada je počeo raditi,
Izvor - izvor podataka,
Sloj - koja se razina pohrane učitava,
ETL Job Name - postupak učitavanja, koji se sastoji od mnogo malih koraka,
Broj koraka - broj koraka koji se izvodi,
Zahvaćeni redovi - koliko je podataka već obrađeno,
Trajanje u sekundama - koliko traje,
Status - je li sve u redu ili ne: OK, GREŠKA, RADI, VISI
Poruka - Zadnja uspješna poruka ili opis greške.

Na temelju statusa unosa možete poslati e-poštu. pismo drugim članovima. Ako nema grešaka, tada pismo nije potrebno.

Tako je u slučaju pogreške jasno naznačeno mjesto incidenta.

Ponekad se dogodi da sam alat za praćenje ne radi. U ovom slučaju moguće je pozvati pregled (pregled) izravno u bazi podataka, na temelju kojeg se gradi izvješće.

ETL nadzorna tablica

Za implementaciju praćenja ETL procesa dovoljna je jedna tablica i jedan pogled.

Da biste to učinili, možete se vratiti na tvoja mala pohrana i stvoriti prototip u sqlite bazi podataka.

DDL tablice

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

Pregledaj/prijavi 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 Provjera je li moguće dobiti novi broj sesije

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

Značajke stola:

  • nakon početka i završetka postupka obrade podataka moraju uslijediti koraci ETL_START i ETL_END
  • u slučaju greške treba kreirati korak ETL_ERROR sa svojim opisom
  • količina obrađenih podataka treba biti istaknuta npr. zvjezdicama
  • ista procedura se može pokrenuti u isto vrijeme s parametrom force_restart=y, bez njega se broj sesije izdaje samo završenoj proceduri
  • u normalnom načinu rada, ne možete pokrenuti isti postupak obrade podataka paralelno

Potrebne operacije za rad s tablicom su sljedeće:

  • dobivanje broja sesije pokrenute ETL procedure
  • umetni unos dnevnika u tablicu
  • dobivanje posljednjeg uspješnog zapisa ETL procedure

U bazama podataka kao što su Oracle ili Postgres, ove se operacije mogu implementirati kao ugrađene funkcije. sqlite zahtijeva vanjski mehanizam, au ovom slučaju to je prototipiziran u PHP-u.

Izlaz

Stoga poruke o pogreškama u alatima za obradu podataka igraju megavažnu ulogu. Ali teško ih je nazvati optimalnim za brzo pronalaženje uzroka problema. Kada se broj postupaka približi stotini, tada praćenje procesa postaje složen projekt.

U članku se daje primjer mogućeg rješenja problema u obliku prototipa. Cijeli prototip malog repozitorija dostupan je u gitlabu SQLite PHP ETL pomoćni programi.

Izvor: www.habr.com

Dodajte komentar