Giám sát các quy trình ETL trong một kho dữ liệu nhỏ

Nhiều người sử dụng các công cụ chuyên dụng để tạo các quy trình trích xuất, chuyển đổi và tải dữ liệu vào cơ sở dữ liệu quan hệ. Quá trình làm việc của các công cụ được ghi lại, các lỗi được sửa chữa.

Trong trường hợp có lỗi, nhật ký chứa thông tin rằng công cụ không hoàn thành tác vụ và mô-đun nào (thường là java) đã dừng ở đâu. Trong những dòng cuối cùng, bạn có thể tìm thấy lỗi cơ sở dữ liệu, chẳng hạn như vi phạm khóa duy nhất của bảng.

Để trả lời câu hỏi thông tin lỗi ETL đóng vai trò gì, tôi đã phân loại tất cả các sự cố xảy ra trong hai năm qua trong một kho lưu trữ khá lớn.

Lỗi cơ sở dữ liệu bao gồm không đủ dung lượng, mất kết nối, treo phiên, v.v.

Các lỗi logic bao gồm như vi phạm các khóa của bảng, các đối tượng không hợp lệ, thiếu quyền truy cập vào các đối tượng, v.v.
Bộ lập lịch có thể không bắt đầu đúng giờ, nó có thể bị treo, v.v.

Lỗi đơn giản không mất nhiều thời gian để sửa chữa. Một ETL tốt có thể tự xử lý hầu hết chúng.

Các lỗi phức tạp khiến việc khám phá và kiểm tra các quy trình làm việc với dữ liệu, khám phá các nguồn dữ liệu trở nên cần thiết. Thường dẫn đến nhu cầu thử nghiệm và triển khai thay đổi.

Vì vậy, một nửa số vấn đề có liên quan đến cơ sở dữ liệu. 48% của tất cả các sai lầm là những sai lầm đơn giản.
Một phần ba của tất cả các vấn đề liên quan đến việc thay đổi mô hình hoặc logic lưu trữ, hơn một nửa trong số các lỗi này là phức tạp.

Và chưa đến một phần tư của tất cả các vấn đề có liên quan đến bộ lập lịch tác vụ, 18% trong số đó là các lỗi đơn giản.

Nhìn chung, 22% trong số tất cả các lỗi xảy ra là phức tạp và việc sửa chúng đòi hỏi nhiều thời gian và sự chú ý nhất. Chúng xảy ra khoảng một lần một tuần. Trong khi những sai lầm đơn giản xảy ra hầu như hàng ngày.

Rõ ràng, việc giám sát các quy trình ETL sẽ hiệu quả khi vị trí lỗi được chỉ ra trong nhật ký càng chính xác càng tốt và thời gian tối thiểu cần thiết để tìm ra nguồn gốc của vấn đề.

Giám sát hiệu quả

Tôi muốn thấy gì trong quy trình giám sát ETL?

Bắt đầu tại - khi anh ấy bắt đầu làm việc,
Nguồn - nguồn dữ liệu,
Lớp - mức lưu trữ nào đang được tải,
Tên công việc ETL - quy trình tải lên, bao gồm nhiều bước nhỏ,
Số bước - số bước đang được thực hiện,
Hàng bị ảnh hưởng - bao nhiêu dữ liệu đã được xử lý,
Duration sec - mất bao lâu,
Trạng thái - mọi thứ có ổn hay không: OK, LỖI, CHẠY, TREO
Thông báo - Thông báo thành công cuối cùng hoặc mô tả lỗi.

Dựa trên trạng thái của các mục, bạn có thể gửi email. thư cho các thành viên khác. Nếu không có lỗi, thì bức thư là không cần thiết.

Do đó, trong trường hợp xảy ra lỗi, vị trí xảy ra sự cố được chỉ định rõ ràng.

Đôi khi, bản thân công cụ giám sát không hoạt động. Trong trường hợp này, có thể gọi một chế độ xem (chế độ xem) trực tiếp trong cơ sở dữ liệu, trên cơ sở đó báo cáo được xây dựng.

bảng giám sát ETL

Để triển khai giám sát các quy trình ETL, một bảng và một dạng xem là đủ.

Để làm điều này, bạn có thể quay lại lưu trữ nhỏ của bạn và tạo nguyên mẫu trong cơ sở dữ liệu sqlite.

bảng 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);

Xem/Báo cáo 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 Kiểm tra xem có thể lấy số phiên mới không

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

Các tính năng của bảng:

• bắt đầu và kết thúc quy trình xử lý dữ liệu phải tuân theo các bước ETL_START và ETL_END
• trong trường hợp có lỗi, bước ETL_ERROR với mô tả của nó sẽ được tạo
• lượng dữ liệu được xử lý phải được đánh dấu, ví dụ, bằng dấu hoa thị
• quy trình tương tự có thể được bắt đầu cùng lúc với tham số force_restart=y, nếu không có tham số này, số phiên chỉ được cấp cho quy trình đã hoàn thành
• ở chế độ bình thường, bạn không thể chạy song song cùng một quy trình xử lý dữ liệu

Các thao tác cần thiết để làm việc với bảng như sau:

• lấy số phiên của thủ tục ETL đang chạy
• chèn mục nhật ký vào bảng
• nhận bản ghi thành công cuối cùng của thủ tục ETL

Trong các cơ sở dữ liệu như Oracle hoặc Postgres, các thao tác này có thể được triển khai dưới dạng các hàm tích hợp sẵn. sqlite yêu cầu một cơ chế bên ngoài và trong trường hợp này, nó nguyên mẫu trong PHP.

Đầu ra

Do đó, các thông báo lỗi trong các công cụ xử lý dữ liệu đóng một vai trò cực kỳ quan trọng. Nhưng thật khó để gọi chúng là tối ưu để nhanh chóng tìm ra nguyên nhân của vấn đề. Khi số lượng thủ tục lên tới hàng trăm, thì quá trình giám sát sẽ biến thành một dự án phức tạp.

Bài báo cung cấp một ví dụ về giải pháp khả thi cho vấn đề ở dạng nguyên mẫu. Toàn bộ nguyên mẫu kho lưu trữ nhỏ có sẵn trong gitlab Tiện ích SQLite PHP ETL.

Nguồn: www.habr.com