8.6 TimescaleDB - priebežná agregácia údajov

V kapitole „8.1 Úvod do TSDB a predstavenie InfluxDB“ sme spomínali podzvorkovanie (downsampling). Mohli by sme si napríklad vytvoriť minútovú agregáciu údajov za posledný deň. Pre tento účel sa používajú pohľady (view), s ktorými sme sa už stretli:

CREATE VIEW ovzdusie_den AS
SELECT
	TIME_BUCKET('1 minute', cas) AS cas,
	miesto,
	ROUND(AVG(teplota), 1) AS teplota,
	ROUND(AVG(vlhkost), 0) AS vlhkost,
	ROUND(AVG(tlak), 2) AS tlak,
	ROUND(AVG(vzduch), 0) AS vzduch,
	ROUND(AVG(co2), 0) AS co2
FROM monitorovanie.ovzdusie
WHERE cas >= TIME_BUCKET('1 minute', NOW()) - INTERVAL '1 day'
GROUP BY miesto, TIME_BUCKET('1 minute', cas)
ORDER BY cas DESC, miesto;

-- vypíšeme prehľad
SELECT * FROM ovzdusie_den;

Každé volanie pohľadu však vždy znovu spustí dopyt v ňom definovaný - a to nie je veľmi efektívne. Navyše by to neriešilo uvoľňovanie nadbytočných (príliš podrobných) starých údajov. Dá sa konštatovať, že tento pohľad je len kozmetický a reálne nič nerieši.

Materializovaný pohľad

Preto si vytvoríme špeciálny pohľad, nazýva sa materializovaný pohľad (z pojmu „materialized view“ v angličtine), čo si môžeme predstaviť ako predpočítanú verziu klasického pohľadu (VIEW), ktorá šetrí výkon tým, že výsledky dopytu fyzicky ukladá na disk:

-- predošlý obyčajný pohľad zmažeme
DROP VIEW IF EXISTS ovzdusie_den;

-- a vytvoríme materializovaný
CREATE MATERIALIZED VIEW ovzdusie_den WITH (tsdb.continuous) AS
SELECT
	TIME_BUCKET('1 minute', cas) AS cas,
	miesto,
	ROUND(AVG(teplota), 1) AS teplota,
	ROUND(AVG(vlhkost), 0) AS vlhkost,
	ROUND(AVG(tlak), 2) AS tlak,
	ROUND(AVG(vzduch), 0) AS vzduch,
	ROUND(AVG(co2), 0) AS co2
FROM monitorovanie.ovzdusie
GROUP BY miesto, TIME_BUCKET('1 minute', cas)
ORDER BY cas DESC, miesto;

Čím sa líši materializovaný pohľad od bežného pohľadu? Na prvý pohľad vidno syntaktické rozdiely:

uviedli sme prívlastok „MATERIALIZED“, čo postačuje na vytvorenie štandardného materializovaného pohľadu v PostgreSQL;
za názov sme pridali TSDB výraz WITH (tsdb.continuous), čím sme ho povýšili na TimescaleDB pohľad s priebežnou aktualizáciou.

Čím sa však líši z funkčného hľadiska? Keď si zobrazíme tento pohľad ihneď po vytvorení, bude sa správať podľa očakávania - zobrazí nám aktuálne údaje zo zbernej tabuľky. No keď ho spustíme neskôr znovu, všimneme si zvláštne správanie - výsledky tohto pohľadu akosi zostali „primrznuté“ - zobrazujú sa stále tie isté, už neaktuálne:

-- môžeme sledovať 100 najaktuálnejších hodnôt
SELECT * FROM ovzdusie_den LIMIT 100;

Takéto správanie materializovaného pohľadu je v poriadku, môžeme ho chápať ako akúsi „cache“, do ktorej sa raz uložia údaje a zostanú tam. No ako tieto údaje zaktualizujeme?

Priebežná aktualizácia

Materializovaný pohľad potrebujeme pravidelne aktualizovať. Na toto slúžia pravidlá agregácie, ktoré nastavíme funkciou add_continuous_aggregate_policy() - v nej musíme uviesť názov materializovaného pohľadu a nastaviť parametre:

schedule_interval - časový interval aktualizácie (ako často sa má pohľad aktualizovať),
- obvykle zodpovedá granularite dopytu v pohľade;
initial_start - čas, od ktorého sa počíta interval (nepovinný),
- obvykle aktuálny čas „zaokrúhlený“ na interval aktualizácie;
start_offset - časový interval začiatku údajov, ktoré sa majú aktualizovať zo zdroja údajov,
- musí byť aspoň 2-násobok intervalu aktualizácie,
- môžeme ponechať celý interval plánovaného uchovávania údajov, aby sa zachytili aj „oneskorene vložené“ údaje (pozor však na poznámku nižšie, pri priebežnom mazaní),
- NULL znamená, že prejde celý zdroj údajov od najstaršieho;
end_offset - časový interval konca údajov, ktoré sa majú aktualizovať zo zdroja údajov,
- '0' znamená aktuálny čas, pričom ignoruje neukončený interval, čo väčšinou vyhovuje.

SELECT add_continuous_aggregate_policy(
	'ovzdusie_den',
	schedule_interval => INTERVAL '1 minute',
	initial_start => TIME_BUCKET('1 minute', NOW()),
	start_offset => INTERVAL '1 day', -- prípadne NULL
	end_offset => '0'
);

Ak sa pomýlime pri vytváraní pravidiel agregácie, môžeme ich poľahky zmazať:
SELECT remove_continuous_aggregate_policy('ovzdusie_den');

Takto nastavená aktualizácia vždy na prelome minút doplní údaje za práve uplynulú minútu. To však prináša nový problém - údaje sa stále pridávajú, ale žiadne neodbúdajú! Môžeme sa o tom presvedčiť:

SELECT COUNT(*) FROM ovzdusie_den;

Automatická aktualizácia údajov cez pravidlá agregácie neodstraňuje staré údaje!

Údaje sa budú každú minútu stále znovu prepisovať zo zdroja za posledný deň?

Nie, server si značí, ktoré údaje sú nové a pridá len tie. Preto dlhší interval v start_offset nepredstavuje zdržiavanie, a to ani pri hodnote NULL.

Priebežné mazanie starých údajov

Pre automatické mazanie starých údajov musíme definovať pravidlá mazania, ktoré nastavíme funkciou add_retention_policy() - v nej musíme opäť uviesť názov materializovaného pohľadu (alebo zdrojovej hypertabuľky) a nastaviť parametre:

drop_after - interval uchovávania údajov;
schedule_interval - časový interval mazania (ako často sa má mazanie vykonať),
- postačuje aj násobne dlhší interval oproti aktualizácii;
initial_start - čas, od ktorého sa počíta interval (nepovinný),
- obvykle aktuálny čas „zaokrúhlený“ na interval mazania.

SELECT add_retention_policy(
	'ovzdusie_den',
	drop_after => INTERVAL '1 day',
	schedule_interval => INTERVAL '1 hour',
	initial_start => TIME_BUCKET('1 hour', NOW())
);

Aj pravidlá mazania môžeme zmazať: SELECT remove_retention_policy('ovzdusie_den');

Pozor na možný konflikt pravidiel mazania a aktualizácie! Parameter start_offset pri aktualizácii pohľadu nesmie byť dlhší ako parameter drop_after pri mazaní zdroja údajov. Teda ak napríklad týždenný pohľad čerpá údaje z denného pohľadu (s drop_after 1 deň), nesmie sa pozerať do minulosti staršej ako 1 deň (start_offset 1 deň), pretože by „videl“, že staršie údaje zmizli a zmazal by ich tiež.

Teraz môžeme skontrolovať stav monitorovania - aktuálne počty údajov:

SELECT COUNT(*) FROM ovzdusie_den;

SELECT
	miesto,
	COUNT(teplota) AS teploty,
	COUNT(vlhkost) AS vlhkosti,
	COUNT(tlak) AS tlaky,
	COUNT(vzduch) AS vzduchy,
	COUNT(co2) AS CO2ky,
	COUNT(*) AS SPOLU
FROM ovzdusie_den
GROUP BY miesto
ORDER BY miesto;

Je pekné, že mažeme staré údaje v materializovanom pohľade, ale naša zberná tabuľka sa stále plní. Rovnakým spôsobom však môžeme nastaviť automatické mazanie starých údajov aj pre ňu.

Nastavil som mazanie údajov, no staré údaje nemiznú, počet zostáva rovnaký. V čom je problém?

Spomeňme si na organizovanie údajov v TimescaleDB do blokov (chunks). Predvolene je tabuľka rozdelená na bloky po 7 dňoch. Databáza z dôvodu extrémneho výkonu nemaže jednotlivé riadky, ale maže (zahadzuje) celé bloky naraz. Blok však môže zahodiť až vtedy, keď sú úplne všetky údaje v ňom staršie ako náš nastavený limit. Preto sa môže stať, že hoci požadujeme mazať po 1 dni, dáta reálne zmiznú až po tom, čo „zostarne“ celý 7-dňový blok. V produkcii sa preto veľkosť blokov prispôsobuje pravidlám mazania. Ak nastavíme blok na 1 deň a mažeme po každom dni, reálne zostanú dostupné údaje z aktuálneho dňa + celého predošlého dňa.

Praktický príklad

V našom scenári s monitorovaním ovzdušia by sme pre každé miesto mohli požadovať prehľady údajov:

ovzdusie: zberná tabuľka, všetky údaje (nanajvýš každých 5 sekúnd) za posledných 24 hodín (do 17280 hodnôt, reálne cca 5760 hodnôt);
ovzdusie_tyzden: pohľad na posledný týždeň v 1-minútových súhrnoch (10080 hodnôt);
ovzdusie_mesiac: pohľad na posledný mesiac v 5-minútových súhrnoch (8640 hodnôt);
ovzdusie_rok: pohľad na posledný rok v 1-hodinových súhrnoch (8760 hodnôt);
ovzdusie_25rok: pohľad na posledných 25 rokov v denných súhrnoch (9131 hodnôt).

Prehľady pre jednotlivé časové obdobia

Chceli by sme však poznať nielen priemery hodnôt v daných časových oknách, ale aj najmenšie a najväčšie hodnoty (toto podrobne rozoberieme nižšie). Vytvoríme si teda jednotlivé materializované pohľady a nastavíme im pravidlá agregácie a mazania.

Prvý prehľad (týždňový) bude brať údaje priamo zo zdrojovej tabuľky:

CREATE MATERIALIZED VIEW ovzdusie_tyzden WITH (tsdb.continuous) AS
SELECT
	TIME_BUCKET('1 minute', cas) AS cas,
	miesto,
	ROUND(AVG(teplota), 4) AS teplota,
	MIN(teplota) AS teplota_min,
	MAX(teplota) AS teplota_max,
	ROUND(AVG(vlhkost), 2) AS vlhkost,
	MIN(vlhkost) AS vlhkost_min,
	MAX(vlhkost) AS vlhkost_max,
	ROUND(AVG(tlak), 4) AS tlak,
	MIN(tlak) AS tlak_min,
	MAX(tlak) AS tlak_max,
	ROUND(AVG(vzduch), 2) AS vzduch,
	MIN(vzduch) AS vzduch_min,
	MAX(vzduch) AS vzduch_max,
	ROUND(AVG(co2), 2) AS co2,
	MIN(co2) AS co2_min,
	MAX(co2) AS co2_max
FROM monitorovanie.ovzdusie
GROUP BY miesto, TIME_BUCKET('1 minute', cas)
ORDER BY cas DESC, miesto;

SELECT add_continuous_aggregate_policy(
	'ovzdusie_tyzden',
	schedule_interval => INTERVAL '1 minute',
	initial_start => TIME_BUCKET('1 minute', NOW()),
	start_offset => INTERVAL '1 day',  -- nesmie byť viac, lebo zdrojovú tabuľku mažeme po 1 dni
	end_offset => '0'
);

SELECT add_retention_policy(
	'ovzdusie_tyzden',
	drop_after => INTERVAL '1 week',
	schedule_interval => INTERVAL '1 day',
	initial_start => TIME_BUCKET('1 day', NOW()) + INTERVAL '30 seconds'
);

Všimnite si odloženie mazania o 30 sekúnd - cieľom je, aby sa nediali všetky operácie naraz.

Druhý prehľad (mesačný) však už nebude brať údaje zo zbernej tabuľky, ale z týždenného prehľadu:

CREATE MATERIALIZED VIEW ovzdusie_mesiac WITH (tsdb.continuous) AS
SELECT
	TIME_BUCKET('5 minutes', cas) AS cas,
	miesto,
	ROUND(AVG(teplota), 4) AS teplota,
	MIN(teplota_min) AS teplota_min,
	MAX(teplota_max) AS teplota_max,
	ROUND(AVG(vlhkost), 2) AS vlhkost,
	MIN(vlhkost_min) AS vlhkost_min,
	MAX(vlhkost_max) AS vlhkost_max,
	ROUND(AVG(tlak), 4) AS tlak,
	MIN(tlak_min) AS tlak_min,
	MAX(tlak_max) AS tlak_max,
	ROUND(AVG(vzduch), 2) AS vzduch,
	MIN(vzduch_min) AS vzduch_min,
	MAX(vzduch_max) AS vzduch_max,
	ROUND(AVG(co2), 2) AS co2,
	MIN(co2_min) AS co2_min,
	MAX(co2_max) AS co2_max
FROM ovzdusie_tyzden
GROUP BY miesto, TIME_BUCKET('5 minutes', cas)
ORDER BY cas DESC, miesto;

SELECT add_continuous_aggregate_policy(
	'ovzdusie_mesiac',
	schedule_interval => INTERVAL '5 minutes',
	initial_start => TIME_BUCKET('5 minutes', NOW()) + INTERVAL '5 seconds',
	start_offset => INTERVAL '1 week',  -- nesmie byť viac, lebo zdrojový pohľad mažeme po 1 týždni
	end_offset => '0'
);

SELECT add_retention_policy(
	'ovzdusie_mesiac',
	drop_after => INTERVAL '1 month',
	schedule_interval => INTERVAL '1 day',
	initial_start => TIME_BUCKET('1 day', NOW()) + INTERVAL '35 seconds'
);

Podobne ~~vytvoríme~~by sme mohli vytvoriť prehľad ročný (z mesačného) a 25-ročný (z ročného) - to už iste zvládne každý aj sám.

Môžeme sa pozrieť na štatistiky, postupne sa budú plniť údajmi:

SELECT '0: ovzdušie' AS tabuľka, COUNT(*) AS riadkov, COUNT(DISTINCT miesto) AS miest, MIN(cas) AS od FROM monitorovanie.ovzdusie
UNION SELECT '1: týžden po minútach', COUNT(*), COUNT(DISTINCT miesto), MIN(cas) FROM ovzdusie_tyzden
UNION SELECT '2: mesiac po 5 minútach', COUNT(*), COUNT(DISTINCT miesto), MIN(cas) FROM ovzdusie_mesiac
UNION SELECT '3: rok po hodinách', COUNT(*), COUNT(DISTINCT miesto), MIN(cas) FROM ovzdusie_rok
UNION SELECT '4: 25 rokov po dňoch', COUNT(*), COUNT(DISTINCT miesto), MIN(cas) FROM ovzdusie_25rok
ORDER BY tabuľka;

Na záver by sme mali nastaviť aj pravidlá mazania samotnej zdrojovej tabuľky:

SELECT add_retention_policy(
	'ovzdusie',
	drop_after => INTERVAL '1 day',
	schedule_interval => INTERVAL '1 day',
	initial_start => TIME_BUCKET('1 day', NOW())
);

Minimálne a maximálne hodnoty

Ďalším ľahko prehliadnuteľným detailom je získavanie minimálnej a maximálnej hodnoty v procese agregácie údajov. Pokiaľ pracujem so zdrojovou tabuľkou, v ktorej sú úplne všetky hodnoty, tak samozrejme nie je problém získať minimum a maximum z akéhokoľvek intervalu, či pri akomkoľvek zoskupení.

Problém nastáva pri samotnej agregácii údajov. Z viacerých hodnôt v určitom časovom intervale robíme priemer, a tak sa minimálne a maximálne hodnoty jednoducho stratia v priemere. Ľahko pochopiteľným príkladom je vonkajšia teplota. Všetci vieme, že sa (aspoň v našich vnútrozemských končinách) počas dňa výrazne mení. Pokiaľ pri agregácii údajov v dlhodobom archíve evidujeme denné priemery, z nich by sme už nemali šancu zistiť, aká bola absolútne najnižšia (nočná) a najvyššia (denná) teplota v mesiaci, či roku.

Z tohoto dôvodu musíme pri každej agregácii zachovať aj minimálnu a maximálnu hodnotu (pokiaľ ich potrebujeme). Teda v našom scenári s monitoringom ovzdušia už pri vytváraní prvého (týždňového) prehľadu nezisťujeme z každej minúty zdrojovej tabuľky len priemer, ale aj minimum a maximum. Následne pri druhom (mesačnom) prehľade v podstate robíme priemer z priemerov, minimum z miním a maximum z maxím 5-minútového intervalu.

Že je vo výsledných hodnotách naozaj rozdiel, sa môžeme veľmi jednoducho presvedčiť - čím dlhší interval agregácie, tým bude rozdiel výraznejší:

SELECT
	MIN(teplota_min) AS min, -- skutočné minimum
	MIN(teplota) AS min_avg, -- minimum z už priemerovaných hodnôt
	MAX(teplota) AS max_avg, -- maximum z už priemerovaných hodnôt
	MAX(teplota_max) AS max -- skutočné maximum
FROM ovzdusie_rok
WHERE miesto = 'obývačka';

Skutočné minimum bude nižšie ako z priemerov a skutočné maximum bude vyššie ako z priemerov, čo je pekne vidno aj na grafe:

Ani toto riešenie však nie je bezproblémové - v realite sa stáva, že niektoré merania sú chybné. Môže sa jednať o nejaké rušenie, chybu komunikácie, či náhodnú udalosť. Predstavme si situáciu, že senzor funguje bezchybne celú hodinu a posiela hodnoty okolo 22 °C. Zrazu dôjde k výkyvu napätia, chybe na zbernici a senzor odošle chybnú hodnotu (nad 100 °C, či pod -100 °C). Tomuto sa hovorí odľahlá hodnota (outlier).

Takto vzniknutý extrém sa potom bude prenášať z aktuálnych hodnôt do týždňových, mesačných i ročných a navyše bude kaziť grafy. V praxi je preto treba uplatniť nejakú formu predspracovania údajov a vylúčiť zjavne chybné hodnoty - aj touto problematikou sa zaoberá Internet vecí (IoT).

Percentil

Šikovným trikom je namiesto najvyššej hodnoty brať hodnotu s percentilom 95 % (zvyšných 5 % je rezerva od chybných hodnôt) a podobne - namiesto najmenšej hodnoty brať percentil 5 %. Na tento účel slúži agregačná SQL funkcia PERCENTILE_CONT:

SELECT
	TIME_BUCKET('1 hour', cas) AS cas,
	miesto,
	ROUND(AVG(teplota), 4) AS teplota,
	ROUND((percentile_cont(0.05) WITHIN GROUP (ORDER BY teplota))::NUMERIC, 4) AS teplota_min,
	ROUND((percentile_cont(0.95) WITHIN GROUP (ORDER BY teplota))::NUMERIC, 4) AS teplota_max
FROM monitorovanie.ovzdusie
WHERE cas >= TIME_BUCKET('1 hour', NOW()) - INTERVAL '3 hours'
GROUP BY miesto, TIME_BUCKET('1 hour', cas)
ORDER BY cas DESC, miesto;

Aby táto funkcia fungovala zmysluplne, musí byť k dispozícii dostatočné množstvo hodnôt. Má zmysel robiť percentil pri vytváraní týždňového pohľadu (s minútovou agregáciou) zo zdrojovej tabuľky (s reálnymi údajmi po 15 sekundách)? Ak sa budú agregovať vždy len 4 hodnoty, percentil z nich získame len sotva, respektíve sa využije aproximácia, a tak namiesto uletenej hodnoty 150 °C uvidíme možno 130 °C, čo náš problém rozhodne nerieši. V našom scenári s monitorovaním ovzdušia teda nemá zmysel riešiť percentil v týždňovom, ba ani v mesačnom pohľade.

Zmysel to však má pri ročnom prehľade, ktorý agreguje údaje v hodinových intervaloch, čo predstavuje 240 nameraných hodnôt - toľko ich však budeme mať len v prípade, že by sme údaje čerpali priamo zo zdrojovej tabuľky a nie kaskádovaním z mesačného pohľadu. Inak povedané - percentil nemožno použiť pri kaskádovaní pohľadov. Ročný prehľad by teda mal vyzerať nasledovne:

CREATE MATERIALIZED VIEW ovzdusie_rok WITH (tsdb.continuous) AS
SELECT
	TIME_BUCKET('1 hour', cas) AS cas,
	miesto,
	ROUND(AVG(teplota), 4) AS teplota,
	ROUND((percentile_cont(0.05) WITHIN GROUP (ORDER BY teplota))::NUMERIC, 4) AS teplota_min,
	ROUND((percentile_cont(0.95) WITHIN GROUP (ORDER BY teplota))::NUMERIC, 4) AS teplota_max,
	ROUND(AVG(vlhkost), 2) AS vlhkost,
	ROUND((percentile_cont(0.05) WITHIN GROUP (ORDER BY vlhkost))::NUMERIC, 2) AS vlhkost_min,
	ROUND((percentile_cont(0.95) WITHIN GROUP (ORDER BY vlhkost))::NUMERIC, 2) AS vlhkost_max,
	ROUND(AVG(tlak), 4) AS tlak,
	ROUND((percentile_cont(0.05) WITHIN GROUP (ORDER BY tlak))::NUMERIC, 4) AS tlak_min,
	ROUND((percentile_cont(0.95) WITHIN GROUP (ORDER BY tlak))::NUMERIC, 4) AS tlak_max,
	ROUND(AVG(vzduch), 2) AS vzduch,
	ROUND((percentile_cont(0.05) WITHIN GROUP (ORDER BY vzduch))::NUMERIC, 2) AS vzduch_min,
	ROUND((percentile_cont(0.95) WITHIN GROUP (ORDER BY vzduch))::NUMERIC, 2) AS vzduch_max,
	ROUND(AVG(co2), 2) AS co2,
	ROUND((percentile_cont(0.05) WITHIN GROUP (ORDER BY co2))::NUMERIC, 2) AS co2_min,
	ROUND((percentile_cont(0.95) WITHIN GROUP (ORDER BY co2))::NUMERIC, 2) AS co2_max
FROM monitorovanie.ovzdusie  -- kvôli percentilu potrebujeme zdrojovú tabuľku so všetkými údajmi
GROUP BY miesto, TIME_BUCKET('1 hour', cas)
ORDER BY cas DESC, miesto;

A čo prehľad za 25 rokov? Ten už nemusí riešiť percentil, môže využiť ročný prehľad a uplatniť jednoduché minimum a maximum.

Keďže odľahlé hodnoty dokážu výrazne skresliť nielen minimum a maximum, ale aj obyčajný aritmetický priemer (AVG), môžeme ho nahradiť percentilom 50 % (mediánom). Medián vyberá strednú hodnotu zo zoradeného radu a prítomnosť rušivých hodnôt ho nijako neovplyvní.