Geekly articles weekly

Memantau proses ETL di gudang data kecil

Banyak yang menggunakan alat khusus untuk membuat prosedur untuk mengekstraksi, mengubah, dan memuat data ke dalam basis data relasional. Proses alat kerja dicatat, kesalahan dicatat.

Jika terjadi kesalahan, log berisi informasi bahwa alat gagal menyelesaikan tugas dan modul mana (sering java) tempat mereka berhenti. Di baris terakhir Anda dapat menemukan kesalahan database, misalnya, pelanggaran kunci tabel yang unik.

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

gambar

Karakteristik penyimpanan tempat klasifikasi dilakukan:

  • 20 sumber data terhubung
  • 10,5 miliar baris diproses setiap hari
  • yang dikumpulkan hingga 50 juta baris,
  • data memproses 140 paket dalam 700 langkah (langkah adalah satu permintaan sql)
  • server - basis data X5 4-simpul

Kesalahan basis data termasuk seperti kehabisan ruang, koneksi terputus, menggantung sesi, dll.

Kesalahan logis termasuk pelanggaran kunci tabel, objek tidak valid, kurangnya akses ke objek, dll.
Penjadwal mungkin tidak memulai pada waktu yang tepat, mungkin macet, dll.

Kesalahan sederhana tidak memerlukan banyak waktu untuk memperbaikinya. Dengan sebagian besar dari mereka, ETL yang baik dapat mengatasi sendiri.

Kesalahan rumit membuatnya perlu untuk membuka dan memeriksa prosedur untuk bekerja dengan data, untuk meneliti sumber data. Seringkali mengarah pada kebutuhan untuk menguji perubahan dan penerapan.

Jadi, setengah dari semua masalah terkait dengan database. 48% dari semua kesalahan adalah kesalahan sederhana.
Bagian ketiga dari semua masalah dikaitkan dengan perubahan dalam logika atau model repositori, lebih dari setengah kesalahan ini kompleks.

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

Secara umum, 22% dari semua kesalahan yang terjadi adalah kompleks, memperbaikinya membutuhkan paling banyak perhatian dan waktu. Mereka terjadi kira-kira seminggu sekali. Sementara kesalahan sederhana terjadi hampir setiap hari.

Jelas, proses pemantauan ETL kemudian akan efektif ketika lokasi kesalahan ditunjukkan seakurat mungkin dalam log dan waktu minimum diperlukan untuk menemukan sumber masalah.

Pemantauan yang efektif


Apa yang ingin saya lihat dalam proses pemantauan ETL?

gambar
Mulai pada - saat mulai
Sumber - sumber data
Layer - berapa level penyimpanan yang dimuat,
Nama Pekerjaan ETL adalah prosedur pemuatan yang terdiri dari banyak langkah kecil,
Nomor Langkah - jumlah langkah yang harus dilakukan,
Baris yang Terkena Dampak - berapa banyak data yang telah diproses,
Durasi dtk - berapa lama untuk dieksekusi,
Status - apakah semuanya baik atau tidak: OK, KESALAHAN, MENJALANKAN, HANGS
Pesan - Deskripsi pesan atau kesalahan terakhir yang berhasil.

Berdasarkan status entri, Anda dapat mengirim email. surat kepada peserta lain. Jika tidak ada kesalahan, maka surat itu tidak perlu.

Dengan demikian, dalam hal terjadi kesalahan, lokasi kejadian jelas ditunjukkan.

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

Tabel Pemantauan ETL


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

Untuk melakukan ini, Anda dapat kembali ke repositori kecil Anda dan membuat 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);

Pengajuan / laporan 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 diikuti oleh langkah-langkah ETL_START dan ETL_END
  • jika terjadi kesalahan, langkah ETL_ERROR harus dibuat dengan uraiannya
  • jumlah data yang diproses harus disorot, misalnya, dengan tanda bintang
  • pada saat yang sama, prosedur yang sama dapat dimulai dengan parameter force_restart = y; tanpanya, nomor sesi hanya dikeluarkan untuk prosedur yang selesai
  • dalam mode normal, Anda tidak dapat menjalankan prosedur pemrosesan data yang sama secara paralel

Operasi yang diperlukan untuk bekerja dengan tabel adalah sebagai berikut:

  • memulai nomor sesi prosedur ETL
  • masukkan entri log ke dalam tabel
  • mendapatkan catatan prosedur ETL sukses terakhir

Dalam basis data seperti Oracle atau Postgres, operasi ini dapat diimplementasikan dengan fungsi bawaan. Sqlite membutuhkan mekanisme eksternal, dan dalam hal ini prototipe di PHP .

Kesimpulan


Dengan demikian, pesan kesalahan dalam alat pemrosesan data memainkan peran mega-penting. Tetapi sulit untuk menyebut mereka optimal untuk pencarian cepat untuk penyebab masalah. Ketika jumlah prosedur mendekati seratus, pemantauan proses berubah menjadi proyek yang kompleks.

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

Source: https://habr.com/ru/post/id465637/

More articles:


All Articles