ProHoster > Bloc > Administració > Supervisió dels processos ETL en un petit magatzem de dades

Supervisió dels processos ETL en un petit magatzem de dades

Molts utilitzen eines especialitzades per crear procediments per extreure, transformar i carregar dades a bases de dades relacionals. Es registra el procés d'eines de treball, es corregeixen els errors.

En cas d'error, el registre conté informació que l'eina no ha pogut completar la tasca i quins mòduls (sovint java) s'han aturat on. A les últimes línies, podeu trobar un error de base de dades, per exemple, una violació de clau única de taula.

Per respondre a la pregunta de quin paper juga la informació d'error ETL, he classificat tots els problemes que s'han produït durant els darrers dos anys en un repositori força gran.

Supervisió dels processos ETL en un petit magatzem de dades

Els errors de la base de dades inclouen espai insuficient, connexió perduda, sessió bloquejada, etc.

Els errors lògics inclouen, com ara la violació de les claus de la taula, objectes no vàlids, manca d'accés als objectes, etc.
És possible que el planificador no s'iniciï a temps, es pugui congelar, etc.

Els errors senzills no triguen a corregir-se. Un bon ETL pot gestionar la majoria d'ells sol.

Els errors complexos fan necessari descobrir i provar procediments per treballar amb dades, per explorar fonts de dades. Sovint condueix a la necessitat de proves de canvi i desplegament.

Per tant, la meitat de tots els problemes estan relacionats amb la base de dades. El 48% de tots els errors són errors simples.
Un terç de tots els problemes estan relacionats amb el canvi de lògica o model d'emmagatzematge, més de la meitat d'aquests errors són complexos.

I menys d'una quarta part de tots els problemes estan relacionats amb el planificador de tasques, un 18% dels quals són errors simples.

En general, el 22% de tots els errors que es produeixen són complexos, i la seva correcció requereix més atenció i temps. Passen aproximadament un cop per setmana. Mentre que els errors simples passen gairebé cada dia.

Òbviament, el seguiment dels processos ETL serà efectiu quan la ubicació de l'error s'indiqui al registre amb la màxima precisió possible i es requereixi el temps mínim per trobar l'origen del problema.

Seguiment efectiu

Què volia veure en el procés de seguiment de l'ETL?

Supervisió dels processos ETL en un petit magatzem de dades
Comença a - quan va començar a treballar,
Font - font de dades,
Capa: quin nivell d'emmagatzematge s'està carregant,
Nom del treball ETL: procediment de càrrega, que consta de molts passos petits,
Número de pas: el número del pas que s'està realitzant,
Files afectades: quantes dades ja s'han processat,
Durada segons: quant de temps triga,
Estat: si tot està bé o no: OK, ERROR, RUNNING, HANGS
Missatge: darrer missatge correcte o descripció de l'error.

En funció de l'estat dels registres, podeu enviar un correu electrònic. carta als altres membres. Si no hi ha errors, la carta no és necessària.

Així, en cas d'error, s'indica clarament el lloc de la incidència.

De vegades passa que l'eina de supervisió en si mateixa no funciona. En aquest cas, és possible cridar una vista (vista) directament a la base de dades, a partir de la qual es construeix l'informe.

Taula de seguiment ETL

Per implementar el seguiment dels processos ETL, n'hi ha prou amb una taula i una vista.

Per fer-ho, podeu tornar a el teu petit emmagatzematge i crear un prototip a la base de dades sqlite.

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

Visualitza/informa 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 Comprovant si és possible obtenir un número de sessió nou

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

Característiques de la taula:

  • l'inici i el final del procediment de tractament de dades han de seguir els passos ETL_START i ETL_END
  • en cas d'error, s'ha de crear el pas ETL_ERROR amb la seva descripció
  • la quantitat de dades processades s'ha de destacar, per exemple, amb asteriscs
  • el mateix procediment es pot iniciar al mateix temps amb el paràmetre force_restart=y, sense ell, el número de sessió només s'emet al procediment completat
  • en mode normal, no podeu executar el mateix procediment de processament de dades en paral·lel

Les operacions necessàries per treballar amb una taula són les següents:

  • obtenint el número de sessió del procediment ETL en execució
  • inseriu l'entrada de registre a la taula
  • obtenir l'últim registre satisfactori d'un procediment ETL

En bases de dades com Oracle o Postgres, aquestes operacions es poden implementar com a funcions integrades. sqlite requereix un mecanisme extern, i en aquest cas prototipat en PHP.

Sortida

Així, els missatges d'error de les eines de processament de dades tenen un paper molt important. Però és difícil anomenar-los òptims per trobar ràpidament la causa del problema. Quan el nombre de procediments s'acosta al centenar, el seguiment del procés es converteix en un projecte complex.

L'article ofereix un exemple d'una possible solució al problema en forma de prototip. Tot el petit prototip de repositori està disponible a gitlab Utilitats SQLite PHP ETL.

Font: www.habr.com

Afegeix comentari