Awọn iṣiro aaye diẹ sii ni ibi ipamọ kekere rẹ

Nipa itupalẹ awọn iṣiro aaye, a ni imọran ohun ti n ṣẹlẹ pẹlu rẹ. A ṣe afiwe awọn abajade pẹlu imọ miiran nipa ọja tabi iṣẹ ati nitorinaa mu iriri wa dara.

Nigbati itupalẹ ti awọn abajade akọkọ ti pari, alaye naa ti ni oye ati pe a ti fa awọn ipinnu, ipele ti o tẹle yoo bẹrẹ. Awọn ero dide: kini yoo ṣẹlẹ ti o ba wo data lati apa keji?

Awọn idiwọn ti awọn irinṣẹ onínọmbà wa ni ipele yii. Eyi jẹ ọkan ninu awọn idi ti Awọn atupale Google ko to fun mi, eyun nitori agbara to lopin lati rii ati ṣiṣakoso data mi.

Mo nigbagbogbo fẹ lati yara gbe data ipilẹ (data titunto si), ṣafikun ipele apapọ miiran, tabi tumọ awọn iye to wa ni oriṣiriṣi.

Eyi rọrun lati ṣe ninu ninu rẹ kekere ipamọ yara da lori faili access.log ati ede SQL ti to fun eyi.

Nitorinaa, awọn ibeere wo ni MO fẹ dahun?

Kini ati nigba ti yipada lori aaye naa

Itan-akọọlẹ ti awọn ayipada ninu data ipilẹ (data oluwa) jẹ iwulo nigbagbogbo.

Ibeere ijabọ 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

Fun apẹẹrẹ, ni aaye kan, wiwa ẹrọ wiwa ti gbe jade tabi akoonu tuntun ti ṣafikun si aaye naa, ati nitori naa a nireti ilosoke ninu ijabọ.

Группы пользователей

Apẹẹrẹ ti o rọrun julọ ti ẹgbẹ kan jẹ aṣoju olumulo tabi orukọ ẹrọ ṣiṣe.

Iwọn aṣoju olumulo ti ṣajọ nipa ẹgbẹrun awọn igbasilẹ ati pe Mo nifẹ lati rii awọn agbara ti pinpin awọn aṣoju laarin ẹgbẹ naa.

Ibeere ijabọ 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

Pupọ awọn akojọpọ oriṣiriṣi ti awọn aṣoju wa si aaye lati agbaye Windows. Lara awọn ti a ko mọ ni WhatsApp, PocketImageCache, PlayStation, SmartTV, ati bẹbẹ lọ.

Iṣẹ ẹgbẹ olumulo nipasẹ ọsẹ

Nipa apapọ awọn ẹgbẹ kan, ọkan le ṣe akiyesi pinpin iṣẹ wọn.

Fun apẹẹrẹ, awọn olumulo iṣupọ Linux njẹ ijabọ oju opo wẹẹbu diẹ sii ju gbogbo eniyan miiran lọ.

Ibeere ijabọ 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

Lilo ijabọ aladanla

Awọn tabili fihan awọn julọ lọwọ olumulo awọn ẹgbẹ ati awọn ọjọ ti won aṣayan iṣẹ-ṣiṣe.
Awọn ti n ṣiṣẹ julọ jẹ ti iṣupọ Linux.

Ibeere ijabọ 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

Lilo ọjọ ati awọn abuda ID aṣoju, o ṣee ṣe lati wa ni kiakia ati tọpa awọn iṣiro ni awọn ọjọ ti awọn ẹgbẹ olumulo kọọkan. Ti o ba jẹ dandan, o le yara wa alaye alaye ni tabili ipele.

Bawo ni lati gba alaye?

Alaye lati faili access.log le jẹ ki o munadoko diẹ sii nipa sisọpọ awọn orisun data afikun ati ṣafihan awọn ipele titun ti iṣakojọpọ ati akojọpọ.

Awọn ipilẹ data ati awọn nkan

Awọn data ipilẹ pẹlu alaye nipa awọn nkan: awọn oju-iwe wẹẹbu, awọn aworan, fidio ati akoonu ohun, ninu ọran ti ile itaja - awọn ọja.

Awọn nkan ti ara wọn ṣe bi awọn wiwọn, ati ilana ti titoju awọn ayipada ninu awọn abuda ni a pe ni itan-akọọlẹ. Ninu aaye data, ilana yii nigbagbogbo ni imuse ni irisi awọn iwọn ti o yatọ laiyara (SCD).

Awọn data orisun le wa lati oriṣiriṣi awọn ọna ṣiṣe, nitorina wọn fẹrẹ nilo nigbagbogbo lati ṣepọ.

Laiyara iyipada iwọn

Iwọn DIM_REQUEST yoo ni alaye ninu awọn ibeere lori aaye ni fọọmu itan.

tabili 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);

Ni afikun si rẹ, a yoo ṣẹda ọkan wiwo ti o nigbagbogbo han gbogbo awọn igbasilẹ ni titun ipinle. Pataki fun ikojọpọ wiwọn ara.

Aṣoju lọwọlọwọ ti 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;

Ati wiwo nibiti a ti gba alaye itan fun titẹ sii kọọkan. O jẹ dandan lati kọ asopọ ti o pe itan-akọọlẹ pẹlu awọn otitọ.

Ifihan itan ti 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;

Akopọ Data

Funmorawon (akopọ) ngbanilaaye lati ṣe iṣiro data ni ipele ti o ga julọ ati rii awọn aiṣedeede ati awọn aṣa ti ko han ni awọn ijabọ alaye.

Fun apẹẹrẹ, ṣafikun ẹgbẹ kan si iwọn pẹlu awọn koodu ipo ibeere DIM_HTTP_STATUS:

Ipò/Ẹgbẹ
0xx/na
1xx/Alaye
2xx/Aseyori
3xx/atunṣe
4xx/Aṣiṣe onibara
5xx/Aṣiṣe olupin

Iwọn aṣoju olumulo DIM_USER_AGENT yoo ni awọn abuda AGENT_OS ati AGENT_BOT ti o ni iduro fun awọn ẹgbẹ. Iwọnyi le jẹ olugbe lakoko ilana ETL:

Nkojọpọ 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

Data Integration

Pẹlu siseto gbigbe data lati ẹrọ ṣiṣe si eto ijabọ. Lati ṣe eyi, o nilo lati ṣẹda tabili ipele kan pẹlu eto ti o jọra si orisun.

Alaye nipa awọn oju-iwe wẹẹbu n lọ si ipele lati afẹyinti CMS ni irisi awọn ibeere fi sii.

Ikojọpọ tabili itan DIM_REQUEST pẹlu data ipilẹ waye ni awọn igbesẹ mẹta: ikojọpọ awọn bọtini titun ati awọn abuda, mimudojuiwọn awọn ti o wa tẹlẹ, ati ṣiṣe awọn igbasilẹ paarẹ.

Ikojọpọ titun SCD2 gbigbasilẹ

/* 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

Ṣiṣe imudojuiwọn Awọn abuda 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 )

Awọn igbasilẹ SCD2 ti paarẹ

/* 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

Orisun data kọọkan gbọdọ wa pẹlu apejuwe ojuṣe, fun apẹẹrẹ, ninu faili readme.txt:

Olugba data ni fọọmu / imọ-ẹrọ: orukọ, adirẹsi imeeli
Olupese data ni fọọmu / imọ-ẹrọ: orukọ, adirẹsi imeeli
Orisun data: ọna faili, awọn orukọ iṣẹ
Alaye wiwọle data: awọn olumulo ati awọn ọrọigbaniwọle

Aworan sisan data yoo ṣe iranlọwọ ninu ilana itọju ati imudojuiwọn, fun apẹẹrẹ, ni fọọmu ọrọ:

Gbigbe faili kan. Orisun: ftp.domain.net: /logs/access.log Àkọlé: /var/www/access.log
Kika lori ipele. Àfojúsùn: STG_ACCESS_LOG
Ikojọpọ ati iyipada. Àfojúsùn: FCT_ACCESS_REQUEST_REF_HH
Ikojọpọ ati iyipada. Àfojúsùn: FCT_ACCESS_USER_AGENT_DD
Iroyin. Àfojúsùn: /var/www/report.html

ipari

Nitorinaa, nkan naa ṣe apejuwe awọn ilana bii isọpọ ti data ti o wa labẹ ati iṣafihan awọn ipele tuntun ti apapọ. Wọn nilo nigba kikọ awọn ile itaja data lati le gba imọ ni afikun ati ilọsiwaju didara alaye.

orisun: www.habr.com