🥇Чакан маалымат кампасында 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 Utilities.

Source: www.habr.com

Чакан маалымат кампасында ETL процесстерине мониторинг жүргүзүү

Натыйжалуу мониторинг

ETL мониторинг таблицасы

жыйынтыктоо

Комментарий кошуу жокко жооп