Molti utilizzano strumenti specializzati per creare procedure di estrazione, trasformazione e caricamento dei dati in database relazionali. Il processo di lavoro degli strumenti viene registrato e gli errori vengono annotati.
In caso di errore, il registro contiene informazioni sul fatto che lo strumento non è riuscito a completare il compito e quali moduli (spesso è java) si sono bloccati. Nelle ultime righe si può trovare l'errore del database, ad esempio, 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 ampio data warehouse.
Tra gli errori del database ci sono quelli come mancanza di spazio, connessione interrotta, sessione bloccata, ecc.
Gli errori logici includono quelli come violazione delle chiavi della tabella, oggetti non validi, mancanza di accesso agli oggetti, ecc.
La pianificazione può essere eseguita in ritardo, può bloccarsi, ecc.
Gli errori semplici non richiedono molto tempo per essere corretti. La maggior parte di essi può essere gestita autonomamente da un buon ETL.
Gli errori complessi richiedono di aprire e verificare le procedure di gestione dei dati, esaminare le fonti di dati. Spesso portano alla necessità di testare le modifiche e il deploy.
Quindi, metà di tutti i problemi è legata al database. Il 48% di tutti gli errori sono errori semplici.
Un terzo di tutti i problemi è associato a cambiamenti nella logica o nel modello del data warehouse, oltre metà di questi errori sono complessi.
Meno di un quarto di tutti i problemi è legato al pianificatore delle attività, il 18% dei quali sono errori semplici.
In generale, il 22% di tutti gli errori verificatisi è complesso, la cui correzione richiede il massimo dell'attenzione e del tempo. Si verificano circa una volta alla settimana. Mentre gli errori semplici accadono quasi ogni giorno.
È evidente che il monitoraggio dei processi ETL è efficace quando nel registro è indicato il luogo dell'errore con la massima precisione e richiede il minimo tempo per trovare la fonte del problema.
Monitoraggio efficace
Cosa mi piacerebbe vedere nel processo di monitoraggio ETL?
Inizio alle — quando è iniziato il lavoro,
Fonte — fonte dei dati,
Livello — quale livello del data warehouse viene caricato,
Nome del lavoro ETL — procedura di caricamento che consiste in molti piccoli passaggi,
Numero del passo — numero del passo in esecuzione,
Righe interessate — quanti dati sono già stati elaborati,
Durata sec — quanto tempo ci vuole per completare,
Stato — è tutto ok o no: OK, ERRORE, IN CORSO, BLOCCATO
Messaggio — ultimo messaggio riuscito o descrizione dell'errore.
In base allo stato delle registrazioni, è possibile inviare un'e-mail ad altri partecipanti. Se non ci sono errori, l'email non è necessaria.
In questo modo, in caso di errore, è indicato chiaramente il luogo dell'incidente.
A volte può capitare che lo strumento di monitoraggio non funzioni. In tal caso, è possibile richiamare direttamente nella base di dati la vista sulla quale è stato costruito il report.
Tabella di monitoraggio ETL
Per monitorare i processi ETL è sufficiente una tabella e una vista.
Per questo, è possibile tornare al e creare un prototipo nel database sqlite.
DDL della tabella
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 vista/report
CREA VISUALIZZAZIONE SE NON ESISTE UTL_JOB_STATUS_V
COME
/* Contenuto: Registro di esecuzione del pacchetto per gli ultimi 3 mesi. */
CON SRC AS (
SELECT 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 'STADIO' /* 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
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 'ERRORE'
WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 AND strftime('%s','now') - JB.MIN_LOG_DT > 0.5*60*60 THEN 'IN FONDO' /* mezz'ora */
WHEN JB.ERROR_FLAG = 0 AND JB.END_FLAG = 0 THEN 'IN ESECUZIONE'
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;Controllo SQL della possibilità di ottenere un nuovo numero di sessione
SELECT SUM (
CASE WHEN start_job.JOB_NAME IS NOT NULL AND end_job.JOB_NAME IS NULL /* il lavoro esistente è terminato */
AND NOT ( 'y' = 'n' ) /* forzare il riavvio PARAMETRO */
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' /* nome lavoro PARAMETRO */
AND STEP_NAME = 'ETL_START'
GROUP BY JOB_NAME, SID
) start_job /* inizio */
ON d_job.dummy = start_job.dummy
LEFT OUTER JOIN
( SELECT JOB_NAME, SID
FROM UTL_JOB_STATUS
WHERE JOB_NAME = 'RPT_ACCESS_LOG' /* nome lavoro PARAMETRO */
AND STEP_NAME in ('ETL_END', 'ETL_ERROR') /* fermata stato */
GROUP BY JOB_NAME, SID
) end_job /* fine */
ON start_job.JOB_NAME = end_job.JOB_NAME
AND 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
- Allo stesso tempo, 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 eseguire in parallelo 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
- Inserimento di 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 tramite funzioni integrate. Per SQLite è necessario un meccanismo esterno, e in questo caso è .
Conclusione
Pertanto, i messaggi di errore negli strumenti di elaborazione dei dati rivestono un'importanza fondamentale. Ma è difficile definirli ottimali per una rapida individuazione della causa del problema. Quando il numero di procedure si avvicina al centinaio, il monitoraggio dei processi si trasforma in un progetto complesso.
L'articolo presenta un esempio di una possibile soluzione al problema sotto forma di prototipo. L'intero prototipo di un piccolo repository è disponibile su gitlab .
Fonte: habr.com
