يستخدم العديد من الأشخاص أدوات متخصصة لإنشاء إجراءات لاستخراج البيانات وتحويلها وتحميلها إلى قواعد بيانات علائقية. يتم تسجيل عملية الأدوات، ويتم تسجيل الأخطاء.
في حالة حدوث خطأ، يحتوي السجل على معلومات تفيد بأن الأداة فشلت في إكمال المهمة وأي الوحدات (غالبًا Java) توقفت عندها. قد تحتوي الأسطر الأخيرة على خطأ في قاعدة البيانات، مثل انتهاك المفتاح الفريد للجدول.
للإجابة على سؤال ما هو الدور الذي تلعبه معلومات أخطاء ETL، قمت بتصنيف جميع المشكلات التي حدثت خلال العامين الماضيين في مستودع كبير إلى حد ما.
تتضمن أخطاء قاعدة البيانات مثل: عدم وجود مساحة كافية، أو فقدان الاتصال، أو تعليق الجلسة، وما إلى ذلك.
تتضمن الأخطاء المنطقية انتهاكات مفاتيح الجدول، والكائنات غير الصالحة، وعدم الوصول إلى الكائنات، وما إلى ذلك.
قد لا يتم تشغيل برنامج الجدولة في الوقت المحدد، وقد يتجمد، وما إلى ذلك.
الأخطاء البسيطة لا تستغرق الكثير من الوقت لتصحيحها. يمكن لـ ETL الجيد التعامل مع معظمها بمفرده.
تجعل الأخطاء المعقدة من الضروري فتح إجراءات معالجة البيانات والتحقق منها والتحقيق في مصادر البيانات. غالبًا ما يؤدي ذلك إلى الحاجة إلى اختبار التغييرات ونشرها.
لذلك، نصف جميع المشاكل تتعلق بقاعدة البيانات. 48% من جميع الأخطاء هي أخطاء بسيطة.
يرتبط ثلث المشكلات بالتغييرات في منطق التخزين أو النموذج، وأكثر من نصف هذه الأخطاء معقدة.
وأقل من ربع المشاكل مرتبطة ببرنامج جدولة المهام، و18% منها عبارة عن أخطاء بسيطة.
وبشكل عام، فإن 22% من جميع الأخطاء التي تحدث تكون معقدة وتتطلب أكبر قدر من الاهتمام والوقت لتصحيحها. تحدث حوالي مرة واحدة في الأسبوع. في حين أن الأخطاء البسيطة تحدث كل يوم تقريبًا.
من الواضح أن مراقبة عمليات ETL ستكون فعالة عندما تتم الإشارة إلى موقع الخطأ في السجل بأكبر قدر ممكن من الدقة ويتطلب الحد الأدنى من الوقت للعثور على مصدر المشكلة.
مراقبة فعالة
ما الذي أردت رؤيته في عملية مراقبة ETL؟
أبدأ عند - عندما بدأت العمل،
المصدر - مصدر البيانات،
الطبقة - مستوى التخزين الذي تم تحميله،
ETL Job Name هو إجراء تحميل يتكون من العديد من الخطوات الصغيرة،
رقم الخطوة - رقم الخطوة التي يتم تنفيذها،
الصفوف المتأثرة - مقدار البيانات التي تمت معالجتها بالفعل،
المدة بالثانية - المدة التي يستغرقها التنفيذ،
الحالة - سواء كان كل شيء على ما يرام أم لا: حسنًا، خطأ، قيد التشغيل، توقف
الرسالة — آخر رسالة ناجحة أو وصف للخطأ.
استنادا إلى حالة السجلات، يمكنك إرسال بريد إلكتروني. رسالة إلى المشاركين الآخرين. إذا لم تكن هناك أخطاء، فلن تكون الرسالة ضرورية.
وبهذه الطريقة، في حالة حدوث خطأ، يتم الإشارة بوضوح إلى موقع الحادث.
يحدث أحيانًا أن أداة المراقبة نفسها لا تعمل. في هذه الحالة، من الممكن استدعاء العرض (العرض) مباشرة في قاعدة البيانات، والتي تم بناء التقرير على أساسها.
جدول مراقبة ETL
لتنفيذ مراقبة عمليات ETL، يكفي جدول واحد وعرض واحد.
للقيام بذلك يمكنك العودة إلى وإنشاء نموذج أولي في قاعدة بيانات sqlite.
جداول دي دي إل
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 آلية خارجية وهي في هذه الحالة .
إنتاج
وبالتالي، يلعب الإبلاغ عن الأخطاء في أدوات معالجة البيانات دورًا بالغ الأهمية. ولكن من الصعب وصفها بأنها الأمثل للعثور بسرعة على سبب المشكلة. عندما يقترب عدد الإجراءات من مائة، تتحول مراقبة العملية إلى مشروع معقد.
توفر المقالة مثالاً للحل المحتمل للمشكلة في شكل نموذج أولي. النموذج الأولي الكامل للمستودع الصغير متاح في gitlab .
المصدر: www.habr.com