á¡áá»á¬ážá¡ááŒá¬ážááẠáááºá ááºáá±áá¬áá±á·á áºáá»á¬ážáá²ááá¯á· áá±áá¬áá¯ááºáá°ááŒááºážá ááŒá±á¬ááºážáá²ááŒááºážááŸáá·áº áááºááŒááºážá¡ááœáẠáá¯ááºáá¯á¶ážáá¯ááºáááºážáá»á¬ážáááºáá®ážááẠá¡áá°ážááŒá¯áááááá¬áá»á¬ážááᯠá¡áá¯á¶ážááŒá¯ááŒáááºá á¡áá¯ááºáá¯ááºááá·áºáááááá¬áá»á¬ážááá¯ááºáááºážá ááºááᯠááŸááºáááºážáááºáá¬ážááŒá®áž á¡ááŸá¬ážá¡ááœááºážáá»á¬ážááᯠááŒááºáááºáá¬ážáááºá
á¡ááŸá¬ážá¡ááœááºážááŸááá«áá ááŸááºáááºážááœáẠtool ááẠáá¯ááºáá±á¬ááºá áá¬ááᯠááŒá®ážááŒá±á¬ááºáááºáá»ááºááœááºááá·áº á¡áá»ááºá¡áááºááŸáá·áº áááºááá·áº modules (áááŒá¬áá java) áá±áá¬ááœáẠáááºááœá¬ážááá·áº á¡áá»ááºá¡áááºáá«ááŸááááºá áá±á¬ááºáá¯á¶ážá á¬ááŒá±á¬ááºážáá»á¬ážááœááºá á¥ááá¬á¡á¬ážááŒáá·áºá ááá¬ážááœááºáá°ážááŒá¬ážáá±á¬áá±á¬á·áá»áá¯ážáá±á¬ááºááŸá¯áá áºáᯠdatabase error ááá¯ááœá±á·ááá¯ááºáááºá
ETL error information ááẠáááºááá·áºá¡áááºážááá¹áááŸáá«áááºáááºááá¯ááá·áºáá±ážááœááºážááá¯ááŒá±ááá¯áááºá ááœááºáá²á·ááá·áºááŸá áºááŸá áºá¡ááœááºáž ááŒá áºááœá¬ážáá²á·áá±á¬ááŒá¿áá¬á¡á¬ážáá¯á¶ážááᯠáá»áœááºá¯ááºááẠááŒá®ážáá¬ážáá±á¬ááá¯ááŸá±á¬ááºááŸá¯áá áºáá¯ááœáẠá¡áá»áá¯ážá¡á á¬ážááœá²ááŒá¬ážáá¬ážáá«áááºá
áá±áá¬áá±á·á Ạá¡ááŸá¬ážá¡ááœááºážáá»á¬ážááœáẠáá±áá¬ááá¯á¶áá±á¬ááºááŒááºážá áá»áááºáááºááŸá¯ ááŒááºáá±á¬ááºááŒááºážá áááºááŸááºáá»áááºááœá²ááŒááºáž á¡á ááŸááááºááá¯á· áá«áááºáááºá
áá¯áá¹áááááºáá±á¬á¡ááŸá¬ážáá»á¬ážááẠááá¬ážáá±á¬á·áá»á¬ážááᯠáá»áá¯ážáá±á¬ááºááŒááºážá ááá¬ážáááºááá¯ááºáá±á¬ á¡áá¬ááá¹áá¯áá»á¬ážá á¡áá¬ááá¹áá¯áá»á¬ážááá¯á· áááºáá±á¬ááºááœáá·áºáááŸáááŒááºáž á
áááºááŒáá·áº áá«áááºáááºá
á¡áá»áááºááá¬ážááœá²áá°ááẠá¡áá»áááºáá®áá
áááºááá¯ááºáá«á áááºážááẠá¡á±ážáá²ááœá¬ážááá¯ááºáááºá
ááá¯ážááŸááºážáá±á¬á¡ááŸá¬ážáá»á¬ážááᯠááŒááºááẠá¡áá»áááºá¡ááŒá¬ááŒá®áž ááá°áá«á áá±á¬ááºážááœááºáá±á¬ ETL ááẠáááºážááá¯á·á¡áá»á¬ážá á¯ááᯠáááºážááá¯ááºááá¯ááºááá¯ááºááœááºááá¯ááºáááºá
ááŸá¯ááºááœá±ážáá±á¬ áá»áá¯á·ááœááºážáá»ááºáá»á¬ážááẠáá±áá¬áááºážááŒá áºáá»á¬ážááᯠááŸá¬ááœá±áááºá áá±áá¬ááŸáá·áº áá¯ááºáá±á¬ááºááŒááºážááá¯ááºáᬠáá¯ááºáá¯á¶ážáá¯ááºáááºážáá»á¬ážááᯠááŸá¬ááœá±áááºááŸáá·áº á ááºážáááºááẠááá¯á¡ááºáá«áááºá áááŒá¬áá ááŒá±á¬ááºážáá²ááŸá¯ á ááºážáááºááŒááºážááŸáá·áº ááŒáá·áºáá»ááºááŒááºážá¡ááœáẠááá¯á¡ááºáááºá
áá«ááŒá±á¬áá·áº ááŒá¿áá¬á¡á¬ážáá¯á¶ážáá²á· áá
áºáááºáᬠdatabase áá²á· áááºááá¯ááºáá«áááºá á¡ááŸá¬ážá¡á¬ážáá¯á¶ážá áá% ááẠááá¯ážááŸááºážáá±á¬á¡ááŸá¬ážáá»á¬ážááŒá
áºáááºá
ááŒá¿áá¬á¡á¬ážáá¯á¶ážá áá¯á¶ážáá¯á¶áá
áºáá¯á¶ááẠááá¯ááŸá±á¬ááºááŸá¯ááá¯ááºáᬠáá¯áá¹áááá±á ááá¯á·ááá¯áẠáá±á¬áºáááºááᯠááŒá±á¬ááºážáá²ááŒááºážááŸáá·áº áááºááá¯ááºáááºá á€á¡ááŸá¬ážáá»á¬ážáá²á០áá
áºáááºáá»á±á¬áºááẠááŸá¯ááºááœá±ážáá«áááºá
ááŒá¿áá¬á¡á¬ážáá¯á¶ážá áá±ážáá¯á¶áá áºáá¯á¶áááºáááºážáá±á¬ 18% ááẠááá¯ážááŸááºážáá±á¬á¡ááŸá¬ážáá»á¬ážááŒá áºáááºá
áá±áá°áá»á¡á¬ážááŒáá·áº á¡ááŸá¬ážá¡ááœááºážá¡á¬ážáá¯á¶ážá 22% ááẠááŸá¯ááºááœá±ážááŒá®áž áááºážááá¯á·á ááá·áºáááºááŸá¯ááẠá¡á¬áá¯á¶á áá¯ááºááŸá¯ááŸáá·áº á¡áá»áááºá¡ááŸááá¯á¶áž ááá¯á¡ááºáááºá áá áºáááºáá»áŸáẠáá áºááŒáááºááá·áº ááŒá áºááœá¬ážáááºáá«áááºá ááá¯ážááŸááºážáá²á·á¡ááŸá¬ážááœá±á áá±á·ááá¯ááºážááá¯ááᯠááŒá áºáááºáá«áááºá
ááŸááºážááŸááºážáááºážáááºážáááºá ETL áá¯ááºáááºážá ááºáá»á¬ážááᯠá á±á¬áá·áºááŒáá·áºá á áºáá±ážááŒááºážááẠá¡ááŸá¬ážáááºáá±áá¬ááᯠááŸááºáááºážááœáẠáááºááá¯ááºááá»áŸ áááá»á áœá¬ááœáŸááºááŒááŒá®áž ááŒá¿áá¬ááááºážááŒá áºááᯠááŸá¬ááœá±ááẠá¡áááºážáá¯á¶ážá¡áá»áááºááá¯á¡ááºáá±á¬á¡áá«ááœáẠáááá±á¬ááºááŸá¯ááŸááááºááŒá áºáááºá
áááá±á¬ááºáá±á¬ á á±á¬áá·áºááŒáá·áºááŸá¯
ETL á á±á¬áá·áºááŒáá·áºáá±áž áá¯ááºáááºážá ááºááœáẠáá»áœááºá¯áẠáá¬ááᯠááŒááºáá»ááºááááºážá
Start at - áá°á¡áá¯ááºá
áá¯ááºáá¯ááºážáá
ááááºážá¡áááºážá¡ááŒá
Ạ- data source á
á¡ááœáŸá¬ - ááá¯ááŸá±á¬ááºááŸá¯á¡ááá·áºááᯠáááºáá±á¬ááºáá±áááºá
ETL Job Name - á¡ááá·áºáááºáá»á¬ážá
áœá¬áá«áááºááá·áº á¡ááºáá¯ááºáá¯ááºáá¯á¶ážáá¯ááºáááºážá
Step Number - áá¯ááºáá±á¬ááºáá±ááá·áº á¡ááá·áºá¡áá±á¡ááœááºá
Affected Rows - áá±áá¬áááºáá»áŸáá±á¬ááºááœááºááŒá®ážááŒá®á
Duration sec - áááºáá±á¬ááºááŒá¬áá²á
á¡ááŒá±á¡áá± - á¡á¬ážáá¯á¶ážá¡áááºááŒá±áááºááŒá
áºá
á± áááŒá±áááºááŒá
áºá
á±- OKá ERRORá Running, HANGS
áááºáá±á·áá»áº - áá±á¬ááºáá¯á¶ážá¡á±á¬ááºááŒááºáá±á¬ áááºáá±á·áá»áº ááá¯á·ááá¯áẠá¡ááŸá¬ážáá±á¬áºááŒáá»ááºá
ááá·áºááœááºážááŸá¯áá»á¬ážá á¡ááŒá±á¡áá±ááᯠá¡ááŒá±áá¶á áááºááẠá¡á®ážáá±ážááºáá áºá á±á¬áẠáá±ážááá¯á·ááá¯ááºáá«áááºá áááŒá¬ážá¡ááœá²á·áááºááœá±áá® á á¬ááá¯á·áááºá á¡ááŸá¬ážá¡ááœááºážáááŸáááẠá á¬ááᯠáááá¯á¡ááºáá«áá°ážá
ááá¯á·ááŒá±á¬áá·áº á¡ááŸá¬ážá¡ááœááºážááŒá áºáá»áŸáẠá¡áááºážááŒá áºááœá¬ážááá·áºáá±áá¬ááᯠááŸááºážááŸááºážáááºážáááºáž ááœáŸááºááŒáá¬ážáááºá
ááá«ááá¶ááœáẠá á±á¬áá·áºááŒáá·áºáá±ážáááááá¬ááá¯ááºááá¯áẠá¡áá¯ááºááá¯ááºáá² ááŒá áºááœá¬ážáááºá á€ááá á¹á ááœááºá á¡á á®áááºáá¶á á¬ááá¯áááºáá±á¬ááºáá¬ážááá·áºá¡ááŒá±áá¶ááŒáá·áºáá±áá¬áá±á·á áºááœááºááŒáá·áºááŸá¯ááŒááºáž (ááŒááºááœááºáž) ááá¯ááá¯ááºááá¯ááºáá±á«áºááá¯ááá¯ááºáááºá
ETL á á±á¬áá·áºááŒáá·áºáá±ážááá¬áž
ETL áá¯ááºáááºážá ááºáá»á¬ážááᯠá á±á¬áá·áºááŒáá·áºá¡áá±á¬ááºá¡áááºáá±á¬áºáááºá ááá¬ážáá áºáá¯ááŸáá·áº á¡ááŒááºáá áºáá¯ááẠáá¯á¶áá±á¬ááºáá«áááºá
áá®ááá¯áá¯ááºááá¯á·, áááºááŒááºáá¬ááá¯ááºáá«áááºá
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);
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 ááẠsession áá¶áá«ááºá¡áá áºáá áºáá¯áááá¯ááºááá¬ážá á áºáá±ážááŒááºážá
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
ááá¬ážá¡ááºá¹áá«áááºáá»á¬áž-
- áá±áá¬áá¯ááºáá±á¬ááºááŒááºážáá¯ááºáááºážá ááºáá¡á ááŸáá·áºá¡áá¯á¶ážááẠETL_START ááŸáá·áº ETL_END á¡ááá·áºáá»á¬ážááŒáá·áº ááá¯ááºáá¬ááááºááŒá áºáááºá
- á¡ááŸá¬ážá¡ááœááºážááŸááá«á áááºážááá±á¬áºááŒáá»ááºáá«ááŸááá±á¬ ETL_ERROR á¡ááá·áºááᯠáááºáá®ážááá·áºáááºá
- á á®áá¶áá±á¬ááºááœááºáá¬ážáá±á¬ áá±áá¬ááá¬áááᯠá¥ááá¬á¡á¬ážááŒáá·áº ááŒááºááœáá·áºáá»á¬ážááŒáá·áº áá®ážáá±á¬ááºážááá¯ážááŒááá·áºáááºá
- áá°áá®áá±á¬áá¯ááºáá¯á¶ážáá¯ááºáááºážá¡á¬áž force_restart=y áá«áá¬áá®áá¬ááŒáá·áº áá áºáá»áááºáááºážááœáẠá áááºááá¯ááºáááºá áááºážááá«áá² áááºááŸááºáá¶áá«ááºááᯠááŒá®ážááŒá±á¬ááºááá·áºáá¯ááºáááºážá ááºá¡ááœááºáᬠáá¯ááºáá±ážáá«áááºá
- áá¯á¶ááŸááºáá¯ááºááœááºá áá°áá®áá±á¬ áá±áá¬áá¯ááºáá±á¬ááºááŒááºážáá¯ááºáááºážá ááºááᯠá¡ááŒáá¯ááºáá¯ááºáá±á¬ááºááááá«á
ááá¬ážáá áºáá¯ááŸáá·áºá¡áá¯ááºáá¯ááºááẠááá¯á¡ááºáá±á¬áá¯ááºáá±á¬ááºááŸá¯áá»á¬ážááŸá¬ á¡á±á¬ááºáá«á¡ááá¯ááºážááŒá áºáááºá
- áá¯ááºáá±á¬ááºáá±ááá·áº ETL áá¯ááºáá¯á¶ážáá¯ááºáááºážá session áá¶áá«ááºááᯠááá°ááŒááºážá
- ááá¬ážáá²ááá¯á· ááŸááºáááºážááá·áºááœááºážáá«á
- ETL áá¯ááºáá¯á¶ážáá¯ááºáááºážá áá±á¬ááºáá¯á¶ážá¡á±á¬ááºááŒááºáá±á¬ááŸááºáááºážááᯠááá°ááŒááºážá
Oracle ááá¯á·ááá¯áẠPostgres áá²á·ááá¯á·áá±á¬ áá±áá¬áá±á·á
áºáá»á¬ážááœáẠá€áá¯ááºáá±á¬ááºáá»ááºáá»á¬ážááᯠáá«áááºááá·áºáá¯ááºáá±á¬ááºáá»ááºáá»á¬ážá¡ááŒá
Ạá¡áá±á¬ááºá¡áááºáá±á¬áºááá¯ááºáááºá sqlite ááẠááŒááºáááá¹ááá¬ážáá
áºáᯠááá¯á¡ááºááŒá®áž á€ááá
á¹á
ááœáẠáááºážááᯠááá¯á¡ááºáááºá
áá±á¬ááºáá»ááº
ááá¯á·ááŒá±á¬áá·áº áá±áá¬áá¯ááºáá±á¬ááºááŒááºážáááááá¬áá»á¬ážááœáẠá¡ááŸá¬ážá¡ááœááºážáááºáá±á·áá»áºáá»á¬ážááẠá¡ááœááºá¡áá±ážááŒá®ážáá±á¬ á¡áááºážááá¹áá០áá«áááºáá«áááºá áá«áá±ááá·áº ááŒá¿áá¬áá²á· á¡ááŒá±á¬ááºážáááºážááᯠá¡ááŒááºááŸá¬ááœá±á·ááá¯á· á¡áá±á¬ááºážáá¯á¶ážááá¯á· áá±á«áºááá¯á· áááºáá«áááºá áá¯ááºáá¯á¶ážáá¯ááºáááºáž á¡áá±á¡ááœáẠáá áºáᬠáá®ážáá¬áá±á¬á¡áá«á áá¯ááºáááºážá áẠá á±á¬áá·áºááŒáá·áºááŒááºáž ááẠááŸá¯ááºááœá±ážáá±á¬ ááá±á¬áá»ááºá¡ááŒá ẠááŒá±á¬ááºážáá²ááœá¬ážáááºá
áá±á¬ááºážáá«ážááẠááŸá±á·ááŒá±ážáá¯á¶á
á¶áá¯á¶á
á¶ááŒáá·áº ááŒá¿áá¬á¡ááœáẠááŒá
áºááá¯ááºáá»á±ááŸááá±á¬ á¡ááŒá±áá
áºáá¯ááᯠá¥ááá¬áá±ážáá¬ážáááºá áá±ážáááºáá±á¬ repository ááŸá±á·ááŒá±ážáá¯á¶á
á¶áá
áºáá¯áá¯á¶ážááᯠgitlab ááœááºáááá¯ááºáááºá
source: www.habr.com