{"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\/fr\/blog\/administrirovanie\/monitoring-etl-protsessov-v-malenkom-hranilishhe-dannyh","title":{"rendered":"Surveillance des processus ETL dans un petit entrep\u00f4t de donn\u00e9es","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Beaucoup utilisent des outils sp\u00e9cialis\u00e9s pour cr\u00e9er des proc\u00e9dures d'extraction, de transformation et de chargement de donn\u00e9es dans des bases de donn\u00e9es relationnelles. Le processus de travail des outils est enregistr\u00e9, et les erreurs sont document\u00e9es.<\/p>\n

En cas d'erreur, le journal contient des informations sur les raisons pour lesquelles l'outil n'a pas r\u00e9ussi \u00e0 ex\u00e9cuter la t\u00e2che et quels modules (souvent en java) se sont arr\u00eat\u00e9s. Dans les derni\u00e8res lignes, on peut trouver l'erreur de la base de donn\u00e9es, par exemple, une violation de cl\u00e9 unique dans la table.<\/p>\n

Pour r\u00e9pondre \u00e0 la question du r\u00f4le des informations d'erreurs ETL, j'ai class\u00e9 tous les probl\u00e8mes survenus au cours des deux derni\u00e8res ann\u00e9es dans un grand entrep\u00f4t. <\/p>\n

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

Les erreurs de base de donn\u00e9es comprennent des probl\u00e8mes tels que le manque d'espace, la rupture de connexion, une session suspendue, etc.<\/p>\n

Les erreurs logiques comprennent des probl\u00e8mes tels que les violations de cl\u00e9s de table, des objets non valides, l'absence d'acc\u00e8s aux objets, etc.
\nLe planificateur peut ne pas \u00eatre lanc\u00e9 \u00e0 temps, peut se bloquer, etc.<\/p>\n

Les erreurs simples ne n\u00e9cessitent pas beaucoup de temps pour \u00eatre corrig\u00e9es. La plupart d'entre elles peuvent \u00eatre g\u00e9r\u00e9es automatiquement par un bon ETL.<\/p>\n

Les erreurs complexes n\u00e9cessitent d'ouvrir et de v\u00e9rifier les proc\u00e9dures de traitement des donn\u00e9es, d'explorer les sources de donn\u00e9es. Elles entra\u00eenent souvent des tests des modifications et des d\u00e9ploiements.<\/p>\n

Ainsi, la moiti\u00e9 de tous les probl\u00e8mes est li\u00e9e \u00e0 la base de donn\u00e9es. 48 % de toutes les erreurs sont des erreurs simples.
\nUn tiers de tous les probl\u00e8mes est li\u00e9 \u00e0 des modifications de la logique ou du mod\u00e8le de l'entrep\u00f4t, et plus de la moiti\u00e9 de ces erreurs sont complexes.<\/p>\n

Moins d'un quart de tous les probl\u00e8mes sont li\u00e9s au planificateur de t\u00e2ches, dont 18 % sont des erreurs simples.<\/p>\n

En g\u00e9n\u00e9ral, 22 % des erreurs survenues sont complexes, leur correction n\u00e9cessite le plus d'attention et de temps. Elles se produisent environ une fois par semaine. Pendant ce temps, les erreurs simples se produisent presque tous les jours.<\/p>\n

Il est \u00e9vident que le suivi des processus ETL sera efficace lorsque le journal indique de mani\u00e8re aussi pr\u00e9cise que possible l'emplacement de l'erreur et n\u00e9cessite un minimum de temps pour localiser la source du probl\u00e8me.<\/p>\n

Suivi efficace<\/h4>\n

\nQu'aimerais-je voir dans le processus de suivi ETL ?<\/p>\n

\"Surveillance
\nStart at \u2014 quand le travail a commenc\u00e9,
\nSource \u2014 source de donn\u00e9es,
\nLayer \u2014 quel niveau d'entrep\u00f4t est charg\u00e9,
\nETL Job Name \u2014 proc\u00e9dure de chargement, compos\u00e9e de nombreuses petites \u00e9tapes,
\nStep Number \u2014 num\u00e9ro de l'\u00e9tape actuellement ex\u00e9cut\u00e9e,
\nLignes affect\u00e9es \u2014 combien de donn\u00e9es ont d\u00e9j\u00e0 \u00e9t\u00e9 trait\u00e9es,
\nDur\u00e9e sec \u2014 combien de temps cela prend,
\nStatut \u2014 tout va bien ou non : OK, ERREUR, EN COURS, BLOQU\u00c9
\nMessage \u2014 dernier message r\u00e9ussi ou description de l'erreur.<\/p>\n

Sur la base du statut des enregistrements, il est possible d'envoyer un e-mail aux autres participants. S'il n'y a pas d'erreurs, l'e-mail n'est pas n\u00e9cessaire.<\/p>\n

Ainsi, en cas d'erreur, l'endroit exact o\u00f9 cela s'est produit est clairement indiqu\u00e9.<\/p>\n

Il arrive parfois que l'outil de surveillance lui-m\u00eame ne fonctionne pas. Dans ce cas, il est possible d'appeler directement dans la base de donn\u00e9es la vue sur laquelle le rapport est bas\u00e9.<\/p>\n

Table de surveillance ETL<\/h4>\n

\nPour mettre en place la surveillance des processus ETL, une seule table et une seule vue sont suffisantes.<\/p>\n

Pour cela, on peut revenir dans son petit entrep\u00f4t<\/a><\/noindex> et cr\u00e9er un prototype dans une base de donn\u00e9es SQLite.<\/p>\n

DDL de la table<\/b><\/p>\n

CREATE TABLE UTL_JOB_STATUS (\n\n\/* Table pour l'enregistrement du journal d'ex\u00e9cution des t\u00e2ches. Important que la t\u00e2che ait les \u00e9tapes ETL_START et ETL_END ou ETL_ERROR *\/\n  UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n  SID               INTEGER NOT NULL DEFAULT -1, \/* Identificateur de session. Unique pour chaque Ex\u00e9cution de t\u00e2che *\/\n  LOG_DT            INTEGER NOT NULL DEFAULT 0,  \/* Date et heure *\/\n  LOG_D             INTEGER NOT NULL DEFAULT 0,  \/* Date *\/\n  JOB_NAME          TEXT NOT NULL DEFAULT 'N\/A', \/* Nom de la t\u00e2che comme JOB_STG2DM_GEO *\/\n  STEP_NAME         TEXT NOT NULL DEFAULT 'N\/A', \/* ETL_START, ..., ETL_END\/ETL_ERROR *\/\n  STEP_DESCR        TEXT,                        \/* Description de la t\u00e2che ou message d'erreur *\/\n  UNIQUE (SID, JOB_NAME, STEP_NAME)\n);\nINSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);<\/code><\/pre>\n
\nDDL de la vue\/rapport<\/b><\/p>\n
CR\u00c9ER UNE VUE SI NON EXISTE UTL_JOB_STATUS_V\nEN TANT QUE \n\/* Contenu : Journal d'ex\u00e9cution du package pour les 3 derniers mois. *\/\nAVEC SRC COMME (\n  S\u00c9LECTIONNER LOG_D,\n    LOG_DT,\n    UTL_JOB_STATUS_ID,\n    SID,\n\tCASE WHEN INSTR(JOB_NAME, 'FTP') THEN 'TRANSFERT' \/* transfert de fichier *\/\n\t     WHEN INSTR(JOB_NAME, 'STG') THEN 'STAGE' \/* \u00e9tape *\/\n\t     WHEN INSTR(JOB_NAME, 'CLS') THEN 'NETTOYAGE' \/* nettoyage *\/\n\t     WHEN INSTR(JOB_NAME, 'DIM') THEN 'DIMENSION' \/* dimension *\/\n\t     WHEN INSTR(JOB_NAME, 'FCT') THEN 'FAIT' \/* fait *\/\n\t\t WHEN INSTR(JOB_NAME, 'ETL') THEN 'STAGE-MART' \/* mart de donn\u00e9es *\/\n\t     WHEN INSTR(JOB_NAME, 'RPT') THEN 'RAPPORT' \/* rapport *\/\n\t     ELSE 'N\/A' END AS COUCHE,\n\tCASE WHEN INSTR(JOB_NAME, 'ACCESS') THEN 'LOG D\u2019ACC\u00c8S' \/* source *\/\n\t     WHEN INSTR(JOB_NAME, 'MASTER') THEN 'DONN\u00c9ES MA\u00ceTRES' \/* 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  DE UTL_JOB_STATUS\n  O\u00d9 datetime(LOG_D, 'unixepoch') >= date('now', 'start of month', '-3 month')\n)\nS\u00c9LECTIONNER JB.SID,\n  JB.MIN_LOG_DT COMME START_DT,\n  strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) AS LOG_DT,\n  JB.SOURCE,\n  JB.COUCHE,\n  JB.JOB_NAME,\n  CASE\n  QUAND JB.ERROR_FLAG = 1 ALORS 'ERREUR'\n  QUAND JB.ERROR_FLAG = 0 ET JB.END_FLAG = 0 ET strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 ALORS 'EN ATTENTE' \/* une demi-heure *\/\n  QUAND JB.ERROR_FLAG = 0 ET JB.END_FLAG = 0 ALORS 'EN COURS'\n  SINON 'OK'\n  FIN AS STATUT,\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\nDE\n  ( S\u00c9LECTIONNER SID, SOURCE, COUCHE, JOB_NAME,\n           MAX(UTL_JOB_STATUS_ID) COMME UTL_JOB_STATUS_ID,\n           MAX(START_FLAG)       COMME START_FLAG,\n           MAX(END_FLAG)         COMME END_FLAG,\n           MAX(ERROR_FLAG)       COMME ERROR_FLAG,\n           MIN(LOG_DT)           COMME MIN_LOG_DT,\n           MAX(LOG_DT)           COMME MAX_LOG_DT,\n           SOMME(1)                COMME CNT,\n           SOMME(IFNULL(AFFECTED_ROWS, 0)) COMME AFFECTED_ROWS\n    DE SRC\n    GROUPE PAR SID, SOURCE, COUCHE, JOB_NAME\n  ) JB,\n  ( S\u00c9LECTIONNER UTL_JOB_STATUS_ID, SID, JOB_NAME, STEP_LOG\n    DE SRC\n    O\u00d9 1 = 1\n  ) ERR\nO\u00d9 1 = 1\n  ET JB.SID = ERR.SID\n  ET JB.JOB_NAME = ERR.JOB_NAME\n  ET 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
\nV\u00e9rification SQL de la possibilit\u00e9 d'obtenir un nouveau num\u00e9ro de session<\/b><\/p>\n
S\u00c9LECTIONNER SOMME (\n  CASE QUAND start_job.JOB_NAME EST NON NULL ET end_job.JOB_NAME EST NULL \/* emploi existant termin\u00e9 *\/\n\t    ET NON ( 'y' = 'n' ) \/* red\u00e9marrer de force PARAM\u00c8TRE *\/\n       ALORS 1 SINON 0\n  FIN ) COMME IS_RUNNING\n  DE\n    ( S\u00c9LECTIONNER 1 COMME dummy DE UTL_JOB_STATUS O\u00d9 sid = -1) d_job\n  JOINDRE EXT\u00c9RIEUR GAUCHE\n    ( S\u00c9LECTIONNER JOB_NAME, SID, 1 COMME dummy\n      DE UTL_JOB_STATUS\n      O\u00d9 JOB_NAME = 'RPT_ACCESS_LOG' \/* nom de job PARAM\u00c8TRE *\/\n\t    ET STEP_NAME = 'ETL_START'\n      GROUPE PAR JOB_NAME, SID\n    ) start_job \/* d\u00e9marre *\/\n  SUR d_job.dummy = start_job.dummy\n  JOINDRE EXT\u00c9RIEUR GAUCHE\n    ( S\u00c9LECTIONNER JOB_NAME, SID\n      DE UTL_JOB_STATUS\n      O\u00d9 JOB_NAME = 'RPT_ACCESS_LOG' \/* nom de job PARAM\u00c8TRE *\/\n\t    ET STEP_NAME IN ('ETL_END', 'ETL_ERROR') \/* statut d'arr\u00eat *\/\n      GROUPE PAR JOB_NAME, SID\n    ) end_job \/* termine *\/\n  SUR start_job.JOB_NAME = end_job.JOB_NAME\n     ET start_job.SID = end_job.SID<\/code><\/pre>\n
\nCaract\u00e9ristiques de la table :<\/p>\n