ProHoster > blog > administrasi > Memantau proses ETL di gudang data kecil

Memantau proses ETL di gudang data kecil

Banyak orang menggunakan alat khusus untuk membuat rutinitas untuk mengekstraksi, mengubah, dan memuat data ke dalam database relasional. Proses alat dicatat, kesalahan dicatat.

Jika terjadi kesalahan, log berisi informasi bahwa alat tersebut gagal menyelesaikan tugas dan modul mana (sering kali Java) berhenti di mana. Baris terakhir mungkin berisi kesalahan database, seperti pelanggaran terhadap kunci unik tabel.

Untuk menjawab pertanyaan tentang apa peran informasi kesalahan ETL, saya mengklasifikasikan semua masalah yang terjadi selama dua tahun terakhir ke dalam repositori yang cukup besar.

Memantau proses ETL di gudang data kecil

Kesalahan database meliputi: tidak ada cukup ruang, koneksi terputus, sesi terhenti, dll.

Kesalahan logis termasuk pelanggaran kunci tabel, objek tidak valid, kurangnya akses ke objek, dll.
Penjadwal mungkin tidak diluncurkan tepat waktu, mungkin terhenti, dll.

Kesalahan sederhana tidak membutuhkan banyak waktu untuk memperbaikinya. ETL yang baik dapat menangani sebagian besar masalah tersebut sendirian.

Kesalahan yang kompleks mengharuskan pembukaan dan pemeriksaan prosedur penanganan data dan menyelidiki sumber data. Seringkali menimbulkan kebutuhan untuk menguji perubahan dan menerapkannya.

Jadi, setengah dari seluruh masalah terkait dengan database. 48% dari semua kesalahan adalah kesalahan sederhana.
Sepertiga dari semua masalah terkait dengan perubahan logika atau model penyimpanan; lebih dari separuh kesalahan ini bersifat kompleks.

Dan kurang dari seperempat dari seluruh masalah terkait dengan penjadwal tugas, 18% di antaranya merupakan kesalahan sederhana.

Secara keseluruhan, 22% dari seluruh kesalahan yang terjadi bersifat kompleks dan memerlukan perhatian dan waktu paling besar untuk memperbaikinya. Itu terjadi sekitar seminggu sekali. Padahal kesalahan sederhana terjadi hampir setiap hari.

Jelasnya, pemantauan proses ETL akan efektif bila lokasi kesalahan ditunjukkan dalam log seakurat mungkin dan diperlukan waktu minimal untuk menemukan sumber masalahnya.

Pemantauan yang efektif

Apa yang ingin saya lihat dalam proses pemantauan ETL?

Memantau proses ETL di gudang data kecil
Mulai pada - saat saya mulai bekerja,
Sumber – sumber data,
Lapisan - tingkat penyimpanan mana yang dimuat,
Nama Pekerjaan ETL adalah prosedur pemuatan yang terdiri dari banyak langkah kecil,
Nomor Langkah β€” nomor langkah yang dijalankan,
Baris yang Terkena Dampak - berapa banyak data yang telah diproses,
Durasi detik - berapa lama waktu yang dibutuhkan untuk mengeksekusi,
Status - apakah semuanya baik atau tidak: OK, ERROR, RUNNING, HANGS
Pesan β€” pesan terakhir yang berhasil atau deskripsi kesalahan.

Berdasarkan status rekaman, Anda dapat mengirim email. surat kepada peserta lain. Jika tidak ada kesalahan, maka surat tidak diperlukan.

Dengan cara ini, jika terjadi kesalahan, lokasi kejadian dapat ditunjukkan dengan jelas.

Terkadang alat pemantauan itu sendiri tidak berfungsi. Dalam hal ini, dimungkinkan untuk memanggil tampilan (view) langsung di database, yang menjadi dasar pembuatan laporan.

Tabel pemantauan ETL

Untuk mengimplementasikan pemantauan proses ETL, satu tabel dan satu tampilan sudah cukup.

Untuk melakukan ini, Anda dapat kembali ke penyimpanan kecil Anda sendiri dan buat prototipe di database sqlite.

tabel 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);

Lihat/laporkan 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 Memeriksa kemampuan untuk mendapatkan nomor sesi baru

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

Fitur Tabel:

  • awal dan akhir prosedur pemrosesan data harus disertai dengan langkah ETL_START dan ETL_END
  • jika terjadi kesalahan, langkah ETL_ERROR harus dibuat dengan deskripsinya
  • jumlah data yang diproses harus disorot, misalnya dengan tanda bintang
  • prosedur yang sama dapat dimulai pada waktu yang sama dengan parameter force_restart=y; tanpanya, nomor sesi hanya dikeluarkan untuk prosedur yang telah selesai
  • dalam mode normal tidak mungkin menjalankan prosedur pemrosesan data yang sama secara paralel

Operasi yang diperlukan untuk bekerja dengan tabel adalah sebagai berikut:

  • mendapatkan nomor sesi prosedur ETL yang diluncurkan
  • memasukkan entri log ke dalam tabel
  • mendapatkan catatan sukses terakhir dari prosedur ETL

Dalam database seperti Oracle atau Postgres, operasi ini dapat diimplementasikan dengan fungsi bawaan. sqlite memerlukan mekanisme eksternal dan dalam hal ini dibuat prototipenya dalam PHP.

Keluaran

Oleh karena itu, pelaporan kesalahan dalam alat pemrosesan data memainkan peran yang sangat penting. Namun mereka hampir tidak bisa disebut optimal untuk menemukan penyebab masalahnya dengan cepat. Ketika jumlah prosedur mendekati seratus, pemantauan proses berubah menjadi proyek yang kompleks.

Artikel ini memberikan contoh solusi yang mungkin untuk masalah tersebut dalam bentuk prototipe. Seluruh prototipe repositori kecil tersedia di gitlab Utilitas SQLite PHP ETL.

Sumber: www.habr.com

Tambah komentar