๋ง์ ์ฌ๋๋ค์ด ํน์ ๋๊ตฌ๋ฅผ ์ฌ์ฉํ์ฌ ๋ฐ์ดํฐ๋ฅผ ๊ด๊ณํ ๋ฐ์ดํฐ๋ฒ ์ด์ค๋ก ์ถ์ถ, ๋ณํ ๋ฐ ๋ก๋ํ๋ ์ ์ฐจ๋ฅผ ๋ง๋ญ๋๋ค. ์์ ๋๊ตฌ ํ๋ก์ธ์ค๊ฐ ๊ธฐ๋ก๋๊ณ ์ค๋ฅ๊ฐ ์์ ๋ฉ๋๋ค.
์ค๋ฅ๊ฐ ๋ฐ์ํ ๊ฒฝ์ฐ ๋ก๊ทธ์๋ ๋๊ตฌ๊ฐ ์์ ์ ์๋ฃํ์ง ๋ชปํ๊ณ ์ด๋ค ๋ชจ๋(์ข ์ข java)์ด ์ด๋์์ ์ค์ง๋์๋์ง์ ๋ํ ์ ๋ณด๊ฐ ํฌํจ๋ฉ๋๋ค. ๋ง์ง๋ง ์ค์์ ๋ฐ์ดํฐ๋ฒ ์ด์ค ์ค๋ฅ(์: ํ ์ด๋ธ ๊ณ ์ ํค ์๋ฐ)๋ฅผ ์ฐพ์ ์ ์์ต๋๋ค.
ETL ์ค๋ฅ ์ ๋ณด๊ฐ ์ด๋ค ์ญํ ์ ํ๋์ง์ ๋ํ ์ง๋ฌธ์ ๋ตํ๊ธฐ ์ํด ์ง๋ XNUMX๋ ๋์ ๋ฐ์ํ ๋ชจ๋ ๋ฌธ์ ๋ฅผ ๋ค์ ํฐ ์ ์ฅ์์ ๋ถ๋ฅํ์ต๋๋ค.
๋ฐ์ดํฐ๋ฒ ์ด์ค ์ค๋ฅ์๋ ๊ณต๊ฐ ๋ถ์กฑ, ์ฐ๊ฒฐ ๋๊น, ์ธ์ ์ค๋จ ๋ฑ์ด ํฌํจ๋ฉ๋๋ค.
๋
ผ๋ฆฌ์ ์ค๋ฅ์๋ ํ
์ด๋ธ ํค ์๋ฐ, ์ ํจํ์ง ์์ ๊ฐ์ฒด, ๊ฐ์ฒด์ ๋ํ ์ก์ธ์ค ๋ถ์กฑ ๋ฑ์ด ํฌํจ๋ฉ๋๋ค.
์ค์ผ์ค๋ฌ๊ฐ ์ ์์ ์์๋์ง ์๊ฑฐ๋ ๋ฉ์ถ ์ ์์ต๋๋ค.
๊ฐ๋จํ ์ค์๋ ์์ ํ๋ ๋ฐ ์ค๋ ๊ฑธ๋ฆฌ์ง ์์ต๋๋ค. ์ข์ ETL์ ๋๋ถ๋ถ์ ์์ฒด์ ์ผ๋ก ์ฒ๋ฆฌํ ์ ์์ต๋๋ค.
๋ณต์กํ ๋ฒ๊ทธ๋ก ์ธํด ๋ฐ์ดํฐ ์๋ณธ์ ํ์ํ๊ธฐ ์ํด ๋ฐ์ดํฐ ์์ ์ ์ํ ์ ์ฐจ๋ฅผ ๋ฐ๊ฒฌํ๊ณ ํ ์คํธํด์ผ ํฉ๋๋ค. ์ข ์ข ๋ณ๊ฒฝ ํ ์คํธ ๋ฐ ๋ฐฐํฌ๊ฐ ํ์ํฉ๋๋ค.
๋ฐ๋ผ์ ๋ชจ๋ ๋ฌธ์ ์ ์ ๋ฐ์ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ๊ด๋ จ์ด ์์ต๋๋ค. ๋ชจ๋ ์ค์์ 48%๋ ๋จ์ํ ์ค์์
๋๋ค.
๋ชจ๋ ๋ฌธ์ ์ XNUMX/XNUMX์ ์คํ ๋ฆฌ์ง ๋
ผ๋ฆฌ ๋๋ ๋ชจ๋ธ ๋ณ๊ฒฝ๊ณผ ๊ด๋ จ์ด ์์ผ๋ฉฐ ์ด๋ฌํ ์ค๋ฅ์ ์ ๋ฐ ์ด์์ด ๋ณต์กํฉ๋๋ค.
๋ชจ๋ ๋ฌธ์ ์ 18/XNUMX ๋ฏธ๋ง์ด ์์ ์ค์ผ์ค๋ฌ์ ๊ด๋ จ์ด ์์ผ๋ฉฐ ๊ทธ ์ค XNUMX%๋ ๋จ์ํ ์ค๋ฅ์ ๋๋ค.
์ผ๋ฐ์ ์ผ๋ก ๋ฐ์ํ๋ ๋ชจ๋ ์ค๋ฅ์ 22%๋ ๋ณต์กํ๋ฉฐ ์ด๋ฅผ ์์ ํ๋ ๋ฐ ๊ฐ์ฅ ๋ง์ ์ฃผ์์ ์๊ฐ์ด ํ์ํฉ๋๋ค. ์ผ์ฃผ์ผ์ ํ ๋ฒ ์ ๋ ๋ฐ์ํฉ๋๋ค. ๋ฐ๋ฉด ๊ฐ๋จํ ์ค์๋ ๊ฑฐ์ ๋งค์ผ ๋ฐ์ํฉ๋๋ค.
ETL ํ๋ก์ธ์ค ๋ชจ๋ํฐ๋ง์ ์ค๋ฅ ์์น๊ฐ ๊ฐ๋ฅํ ํ ์ ํํ๊ฒ ๋ก๊ทธ์ ํ์๋๊ณ ๋ฌธ์ ์ ์์ธ์ ์ฐพ๋ ๋ฐ ์ต์ํ์ ์๊ฐ์ด ํ์ํ ๋ ํจ๊ณผ์ ์ ๋๋ค.
ํจ๊ณผ์ ์ธ ๋ชจ๋ํฐ๋ง
ETL ๋ชจ๋ํฐ๋ง ํ๋ก์ธ์ค์์ ๋ณด๊ณ ์ถ์๋ ๊ฒ์ ๋ฌด์์ ๋๊น?
์์ - ๊ทธ๊ฐ ์ผ์ ์์ํ ๋,
์์ค - ๋ฐ์ดํฐ ์์ค,
๋ ์ด์ด - ์ด๋ค ์์ค์ ์คํ ๋ฆฌ์ง๊ฐ ๋ก๋๋๊ณ ์๋์ง,
ETL ์์
์ด๋ฆ - ๋ง์ ์์ ๋จ๊ณ๋ก ๊ตฌ์ฑ๋ ์
๋ก๋ ์ ์ฐจ,
๋จ๊ณ ๋ฒํธ - ์ํ ์ค์ธ ๋จ๊ณ์ ๋ฒํธ,
์ํฅ์ ๋ฐ๋ ํ - ์ด๋ฏธ ์ฒ๋ฆฌ๋ ๋ฐ์ดํฐ์ ์,
๊ธฐ๊ฐ ์ด - ๊ฑธ๋ฆฌ๋ ์๊ฐ,
์ํ - ๋ชจ๋ ๊ฒ์ด ์ ์์ธ์ง ์ฌ๋ถ: 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 ์ ์ธ์ ๋ฒํธ๋ฅผ ์ป์ ์ ์๋์ง ํ์ธ
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๋ ์ธ๋ถ ๋ฉ์ปค๋์ฆ์ด ํ์ํ๋ฉฐ ์ด ๊ฒฝ์ฐ
์ถ๋ ฅ
๋ฐ๋ผ์ ๋ฐ์ดํฐ ์ฒ๋ฆฌ ๋๊ตฌ์ ์ค๋ฅ ๋ฉ์์ง๋ ๋งค์ฐ ์ค์ํ ์ญํ ์ ํฉ๋๋ค. ๊ทธ๋ฌ๋ ๋ฌธ์ ์ ์์ธ์ ๋นจ๋ฆฌ ์ฐพ๊ธฐ ์ํด ์ต์ ์ด๋ผ๊ณ ๋ถ๋ฅด๊ธฐ๋ ์ด๋ ต์ต๋๋ค. ์ ์ฐจ ์๊ฐ XNUMX๊ฐ์ ๊ฐ๊น์์ง๋ฉด ํ๋ก์ธ์ค ๋ชจ๋ํฐ๋ง์ด ๋ณต์กํ ํ๋ก์ ํธ๋ก ๋ฐ๋๋๋ค.
์ด ๊ธฐ์ฌ๋ ํ๋กํ ํ์
์ ํํ๋ก ๋ฌธ์ ์ ๋ํ ๊ฐ๋ฅํ ์๋ฃจ์
์ ์๋ฅผ ์ ๊ณตํฉ๋๋ค. ์ ์ฒด ์์ ์ ์ฅ์ ํ๋กํ ํ์
์ gitlab์์ ์ฌ์ฉํ ์ ์์ต๋๋ค.
์ถ์ฒ : habr.com