{"id":37701,"date":"2019-10-31T22:19:10","date_gmt":"2019-10-31T19:19:10","guid":{"rendered":"https:\/\/prohoster.info\/blog\/monitoring-etl-protsessov-v-malenkom-hranilishhe-dannyh\/"},"modified":"2019-10-31T22:19:10","modified_gmt":"2019-10-31T19:19:10","slug":"monitoring-etl-protsessov-v-malenkom-hranilishhe-dannyh","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/monitoring-etl-protsessov-v-malenkom-hranilishhe-dannyh","title":{"rendered":"Monitoring ETL processes in a small data warehouse","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Many use specialized tools to create extraction, transformation, and loading procedures for relational databases. The operation of these tools is logged, and errors are recorded.<\/p>\n

In case of an error, the log contains information about which tasks the tool failed to execute and which modules (often this is java) where it stopped. In the last lines, you can find the database error, for example, a violation of the unique key of the table.<\/p>\n

To address the question of what role error information plays in ETL, I classified all the issues that occurred over the past two years in a sizable warehouse. <\/p>\n

\"Monitoring<\/a><\/noindex><\/p>\n

Database errors include issues such as insufficient space, broken connections, session hangs, etc.<\/p>\n

Logical errors include violations of table keys, invalid objects, lack of access to objects, etc.
\nThe scheduler may not run on time, may hang, etc.<\/p>\n

Simple errors do not require much time to fix. Most of them a good ETL can handle by itself.<\/p>\n

Complex errors necessitate opening and checking data processing procedures, investigating data sources. They often lead to the need for testing changes and deployment.<\/p>\n

So, half of all problems are related to the database. 48% of all errors are simple errors.
\nA third of all problems are related to changes in logic or storage model, with more than half of these errors being complex.<\/p>\n

And less than a quarter of all problems are related to the task scheduler, of which 18% are simple errors.<\/p>\n

Overall, 22% of all occurred errors are complex, their fixing requires the most attention and time. They happen approximately once a week, while simple errors occur almost every day.<\/p>\n

It is clear that monitoring of ETL processes will be effective when the log accurately specifies the location of the error and requires minimal time to find the source of the problem.<\/p>\n

Effective monitoring<\/h4>\n

\nWhat I would like to see in the ETL monitoring process?<\/p>\n

\"Monitoring
\nStart at \u2014 when the work began,
\nSource \u2014 data source,
\nLayer \u2014 which level of the warehouse is being loaded,
\nETL Job Name \u2014 a loading procedure consisting of many small steps,
\nStep Number \u2014 the number of the step being executed,
\nAffected Rows \u2014 how much data has already been processed,
\nDuration sec \u2014 how long it takes to execute,
\nStatus \u2014 whether everything is alright or not: OK, ERROR, RUNNING, HANGS
\nMessage \u2014 the last successful message or error description.<\/p>\n

Based on the status of the records, an email can be sent to other participants. If there are no errors, then the email is not necessary.<\/p>\n

Thus, in case of an error, the exact location of the incident is indicated.<\/p>\n

Sometimes the monitoring tool itself may not work. In such cases, there is an option to directly call the view from the database, based on which the report is generated.<\/p>\n

ETL Monitoring Table<\/h4>\n

\nTo implement ETL process monitoring, only one table and one view are sufficient.<\/p>\n

To do this, you can go back to your small storage<\/a><\/noindex> and create a prototype in the sqlite database.<\/p>\n

DDL of the table<\/b><\/p>\n

CREATE TABLE UTL_JOB_STATUS (\n\n\/* Table for logging of job execution log. Important that the job has the steps ETL_START and ETL_END or ETL_ERROR *\/\n  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n  SID               INTEGER NOT NULL DEFAULT -1, \/* Session Identifier. Unique for every Run of job *\/\n  LOG_DT            INTEGER NOT NULL DEFAULT 0,  \/* Date time *\/\n  LOG_D             INTEGER NOT NULL DEFAULT 0,  \/* Date *\/\n  JOB_NAME          TEXT NOT NULL DEFAULT 'N\/A', \/* Job name like JOB_STG2DM_GEO *\/\n  STEP_NAME         TEXT NOT NULL DEFAULT 'N\/A', \/* ETL_START, ..., ETL_END\/ETL_ERROR *\/\n  STEP_DESCR        TEXT,                        \/* Description of task or error message *\/\n  UNIQUE (SID, JOB_NAME, STEP_NAME)\n);\nINSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);<\/code><\/pre>\n
\nDDL of the view\/report<\/b><\/p>\n
CREATE VIEW IF NOT EXISTS UTL_JOB_STATUS_V\nAS \/* Content: Package Execution Log for the last 3 Months. *\/\nWITH SRC AS (\n  SELECT LOG_D,\n    LOG_DT,\n    UTL_JOB_STATUS_ID,\n    SID,\n\tCASE WHEN INSTR(JOB_NAME, 'FTP') THEN 'TRANSFER' \/* file transfer *\/\n\t     WHEN INSTR(JOB_NAME, 'STG') THEN 'STAGE' \/* stage *\/\n\t     WHEN INSTR(JOB_NAME, 'CLS') THEN 'CLEANSING' \/* cleansing *\/\n\t     WHEN INSTR(JOB_NAME, 'DIM') THEN 'DIMENSION' \/* dimension *\/\n\t     WHEN INSTR(JOB_NAME, 'FCT') THEN 'FACT' \/* fact *\/\n\t\t WHEN INSTR(JOB_NAME, 'ETL') THEN 'STAGE-MART' \/* data mart *\/\n\t     WHEN INSTR(JOB_NAME, 'RPT') THEN 'REPORT' \/* report *\/\n\t     ELSE 'N\/A' END AS LAYER,\n\tCASE WHEN INSTR(JOB_NAME, 'ACCESS') THEN 'ACCESS LOG' \/* source *\/\n\t     WHEN INSTR(JOB_NAME, 'MASTER') THEN 'MASTER DATA' \/* source *\/\n\t     WHEN INSTR(JOB_NAME, 'AD-HOC') THEN 'AD-HOC' \/* source *\/\n\t     ELSE 'N\/A' END AS SOURCE,\n    JOB_NAME,\n    STEP_NAME,\n    CASE WHEN STEP_NAME='ETL_START' THEN 1 ELSE 0 END AS START_FLAG,\n    CASE WHEN STEP_NAME='ETL_END' THEN 1 ELSE 0 END AS END_FLAG,\n    CASE WHEN STEP_NAME='ETL_ERROR' THEN 1 ELSE 0 END AS ERROR_FLAG,\n    STEP_NAME || ' : ' || STEP_DESCR AS STEP_LOG,\n\tSUBSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), 1, INSTR(SUBSTR(STEP_DESCR, INSTR(STEP_DESCR, '***')+4), '***')-2) AS AFFECTED_ROWS\n  FROM UTL_JOB_STATUS\n  WHERE datetime(LOG_D, 'unixepoch') >= date('now', 'start of month', '-3 month')\n)\nSELECT JB.SID,\n  JB.MIN_LOG_DT AS START_DT,\n  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS LOG_DT,\n  JB.SOURCE,\n  JB.LAYER,\n  JB.JOB_NAME,\n  CASE\n  WHEN JB.ERROR_FLAG = 1 THEN 'ERROR'\n  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 *\/\n  WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 THEN 'RUNNING'\n  ELSE 'OK'\n  END AS STATUS,\n  ERR.STEP_LOG     AS STEP_LOG,\n  JB.CNT           AS STEP_CNT,\n  JB.AFFECTED_ROWS AS AFFECTED_ROWS,\n  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS JOB_START_DT,\n  strftime('%d.%m.%Y %H:%M', datetime(JB.MAX_LOG_DT, 'unixepoch')) AS JOB_END_DT,\n  JB.MAX_LOG_DT - JB.MIN_LOG_DT AS JOB_DURATION_SEC\nFROM\n  ( SELECT SID, SOURCE, LAYER, JOB_NAME,\n           MAX(UTL_JOB_STATUS_ID) AS UTL_JOB_STATUS_ID,\n           MAX(START_FLAG)       AS START_FLAG,\n           MAX(END_FLAG)         AS END_FLAG,\n           MAX(ERROR_FLAG)       AS ERROR_FLAG,\n           MIN(LOG_DT)           AS MIN_LOG_DT,\n           MAX(LOG_DT)           AS MAX_LOG_DT,\n           SUM(1)                AS CNT,\n           SUM(IFNULL(AFFECTED_ROWS, 0)) AS AFFECTED_ROWS\n    FROM SRC\n    GROUP BY SID, SOURCE, LAYER, JOB_NAME\n  ) JB,\n  ( SELECT UTL_JOB_STATUS_ID, SID, JOB_NAME, STEP_LOG\n    FROM SRC\n    WHERE 1 = 1\n  ) ERR\nWHERE 1 = 1\n  AND JB.SID = ERR.SID\n  AND JB.JOB_NAME = ERR.JOB_NAME\n  AND JB.UTL_JOB_STATUS_ID = ERR.UTL_JOB_STATUS_ID\nORDER BY JB.MIN_LOG_DT DESC, JB.SID DESC, JB.SOURCE;<\/code><\/pre>\n
\nSQL Check for the possibility to obtain a new session number<\/b><\/p>\n
SELECT SUM (\n  CASE WHEN start_job.JOB_NAME IS NOT NULL AND end_job.JOB_NAME IS NULL \/* existed job finished *\/\n\t    AND NOT ( 'y' = 'n' ) \/* force restart PARAMETER *\/\n       THEN 1 ELSE 0\n  END ) AS IS_RUNNING\n  FROM\n    ( SELECT 1 AS dummy FROM UTL_JOB_STATUS WHERE sid = -1) d_job\n  LEFT OUTER JOIN\n    ( SELECT JOB_NAME, SID, 1 AS dummy\n      FROM UTL_JOB_STATUS\n      WHERE JOB_NAME = 'RPT_ACCESS_LOG' \/* job name PARAMETER *\/\n\t    AND STEP_NAME = 'ETL_START'\n      GROUP BY JOB_NAME, SID\n    ) start_job \/* starts *\/\n  ON d_job.dummy = start_job.dummy\n  LEFT OUTER JOIN\n    ( SELECT JOB_NAME, SID\n      FROM UTL_JOB_STATUS\n      WHERE JOB_NAME = 'RPT_ACCESS_LOG'  \/* job name PARAMETER *\/\n\t    AND STEP_NAME in ('ETL_END', 'ETL_ERROR') \/* stop status *\/\n      GROUP BY JOB_NAME, SID\n    ) end_job \/* ends *\/\n  ON start_job.JOB_NAME = end_job.JOB_NAME\n     AND start_job.SID = end_job.SID<\/code><\/pre>\n
\nTable Features:<\/p>\n