ניטור תהליכי ETL במחסן נתונים קטן

רבים משתמשים בכלים מיוחדים כדי ליצור נהלים לחילוץ, טרנספורמציה וטעינת נתונים לתוך מסדי נתונים יחסיים. תהליך העבודה של כלי העבודה נרשם, שגיאות מתוקנות.

במקרה של שגיאה, היומן מכיל מידע שהכלי לא הצליח להשלים את המשימה ואילו מודולים (לעיתים קרובות Java) עצרו היכן. בשורות האחרונות, ניתן למצוא שגיאת מסד נתונים, למשל, הפרת מפתח ייחודי בטבלה.

כדי לענות על השאלה איזה תפקיד ממלא מידע שגיאות ETL, סיווגתי את כל הבעיות שהתרחשו במהלך השנתיים האחרונות במאגר די גדול.

שגיאות מסד נתונים כוללות לא מספיק מקום, חיבור שאבד, סשן נתקע וכו'.

שגיאות לוגיות כוללות כגון הפרה של מפתחות טבלה, אובייקטים לא חוקיים, חוסר גישה לאובייקטים וכו'.
ייתכן שהמתזמן לא יתחיל בזמן, הוא עלול לקפוא וכו'.

טעויות פשוטות לא לוקח הרבה זמן לתקן. ETL טוב יכול להתמודד עם רובם בעצמו.

באגים מורכבים מחייבים לגלות ולבדוק נהלים לעבודה עם נתונים, לחקור מקורות נתונים. לעתים קרובות מובילים לצורך בבדיקות ופריסה של שינויים.

אז, מחצית מכל הבעיות קשורות למסד הנתונים. 48% מכל הטעויות הן טעויות פשוטות.
שליש מכל הבעיות קשורות לשינוי לוגיקה או מודל האחסון, יותר ממחצית מהשגיאות הללו מורכבות.

ופחות מרבע מכל הבעיות קשורות למתזמן המשימות, 18% מהן הן שגיאות פשוטות.

באופן כללי, 22% מכלל השגיאות המתרחשות הן מורכבות, ותיקונן דורש את מירב תשומת הלב והזמן. הם קורים בערך פעם בשבוע. ואילו טעויות פשוטות קורות כמעט כל יום.

ברור, ניטור תהליכי ETL יהיה יעיל כאשר מיקום השגיאה יצוין ביומן בצורה מדויקת ככל האפשר ונדרש הזמן המינימלי כדי למצוא את מקור הבעיה.

ניטור יעיל

מה רציתי לראות בתהליך ניטור ETL?

התחל ב - כשהוא התחיל לעבוד,
מקור - מקור נתונים,
שכבה - איזו רמת אחסון נטען,
שם עבודה ETL - הליך העלאה, המורכב מהרבה שלבים קטנים,
מספר צעד - מספר הצעד המתבצע,
שורות מושפעות - כמה נתונים כבר עובדו,
משך שניה - כמה זמן זה לוקח,
סטטוס - אם הכל בסדר או לא: בסדר, שגיאה, ריצה, נתקע
הודעה - הודעה מוצלחת אחרונה או תיאור שגיאה.

בהתבסס על מצב הערכים, ניתן לשלוח מייל. מכתב לחברים אחרים. אם אין שגיאות, אז המכתב אינו הכרחי.

כך, במקרה של טעות, מצוין מיקום האירוע בצורה ברורה.

לפעמים קורה שכלי הניטור עצמו לא עובד. במקרה זה, ניתן לקרוא ל-view (view) ישירות במסד הנתונים שעל בסיסו נבנה הדוח.

טבלת ניטור 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.

מקור: www.habr.com