🥇 نظارت بر فرآیندهای ETL در یک انبار داده کوچک

بسیاری از ابزارهای تخصصی برای ایجاد رویه هایی برای استخراج، تبدیل و بارگذاری داده ها در پایگاه های داده رابطه ای استفاده می کنند. روند کار ابزار ثبت شده است، خطاها ثابت می شوند.

در صورت بروز خطا، گزارش حاوی اطلاعاتی است مبنی بر اینکه ابزار نتوانسته کار را کامل کند و کدام ماژول ها (اغلب جاوا) در کجا متوقف شده اند. در آخرین خطوط، می توانید یک خطای پایگاه داده، به عنوان مثال، نقض کلید منحصر به فرد جدول را پیدا کنید.

برای پاسخ به این سوال که اطلاعات خطای ETL چه نقشی ایفا می کند، من تمام مشکلاتی را که در دو سال گذشته رخ داده است در یک مخزن نسبتاً بزرگ طبقه بندی کرده ام.

خطاهای پایگاه داده عبارتند از: فضای کافی، اتصال از دست رفته، نشست قطع شده و غیره.

خطاهای منطقی شامل نقض کلیدهای جدول، اشیاء غیر معتبر، عدم دسترسی به اشیا و غیره است.
ممکن است زمانبندی به موقع شروع نشود، ممکن است مسدود شود و غیره.

رفع اشتباهات ساده زمان زیادی نمی برد. یک ETL خوب می تواند اکثر آنها را به تنهایی مدیریت کند.

اشکالات پیچیده، کشف و آزمایش روش‌های کار با داده‌ها، برای کشف منابع داده را ضروری می‌سازد. اغلب منجر به نیاز به آزمایش تغییر و استقرار می شود.

بنابراین، نیمی از مشکلات مربوط به پایگاه داده است. 48 درصد از همه اشتباهات اشتباهات ساده هستند.
یک سوم از همه مشکلات مربوط به تغییر منطق یا مدل ذخیره سازی است، بیش از نیمی از این خطاها پیچیده هستند.

و کمتر از یک چهارم تمام مشکلات مربوط به زمانبندی وظایف است که 18 درصد آن خطاهای ساده است.

به طور کلی 22 درصد از تمام خطاهای رخ داده پیچیده هستند و اصلاح آنها بیشترین توجه و زمان را می طلبد. آنها حدود یک بار در هفته اتفاق می افتد. در حالی که اشتباهات ساده تقریبا هر روز اتفاق می افتد.

بدیهی است که نظارت بر فرآیندهای ETL زمانی مؤثر خواهد بود که محل خطا در لاگ تا حد امکان دقیق نشان داده شود و حداقل زمان برای یافتن منبع مشکل مورد نیاز باشد.

نظارت موثر

چه چیزی را می خواستم در فرآیند نظارت ETL ببینم؟

شروع در - زمانی که او کار را شروع کرد،
منبع - منبع داده،
لایه - چه سطحی از ذخیره سازی در حال بارگیری است،
نام شغل ETL - روش آپلود، که شامل بسیاری از مراحل کوچک است،
شماره مرحله - تعداد مرحله در حال انجام،
ردیف های تحت تأثیر - چه مقدار داده قبلاً پردازش شده است،
مدت ثانیه - چقدر طول می کشد،
وضعیت - آیا همه چیز خوب است یا خیر: OK، ERROR، RUNNING، HANGS
پیام - آخرین پیام موفقیت آمیز یا شرح خطا.

بر اساس وضعیت ورودی ها، می توانید یک ایمیل ارسال کنید. نامه ای به سایر اعضا اگر خطایی وجود نداشته باشد، نامه ضروری نیست.

بنابراین در صورت بروز خطا، محل حادثه به وضوح مشخص می شود.

گاهی اوقات اتفاق می افتد که خود ابزار نظارت کار نمی کند. در این صورت امکان فراخوانی یک 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

نظارت بر فرآیندهای ETL در یک انبار داده کوچک

نظارت موثر

جدول مانیتورینگ ETL

نتیجه

اضافه کردن نظر انصراف از پاسخ