ProHoster > Блог > Администриране > Наблюдение на ETL процеси в малко хранилище за данни

Наблюдение на ETL процеси в малко хранилище за данни

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

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

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

Наблюдение на ETL процеси в малко хранилище за данни

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблица за мониторинг на ETL

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

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

DDL таблица

CREATE TABLE 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)
);
INSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-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') ТОГДА 'ОТЧЕТ' /* отчет */
	     ИНАЧЕ 'Н/Д' КОНЕЦ КАК СЛОЙ,
	СЛУЧАЙ, ЕСЛИ INSTR(JOB_NAME, 'ACCESS') ТОГДА 'ЖУРНАЛ ДОСТУПА' /* источник */
	     ИНАЧЕ ЕСЛИ INSTR(JOB_NAME, 'MASTER') ТОГДА 'МАСТЕР ДАННЫХ' /* источник */
	     ИНАЧЕ ЕСЛИ INSTR(JOB_NAME, 'AD-HOC') ТОГДА 'AD-HOC' /* источник */
	     ИНАЧЕ 'Н/Д' КОНЕЦ КАК ИСТОЧНИК,
    JOB_NAME,
    STEP_NAME,
    СЛУЧАЙ, ЕСЛИ STEP_NAME='ETL_START' ТОГДА 1 ИНАЧЕ 0 КОНЕЦ КАК ФЛАГ_НАЧАЛА,
    СЛУЧАЙ, ЕСЛИ STEP_NAME='ETL_END' ТОГДА 1 ИНАЧЕ 0 КОНЕЦ КАК ФЛАГ_КОНЦА,
    СЛУЧАЙ, ЕСЛИ STEP_NAME='ETL_ERROR' ТОГДА 1 ИНАЧЕ 0 КОНЕЦ КАК ФЛАГ_ОШИБКИ,
    STEP_NAME || ' : ' || STEP_DESCR КАК ЖУРНАЛ_ШАГА,
	SUBSTR( SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), 1, INSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), '***')-2 ) КАК ЗАЕЗЖЕННЫЕ_СТРОКИ
  ИЗ 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 ТОГДА 'В ПРОЦЕССЕ'
  ИНАЧЕ 'ОК'
  КОНЕЦ КАК СТАТУС,
  ERR.STEP_LOG     КАК STEP_LOG,
  JB.CNT           КАК STEP_CNT,
  JB.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)       КАК ФЛАГ_НАЧАЛА,
           MAX(END_FLAG)         КАК ФЛАГ_КОНЦА,
           MAX(ERROR_FLAG)       КАК ФЛАГ_ОШИБКИ,
           MIN(LOG_DT)           КАК MIN_LOG_DT,
           MAX(LOG_DT)           КАК MAX_LOG_DT,
           SUM(1)                КАК CNT,
           SUM(IFNULL(AFFECTED_ROWS, 0)) КАК ЗАЕЗЖЕННЫЕ_СТРОКИ
    ИЗ SRC
    ГРУППИРОВАТЬ ПО 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 (
  СЛУЧАЙ, ЕСЛИ start_job.JOB_NAME IS NOT NULL И end_job.JOB_NAME IS NULL /* существующая работа завершена */
	    И НЕ ( 'y' = 'n' ) /* принудительная перезапуск ПАРАМЕТР */
       ТОГДА 1 ИНАЧЕ 0
  КОНЕЦ ) AS IS_RUNNING
  ИЗ
    ( SELECT 1 AS dummy ИЗ UTL_JOB_STATUS ГДЕ sid = -1) d_job
  ЛЕВЫЙ ВНЕШНИЙ СОЕДИНИТЬ
    ( SELECT JOB_NAME, SID, 1 AS dummy
      ИЗ UTL_JOB_STATUS
      ГДЕ JOB_NAME = 'RPT_ACCESS_LOG' /* имя работы ПАРАМЕТР */
	    И STEP_NAME = 'ETL_START'
      ГРУППИРОВАТЬ ПО JOB_NAME, SID
    ) start_job /* начинается */
  ON d_job.dummy = start_job.dummy
  ЛЕВЫЙ ВНЕШНИЙ СОЕДИНИТЬ
    ( SELECT JOB_NAME, SID
      ИЗ UTL_JOB_STATUS
      ГДЕ JOB_NAME = 'RPT_ACCESS_LOG'  /* имя работы ПАРАМЕТР */
	    И STEP_NAME in ('ETL_END', 'ETL_ERROR') /* статус остановки */
      ГРУППИРОВАТЬ ПО JOB_NAME, SID
    ) end_job /* завершается */
  ON start_job.JOB_NAME = end_job.JOB_NAME
     И 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.

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

Купете надежден хостинг за сайтове със защита от DDoS, VPS и VDS сървъри 🔥 Купете надежден хостинг за сайтове със защита от DDoS, VPS и VDS сървъри | ProHoster