рд╕рд╛рдЗрдЯрдЪреНрдпрд╛ рдЖрдХрдбреЗрд╡рд╛рд░реАрдЪреЗ рд╡рд┐рд╢реНрд▓реЗрд╖рдг рдХрд░реВрди, рдЖрдореНрд╣рд╛рд▓рд╛ рддреНрдпрд╛рдЪреНрдпрд╛рд╕рд╣ рдХрд╛рдп рд╣реЛрдд рдЖрд╣реЗ рдпрд╛рдЪреА рдХрд▓реНрдкрдирд╛ рдпреЗрддреЗ. рдЖрдореНрд╣реА рдЙрддреНрдкрд╛рджрди рдХрд┐рдВрд╡рд╛ рд╕реЗрд╡реЗрдмрджреНрджрд▓рдЪреНрдпрд╛ рдЗрддрд░ рдЬреНрдЮрд╛рдирд╛рд╢реА рдкрд░рд┐рдгрд╛рдорд╛рдВрдЪреА рддреБрд▓рдирд╛ рдХрд░рддреЛ рдЖрдгрд┐ рддреНрдпрд╛рджреНрд╡рд╛рд░реЗ рдЖрдордЪрд╛ рдЕрдиреБрднрд╡ рд╕реБрдзрд╛рд░рддреЛ.
рдЬреЗрд╡реНрд╣рд╛ рдкрд╣рд┐рд▓реНрдпрд╛ рдирд┐рдХрд╛рд▓рд╛рдВрдЪреЗ рд╡рд┐рд╢реНрд▓реЗрд╖рдг рдкреВрд░реНрдг рд╣реЛрддреЗ, рдорд╛рд╣рд┐рддреА рд╕рдордЬрд▓реА рдЬрд╛рддреЗ рдЖрдгрд┐ рдирд┐рд╖реНрдХрд░реНрд╖ рдХрд╛рдврд▓реЗ рдЬрд╛рддрд╛рдд, рддреЗрд╡реНрд╣рд╛ рдкреБрдврдЪрд╛ рдЯрдкреНрдкрд╛ рд╕реБрд░реВ рд╣реЛрддреЛ. рдХрд▓реНрдкрдирд╛ рдЙрджреНрднрд╡рддрд╛рдд: рдЖрдкрдг рджреБрд╕рд▒реНрдпрд╛ рдмрд╛рдЬреВрдиреЗ рдбреЗрдЯрд╛ рдкрд╛рд╣рд┐рд▓реНрдпрд╛рд╕ рдХрд╛рдп рд╣реЛрдИрд▓?
рдпрд╛ рдЯрдкреНрдкреНрдпрд╛рд╡рд░ рд╡рд┐рд╢реНрд▓реЗрд╖рдг рд╕рд╛рдзрдирд╛рдВрдЪреНрдпрд╛ рдорд░реНрдпрд╛рджрд╛ рдЖрд╣реЗрдд. рдорд╛рдЭреНрдпрд╛рд╕рд╛рдареА 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 рдЗ.
рдЖрдард╡рдбреНрдпрд╛рдиреБрд╕рд╛рд░ рд╡рд╛рдкрд░рдХрд░реНрддрд╛ рдЧрдЯ рдХреНрд░рд┐рдпрд╛рдХрд▓рд╛рдк
рдХрд╛рд╣реА рдЧрдЯ рдПрдХрддреНрд░ рдХрд░реВрди, рдХреЛрдгреАрд╣реА рддреНрдпрд╛рдВрдЪреНрдпрд╛ рдХреНрд░рд┐рдпрд╛рдХрд▓рд╛рдкрд╛рдВрдЪреЗ рд╡рд┐рддрд░рдг рдкрд╛рд╣реВ рд╢рдХрддреЛ.
рдЙрджрд╛рд╣рд░рдгрд╛рд░реНрде, рд▓рд┐рдирдХреНрд╕ рдХреНрд▓рд╕реНрдЯрд░ рд╡рд╛рдкрд░рдХрд░реНрддреЗ рдЗрддрд░ рд╕рд░реНрд╡рд╛рдВрдкреЗрдХреНрд╖рд╛ рдЕрдзрд┐рдХ рд╡реЗрдмрд╕рд╛рдЗрдЯ рдЯреНрд░реЕрдлрд┐рдХ рд╡рд╛рдкрд░рддрд╛рдд.
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
рд╕рдзрди рд░рд╣рджрд╛рд░реАрдЪрд╛ рд╡рд╛рдкрд░
рд╕рд╛рд░рдгреА рд╕рд░реНрд╡рд╛рдд рд╕рдХреНрд░рд┐рдп рд╡рд╛рдкрд░рдХрд░реНрддрд╛ рдЧрдЯ рдЖрдгрд┐ рддреНрдпрд╛рдВрдЪреНрдпрд╛ рдХреНрд░рд┐рдпрд╛рдХрд▓рд╛рдкрд╛рдЪрд╛ рджрд┐рд╡рд╕ рджрд░реНрд╢рд╡рд┐рддреЗ.
рд╕рд░реНрд╡рд╛рдд рд╕рдХреНрд░рд┐рдп рд▓рд┐рдирдХреНрд╕ рдХреНрд▓рд╕реНрдЯрд░рд╢реА рд╕рдВрдмрдВрдзрд┐рдд рдЖрд╣реЗрдд.
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
рджрд┐рд╡рд╕ рдЖрдгрд┐ рдПрдЬрдВрдЯ рдЖрдпрдбреА рд╡рд┐рд╢реЗрд╖рддрд╛ рд╡рд╛рдкрд░реВрди, рд╡реИрдпрдХреНрддрд┐рдХ рд╡рд╛рдкрд░рдХрд░реНрддрд╛ рдЧрдЯрд╛рдВрдЪреНрдпрд╛ рджрд┐рд╡рд╕рд╛рдВрдЪреА рдЖрдХрдбреЗрд╡рд╛рд░реА рджреНрд░реБрддрдкрдгреЗ рд╢реЛрдзрдгреЗ рдЖрдгрд┐ рдЯреНрд░реЕрдХ рдХрд░рдгреЗ рд╢рдХреНрдп рд╣реЛрддреЗ. рдЖрд╡рд╢реНрдпрдХ рдЕрд╕рд▓реНрдпрд╛рд╕, рдЖрдкрдг рд╕реНрдЯреЗрдЬ рдЯреЗрдмрд▓рдордзреНрдпреЗ рддреНрд╡рд░рд┐рдд рддрдкрд╢реАрд▓рд╡рд╛рд░ рдорд╛рд╣рд┐рддреА рд╢реЛрдзреВ рд╢рдХрддрд╛.
рдорд╛рд╣рд┐рддреА рдХрд╢реА рдорд┐рд│рд╡рд╛рдпрдЪреА?
рдореВрд▓рднреВрдд рдбреЗрдЯрд╛ рдЖрдгрд┐ рд╕рдВрд╕реНрдерд╛
рдореВрд▓рднреВрдд рдбреЗрдЯрд╛рдордзреНрдпреЗ рд╕рдВрд╕реНрдерд╛рдВрдмрджреНрджрд▓ рдорд╛рд╣рд┐рддреА рд╕рдорд╛рд╡рд┐рд╖реНрдЯ рдЕрд╕рддреЗ: рд╡реЗрдм рдкреГрд╖реНрдареЗ, рдЪрд┐рддреНрд░реЗ, рд╡реНрд╣рд┐рдбрд┐рдУ рдЖрдгрд┐ рдСрдбрд┐рдУ рд╕рд╛рдордЧреНрд░реА, рд╕реНрдЯреЛрдЕрд░рдЪреНрдпрд╛ рдмрд╛рдмрддреАрдд - рдЙрддреНрдкрд╛рджрдиреЗ.
рдШрдЯрдХ рд╕реНрд╡рддрдГ рдореЛрдЬрдорд╛рдк рдореНрд╣рдгреВрди рдХрд╛рд░реНрдп рдХрд░рддрд╛рдд рдЖрдгрд┐ рдЧреБрдгрдзрд░реНрдорд╛рдВрдордзреАрд▓ рдмрджрд▓ рд╕рдВрдЪрдпрд┐рдд рдХрд░рдгреНрдпрд╛рдЪреНрдпрд╛ рдкреНрд░рдХреНрд░рд┐рдпреЗрд╕ рдРрддрд┐рд╣рд╛рд╕рд┐рдХреАрдХрд░рдг рдореНрд╣рдгрддрд╛рдд. рдбреЗрдЯрд╛рдмреЗрд╕рдордзреНрдпреЗ, рд╣реА рдкреНрд░рдХреНрд░рд┐рдпрд╛ рдмрд╣реБрддреЗрдХ рд╡реЗрд│рд╛ рд╣рд│реВрд╣рд│реВ рдмрджрд▓рдгрд╛рд▒реНрдпрд╛ рдЖрдпрд╛рдорд╛рдВрдЪреНрдпрд╛ (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 рд╕рд╣ рдкрд░рд┐рдорд╛рдгрд╛рдд рдПрдХ рдЧрдЯ рдЬреЛрдбрд╛:
рд╕реНрдерд┐рддреА/рдЧреНрд░реБрдк
0xx/na
1xx/рдорд╛рд╣рд┐рддреА
2xx/рдпрд╢рд╕реНрд╡реА
3xx/рдкреБрдирд░реНрдирд┐рд░реНрджреЗрд╢рди
4xx/рдХреНрд▓рд╛рдпрдВрдЯ рдПрд░рд░
5xx/рд╕рд░реНрд╡реНрд╣рд░ рддреНрд░реБрдЯреА
рд╡рд╛рдкрд░рдХрд░реНрддрд╛ рдПрдЬрдВрдЯ рдкрд░рд┐рдорд╛рдг DIM_USER_AGENT рдордзреНрдпреЗ AGENT_OS рдЖрдгрд┐ AGENT_BOT рд╡рд┐рд╢реЗрд╖рддрд╛ рдЕрд╕рддреАрд▓ рдЬреА рдЧрдЯрд╛рдВрд╕рд╛рдареА рдЬрдмрд╛рдмрджрд╛рд░ рдЖрд╣реЗрдд. рд╣реЗ рдИрдЯреАрдПрд▓ рдкреНрд░рдХреНрд░рд┐рдпреЗрджрд░рдореНрдпрд╛рди рднрд░рд▓реЗ рдЬрд╛рдК рд╢рдХрддреЗ:
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
рдбреЗрдЯрд╛ рдПрдХрддреНрд░реАрдХрд░рдг
рдСрдкрд░реЗрдЯрд┐рдВрдЧ рд╕рд┐рд╕реНрдЯрдорд╡рд░реВрди рд░рд┐рдкреЛрд░реНрдЯрд┐рдВрдЧ рд╕рд┐рд╕реНрдЯрдордордзреНрдпреЗ рдбреЗрдЯрд╛рдЪреЗ рд╣рд╕реНрддрд╛рдВрддрд░рдг рдЖрдпреЛрдЬрд┐рдд рдХрд░рдгреЗ рд╕рдорд╛рд╡рд┐рд╖реНрдЯ рдЖрд╣реЗ. рд╣реЗ рдХрд░рдгреНрдпрд╛рд╕рд╛рдареА, рдЖрдкрд▓реНрдпрд╛рд▓рд╛ рд╕реНрддреНрд░реЛрддрд╛рд╕рд╛рд░рдЦреА рд░рдЪрдирд╛ рдЕрд╕рд▓реЗрд▓реА рд╕реНрдЯреЗрдЬ рдЯреЗрдмрд▓ рддрдпрд╛рд░ рдХрд░рдгреЗ рдЖрд╡рд╢реНрдпрдХ рдЖрд╣реЗ.
рд╡реЗрдм рдкреЗрдЬреЗрд╕рдЪреА рдорд╛рд╣рд┐рддреА рд╕реАрдПрдордПрд╕ рдмреЕрдХрдЕрдкрдордзреВрди рдЗрдиреНрд╕рд░реНрдЯ рд░рд┐рдХреНрд╡реЗрд╕реНрдЯрдЪреНрдпрд╛ рд╕реНрд╡рд░реВрдкрд╛рдд рд╕реНрдЯреЗрдЬрд╡рд░ рдкреЛрд╣реЛрдЪрддреЗ.
рдореВрд▓рднреВрдд рдбреЗрдЯрд╛рд╕рд╣ рдРрддрд┐рд╣рд╛рд╕рд┐рдХ рд╕рд╛рд░рдгреА 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