แกแแแขแแก แกแขแแขแแกแขแแแแก แแแแแแแแแแแแ, แฉแแแ แแแฆแแแ แฌแแ แแแแแแแแก แแแแก แจแแกแแฎแแ, แแฃ แ แ แฎแแแแ แแแกแแแ. แฉแแแ แแแแแ แแแ แจแแแแแแแก แแ แแแฃแฅแขแแก แแ แกแแ แแแกแแก แจแแกแแฎแแ แกแฎแแ แชแแแแแก แแ แแแแ แแแฃแแฏแแแแกแแแ แฉแแแแก แแแแแชแแแแแแแก.
แ แแแแกแแช แแแ แแแแ แจแแแแแแแแก แแแแแแแ แแแกแ แฃแแแแฃแแแ, แแแคแแ แแแชแแ แแแแแ แแแฃแแ แแฅแแแแ แแ แแแกแแแแแแ แแแแแแแแแ, แแฌแงแแแ แจแแแแแแ แแขแแแ. แฉแแแแแ แแแแแแ: แ แ แแแฎแแแแ, แแฃ แแแแแชแแแแแก แแแแ แ แแฎแ แแแแ แแแแแฎแแแแแ?
แแ แแขแแแแ แแ แกแแแแแก แแแแแแแแก แแแกแขแ แฃแแแแขแแแแก แจแแแฆแฃแแแแแ. แแก แแ แแก แแ แ-แแ แแ แแแแแแ, แ แแก แแแแแช Google Analytics แแ แแงแ แฉแแแแแแก แกแแแแแ แแกแ, แแแ แซแแ, แฉแแแ แแแแแชแแแแแแก แแแฎแแแกแ แแ แแแแแแฃแแแ แแแแก แจแแแฆแฃแแฃแแ แจแแกแแซแแแแแแแแก แแแแ.
แงแแแแแแแแก แแแแแแแ แกแแแแแแกแ แแแแแชแแแแแแก แกแฌแ แแคแแ แฉแแขแแแ แแแ (แซแแ แแแแแ แแแแแชแแแแแ), แแแ แแแแชแแแก แกแฎแแ แแแแแก แแแแแขแแแ แแ แแ แกแแแฃแแ แแแแจแแแแแแแแแแก แแแแกแฎแแแแแแฃแแแ แแแขแแ แแ แแขแแชแแ.
แแแแก แแแแแแแแ แแแแแแแ
แแแจ, แ แ แแแแฎแแแแแ แแแแแแแ แแแกแฃแฎแแก แแแชแแแ?
แ แ แแ แ แแแแก แจแแแชแแแแ แกแแแขแแ
แซแแ แแแแแ แแแแแชแแแแแแก (แซแแ แแแแแ แแแแแชแแแแแ) แชแแแแแแแแแแก แแกแขแแ แแ แงแแแแแแแแก แกแแแแขแแ แแกแแ.
SQL แแแแแ แแจแแก แแแแฎแแแแ
SELECT
1 as 'SideStackedBar: Content Updates by Months',
strftime('%m/%Y', datetime(UPDATE_DT, 'unixepoch')) AS 'Day',
COUNT(CASE WHEN PAGE_TITLE != 'n.a.' THEN DIM_REQUEST_ID END) AS 'Web page updates',
COUNT(CASE WHEN PAGE_DESCR = 'IMAGES' THEN DIM_REQUEST_ID END) AS 'Image uploads',
COUNT(CASE WHEN PAGE_DESCR = 'VIDEO' THEN DIM_REQUEST_ID END) AS 'Video uploads',
COUNT(CASE WHEN PAGE_DESCR = 'AUDIO' THEN DIM_REQUEST_ID END) AS 'Audio uploads'
FROM DIM_REQUEST
WHERE PAGE_TITLE != 'n.a.' OR PAGE_DESCR != 'n.a.'
GROUP BY strftime('%m/%Y', datetime(UPDATE_DT, 'unixepoch'))
ORDER BY UPDATE_DT
แแแแแแแแแ, แ แแฆแแช แแแแแแขแจแ แแแแฎแแ แชแแแแแ แกแแซแแแแ แกแแกแขแแแแก แแแขแแแแแแชแแ แแ แกแแแขแก แแแแแแขแ แแฎแแแ แแแแขแแแขแ แแ แจแแกแแแแแแกแแ แแแกแแแแแแแแแ แขแ แแคแแแแก แแ แแ.
แแแแฎแแแ แแแแแแ แฏแแฃแคแ
แฏแแฃแคแแก แฃแแแ แขแแแแกแ แแแแแแแแแ แแแแฎแแแ แแแแแก แแแแแขแ แแ แแแแ แแชแแฃแแ แกแแกแขแแแแก แกแแฎแแแ.
แแแแฎแแแ แแแแแก แแแแแขแแก แแแแแแแแแแแแ แแแแแ แแแ แแแแฎแแแแแแ แแแแกแ แฉแแแแฌแแ แ แแ แแ แแแแแขแแ แแกแแแแ แฏแแฃแคแจแ แแแแแขแแแแก แแแแแฌแแแแแแก แแแแแแแแ.
SQL แแแแแ แแจแแก แแแแฎแแแแ
SELECT
1 AS 'SideStackedBar: User Agents',
AGENT_OS AS 'OS',
SUM(CASE WHEN AGENT_BOT = 'n.a.' THEN 1 ELSE 0 END ) AS 'User Agent of Users',
SUM(CASE WHEN AGENT_BOT != 'n.a.' THEN 1 ELSE 0 END ) AS 'User Agent of Bots'
FROM DIM_USER_AGENT
WHERE DIM_USER_AGENT_ID != -1
GROUP BY AGENT_OS
ORDER BY 3 DESC
แแแแแขแแแแก แงแแแแแแ แแแแกแฎแแแแแแฃแแ แแแแแแแแชแแแแ แกแแแขแแ แแแแแก Windows-แแก แกแแแงแแ แแแแ. แแแฃแแแแแแแแ แจแแ แแก แแงแ WhatsApp, PocketImageCache, PlayStation, SmartTV แแ แ.แจ.
แแแแฎแแแ แแแแแแ แฏแแฃแคแแก แแฅแขแแแแแ แแแแ แแจแ
แแแแแแ แแ แฏแแฃแคแแก แแแแ แแแแแแแแ แจแแแซแแแแ แแแแ แแฅแขแแแแแแก แแแแแฌแแแแแแก แแแแแแ แแแแ.
แแแแแแแแแ, Linux แแแแกแขแแ แแก แแแแฎแแแ แแแแแแ แแแแฎแแแ แแ แฃแคแ แ แแแข แขแ แแคแแแก, แแแแ แ แงแแแแ แกแฎแแ.
SQL แแแแแ แแจแแก แแแแฎแแแแ
SELECT
1 as 'StackedBar: Traffic Volume by User OS and by Week',
strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Week',
SUM(CASE WHEN USG.AGENT_OS IN ('Android', 'Linux') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Android/Linux Users',
SUM(CASE WHEN USG.AGENT_OS IN ('Windows') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Windows Users',
SUM(CASE WHEN USG.AGENT_OS IN ('Macintosh', 'iOS') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Mac/iOS Users',
SUM(CASE WHEN USG.AGENT_OS IN ('n.a.', 'BlackBerry') THEN FCT.BYTES ELSE 0 END)/1000 AS 'Other'
FROM
FCT_ACCESS_USER_AGENT_DD FCT,
DIM_USER_AGENT USG,
DIM_HTTP_STATUS HST
WHERE FCT.DIM_USER_AGENT_ID=USG.DIM_USER_AGENT_ID
AND FCT.DIM_HTTP_STATUS_ID = HST.DIM_HTTP_STATUS_ID
AND USG.AGENT_BOT = 'n.a.' /* users only */
AND HST.STATUS_GROUP IN ('Successful') /* good pages */
AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')
GROUP BY strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DT
แแแขแแแกแแฃแ แ แขแ แแคแแแแก แแแฎแแแ แแแ
แชแฎแ แแแจแ แแแฉแแแแแแแ แแแแฎแแแ แแแแแแ แงแแแแแแ แแฅแขแแฃแ แ แฏแแฃแคแแแ แแ แแแแ แแฅแขแแแแแแก แแฆแ.
แงแแแแแแ แแฅแขแแฃแ แแแ แแแฃแแแแแก Linux แแแแกแขแแ แก.
SQL แแแแแ แแจแแก แแแแฎแแแแ
SELECT
1 AS 'Table: User Agent with Havy Usage',
strftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Day',
ROUND(1.0*SUM(FCT.BYTES)/1000000, 1) AS 'Traffic MB',
ROUND(1.0*SUM(FCT.IP_CNT)/SUM(1), 1) AS 'IPs',
ROUND(1.0*SUM(FCT.REQUEST_CNT)/SUM(1), 1) AS 'Requests',
USA.DIM_USER_AGENT_ID AS 'ID',
MAX(USA.USER_AGENT_NK) AS 'User Agent',
MAX(USA.AGENT_BOT) AS 'Bot'
FROM
FCT_ACCESS_USER_AGENT_DD FCT,
DIM_USER_AGENT USA
WHERE FCT.DIM_USER_AGENT_ID = USA.DIM_USER_AGENT_ID
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-30 day')
GROUP BY USA.DIM_USER_AGENT_ID, strftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY SUM(FCT.BYTES) DESC, FCT.EVENT_DT
LIMIT 10
แแฆแแกแ แแ แแแแแขแแก ID แแขแ แแแฃแขแแแแก แแแแแงแแแแแแ, แจแแกแแซแแแแแแ แฎแแแแ แกแฌแ แแคแแ แแแแแแ แแ แแแแแงแฃแ แ แแแแแแแ แกแขแแขแแกแขแแแแก แแแแแแแแฃแแแฃแ แ แแแแฎแแแ แแแแแแ แฏแแฃแคแแแแก แแฆแแแแจแ. แกแแญแแ แแแแแก แจแแแแฎแแแแแจแ, แแฅแแแ แจแแแแซแแแแ แกแฌแ แแคแแ แแแแแแ แแแขแแแฃแ แ แแแคแแ แแแชแแ แกแแกแชแแแ แชแฎแ แแแจแ.
แ แแแแ แแแแแฆแแ แแแคแแ แแแชแแ?
แซแแ แแแแแ แแแแแชแแแแแ แแ แแแ แแแ
แซแแ แแแแแ แแแแแชแแแแแ แแแแชแแแก แแแคแแ แแแชแแแก แกแฃแแแแฅแขแแแแก แจแแกแแฎแแ: แแแ แแแแ แแแแ, แกแฃแ แแแแแ, แแแแแ แแ แแฃแแแ แแแแขแแแขแ, แแแฆแแแแแก แจแแแแฎแแแแแจแ - แแ แแแฃแฅแขแแแ.
แแแแแ แแ แแแฃแแแแ แแแฅแแแแแแแ แ แแแแ แช แกแแแแแแแ แแ แแขแ แแแฃแขแแแจแ แชแแแแแแแแแแก แจแแแแฎแแแก แแ แแชแแกแก แแฌแแแแแ แแกแขแแ แแแแแชแแ. แแแแแชแแแแ แแแแแจแ แแก แแ แแชแแกแ แฎแจแแ แแ แฎแแ แชแแแแแแแ แแแ-แแแแ แชแแแแแแแแ แแแแแแแแแแแแแแก (SCD) แกแแฎแแ.
แฌแงแแ แแก แแแแแชแแแแแ แจแแแซแแแแ แแแแแแแแก แกแฎแแแแแกแฎแแ แกแแกแขแแแแแแ, แแแแขแแ แแกแแแ แแแแฅแแแก แงแแแแแแแแก แฃแแแ แแงแแก แแแขแแแ แแ แแแฃแแ.
แแแ-แแแแ แแชแแแแแ แแแแแแแแแแแ
DIM_REQUEST แแแแแแแแแแแ แจแแแชแแแก แแแคแแ แแแชแแแก แกแแแขแแ แแ แกแแแฃแแ แแแแฎแแแแแแแก แจแแกแแฎแแ แแกแขแแ แแฃแแ แคแแ แแแ.
แแแแแแ SCD2
CREATE TABLE DIM_REQUEST ( /* scd table for user requests */
DIM_REQUEST_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
DIM_REQUEST_ID_HIST INTEGER NOT NULL DEFAULT -1,
REQUEST_NK TEXT NOT NULL DEFAULT 'n.a.', /* request without ?parameters */
PAGE_TITLE TEXT NOT NULL DEFAULT 'n.a.',
PAGE_DESCR TEXT NOT NULL DEFAULT 'n.a.',
PAGE_KEYWORDS TEXT NOT NULL DEFAULT 'n.a.',
DELETE_FLAG INTEGER NOT NULL DEFAULT 0,
UPDATE_DT INTEGER NOT NULL DEFAULT 0,
UNIQUE (REQUEST_NK, DIM_REQUEST_ID_HIST)
);
INSERT INTO DIM_REQUEST (DIM_REQUEST_ID) VALUES (-1);
แแแ แแ แแแแกแ, แฉแแแ แจแแแฅแแแแ แแ แ แฎแแแก, แ แแแแแแช แงแแแแแแแแก แแฉแแแแแแก แงแแแแ แฉแแแแฌแแ แก แฃแแฎแแแก แแแแแแแ แแแแแจแ. แแฃแชแแแแแแแแ แแแแแ แแแแแแแแก แฉแแขแแแ แแแแกแแแแก.
SCD2-แแก แแแแแแแแแแแ แฌแแ แแแแแแแแแแแแ
/* Content: actual view on scd table */
SELECT HI.DIM_REQUEST_ID,
HI.DIM_REQUEST_ID_HIST,
HI.REQUEST_NK,
HI.PAGE_TITLE,
HI.PAGE_DESCR,
HI.PAGE_KEYWORDS,
NK.CNT AS HIST_CNT,
HI.DELETE_FLAG,
strftime('%d.%m.%Y %H:%M', datetime(HI.UPDATE_DT, 'unixepoch')) AS UPDATE_DT
FROM
( SELECT REQUEST_NK, MAX(DIM_REQUEST_ID) AS DIM_REQUEST_ID, SUM(1) AS CNT
FROM DIM_REQUEST
GROUP BY REQUEST_NK
) NK,
DIM_REQUEST HI
WHERE 1 = 1
AND NK.REQUEST_NK = HI.REQUEST_NK
AND NK.DIM_REQUEST_ID = HI.DIM_REQUEST_ID;
แแ แฎแแแ, แกแแแแช แแกแขแแ แแฃแแ แแแคแแ แแแชแแ แแ แแแแแแ แแแแแแฃแแ แฉแแแแฌแแ แแกแแแแก. แแฃแชแแแแแแแแ แคแแฅแขแแแแแ แแกแขแแ แแฃแแแ แกแฌแแ แ แแแแจแแ แแก แแแแงแแ แแแ.
SCD2-แแก แแกแขแแ แแฃแแ แแ แแแแแขแแชแแ
/* Content: actual view on scd table */
SELECT SCD.DIM_REQUEST_ID,
SCD.DIM_REQUEST_ID_HIST,
SCD.REQUEST_NK,
SCD.PAGE_TITLE,
SCD.PAGE_DESCR,
SCD.PAGE_KEYWORDS,
SCD.DELETE_FLAG,
CASE
WHEN HIS.UPDATE_DT IS NULL
THEN 1
ELSE 0 END ACTIVE_FLAG,
SCD.DIM_REQUEST_ID_HIST AS ID_FROM,
SCD.DIM_REQUEST_ID AS ID_TO,
CASE
WHEN SCD.DIM_REQUEST_ID_HIST=-1
THEN 3600
ELSE IFNULL(SCD.UPDATE_DT,3600)
END AS TIME_FROM,
CASE
WHEN HIS.UPDATE_DT IS NULL
THEN 253370764800
ELSE HIS.UPDATE_DT
END AS TIME_TO,
CASE
WHEN SCD.DIM_REQUEST_ID_HIST=-1
THEN STRFTIME('%d.%m.%Y %H:%M', DATETIME(3600, 'unixepoch'))
ELSE STRFTIME('%d.%m.%Y %H:%M', DATETIME(IFNULL(SCD.UPDATE_DT,3600), 'unixepoch'))
END AS ACTIVE_FROM,
CASE
WHEN HIS.UPDATE_DT IS NULL
THEN STRFTIME('%d.%m.%Y %H:%M', DATETIME(253370764800, 'unixepoch'))
ELSE STRFTIME('%d.%m.%Y %H:%M', DATETIME(HIS.UPDATE_DT, 'unixepoch'))
END AS ACTIVE_TO
FROM
DIM_REQUEST SCD
LEFT OUTER JOIN DIM_REQUEST HIS
ON SCD.REQUEST_NK = HIS.REQUEST_NK AND SCD.DIM_REQUEST_ID = HIS.DIM_REQUEST_ID_HIST;
แแแแแชแแแแ แแแ แแแแชแแ
แจแแแฃแแจแแ (แแแ แแแแชแแ) แกแแจแฃแแแแแแก แแแซแแแแ แจแแแคแแกแแ แแแแแชแแแแแ แฃแคแ แ แแแฆแแ แแแแแแ แแ แแแแแแแแแแแ แแแแแแแแแแ แแ แขแแแแแแชแแแแ, แ แแแแแแแช แแ แฉแแแก แแแขแแแฃแ แแแแแ แแจแแแจแ.
แแแแแแแแแ, แแแแแแขแแ แฏแแฃแคแ แแแแแแแแแแแแจแ แแแแฎแแแแแก แกแขแแขแฃแกแแก แแแแแแแ DIM_HTTP_STATUS:
STATUS/GROUP
0xx/na
1xx/แแแคแแ แแแชแแ
2xx / แฌแแ แแแขแแแฃแแ
3xx/แแแแแแแกแแแแ แแแแ
4xx/แแแแแแขแแก แจแแชแแแแ
5xx/แกแแ แแแ แแก แจแแชแแแแ
แแแแฎแแแ แแแแแก แแแแแขแแก แแแแแแแแแแแ DIM_USER_AGENT แจแแแชแแแก AGENT_OS แแ AGENT_BOT แแขแ แแแฃแขแแแก, แ แแแแแแแช แแแกแฃแฎแแกแแแแแแแแ แแ แแแ แฏแแฃแคแแแแ. แแก แจแแแซแแแแ แแงแแก แแแกแแฎแแแแฃแแ ETL แแ แแชแแกแแก แแ แแก:
แแขแแแ แแแแ DIM_USER_AGENT
/* Propagate the user agent from access log */
INSERT INTO DIM_USER_AGENT (USER_AGENT_NK, AGENT_OS, AGENT_ENGINE, AGENT_DEVICE, AGENT_BOT, UPDATE_DT)
WITH CLS AS (
SELECT BROWSER
FROM STG_ACCESS_LOG WHERE LENGTH(BROWSER)>1
GROUP BY BROWSER
)
SELECT
CLS.BROWSER AS USER_AGENT_NK,
CASE
WHEN INSTR(CLS.BROWSER,'Macintosh')>0
THEN 'Macintosh'
WHEN INSTR(CLS.BROWSER,'iPhone')>0
OR INSTR(CLS.BROWSER,'iPad')>0
OR INSTR(CLS.BROWSER,'iPod')>0
OR INSTR(CLS.BROWSER,'Apple TV')>0
OR INSTR(CLS.BROWSER,'Darwin')>0
THEN 'iOS'
WHEN INSTR(CLS.BROWSER,'Android')>0
THEN 'Android'
WHEN INSTR(CLS.BROWSER,'X11;')>0 OR INSTR(CLS.BROWSER,'Wayland;')>0 OR INSTR(CLS.BROWSER,'linux-gnu')>0
THEN 'Linux'
WHEN INSTR(CLS.BROWSER,'BB10;')>0 OR INSTR(CLS.BROWSER,'BlackBerry')>0
THEN 'BlackBerry'
WHEN INSTR(CLS.BROWSER,'Windows')>0
THEN 'Windows'
ELSE 'n.a.' END AS AGENT_OS, -- OS
CASE
WHEN INSTR(CLS.BROWSER,'AppleCoreMedia')>0
THEN 'AppleWebKit'
WHEN INSTR(CLS.BROWSER,') ')>1 AND LENGTH(CLS.BROWSER)>INSTR(CLS.BROWSER,') ')
THEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,') ')+2, LENGTH(CLS.BROWSER) - INSTR(CLS.BROWSER,') ')-1), 'N/A')
ELSE 'n.a.' END AS AGENT_ENGINE, -- Engine
CASE
WHEN INSTR(CLS.BROWSER,'iPhone')>0
THEN 'iPhone'
WHEN INSTR(CLS.BROWSER,'iPad')>0
THEN 'iPad'
WHEN INSTR(CLS.BROWSER,'iPod')>0
THEN 'iPod'
WHEN INSTR(CLS.BROWSER,'Apple TV')>0
THEN 'Apple TV'
WHEN INSTR(CLS.BROWSER,'Android ')>0 AND INSTR(CLS.BROWSER,'Build')>0
THEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,'Android '), INSTR(CLS.BROWSER,'Build')-INSTR(CLS.BROWSER,'Android ')), 'n.a.')
WHEN INSTR(CLS.BROWSER,'Android ')>0 AND INSTR(CLS.BROWSER,'MIUI')>0
THEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,'Android '), INSTR(CLS.BROWSER,'MIUI')-INSTR(CLS.BROWSER,'Android ')), 'n.a.')
ELSE 'n.a.' END AS AGENT_DEVICE, -- Device
CASE
WHEN INSTR(LOWER(CLS.BROWSER),'yandex.com')>0
THEN 'yandex'
WHEN INSTR(LOWER(CLS.BROWSER),'googlebot')>0
THEN 'google'
WHEN INSTR(LOWER(CLS.BROWSER),'bingbot')>0
THEN 'microsoft'
WHEN INSTR(LOWER(CLS.BROWSER),'ahrefsbot')>0
THEN 'ahrefs'
WHEN INSTR(LOWER(CLS.BROWSER),'jobboersebot')>0 OR INSTR(LOWER(CLS.BROWSER),'jobkicks')>0
THEN 'job.de'
WHEN INSTR(LOWER(CLS.BROWSER),'mail.ru')>0
THEN 'mail.ru'
WHEN INSTR(LOWER(CLS.BROWSER),'baiduspider')>0
THEN 'baidu'
WHEN INSTR(LOWER(CLS.BROWSER),'mj12bot')>0
THEN 'majestic-12'
WHEN INSTR(LOWER(CLS.BROWSER),'duckduckgo')>0
THEN 'duckduckgo'
WHEN INSTR(LOWER(CLS.BROWSER),'bytespider')>0
THEN 'bytespider'
WHEN INSTR(LOWER(CLS.BROWSER),'360spider')>0
THEN 'so.360.cn'
WHEN INSTR(LOWER(CLS.BROWSER),'compatible')>0 OR INSTR(LOWER(CLS.BROWSER),'http')>0
OR INSTR(LOWER(CLS.BROWSER),'libwww')>0 OR INSTR(LOWER(CLS.BROWSER),'spider')>0
OR INSTR(LOWER(CLS.BROWSER),'java')>0 OR INSTR(LOWER(CLS.BROWSER),'python')>0
OR INSTR(LOWER(CLS.BROWSER),'robot')>0 OR INSTR(LOWER(CLS.BROWSER),'curl')>0 OR INSTR(LOWER(CLS.BROWSER),'wget')>0
THEN 'other'
ELSE 'n.a.' END AS AGENT_BOT, -- Bot
STRFTIME('%s','now') AS UPDATE_DT
FROM CLS
LEFT OUTER JOIN DIM_USER_AGENT TRG
ON CLS.BROWSER = TRG.USER_AGENT_NK
WHERE TRG.DIM_USER_AGENT_ID IS NULL
แแแแแชแแแแ แแแขแแแ แแชแแ
แแแแชแแแก แแแแ แแชแแฃแแ แกแแกแขแแแแแแ แแแแแ แแจแแแแแก แกแแกแขแแแแจแ แแแแแชแแแแ แแแแแชแแแแก แแ แแแแแแแแแก. แแแแกแแแแแก แแฅแแแ แฃแแแ แจแแฅแแแแ แกแแกแชแแแ แชแฎแ แแแ แฌแงแแ แแก แแกแแแแกแ แกแขแ แฃแฅแขแฃแ แแ.
แแแคแแ แแแชแแ แแแ แแแแ แแแแแก แจแแกแแฎแแ แแขแแแแแแ แแฆแฌแแแก CMS แกแแ แแแแ แแ แแกแแแแแ แฉแแกแแแก แแแแฎแแแแแก แกแแฎแแ.
แแกแขแแ แแฃแแ แชแฎแ แแแแก DIM_REQUEST แซแแ แแแแแ แแแแแชแแแแแแ แฉแแขแแแ แแแ แฎแแแแ แกแแ แแขแแแแ: แแฎแแแ แแแกแแฆแแแแแแก แแ แแขแ แแแฃแขแแแแก แฉแแขแแแ แแแ, แแ แกแแแฃแแแก แแแแแฎแแแแ แแ แฌแแจแแแแ แฉแแแแฌแแ แแแแก แแแแแแ.
แแขแแแ แแแแ แแฎแแแ SCD2 แฉแแแแฌแแ แแแ
/* Load request table SCD from master data */
INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT)
WITH CLS AS ( -- prepare keys
SELECT
'/' || NAME AS REQUEST_NK,
TITLE AS PAGE_TITLE,
CASE WHEN DESCRIPTION = '' OR DESCRIPTION IS NULL
THEN 'n.a.' ELSE DESCRIPTION
END AS PAGE_DESCR,
CASE WHEN KEYWORDS = '' OR KEYWORDS IS NULL
THEN 'n.a.' ELSE KEYWORDS
END AS PAGE_KEYWORDS
FROM STG_CMS_MENU
WHERE CONTENT_TYPE != 'folder' -- only web pages
AND PAGE_TITLE != 'n.a.' -- master data which make sense
)
/* new records from stage: CLS */
SELECT
-1 AS DIM_REQUEST_ID_HIST,
CLS.REQUEST_NK,
CLS.PAGE_TITLE,
CLS.PAGE_DESCR,
CLS.PAGE_KEYWORDS,
0 AS DELETE_FLAG,
STRFTIME('%s','now') AS UPDATE_DT
FROM CLS
LEFT OUTER JOIN
(
SELECT
DIM_REQUEST_ID,
REQUEST_NK,
PAGE_TITLE,
PAGE_DESCR,
PAGE_KEYWORDS
FROM DIM_REQUEST_V_ACT
) TRG ON CLS.REQUEST_NK = TRG.REQUEST_NK
WHERE TRG.REQUEST_NK IS NULL -- no such record in data mart
SCD2 แแขแ แแแฃแขแแแแก แแแแแฎแแแแ
/* Load request table SCD from master data */
INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT)
WITH CLS AS ( -- prepare keys
SELECT
'/' || NAME AS REQUEST_NK,
TITLE AS PAGE_TITLE,
CASE WHEN DESCRIPTION = '' OR DESCRIPTION IS NULL
THEN 'n.a.' ELSE DESCRIPTION
END AS PAGE_DESCR,
CASE WHEN KEYWORDS = '' OR KEYWORDS IS NULL
THEN 'n.a.' ELSE KEYWORDS
END AS PAGE_KEYWORDS
FROM STG_CMS_MENU
WHERE CONTENT_TYPE != 'folder' -- only web pages
AND PAGE_TITLE != 'n.a.' -- master data which make sense
)
/* updated records from stage: CLS and build reference to history: HIST */
SELECT
HIST.DIM_REQUEST_ID AS DIM_REQUEST_ID_HIST,
HIST.REQUEST_NK,
CLS.PAGE_TITLE,
CLS.PAGE_DESCR,
CLS.PAGE_KEYWORDS,
0 AS DELETE_FLAG,
STRFTIME('%s','now') AS UPDATE_DT
FROM CLS,
DIM_REQUEST_V_ACT TRG,
DIM_REQUEST HIST
WHERE CLS.REQUEST_NK = TRG.REQUEST_NK
AND TRG.DIM_REQUEST_ID = HIST.DIM_REQUEST_ID
AND ( CLS.PAGE_TITLE != HIST.PAGE_TITLE /* changes only */
OR CLS.PAGE_DESCR != HIST.PAGE_DESCR
OR CLS.PAGE_KEYWORDS != HIST.PAGE_KEYWORDS )
แฌแแจแแแแแ SCD2 แฉแแแแฌแแ แแแ
/* Load request table SCD from master data */
INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT)
WITH CLS AS ( -- prepare keys
SELECT
'/' || NAME AS REQUEST_NK,
TITLE AS PAGE_TITLE
FROM STG_CMS_MENU
WHERE CONTENT_TYPE != 'folder' -- only web pages
AND PAGE_TITLE != 'n.a.' -- master data which make sense
)
/* deleted records in data mart: TRG */
SELECT
TRG.DIM_REQUEST_ID AS DIM_REQUEST_ID_HIST,
TRG.REQUEST_NK,
TRG.PAGE_TITLE,
TRG.PAGE_DESCR,
TRG.PAGE_KEYWORDS,
1 AS DELETE_FLAG,
STRFTIME('%s','now') AS UPDATE_DT
FROM (
SELECT
DIM_REQUEST_ID,
REQUEST_NK,
PAGE_TITLE,
PAGE_DESCR,
PAGE_KEYWORDS
FROM DIM_REQUEST_V_ACT
WHERE PAGE_TITLE != 'n.a.' -- track master data only
AND DELETE_FLAG = 0 -- not already deleted
) TRG
LEFT OUTER JOIN CLS ON TRG.REQUEST_NK = CLS.REQUEST_NK
WHERE CLS.REQUEST_NK IS NULL -- no such record in stage
แแแแแชแแแแ แแแแแแฃแ แฌแงแแ แแก แฃแแแ แแฎแแแแก แแคแแชแแแแฃแ แ แแฆแฌแแ แ, แแแแแแแแแ, readme.txt แคแแแแจแ:
แแแแแชแแแแแแก แแแแฆแแแ แคแแ แแแแฃแ แแ/แขแแฅแแแแฃแ แแ: แกแแฎแแแ, แแแแฅแขแ แแแฃแแ แคแแกแขแแก แแแกแแแแ แแ
แแแแแชแแแแ แแ แแแแแแแ แ แคแแ แแแแฃแ แแ/แขแแฅแแแแฃแ แแ: แกแแฎแแแ, แแแแฅแขแ แแแฃแแ แคแแกแขแแก แแแกแแแแ แแ
แแแแแชแแแแ แฌแงแแ แ: แคแแแแแก แแแ, แกแแ แแแกแแแแก แกแแฎแแแแแ
แแแแแชแแแแ แฎแแแแแกแแฌแแแแแแแแก แแแคแแ แแแชแแ: แแแแฎแแแ แแแแแแ แแ แแแ แแแแแ
แแแแแชแแแแ แแแแแแแก แแแแแ แแแ แแแแแฎแแแ แแแแ แจแแแแ แฉแฃแแแแแกแ แแ แแแแแฎแแแแแก แแ แแชแแกแจแ, แแแแแแแแแ, แขแแฅแกแขแแก แกแแฎแแ:
แคแแแแแก แแแแแขแแแ. แฌแงแแ แ: ftp.domain.net: /logs/access.log แกแแแแแแ: /var/www/access.log
แแแแฎแแ แกแชแแแแแ. แกแแแแแแ: STG_ACCESS_LOG
แแแขแแแ แแแ แแ แขแ แแแกแคแแ แแแชแแ. แกแแแแแแ: FCT_ACCESS_REQUEST_REF_HH
แแแขแแแ แแแ แแ แขแ แแแกแคแแ แแแชแแ. แกแแแแแแ: FCT_ACCESS_USER_AGENT_DD
แแแฎแกแแแแแ. แกแแแแแแ: /var/www/report.html
แแแแแงแแแแแก
แแแ แแแแ, แกแขแแขแแแจแ แแฆแฌแแ แแแแ แแกแแแ แแแฅแแแแแแแแ, แ แแแแ แแชแแ แซแแ แแแแแ แแแแแชแแแแแแก แแแขแแแ แแชแแ แแ แแแ แแแแชแแแก แแฎแแแ แแแแแแแแก แแแแแ แแแ. แแกแแแ แกแแญแแ แแ แแแแแชแแแแ แกแแฌแงแแแแแแก แแจแแแแแแกแแก แแแแแขแแแแแ แชแแแแแก แแแกแแฆแแแแ แแ แแแคแแ แแแชแแแก แฎแแ แแกแฎแแก แแแกแแฃแแฏแแแแกแแแแแ.
แฌแงแแ แ: www.habr.com