{"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\/nl\/blog\/administrirovanie\/monitoring-etl-protsessov-v-malenkom-hranilishhe-dannyh","title":{"rendered":"Monitoring van ETL-processen in een kleine databasemagazijn","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Veel mensen gebruiken gespecialiseerde tools voor het cre\u00ebren van extractie-, transformatie- en laadprocedures in relationele databases. Het werkproces van de tools wordt gelogd, fouten worden vastgelegd.<\/p>\n

In geval van een fout bevat het log informatie over wat de tool niet kon uitvoeren en welke modules (vaak java) waar zijn stopgezet. In de laatste regels kan een databasefout worden gevonden, bijvoorbeeld een schending van de unieke sleutel van de tabel.<\/p>\n

Om de rol van foutinformatie in ETL te beantwoorden, heb ik alle problemen die de afgelopen twee jaar zijn voorgekomen in een grote opslag geclassificeerd. <\/p>\n

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

Databasefouten omvatten zaken zoals gebrek aan ruimte, verbroken verbinding, vastgelopen sessies, enz.<\/p>\n

Logische fouten omvatten zaken zoals schending van tabelsleutels, ongeldige objecten, gebrek aan toegang tot objecten, enz.
\nDe planner kan op een ongelegen moment worden gestart, kan vastlopen, enz.<\/p>\n

Eenvoudige fouten vergen niet veel tijd om op te lossen. Met de meeste daarvan kan een goede ETL zelfstandig omgaan.<\/p>\n

Complexe fouten vereisen het openen en controleren van gegevensprocedures, het onderzoeken van gegevensbronnen. Dit leidt vaak tot de noodzaak om veranderingen te testen en uit te rollen.<\/p>\n

Dus de helft van alle problemen heeft te maken met de database. 48% van alle fouten zijn eenvoudige fouten.
\nEen derde van alle problemen heeft te maken met het wijzigen van de logica of het model van de opslag, meer dan de helft van deze fouten zijn complex.<\/p>\n

En minder dan een kwart van alle problemen heeft te maken met de taakplanner, waarvan 18% eenvoudige fouten zijn.<\/p>\n

Over het algemeen is 22% van alle opgetreden fouten complex, hun oplossing vergt de meeste aandacht en tijd. Deze komen ongeveer wekelijks voor. Terwijl eenvoudige fouten bijna elke dag voorkomen.<\/p>\n

Het is duidelijk dat monitoring van ETL-processen effectief zal zijn wanneer in het logboek zo nauwkeurig mogelijk het foutlocatie is aangegeven en er minimale tijd nodig is om de bron van het probleem te vinden.<\/p>\n

Effectieve monitoring<\/h4>\n

\nWat wilde ik zien in het monitoringproces van ETL?<\/p>\n

\"Monitoring
\nStart at \u2014 wanneer de werking begon,
\nSource \u2014 gegevensbron,
\nLayer \u2014 welk niveau van opslag wordt geladen,
\nETL Job Name \u2014 de laadprocedure die uit veel kleine stappen bestaat,
\nStep Number \u2014 nummer van de uitgevoerde stap,
\nBenadeelde Rij \u2014 hoeveel gegevens zijn al verwerkt,
\nDuur sec \u2014 hoe lang het duurt om uit te voeren,
\nStatus \u2014 is alles goed of niet: OK, ERROR, RUNNING, HANGS
\nBericht \u2014 het laatste succesvolle bericht of een foutbeschrijving.<\/p>\n

Op basis van de status van de records kan een e-mail naar andere deelnemers worden verzonden. Als er geen fouten zijn, is de e-mail ook niet verplicht.<\/p>\n

Dus in het geval van een fout is de locatie van het voorval duidelijk aangegeven.<\/p>\n

Soms komt het voor dat de monitoringtool zelf niet werkt. In dat geval is het mogelijk om direct in de database de weergave (view) aan te roepen, waarop het rapport is gebaseerd.<\/p>\n

Monitoring tabel ETL<\/h4>\n

\nOm ETL-processen te monitoren, is slechts \u00e9\u00e9n tabel en \u00e9\u00e9n weergave nodig.<\/p>\n

Daarvoor kun je teruggaan naar jouw kleine opslagplaats<\/a><\/noindex> en een prototype in de sqlite-database maken.<\/p>\n

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

CREATE TABLE UTL_JOB_STATUS (\n\n\/* Tabel voor logging van jobuitvoeringslog. Belangrijk dat de job de stappen ETL_START en ETL_END of ETL_ERROR heeft *\/\n  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n  SID               INTEGER NOT NULL DEFAULT -1, \/* Sessienaam. Uniek voor elke uitvoering van de job *\/\n  LOG_DT            INTEGER NOT NULL DEFAULT 0,  \/* Datum tijd *\/\n  LOG_D             INTEGER NOT NULL DEFAULT 0,  \/* Datum *\/\n  JOB_NAME          TEXT NOT NULL DEFAULT 'N\/A', \/* Jobnaam zoals JOB_STG2DM_GEO *\/\n  STEP_NAME         TEXT NOT NULL DEFAULT 'N\/A', \/* ETL_START, ... , ETL_END\/ETL_ERROR *\/\n  STEP_DESCR        TEXT,                        \/* Beschrijving van de taak of foutmelding *\/\n  UNIQUE (SID, JOB_NAME, STEP_NAME)\n);\nINSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);<\/code><\/pre>\n
\nDDL weergave\/rapport<\/b><\/p>\n
MAAK VIEW ALS DEZE NOG NIET BESTAAT UTL_JOB_STATUS_V\nALS \n\/* Inhoud: Uitvoeringslogboek van pakketten voor de laatste 3 maanden. *\/\nMET SRC AS (\n  SELECT LOG_D,\n    LOG_DT,\n    UTL_JOB_STATUS_ID,\n    SID,\n\tCASE WHEN INSTR(JOB_NAME, 'FTP') THEN 'OVERDRACHT' \/* bestandsoverdracht *\/\n\t     WHEN INSTR(JOB_NAME, 'STG') THEN 'STADIA' \/* stadium *\/\n\t     WHEN INSTR(JOB_NAME, 'CLS') THEN 'SCHOONMAKEN' \/* schoonmaak *\/\n\t     WHEN INSTR(JOB_NAME, 'DIM') THEN 'DIMENSIE' \/* dimensie *\/\n\t     WHEN INSTR(JOB_NAME, 'FCT') THEN 'FEIT' \/* feit *\/\n\t\t WHEN INSTR(JOB_NAME, 'ETL') THEN 'STAGE-MART' \/* gegevensmart *\/\n\t     WHEN INSTR(JOB_NAME, 'RPT') THEN 'VERSLAG' \/* verslag *\/\n\t     ELSE 'N\/A' END AS LAYER,\n\tCASE WHEN INSTR(JOB_NAME, 'ACCESS') THEN 'TOEGANGSLOG' \/* bron *\/\n\t     WHEN INSTR(JOB_NAME, 'MASTER') THEN 'MASTERGEGEVENS' \/* bron *\/\n\t     WHEN INSTR(JOB_NAME, 'AD-HOC') THEN 'AD-HOC' \/* bron *\/\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 'FOUT'\n  WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 AND strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 THEN 'HANGT' \/* een half uur *\/\n  WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 THEN 'LOOPT'\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 Controle op de mogelijkheid om een nieuw sessienummer te verkrijgen<\/b><\/p>\n
SELECT SUM (\n  CASE WHEN start_job.JOB_NAME IS NOT NULL AND end_job.JOB_NAME IS NULL \/* bestaand job is be\u00ebindigd *\/\n\t    AND NOT ( 'y' = 'n' ) \/* geforceerde herstart 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' \/* jobnaam PARAMETER *\/\n\t    AND STEP_NAME = 'ETL_START'\n      GROUP BY JOB_NAME, SID\n    ) start_job \/* begint *\/\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'  \/* jobnaam PARAMETER *\/\n\t    AND STEP_NAME in ('ETL_END', 'ETL_ERROR') \/* stopstatus *\/\n      GROUP BY JOB_NAME, SID\n    ) end_job \/* eindigt *\/\n  ON start_job.JOB_NAME = end_job.JOB_NAME\n     AND start_job.SID = end_job.SID<\/code><\/pre>\n
\nKenmerken van de tabel:<\/p>\n