ProHoster > блог > Администрација > Следење на процесите на ETL во мало складиште на податоци

Следење на процесите на ETL во мало складиште на податоци

Многу луѓе користат специјализирани алатки за да креираат рутини за извлекување, трансформирање и вчитување податоци во релациони бази на податоци. Процесот на алатките е евидентиран, грешките се евидентираат.

Во случај на грешка, дневникот содржи информации дека алатката не успеала да ја заврши задачата и кои модули (често java) каде запреле. Последните редови може да содржат грешка во базата на податоци, како што е прекршување на единствениот клуч на табелата.

За да одговорам на прашањето каква улога играат информациите за грешка на ETL, ги класифицирав сите проблеми што се појавија во текот на изминатите две години во прилично големо складиште.

Следење на процесите на ETL во мало складиште на податоци

Грешките во базата на податоци вклучуваат: немаше доволно простор, врската е изгубена, сесијата висеше итн.

Логичките грешки вклучуваат прекршување на копчињата на табелата, неважечки објекти, недостаток на пристап до објекти итн.
Распоредувачот може да не се активира навреме, може да замрзне итн.

Едноставните грешки не одземаат многу време за да се поправат. Еден добар ETL може сам да се справи со повеќето од нив.

Комплексните грешки го прават неопходно отворање и проверка на процедурите за ракување со податоци и истражување на изворите на податоци. Честопати доведуваат до потреба да се тестираат промените и да се распоредат.

Значи, половина од сите проблеми се поврзани со базата на податоци. 48% од сите грешки се едноставни грешки.
Третина од сите проблеми се поврзани со промените во логиката или моделот за складирање, повеќе од половина од овие грешки се сложени.

И помалку од една четвртина од сите проблеми се поврзани со распоредувачот на задачи, од кои 18% се едноставни грешки.

Севкупно, 22% од сите грешки што се појавуваат се сложени и бараат најмногу внимание и време за да се поправат. Тие се случуваат околу еднаш неделно. Додека едноставните грешки се случуваат речиси секој ден.

Очигледно, следењето на процесите на ETL ќе биде ефективно кога локацијата на грешката е наведена во дневникот што е можно попрецизно и потребно е минимално време за да се најде изворот на проблемот.

Ефективно следење

Што сакав да видам во процесот на следење на ETL?

Следење на процесите на ETL во мало складиште на податоци
Започнете во - кога почнав да работам,
Извор - извор на податоци,
Слој - кое ниво на складирање е вчитано,
ETL Job Name е процедура за вчитување која се состои од многу мали чекори,
Број на чекор - број на чекор што се извршува,
Погодени редови - колку податоци се веќе обработени,
Времетраење сек - колку време е потребно за да се изврши,
Статус - дали сè е добро или не: ОК, ГРЕШКА, ТРЧАЊЕ, виси
Порака - последна успешна порака или опис на грешка.

Врз основа на статусот на записите, можете да испратите е-пошта. писмо до другите учесници. Ако нема грешки, тогаш буквата не е потребна.

На овој начин, во случај на грешка, јасно се означува локацијата на инцидентот.

Понекогаш се случува самата алатка за следење да не работи. Во овој случај, можно е да се повика погледот (погледот) директно во базата на податоци, врз основа на кој е изграден извештајот.

Табела за следење на ETL

За спроведување на мониторинг на ETL процесите, доволно е една табела и еден поглед.

За да го направите ова, можете да се вратите на сопствено мало складирање и креирајте прототип во базата на податоци sqlite.

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

Прикажи/пријави 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 Проверка на способноста да се добие нов број на сесија

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

Карактеристики на табелата:

  • почетокот и крајот на постапката за обработка на податоци мора да бидат придружени со чекорите ETL_START и ETL_END
  • во случај на грешка, треба да се креира чекор ETL_ERROR со неговиот опис
  • количината на обработени податоци треба да се означи, на пример, со ѕвездички
  • истата постапка може да се започне истовремено со параметарот force_restart=y без него, бројот на сесијата се издава само на завршената процедура;
  • во нормален режим невозможно е паралелно да се води истата процедура за обработка на податоци

Потребните операции за работа со табелата се следните:

  • добивање на бројот на сесијата на ETL процедурата што се започнува
  • вметнување на запис од дневник во табела
  • добивање на последната успешна евиденција на ETL постапка

Во базите на податоци како Oracle или Postgres, овие операции може да се имплементираат со вградени функции. sqlite бара надворешен механизам и во овој случај тоа прототип во PHP.

Излез

Така, известувањето за грешки во алатките за обработка на податоци игра мега-важна улога. Но, тие тешко можат да се наречат оптимални за брзо наоѓање на причината за проблемот. Кога бројот на процедури се приближува до сто, следењето на процесот се претвора во сложен проект.

Написот дава пример за можно решение на проблемот во форма на прототип. Целиот прототип на малото складиште е достапен во gitlab SQLite PHP ETL Utilities.

Извор: www.habr.com

Додадете коментар