หลายคนใช้เครื่องมือพิเศษเพื่อสร้างขั้นตอนสำหรับการแยก แปลง และโหลดข้อมูลลงในฐานข้อมูลเชิงสัมพันธ์ มีการบันทึกกระบวนการทำงานของเครื่องมือ ข้อผิดพลาดได้รับการแก้ไข
ในกรณีที่เกิดข้อผิดพลาด บันทึกประกอบด้วยข้อมูลที่เครื่องมือทำงานไม่สำเร็จ และโมดูลใด (มักจะเป็นจาวา) ที่หยุดทำงาน ในบรรทัดสุดท้าย คุณจะพบข้อผิดพลาดของฐานข้อมูล ตัวอย่างเช่น การละเมิดคีย์เฉพาะของตาราง
เพื่อตอบคำถามเกี่ยวกับบทบาทของข้อมูลข้อผิดพลาด ETL ฉันได้จำแนกปัญหาทั้งหมดที่เกิดขึ้นในช่วงสองปีที่ผ่านมาในพื้นที่เก็บข้อมูลที่ค่อนข้างใหญ่
ข้อผิดพลาดของฐานข้อมูล ได้แก่ พื้นที่ไม่เพียงพอ ขาดการเชื่อมต่อ เซสชันหยุดทำงาน ฯลฯ
ข้อผิดพลาดเชิงตรรกะรวมถึงการละเมิดคีย์ตาราง อ็อบเจ็กต์ที่ไม่ถูกต้อง ขาดการเข้าถึงอ็อบเจ็กต์ เป็นต้น
ตัวกำหนดตารางเวลาอาจไม่เริ่มตรงเวลา อาจค้าง เป็นต้น
ข้อผิดพลาดง่ายๆ ใช้เวลาแก้ไขไม่นาน ETL ที่ดีสามารถจัดการส่วนใหญ่ได้ด้วยตัวเอง
จุดบกพร่องที่ซับซ้อนทำให้จำเป็นต้องค้นหาและทดสอบขั้นตอนการทำงานกับข้อมูล เพื่อสำรวจแหล่งข้อมูล มักนำไปสู่ความจำเป็นในการทดสอบการเปลี่ยนแปลงและการปรับใช้
ดังนั้น ครึ่งหนึ่งของปัญหาทั้งหมดเกี่ยวข้องกับฐานข้อมูล 48% ของข้อผิดพลาดทั้งหมดเป็นข้อผิดพลาดง่ายๆ
หนึ่งในสามของปัญหาทั้งหมดเกี่ยวข้องกับการเปลี่ยนตรรกะของพื้นที่เก็บข้อมูลหรือโมเดล ข้อผิดพลาดเหล่านี้มากกว่าครึ่งมีความซับซ้อน
และน้อยกว่าหนึ่งในสี่ของปัญหาทั้งหมดเกี่ยวข้องกับตัวกำหนดตารางเวลางาน โดย 18% เป็นข้อผิดพลาดง่ายๆ
โดยทั่วไปแล้ว 22% ของข้อผิดพลาดทั้งหมดที่เกิดขึ้นนั้นซับซ้อน และการแก้ไขนั้นต้องใช้ความเอาใจใส่และเวลามากที่สุด พวกเขาเกิดขึ้นประมาณสัปดาห์ละครั้ง ในขณะที่ความผิดพลาดเกิดขึ้นแทบทุกวัน
เห็นได้ชัดว่าการตรวจสอบกระบวนการ ETL จะมีผลเมื่อมีการระบุตำแหน่งของข้อผิดพลาดในบันทึกอย่างแม่นยำที่สุดเท่าที่จะเป็นไปได้ และต้องใช้เวลาน้อยที่สุดในการค้นหาแหล่งที่มาของปัญหา
การตรวจสอบที่มีประสิทธิภาพ
ฉันต้องการเห็นอะไรในกระบวนการตรวจสอบ ETL
เริ่มที่ - เมื่อเขาเริ่มทำงาน
ที่มา - แหล่งข้อมูล
เลเยอร์ - กำลังโหลดพื้นที่เก็บข้อมูลระดับใด
ETL Job Name - ขั้นตอนการอัพโหลดซึ่งประกอบด้วยขั้นตอนเล็กๆ มากมาย
หมายเลขขั้นตอน - หมายเลขของขั้นตอนที่กำลังดำเนินการ
แถวที่ได้รับผลกระทบ - จำนวนข้อมูลที่ได้รับการประมวลผลแล้ว
ระยะเวลา วินาที - นานแค่ไหน
สถานะ - ไม่ว่าทุกอย่างเรียบร้อยดีหรือไม่: ตกลง ข้อผิดพลาด กำลังทำงาน แฮงค์
ข้อความ - ข้อความที่สำเร็จล่าสุดหรือคำอธิบายข้อผิดพลาด
คุณสามารถส่งอีเมลได้ตามสถานะของระเบียน จดหมายถึงสมาชิกคนอื่นๆ หากไม่มีข้อผิดพลาดก็ไม่จำเป็น
ดังนั้นในกรณีที่เกิดข้อผิดพลาดจะมีการระบุสถานที่เกิดเหตุอย่างชัดเจน
บางครั้งมันเกิดขึ้นที่เครื่องมือตรวจสอบไม่ทำงาน ในกรณีนี้ คุณสามารถเรียกมุมมอง (มุมมอง) ได้โดยตรงในฐานข้อมูล โดยอิงตามรายงานที่สร้างขึ้น
ตารางการตรวจสอบ 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 ต้องการกลไกภายนอกและในกรณีนี้ .
เอาท์พุต
ดังนั้น ข้อความแสดงข้อผิดพลาดในเครื่องมือประมวลผลข้อมูลจึงมีบทบาทสำคัญอย่างยิ่ง แต่เป็นการยากที่จะเรียกว่าเหมาะสมที่สุดสำหรับการค้นหาสาเหตุของปัญหาอย่างรวดเร็ว เมื่อจำนวนขั้นตอนเข้าใกล้หนึ่งร้อย การตรวจสอบกระบวนการจะกลายเป็นโครงการที่ซับซ้อน
บทความนี้แสดงตัวอย่างวิธีแก้ปัญหาที่เป็นไปได้ในรูปแบบของต้นแบบ ต้นแบบพื้นที่เก็บข้อมูลขนาดเล็กทั้งหมดมีอยู่ใน gitlab .
ที่มา: will.com