ααα»αααααΆα αααΎαααααΎα§αααααα―ααααααΎααααΈαααααΎαααΈαα·αα·ααΈαααααΆααααΆααααααα αα ααααααα αα·ααααα»ααα·αααααααα αααα»αααΌαααααΆααα·ααααααααααΆααααααα ααααΎαααΆαααα§αααααααααΎααΆαααααΌαααΆααααααααΆααα α»αααααΌαααΆααα½ααα»αα
αααα»αβααααΈβααΆαβααα α»α αααααα ααα»βααΆαβααααααΆαβαααβα§αααααβααΆαβαααΆαααβαααα»αβααΆαβαααααβαα·α αα ααΆα α αΎαβαααΌαα»αβααΆβαααα (ααΆβααΉαααΆαα java) ααΆαβαααβαα βααααααβααΆα αα αααα»ααααααΆααα α»αααααα α’αααα’αΆα ααααΎαααα α»αααΌαααααΆααα·αααααα α§ααΆα ααα ααΆααααααααΎαααααΉααααα½ααααααααααΆααΆαα
ααΎααααΈααααΎααααα½αααΆααΎααααααΆαααα α»α ETL ααΆααα½ααΆααΈα’αααΈ αααα»αααΆαα αΆααααααΆαααααα αΆααΆααα’αααααααΆαααΎαα‘αΎααααα»αααααααααΈαααααΆααααααααααααα αααα»αααααΆααααααΆαα
ααα α»αβααΌαααααΆαβαα·ααααααβαα½αβααΆαβααα αβαα·αβαααααβααααΆαα ααΆαβααΆααβαααβααΆαβαααααΆαα ααΆαβαααα½αβααααβααΆααΎαα
ααα α»αβα‘αΌααΈααβαα½αβααΆαβααΌα
βααΆβααΆαβαααααβααΎβααααΆααα
α»α
βααΆααΆα ααααα»βαα·αβααααΉαααααΌα αααααβααΆαβα
αΌαβααααΎβααααα»βααΆααΎαα
αααααα·ααΈαααααααααααα ααααΆαα·αα
αΆααααααΎαααΆααααααα ααΆα’αΆα
αααααααα
ααα α»αααΆαααααα·αα αααΆααααααΌαααΎααααΈαα½ααα»αα ETL ααα’α’αΆα ααααααααααα½αααΆααΆαα αααΎαααααααα½αα―αα
ααα α»ααααα»αααααΆαααααΎα±ααααΆα αΆαααΆα ααααα»αααΆαααααααα αα·αααΆαααααααΈαα·αα·ααΈαααααΆααααααΎααΆαααΆαα½ααα·αααααα ααΎααααΈαααααααααααααα·ααααααα ααΆααΏααααΆαα±ααααΆααααααΌαααΆααααααΆααααΆαααααΆααααααΌαααΆαααααΎααααααα·αααΆαααΆαααααααΆαα
ααΌα
ααααααΆαααααααΆααααααα αΆααΆααα’ααααΊααΆαααααα
ααΉαααΌαααααΆααα·ααααααα 48% ααααα α»αααΆααα’ααααΊααΆααα α»αααΆααααα
αα½αααΆαααΈαααααα αΆααΆααα’ααααΊααΆαααααα
ααΉαααΆαααααΆααααααΌααααααα·ααααΆααΆααααα»αα¬ααααΌ ααΆαααΆαααααααΆαααααα α»αααΆαααααααΊαααα»αααααΆαα
α αΎααα·α ααΆααα½αααΆααα½ααααααα αΆααΆααα’ααααΊααΆαααααα ααΉααααααα·ααΈααααααααααΆααα·α αα ααα 18% ααΊααΆααα α»αααΆααααα
ααΆααΌαα 22% ααααα α»αααΆααα’αααααααΎαα‘αΎαααΊαααα»αααααΆα α αΎαααΆααααααααΌααααααα½αααααΆαααΆαααΆαααα α·ααααα»αααΆαα αα·ααααααααΆα αααΎααααα»αα αα½αααΆααΎαα‘αΎααααα αααααααααα»ααα½ααααααΆα αα α ααααβα―βααα α»αβααΆααααβααΎαβα‘αΎαβααααΎαβααΆααβααααα
ααΆααααααα ααΆααααα½ααα·αα·αααααααΎαααΆα ETL ααΉαααΆαααααα·αααααΆααα ααααααααΈααΆααααα α»αααααΌαααΆαα ααα’α»ααααα αΆααα αααα»ααααααα ααα»α±ααααΆαααααΉαααααΌαααΆααααα’αΆα ααααΎααΆα α αΎαααααΌαααΆααααααααΆα’αααααααΆααΎααααΈαααααααααααααααααα αΆα
ααΆααααα½ααα·αα·αααααααααααααααα·αααααΆα
ααΎαααα»αα ααααΎαα’αααΈαααααα αααα»αααααΎαααΆααααα½ααα·αα·ααα ETL?
α
αΆααααααΎα - αα
αααααΆααα
αΆααααααΎαααΆαααΆαα
ααααα - ααααααα·αααααα,
αααααΆαα - ααΎααααα·αααααΆααααα»αααααΌαααΆααααα»α,
αααααααΆαααΆα ETL - ααααΎαααΆααααα»αα‘αΎααααααΆαααα αΆαααΌα
αααΆα
αααΎα
αααααα αΆα - α
ααα½αααα αΆαααααααα»αααααΌαααΆαα’αα»αααα
αα½αααααααααααααΆαα - ααΎαα·ααααααααα»ααααΆαααααΌαααΆαααααΎαααΆααα½α
α αΎα
αααααααα·ααΆααΈ - ααΎααΆααααΌαα
αααΆααααααα»ααααΆα,
ααααΆαααΆα - ααΆααΎα’αααΈααααααααΆαααα’α¬α’ααα ααααααα, ααα α»α, ααααΎαααΆα, αααα½α
ααΆα - ααΆαααααααα
α»αααααα α¬ααΆααα·αααααΆα’αααΈααα α»αα
αααααα’ααααΎααααΆαααΆααααααααααααΆ α’αααα’αΆα ααααΎα’ααΈαααα αααα»ααααα αααΆαα·αααααααααα ααααα·αααΎαα·αααΆαααα α»αααααααα·αα·ααα·αα αΆαααΆα αααα
ααΌα αααααααα»αααααΈααΆαααα α»α ααΈααΆααααα§ααααααα·α ααα»ααααΌαααΆαα ααα’α»ααααα αΆααααΆαα αααΆααα
αα½αααΆαααΆααΎαα‘αΎαααΆα§ααααααααα½ααα·αα·ααααααα½αα―ααα·αααααΎαααΆαααα αααα»αααααΈααα ααα’αΆα α α αα·αααααΆα (ααΎα) αααααααΆαααα αααα»αααΌαααααΆααα·αααααα αααααααΎααΌαααααΆαααααααΆαααΆαααααααΌαααΆααααααΎαα‘αΎαα
ααΆααΆααααα½ααα·αα·ααα 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 αααα»ααα·αα·αααααΎαααΆααΎααΆα’αΆα αα αα½α ααΎααααΈααα½αααΆααααααααααααΈα
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 αααααααΎαααΆα
- αααα αΌααααααα ααα»αα αααα»αααΆααΆα
- ααα½αααΆααααααααααΆααααααα α»ααααααααααΈαα·αα·ααΈ ETL
αα
αααα»αααΌαααααΆααα·ααααααααΌα
ααΆ Oracle α¬ Postgres ααααα·ααααα·ααΆαααΆαααααα’αΆα
ααααΌαααΆαα’αα»ααααααΆαα»αααΆααααααααΆααααααΆαα½αα sqlite ααΆαααΆαααααααΆαααΆααααα
α αΎααααα»αααααΈααα
ααα ααααΈααααα·ααααΆα
ααΌα αααα ααΆαααα α»ααα αααα»αα§αααααααααΎαααΆααα·ααααααααΎααα½ααΆααΈαααΆαααααΆααα ααα»ααααααΆαα·ααΆααααα»αααΆαα α αα½αααααΆααα’αααα»ααααααΆααααΆααααααααααΌαα ααα»αααααα αΆααΆααααΆαααΆαααα ααα αα ααααααα ααα½αααΈαα·αα·ααΈααΆαααααα½ααα ααΆααααα½ααα·αα·αααααααΎαααΆααααααα ααΆαααααααααα»αααααΆαα
α’ααααααααααααΌαα§ααΆα ααααααααααααααΆααααα’αΆα
ααΎαααΆαα
αααααααα αΆαααα»αααααααααΆααααΌα ααααΌααααΆααααΌα
ααΆααααΌαααΆααα
αααα»α gitlab
ααααα: www.habr.com