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

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

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

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

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

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

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

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

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

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

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

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

Ефективен мониторинг

Какво бих искал да видя в процеса на мониторинг на ETL?

Start at — кога е започнала работата,
Source — източник на данни,
Layer — на какво ниво от хранилището се зарежда,
ETL Име на работата — процедура за зареждане, която се състои от множество малки стъпки,
Номер на стъпката — номер на изпълняваната стъпка,
Засегнати редове — колко данни вече са обработени,
Продължителност в секунди — колко дълго се изпълнява,
Състояние — всичко ли е наред или не: OK, ERROR, RUNNING, HANGS
Съобщение — последното успешно съобщение или описание на грешката.

На основание на състоянието на записите може да се изпрати имейл на другите участници. Ако няма грешки, то и имейлът не е задължителен.

По този начин, в случай на грешка, ясно е посочено мястото на инцидента.

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

Таблица за наблюдение на ETL

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

За това можете да се върнете към вашето малко хранилище и да създадете прототип в базата данни sqlite.

DDL таблица

СЪЗДАЙ ТАБЛИЦА UTL_JOB_STATUS (
/* Таблица за логване на изпълнението на задачи. Важно е, че задачата има стъпките ETL_START и ETL_END или ETL_ERROR */
  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  SID               INTEGER NOT NULL DEFAULT -1, /* Идентификатор на сесията. Уникален за всяко изпълнение на задачата */
  LOG_DT            INTEGER NOT NULL DEFAULT 0,  /* Дата и час */
  LOG_D             INTEGER NOT NULL DEFAULT 0,  /* Дата */
  JOB_NAME          TEXT NOT NULL DEFAULT 'N/A', /* Име на задачата, например JOB_STG2DM_GEO */
  STEP_NAME         TEXT NOT NULL DEFAULT 'N/A', /* ETL_START, ... , ETL_END/ETL_ERROR */
  STEP_DESCR        TEXT,                        /* Описание на задачата или съобщение за грешка */
  UNIQUE (SID, JOB_NAME, STEP_NAME)
);
ВМЕСТИ В UTL_JOB_STATUS (UTL_JOB_STATUS_ID) СТОЙНОСТИ (-1);

DDL представяне/отчет

СЪЗДАВАНЕ НА ПРЕГЛЕД, АКО НЕ СЪЩЕСТВУВА UTL_JOB_STATUS_V
КАТО 
/* Съдържание: Лог за изпълнение на пакета за последните 3 месеца. */
С СРЦ КАТО (
  ИЗБЕРЕТЕ LOG_D,
    LOG_DT,
    UTL_JOB_STATUS_ID,
    SID,
	КАЗВАЙКИ, КОГАТО INSTR(JOB_NAME, 'FTP') ТОГАВА 'ПРЕНАСЯНЕ' /* пренос на файл */
	     КОГАТО INSTR(JOB_NAME, 'STG') ТОГАВА 'СТАЖ' /* етап */
	     КОГАТО INSTR(JOB_NAME, 'CLS') ТОГАВА 'ПОДЧИСТВАНЕ' /* пречистване */
	     КОГАТО INSTR(JOB_NAME, 'DIM') ТОГАВА 'ИЗМЕРЕНИЕ' /* измерение */
	     КОГАТО INSTR(JOB_NAME, 'FCT') ТОГАВА 'ФАКТ' /* факт */
		 КОГАТО INSTR(JOB_NAME, 'ETL') ТОГАВА 'СТАЖ-МАРТ' /* данни за марти /
	     КОГАТО INSTR(JOB_NAME, 'RPT') ТОГАВА 'ДОКЛАД' /* доклад */
	     ИНАК 'N/A' КРАЙ КАТО СЛОЙ,
	КАЗВАЙКИ, КОГАТО INSTR(JOB_NAME, 'ACCESS') ТОГАВА 'ДОСТЪП ДО ЛОГ' /* източник */
	     КОГАТО INSTR(JOB_NAME, 'MASTER') ТОГАВА 'МАСТЕР ДАННИ' /* източник */
	     КОГАТО INSTR(JOB_NAME, 'AD-HOC') ТОГАВА 'AD-HOC' /* източник */
	     ИНАК 'N/A' КРАЙ КАТО ИЗТОЧНИК,
    JOB_NAME,
    STEP_NAME,
    КАЗВАЙКИ, КОГА STEP_NAME='ETL_START' ТОГАВА 1 ИНАЧЕ 0 КАТО START_FLAG,
    КАЗВАЙКИ, КОГА STEP_NAME='ETL_END' ТОГАВА 1 ИНАЧЕ 0 КАТО END_FLAG,
    КАЗВАЙКИ, КОГА STEP_NAME='ETL_ERROR' ТОГАВА 1 ИНАЧЕ 0 КАТО ERROR_FLAG,
    STEP_NAME || ' : ' || STEP_DESCR КАТО STEP_LOG,
	SUBSTR( SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), 1, INSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), '***')-2 ) КАТО AFFECTED_ROWS
  ОТ UTL_JOB_STATUS
  КЪДЕТО datetime(LOG_D, 'unixepoch') >= date('now', 'start of month', '-3 month')
)
ИЗБЕРЕТЕ JB.SID,
  JB.MIN_LOG_DT КАТО START_DT,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) КАТО LOG_DT,
  JB.SOURCE,
  JB.LAYER,
  JB.JOB_NAME,
  КАЗВАЙКИ
  КОГА JB.ERROR_FLAG = 1 ТОГАВА 'ГРЕШКА'
  КОГА JB.ERROR_FLAG = 0 И JB.END_FLAG = 0 И strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 ТОГАВА 'ЗАВИСНАЛ' /* половин час */
  КОГА JB.ERROR_FLAG = 0 И JB.END_FLAG = 0 ТОГАВА 'ТЕКУЩ'
  ИНАЧЕ 'OK'
  КРАЙ КАТО СТАТУС,
  ERR.STEP_LOG     КАТО STEP_LOG,
  JB.CNT           КАТО STEP_CNT,
  JB.AFFECTED_ROWS КАТО AFFECTED_ROWS,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) КАТО JOB_START_DT,
  strftime('%d.%m.%Y %H:%M', datetime(JB.MAX_LOG_DT, 'unixepoch')) КАТО JOB_END_DT,
  JB.MAX_LOG_DT - JB.MIN_LOG_DT КАТО JOB_DURATION_SEC
ИЗ
  ( ИЗБЕРЕТЕ SID, SOURCE, LAYER, JOB_NAME,
           MAX(UTL_JOB_STATUS_ID) КАТО UTL_JOB_STATUS_ID,
           MAX(START_FLAG)       КАТО START_FLAG,
           MAX(END_FLAG)         КАТО END_FLAG,
           MAX(ERROR_FLAG)       КАТО ERROR_FLAG,
           MIN(LOG_DT)           КАТО MIN_LOG_DT,
           MAX(LOG_DT)           КАТО MAX_LOG_DT,
           SUM(1)                КАТО CNT,
           SUM(IFNULL(AFFECTED_ROWS, 0)) КАТО AFFECTED_ROWS
    ОТ SRC
    GROUP BY SID, SOURCE, LAYER, JOB_NAME
  ) JB,
  ( ИЗБЕРЕТЕ UTL_JOB_STATUS_ID, SID, JOB_NAME, STEP_LOG
    ОТ SRC
    КЪДЕТО 1 = 1
  ) ERR
КЪДЕ 1 = 1
  И JB.SID = ERR.SID
  И JB.JOB_NAME = ERR.JOB_NAME
  И JB.UTL_JOB_STATUS_ID = ERR.UTL_JOB_STATUS_ID
НАРЕДИ ПО 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 
	    AND NOT ( 'y' = 'n' ) 
       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' 
	    AND STEP_NAME = 'ETL_START'
      GROUP BY JOB_NAME, SID
    ) start_job 
  ON d_job.dummy = start_job.dummy
  LEFT OUTER JOIN
    ( SELECT JOB_NAME, SID
      FROM UTL_JOB_STATUS
      WHERE JOB_NAME = 'RPT_ACCESS_LOG'  
	    AND STEP_NAME in ('ETL_END', 'ETL_ERROR') 
      GROUP BY JOB_NAME, SID
    ) end_job 
  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 инструменти.

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

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

Ефективен мониторинг

Таблица за наблюдение на ETL

Извод