Мониторинг на ETL процеси в малък склад за данни

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

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

За да отговоря на въпроса каква роля играе информацията за грешка в ETL, класифицирах всички проблеми, възникнали през последните две години в доста голямо хранилище.

Грешките в базата данни включват недостатъчно място, загубена връзка, прекъсване на сесията и др.

Логическите грешки включват нарушаване на ключовете на таблицата, невалидни обекти, липса на достъп до обекти и др.
Планировчикът може да не стартира навреме, да замръзне и т.н.

Простите грешки не отнемат много време за отстраняване. Един добър ETL може да се справи с повечето от тях сам.

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

Така че половината от всички проблеми са свързани с базата данни. 48% от всички грешки са прости грешки.
Една трета от всички проблеми са свързани с промяна на логиката или модела на съхранение, повече от половината от тези грешки са сложни.

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

Като цяло 22% от всички възникнали грешки са сложни и коригирането им изисква най-много внимание и време. Те се случват около веднъж седмично. Докато прости грешки се случват почти всеки ден.

Очевидно наблюдението на 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.

Източник: www.habr.com