Durch die Analyse der Statistiken der Website erhalten wir eine Vorstellung davon, was damit passiert. Wir vergleichen die Ergebnisse mit anderen Kenntnissen über das Produkt oder die Dienstleistung und verbessern dadurch unsere Erfahrung.
Wenn die Analyse der ersten Ergebnisse abgeschlossen ist, die Informationen verstanden wurden und Schlussfolgerungen gezogen wurden, beginnt die nächste Stufe. Ideen entstehen: Was passiert, wenn Sie die Daten aus einer anderen Perspektive betrachten?
Zu diesem Zeitpunkt gibt es Einschränkungen für die Analysewerkzeuge. Dies ist einer der Gründe, warum mir das Google Analytics-Tool nicht ausreichte, weil ich meine Daten nur eingeschränkt sehen und bearbeiten kann.
Ich wollte immer schnell die Basisdaten (Stammdaten) laden, eine weitere Aggregationsebene hinzufügen oder die vorhandenen Werte auf andere Weise interpretieren.
Dies ist in
Ihrem kleinen Repository basierend auf der Datei access.log einfach und die SQL-Sprache reicht dafür aus.
Auf welche Fragen wollte ich die Antwort finden?
Was und wann hat sich auf der Website geändert?
Der Verlauf von Änderungen der zugrunde liegenden Daten (Stammdaten) ist immer von Interesse.
SQL-BerichtsanforderungSELECT 1 as 'SideStackedBar: Content Updates by Months', strftime('%m/%Y', datetime(UPDATE_DT, 'unixepoch')) AS 'Day', COUNT(CASE WHEN PAGE_TITLE != 'na' 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 != 'na' OR PAGE_DESCR != 'na' GROUP BY strftime('%m/%Y', datetime(UPDATE_DT, 'unixepoch')) ORDER BY UPDATE_DT
Zum Beispiel wurde irgendwann eine Suchmaschinenoptimierung durchgeführt oder neue Inhalte zur Website hinzugefügt. In dieser Hinsicht wird eine Zunahme des Datenverkehrs erwartet.
Benutzergruppen
Das einfachste Beispiel einer Gruppe ist ein Benutzeragent oder der Name eines Betriebssystems.
Die Benutzeragentenmessung hat ungefähr tausend Datensätze gesammelt, und ich war daran interessiert, die Dynamik der Agentenverteilung innerhalb der Gruppe zu sehen.
SQL-Berichtsanforderung SELECT 1 AS 'SideStackedBar: User Agents', AGENT_OS AS 'OS', SUM(CASE WHEN AGENT_BOT = 'na' THEN 1 ELSE 0 END ) AS 'User Agent of Users', SUM(CASE WHEN AGENT_BOT != 'na' 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
Die meisten der verschiedenen Kombinationen von Agenten kommen aus der Windows-Welt auf die Site. Zu den unsicheren gehörten WhatsApp, PocketImageCache, PlayStation, SmartTV usw.
Wöchentliche Benutzergruppenaktivität
Durch die Kombination einiger Gruppen können wir die Verteilung ihrer Aktivität beobachten.
Beispielsweise verbrauchen Benutzer eines Linux-Clusters mehr Datenverkehr auf einer Site als alle anderen.
SQL-Berichtsanforderung 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 ('na', '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 = 'na' AND HST.STATUS_GROUP IN ('Successful') 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
Starker Verkehrsverbrauch
Die Tabelle zeigt die aktivsten Benutzergruppen und den Tag ihrer Aktivität.
Die aktivsten gehören zum Linux-Cluster.
SQL-Berichtsanforderung 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
Mithilfe der Attribute Tag und Agenten-ID können Sie schnell Statistiken zu den Tagen einzelner Benutzergruppen finden und verfolgen. Bei Bedarf finden Sie schnell detaillierte Informationen in der Bühnentabelle.
Wie bekomme ich Informationen?
Die Informationen aus der Datei access.log können noch effektiver gestaltet werden, indem zusätzliche Datenquellen integriert und neue Ebenen für Aggregation und Gruppierung eingeführt werden.
Grunddaten und Entitäten
Die Basisdaten umfassen Informationen zu Entitäten: Webseiten, Bilder, Video- und Audioinhalte, im Falle eines Geschäfts Produkte.
Entitäten selbst spielen die Rolle von Dimensionen, und der Prozess des Speicherns von Attributänderungen wird als Historisierung bezeichnet. In einer Datenbank wird dieser Prozess häufig in Form von sich langsam ändernden Dimensionen (SCD) implementiert.
Eine Vielzahl von Systemen kann die Quelle für Basisdaten sein, daher müssen sie fast immer integriert werden.
Langsam wechselnde Dimension
Die Dimension DIM_REQUEST enthält Informationen zu Abfragen auf der Site in historischer Form.
SCD2-Tabelle CREATE TABLE DIM_REQUEST ( DIM_REQUEST_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, DIM_REQUEST_ID_HIST INTEGER NOT NULL DEFAULT -1, REQUEST_NK TEXT NOT NULL DEFAULT 'na', PAGE_TITLE TEXT NOT NULL DEFAULT 'na', PAGE_DESCR TEXT NOT NULL DEFAULT 'na', PAGE_KEYWORDS TEXT NOT NULL DEFAULT 'na', 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);
Erstellen Sie außerdem eine Ansicht, in der immer alle Datensätze im letzten Status angezeigt werden. Es ist notwendig, die Messung selbst zu laden.
Aktuelle Ansicht von SCD2 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;
Und eine Ansicht, wo historische Informationen für jeden Datensatz gesammelt werden. Es ist notwendig, eine historisch korrekte Verbindung mit Fakten aufzubauen.
Historische Ansicht von SCD2 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;
Datenaggregation
Durch Komprimierung (Aggregation) können Sie Daten auf einer höheren Ebene auswerten und Anomalien und Trends erkennen, die in detaillierten Berichten nicht sichtbar sind.
Fügen Sie beispielsweise in der Dimension mit den Anforderungsstatuscodes DIM_HTTP_STATUS die Gruppe hinzu:
STATUS / GRUPPE
0xx / na
1xx / Information
2xx / Erfolgreich
3xx / Umleitung
4xx / Client-Fehler
5xx / Serverfehler
Die Benutzeragendimension DIM_USER_AGENT enthält die Attribute AGENT_OS und AGENT_BOT für die Gruppen. Sie können während des ETL-Prozesses ausgefüllt werden:
Laden Sie DIM_USER_AGENT herunter 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 'na' END AS AGENT_OS,
Datenintegration
Es umfasst die Organisation der Datenübertragung vom Betriebssystem zum Berichterstellungssystem. Erstellen Sie dazu eine Stufentabelle mit einer der Quelle ähnlichen Struktur.
Informationen zu Webseiten werden aus der CMS-Sicherung in Form von Einfügeanforderungen auf die Bühne gebracht.
Das Laden der historischen DIM_REQUEST-Tabelle mit Basisdaten erfolgt in drei Schritten: Laden neuer Schlüssel und Attribute, Aktualisieren vorhandener und Korrigieren gelöschter Datensätze.
Laden Sie neue SCD2-Einträge herunter INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT) WITH CLS AS (
Aktualisieren Sie die SCD2-Attribute INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT) WITH CLS AS (
Gelöschte SCD2-Einträge INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT) WITH CLS AS (
Jeder Datenquelle muss eine formale Beschreibung beigefügt sein, z. B. in der Datei readme.txt:
Empfänger formal / technisch: Name, E-Mail-Adresse
Datenanbieter formal / technisch: Name, E-Mail
Datenquelle: Dateipfad, Dienstnamen
Datenzugriffsinformationen: Benutzer und Kennwörter
Das Datenverschiebungsschema hilft bei der Wartung und Aktualisierung, beispielsweise in Textform:
Eine Datei verschieben. Quelle: ftp.domain.net: /logs/access.log Ziel: /var/www/access.log
Lesen auf der Bühne. Ziel: STG_ACCESS_LOG
Download und Transformation. Ziel: FCT_ACCESS_REQUEST_REF_HH
Download und Transformation. Ziel: FCT_ACCESS_USER_AGENT_DD
Bericht. Ziel: /var/www/report.html
Fazit
Der Artikel beschreibt daher Mechanismen wie die Integration von Basisdaten und die Einführung neuer Aggregationsebenen. Sie werden beim Aufbau von Data Warehouses benötigt, um zusätzliches Wissen zu erhalten und die Qualität der Informationen zu verbessern.