{"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\/da\/blog\/administrirovanie\/monitoring-etl-protsessov-v-malenkom-hranilishhe-dannyh","title":{"rendered":"Overv\u00e5gning af ETL-processer i et lille datalager","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Mange bruger specialiserede v\u00e6rkt\u00f8jer til at oprette procedurer til udtr\u00e6kning, transformation og indl\u00e6sning af data i relationelle databaser. Arbejdsprocessen for v\u00e6rkt\u00f8jer logges, og fejl registreres.<\/p>\n

I tilf\u00e6lde af en fejl indeholder loggen oplysninger om, at v\u00e6rkt\u00f8jet ikke kunne udf\u00f8re opgaven, og hvilke moduler (ofte java) der stoppede. I de sidste linjer kan man finde databasefejlen, eksempelvis overtr\u00e6delse af en unik n\u00f8gle i tabellen.<\/p>\n

For at besvare sp\u00f8rgsm\u00e5let om, hvilken rolle information om ETL-fejl spiller, har jeg klassificeret alle problemer, der er opst\u00e5et i l\u00f8bet af de sidste to \u00e5r i et stort datalager. <\/p>\n

\"Overv\u00e5gning<\/a><\/noindex><\/p>\n

Databasefejl omfatter s\u00e5danne som mangel p\u00e5 plads, afbrudt forbindelse, fastl\u00e5sning af sessioner osv.<\/p>\n

Logiske fejl omfatter s\u00e5danne som overtr\u00e6delse af tabellens n\u00f8gler, ugyldige objekter, manglende adgang til objekter osv.
\nPlanl\u00e6ggeren kan blive k\u00f8rt p\u00e5 et forkert tidspunkt, kan fryse osv.<\/p>\n

Enkle fejl kr\u00e6ver ikke meget tid til at rette. De fleste af dem kan en god ETL h\u00e5ndtere selv.<\/p>\n

Komplekse fejl medf\u00f8rer behov for at \u00e5bne og kontrollere procedurerne for databehandling, unders\u00f8ge datakilder. Ofte f\u00f8rer de til behovet for at teste \u00e6ndringer og implementering.<\/p>\n

S\u00e5ledes er halvdelen af alle problemer relateret til databasen. 48% af alle fejl er enkle fejl.
\nEn tredjedel af alle problemer er relateret til \u00e6ndringer af logik eller modellens lager, mere end halvdelen af disse fejl er komplekse.<\/p>\n

Og mindre end en fjerdedel af alle problemer er relateret til opgaveplanl\u00e6ggeren, 18% af dem er enkle fejl.<\/p>\n

Generelt er 22% af alle opst\u00e5ede fejl komplekse, deres rettelse kr\u00e6ver mest opm\u00e6rksomhed og tid. De opst\u00e5r cirka en gang om ugen, mens enkle fejl sker n\u00e6sten dagligt.<\/p>\n

Det er tydeligt, at overv\u00e5gning af ETL-processer vil v\u00e6re effektiv, n\u00e5r loggen pr\u00e6cist angiver fejlstedet og kr\u00e6ver minimal tid til at finde problemmets kilde.<\/p>\n

Effektiv overv\u00e5gning<\/h4>\n

\nHvad ville jeg gerne se i overv\u00e5gningsprocessen for ETL?<\/p>\n

\"Overv\u00e5gning
\nStart ved \u2014 hvorn\u00e5r det begyndte at fungere,
\nKilde \u2014 datakilden,
\nLag \u2014 hvilket niveau af lageret der indl\u00e6ses,
\nETL Job Name \u2014 en procedure for indl\u00e6sning, der best\u00e5r af mange sm\u00e5 trin,
\nStep Number \u2014 nummeret p\u00e5 det udf\u00f8rte trin,
\nAffected Rows \u2014 hvor mange data der allerede er blevet behandlet,
\nDuration sec \u2014 hvor l\u00e6nge det tager,
\nStatus \u2014 er alt i orden eller ej: OK, ERROR, RUNNING, HANGS
\nMessage \u2014 den seneste succesfulde besked eller fejbeskrivelse.<\/p>\n

Baseret p\u00e5 status for posterne kan der sendes en e-mail til andre deltagere. Hvis der ikke er nogen fejl, er e-mailen ikke n\u00f8dvendig.<\/p>\n

Derfor er det klart angivet, hvor fejlen opstod.<\/p>\n

Nogle gange kan det ske, at selve overv\u00e5gningsv\u00e6rkt\u00f8jet ikke fungerer. I s\u00e5 fald er der mulighed for direkte at kalde visningen i databasen, som rapporten er baseret p\u00e5.<\/p>\n

ETL Overv\u00e5gnings Tabel<\/h4>\n

\nFor at implementere overv\u00e5gning af ETL-processer kr\u00e6ves der kun \u00e9n tabel og \u00e9n visning.<\/p>\n

For dette kan man vende tilbage til sit lille lager<\/a><\/noindex> og oprette en prototype i sqlite-databasen.<\/p>\n

DDL tabel<\/b><\/p>\n

CREATE TABLE UTL_JOB_STATUS (\n\t\/* Tabel til registrering af jobudf\u00f8relseslog. Det er vigtigt, at jobbet har trinene ETL_START og ETL_END eller ETL_ERROR *\/\n  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n  SID               INTEGER NOT NULL DEFAULT -1, \/* Session Identificator. Unik for hver k\u00f8rsel af job *\/\n  LOG_DT            INTEGER NOT NULL DEFAULT 0,  \/* Dato tid *\/\n  LOG_D             INTEGER NOT NULL DEFAULT 0,  \/* Dato *\/\n  JOB_NAME          TEXT NOT NULL DEFAULT 'N\/A', \/* Jobnavn som JOB_STG2DM_GEO *\/\n  STEP_NAME         TEXT NOT NULL DEFAULT 'N\/A', \/* ETL_START, ... , ETL_END\/ETL_ERROR *\/\n  STEP_DESCR        TEXT,                        \/* Beskrivelse af opgave eller fejlmeddelelse *\/\n  UNIQUE (SID, JOB_NAME, STEP_NAME)\n);\nINSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);<\/code><\/pre>\n
\nDDL visning\/rapport<\/b><\/p>\n
OPRET VISNING HVIS IKKE EKSTERNE UTL_JOB_STATUS_V\nAS \n\/* Indhold: Pakkeeksekveringslog for de sidste 3 m\u00e5neder. *\/\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 'OVERF\u00d8RSEL' \/* filoverf\u00f8rsel *\/\n\t     WHEN INSTR(JOB_NAME, 'STG') THEN 'STAGE' \/* trin *\/\n\t     WHEN INSTR(JOB_NAME, 'CLS') THEN 'RENHOLD' \/* rensning *\/\n\t     WHEN INSTR(JOB_NAME, 'DIM') THEN 'DIMENSION' \/* dimension *\/\n\t     WHEN INSTR(JOB_NAME, 'FCT') THEN 'FAKTUM' \/* faktum *\/\n\t\t WHEN INSTR(JOB_NAME, 'ETL') THEN 'STAGE-MART' \/* datamart *\/\n\t     WHEN INSTR(JOB_NAME, 'RPT') THEN 'RAPPORT' \/* rapport *\/\n\t     ELSE 'N\/A' END AS LAYER,\n\tCASE WHEN INSTR(JOB_NAME, 'ACCESS') THEN 'ADGANGSLOG' \/* kilde *\/\n\t     WHEN INSTR(JOB_NAME, 'MASTER') THEN 'MASTERDATA' \/* kilde *\/\n\t     WHEN INSTR(JOB_NAME, 'AD-HOC') THEN 'AD-HOC' \/* kilde *\/\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 'FEJL'\n  WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 AND strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 THEN 'H\u00c6NGER' \/* en halv time *\/\n  WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 THEN 'K\u00d8RER'\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 Tjek for at f\u00e5 nyt sessionnummer<\/b><\/p>\n
SELECT SUM (\n  CASE WHEN start_job.JOB_NAME IS NOT NULL AND end_job.JOB_NAME IS NULL \/* eksisterende job afsluttet *\/\n\t    AND NOT ( 'y' = 'n' ) \/* tvungen genstart 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' \/* jobnavn PARAMETER *\/\n\t    AND STEP_NAME = 'ETL_START'\n      GROUP BY JOB_NAME, SID\n    ) start_job \/* starter *\/\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'  \/* jobnavn PARAMETER *\/\n\t    AND STEP_NAME in ('ETL_END', 'ETL_ERROR') \/* stop status *\/\n      GROUP BY JOB_NAME, SID\n    ) end_job \/* slutter *\/\n  ON start_job.JOB_NAME = end_job.JOB_NAME\n     AND start_job.SID = end_job.SID<\/code><\/pre>\n
\nTabelspecifikationer:<\/p>\n