Molti utilizzano strumenti specializzati per creare procedure di estrazione, trasformazione e caricamento dei dati in database relazionali. Il processo di utilizzo degli strumenti viene registrato e gli errori vengono documentati.
In caso di errore, il log contiene informazioni su cosa ha impedito all'utensile di portare a termine il compito e quali moduli (spesso sono java) si sono bloccati. Nelle ultime righe si può trovare l'errore del database, ad esempio, una violazione della chiave unica della tabella.
Per rispondere alla domanda riguardo al ruolo delle informazioni sugli errori ETL, ho classificato tutti i problemi verificatisi negli ultimi due anni in un considerevole data warehouse.
Gli errori di database includono situazioni come mancanza di spazio, connessione interrotta, sessione bloccata, e così via.
Gli errori logici comprendono questioni come violazione delle chiavi di tabella, oggetti non validi, mancanza di accesso agli oggetti, ecc.
Il pianificatore può essere avviato in ritardo, può bloccarsi, e così via.
Gli errori semplici non richiedono molto tempo per essere corretti. La maggior parte di essi un buon ETL riesce a gestirli autonomamente.
Gli errori complessi richiedono l'apertura e il controllo delle procedure di gestione dei dati, l'esplorazione delle fonti di dati. Spesso portano alla necessità di testare le modifiche e fare il deployment.
Quindi, metà di tutti i problemi è correlata al database. Il 48% di tutti gli errori sono semplici errori.
Un terzo di tutti i problemi è legato al cambiamento della logica o del modello del data warehouse, la maggior parte di questi errori è complessa.
E meno di un quarto di tutti i problemi è associato al pianificatore di attività, il 18% di questi sono errori semplici.
In generale, il 22% di tutti gli errori registrati sono complessi, la loro correzione richiede il maggior tempo e attenzione. Si verificano circa una volta a settimana, mentre gli errori semplici accadono quasi ogni giorno.
È evidente che il monitoraggio dei processi ETL sarà efficace solo quando il log indica con precisione il luogo dell'errore e richiede il minimo tempo per reperire la fonte del problema.
Monitoraggio efficace
Cosa mi piacerebbe vedere nel processo di monitoraggio ETL?
Start at — quando ha iniziato a lavorare,
Source — fonte dei dati,
Layer — quale livello del data warehouse vieneCaricato,
ETL Job Name — procedura di caricamento che consiste in molti piccoli passi,
Step Number — numero del passo eseguito,
Affected Rows — quanti dati sono già stati elaborati,
Duration sec — quanto tempo impiega,
Status — tutto bene o no: OK, ERROR, RUNNING, HANGS
Message — ultimo messaggio riuscito o descrizione dell'errore.
Sulla base dello stato dei record, è possibile inviare un'email ad altri partecipanti. Se non ci sono errori, l'email non è necessaria.
In questo modo, in caso di errore, il luogo dell'incidente è chiaramente indicato.
A volte accade che lo stesso strumento di monitoraggio non funzioni. In tal caso, è possibile richiamare direttamente il view nel database sul quale si basa il report.
Tabella di monitoraggio ETL
Per implementare il monitoraggio dei processi ETL è sufficiente una tabella e una vista.
Per fare ciò, possiamo tornare nel e creare un prototipo nel database sqlite.
DDL tabella
CREATE TABLE UTL_JOB_STATUS (
/* Tabella per la registrazione del log di esecuzione del lavoro. È importante che il lavoro abbia i passi ETL_START e ETL_END o ETL_ERROR */
UTL_JOB_STATUS_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
SID INTEGER NOT NULL DEFAULT -1, /* Identificatore di sessione. Unico per ogni esecuzione del lavoro */
LOG_DT INTEGER NOT NULL DEFAULT 0, /* Data e ora */
LOG_D INTEGER NOT NULL DEFAULT 0, /* Data */
JOB_NAME TEXT NOT NULL DEFAULT 'N/A', /* Nome del lavoro come JOB_STG2DM_GEO */
STEP_NAME TEXT NOT NULL DEFAULT 'N/A', /* ETL_START, ... , ETL_END/ETL_ERROR */
STEP_DESCR TEXT, /* Descrizione del compito o messaggio d'errore */
UNIQUE (SID, JOB_NAME, STEP_NAME)
);
INSERT INTO UTL_JOB_STATUS (UTL_JOB_STATUS_ID) VALUES (-1);DDL vista/report
CREA VISTA SE NON ESISTE UTL_JOB_STATUS_V
AS /* Contenuto: Log di Esecuzione del Pacchetto per gli ultimi 3 Mesi. */
CON SRC AS (
SELEZIONA LOG_D,
LOG_DT,
UTL_JOB_STATUS_ID,
SID,
CASE WHEN INSTR(JOB_NAME, 'FTP') THEN 'TRASFERIMENTO' /* trasferimento file */
WHEN INSTR(JOB_NAME, 'STG') THEN 'STAGE' /* fase */
WHEN INSTR(JOB_NAME, 'CLS') THEN 'PULIZIA' /* pulizia */
WHEN INSTR(JOB_NAME, 'DIM') THEN 'DIMENSIONE' /* dimensione */
WHEN INSTR(JOB_NAME, 'FCT') THEN 'FATTO' /* fatto */
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 'LOG ACCESSO' /* sorgente */
WHEN INSTR(JOB_NAME, 'MASTER') THEN 'DATI MASTER' /* sorgente */
WHEN INSTR(JOB_NAME, 'AD-HOC') THEN 'AD-HOC' /* sorgente */
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
DA UTL_JOB_STATUS
DOVE datetime(LOG_D, 'unixepoch') >= date('now', 'start of month', '-3 month')
)
SELEZIONA JB.SID,
JB.MIN_LOG_DT COME START_DT,
strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) COME LOG_DT,
JB.SOURCE,
JB.LAYER,
JB.JOB_NAME,
CASE
WHEN JB.ERROR_FLAG = 1 ALLORA 'ERRORE'
WHEN JB.ERROR_FLAG = 0 E JB.END_FLAG = 0 E strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 ALLORA 'IN ATTESA' /* mezz'ora */
WHEN JB.ERROR_FLAG = 0 E JB.END_FLAG = 0 ALLORA 'IN ESECUZIONE'
ELSE 'OK'
FINE COME STATO,
ERR.STEP_LOG COME STEP_LOG,
JB.CNT COME STEP_CNT,
JB.AFFECTED_ROWS COME AFFECTED_ROWS,
strftime('%d.%m.%Y %H:%M', datetime(JB.MIN_LOG_DT, 'unixepoch')) COME JOB_START_DT,
strftime('%d.%m.%Y %H:%M', datetime(JB.MAX_LOG_DT, 'unixepoch')) COME JOB_END_DT,
JB.MAX_LOG_DT - JB.MIN_LOG_DT COME JOB_DURATION_SEC
DA
( SELECT SID, SOURCE, LAYER, JOB_NAME,
MAX(UTL_JOB_STATUS_ID) COME UTL_JOB_STATUS_ID,
MAX(START_FLAG) COME START_FLAG,
MAX(END_FLAG) COME END_FLAG,
MAX(ERROR_FLAG) COME ERROR_FLAG,
MIN(LOG_DT) COME MIN_LOG_DT,
MAX(LOG_DT) COME MAX_LOG_DT,
SUM(1) COME CNT,
SUM(IFNULL(AFFECTED_ROWS, 0)) COME AFFECTED_ROWS
DA SRC
GROUP BY SID, SOURCE, LAYER, JOB_NAME
) JB,
( SELECT UTL_JOB_STATUS_ID, SID, JOB_NAME, STEP_LOG
DA SRC
DOVE 1 = 1
) ERR
DOVE 1 = 1
E JB.SID = ERR.SID
E JB.JOB_NAME = ERR.JOB_NAME
E JB.UTL_JOB_STATUS_ID = ERR.UTL_JOB_STATUS_ID
ORDINA PER JB.MIN_LOG_DT DESC, JB.SID DESC, JB.SOURCE;Verifica SQL della possibilità di ottenere un nuovo numero di sessione
SELEZIONA SOMMA (
CASE WHEN start_job.JOB_NAME IS NOT NULL E end_job.JOB_NAME IS NULL /* il lavoro esistente è terminato */
E NON ( 'y' = 'n' ) /* forza riavvio PARAMETRO */
ALLORA 1 ELSE 0
FINE ) COME IS_RUNNING
DA
( SELEZIONA 1 COME dummy DA UTL_JOB_STATUS DOVE sid = -1) d_job
LEFT OUTER JOIN
( SELEZIONA JOB_NAME, SID, 1 COME dummy
DA UTL_JOB_STATUS
DOVE JOB_NAME = 'RPT_ACCESS_LOG' /* nome lavoro PARAMETRO */
E STEP_NAME = 'ETL_START'
GROUP BY JOB_NAME, SID
) start_job /* inizio */
ON d_job.dummy = start_job.dummy
LEFT OUTER JOIN
( SELEZIONA JOB_NAME, SID
DA UTL_JOB_STATUS
DOVE JOB_NAME = 'RPT_ACCESS_LOG' /* nome lavoro PARAMETRO */
E STEP_NAME in ('ETL_END', 'ETL_ERROR') /* stato di fermo */
GROUP BY JOB_NAME, SID
) end_job /* fine */
ON start_job.JOB_NAME = end_job.JOB_NAME
E start_job.SID = end_job.SIDCaratteristiche della tabella:
- l'inizio e la fine della procedura di elaborazione dei dati devono essere accompagnati dai passaggi ETL_START e ETL_END
- in caso di errore, deve essere creato un passaggio ETL_ERROR con la sua descrizione
- il numero di dati elaborati deve essere evidenziato, ad esempio, con asterischi
- nello stesso momento, la stessa procedura può essere avviata con il parametro force_restart=y; senza di esso, il numero di sessione viene assegnato solo alla procedura completata
- in modalità normale non è possibile avviare parallelamente la stessa procedura di elaborazione dei dati
Le operazioni necessarie per lavorare con la tabella sono le seguenti:
- ottenere il numero di sessione della procedura ETL in esecuzione
- inserire una registrazione del log nella tabella
- ottenere l'ultima registrazione di successo della procedura ETL
In database come Oracle o Postgres, queste operazioni possono essere implementate utilizzando funzioni integrate. Per sqlite è necessario un meccanismo esterno e in questo caso esso .
Risultato
Pertanto, i messaggi di errore negli strumenti di elaborazione dei dati giocano un ruolo molto importante. Tuttavia, è difficile chiamarli ottimali per una rapida ricerca della causa del problema. Quando il numero di procedure si avvicina a cento, il monitoraggio dei processi diventa un progetto complesso.
L'articolo presenta un esempio possibile di soluzione al problema sotto forma di prototipo. L'intero prototipo di un piccolo repository è disponibile su gitlab .
Fonte: habr.com
