Behandling av tidserie data i SQL database

[siDDis]

Blackbrrd, det er feil med dataintegritet. Ein kan fint få til skikkeleg dataintegritet, på bekostning av enda meir utviklingstid og ytelse

Men i dette tilfellet så trenger ikkje SeaWolf den funksjonaliteten.

 

Å lese spesifikk data frå ein fil er derimot ingen enkel sak, der er ein database mykje meir effektiv. Men så lenge SeaWolf skal lese alt av data frå den fila så er det ingen problem.

 

Fil IO er det raskaste fordi det er minst overhead, dataen trenger ikkje å gå over TCP/IP, den trenger ikkje å gå igjennom logikk for å plassere den i ein tabell, sjekke constraints og andre ting. Alt du gjer er å appende ein fil noko som krever så og si null ressurser. F.eks så logger Apache webserveren kvar einaste forespørsel den får, og det går minimalt utover ytelsen. Og det kan være svært mange i sekundet på eit større nettsted.

[kaffenils]

Å lese spesifikk data frå ein fil er derimot ingen enkel sak, der er ein database mykje meir effektiv. Men så lenge SeaWolf skal lese alt av data frå den fila så er det ingen problem.

Hvor har du lest at Seawolf skal lese alle data? Han skal vise spesifikke tidsintervaller og foreta kalkulasjoner basert på disse. Da må du nesten

 

Til Seawolf: Du skal ikke jobbe mange timene med å prøve å lage din eget lagringssystem for disse dataene, før du har sløst bort mer penger i timekostnader enn en skikkelig time series database koster.

[siDDis]

Ettersom eg leser så skal han lagre visse spesifikke tidsintervaller som han skal kaste etter utførte kalkulasjoner.

[norsemanGrey]

Takk for mange gode inputs. Det er ting som jeg skal se på fremover. Men akkurat for øyeblikket er jeg nødt til å bruke MySQL, det funger mer eller mindre slik som det er nå. Jeg logger 1000 verdier til 4 forskjellige tabeller hvert sekund uten noe problemer (så langt). Så litt tilbake til det jeg egentlig lurte på.

 

Om jeg har forstått rett vil noe som dette fungere:

INSERT INTO aggregatedtable ( time, value ) SELECT DATE_FORMAT( datetime, 'Y-m-d H:00:00' ) AS hour, AVG( value ) FROM rawtable GROUP BY hour;

 

Under er et bilde av hvordan tabellene er satt opp. Hvis jeg skal skrive en query som aggregerer verdiene over en time og legger disse tallene inn i en annen tabell. Hvordan bør jeg skrive queryen da? La oss si at tabellen med "rådata" heter values_unit_a_second og tabellen jeg skal legge de aggregerte verdiene i heter values_unit_a_hour. (Jeg kan ikke så mye om SQL språket så derfor jeg trenger litt hjelp til dette)

 

[JohndoeMAKT]

Her er litt SQL-kommandoer som kan hjelpe deg:

 

Først oppretter jeg to identiske tabeller, én for sekunddata og én for timesdata.

CREATE TABLE `test`.`valuessecond` (
`date` DATETIME NOT NULL ,
`a1` FLOAT NULL DEFAULT NULL ,
`a2` FLOAT NULL DEFAULT NULL ,
PRIMARY KEY ( `date` ) 
) ENGINE = InnoDB;

CREATE TABLE valueshour LIKE valuessecond;

 

Så putter jeg litt dummy-data inn i sekundtabellen:

INSERT INTO valuessecond ( date, a1, a2 ) VALUES ( '2009-09-29 00:00:00', 1.5, 2.5 ), ( '2009-09-29 00:00:01', 3.0, 3.1 ), ( '2009-09-29 01:00:00', 5.0, 5.0 );

 

Så kjører jeg til slutt en kommando som f.eks kan settes til å kjøres hver natt klokke tre. Kommandoen vil aggregere all sekunddata for forrige døgn til timesverdier og inserte inn i timetabellen.

INSERT IGNORE INTO valueshour( date, a1, a2 ) 
SELECT date_format( date, '%Y-%m-%d %H:00:00' ) AS HOUR , avg( a1 ) , avg( a2 ) 
FROM valuessecond WHERE date >= date_format( date_sub( now( ) , INTERVAL 1 DAY ) , '%Y-%m-%d 00:00:00' ) AND date < date_format( now( ) , '%Y-%m-%d 00:00:00' ) 
GROUP BY HOUR ;

[norsemanGrey]

Her er litt SQL-kommandoer som kan hjelpe deg:

 

Først oppretter jeg to identiske tabeller, én for sekunddata og én for timesdata.

CREATE TABLE `test`.`valuessecond` (
`date` DATETIME NOT NULL ,
`a1` FLOAT NULL DEFAULT NULL ,
`a2` FLOAT NULL DEFAULT NULL ,
PRIMARY KEY ( `date` ) 
) ENGINE = InnoDB;

CREATE TABLE valueshour LIKE valuessecond;

 

Så putter jeg litt dummy-data inn i sekundtabellen:

INSERT INTO valuessecond ( date, a1, a2 ) VALUES ( '2009-09-29 00:00:00', 1.5, 2.5 ), ( '2009-09-29 00:00:01', 3.0, 3.1 ), ( '2009-09-29 01:00:00', 5.0, 5.0 );

 

Så kjører jeg til slutt en kommando som f.eks kan settes til å kjøres hver natt klokke tre. Kommandoen vil aggregere all sekunddata for forrige døgn til timesverdier og inserte inn i timetabellen.

INSERT IGNORE INTO valueshour( date, a1, a2 ) 
SELECT date_format( date, '%Y-%m-%d %H:00:00' ) AS HOUR , avg( a1 ) , avg( a2 ) 
FROM valuessecond WHERE date >= date_format( date_sub( now( ) , INTERVAL 1 DAY ) , '%Y-%m-%d 00:00:00' ) AND date < date_format( now( ) , '%Y-%m-%d 00:00:00' ) 
GROUP BY HOUR ;

 

Takk for hjelpen Jeg skal teste ut litt med dette.

 

FROM valuessecond WHERE date >= date_format( date_sub( now( ) , INTERVAL 1 DAY ) , '%Y-%m-%d 00:00:00' ) AND date < date_format( now( ) , '%Y-%m-%d 00:00:00' ) 
GROUP BY HOUR;

 

Det jeg lurer litt på er der du setter grensene. Forteller du databasen her at den skal se fra tiden nå, now(), og en dag bakover i tid, INTERVAL 1 DAY. Kan det i så fall settes til noen timer, f.eks. INTERVAL 10 HOUR?

Endret 30. september 2009 av SeaWolf

[norsemanGrey]

INSERT IGNORE INTO log_analog_generator_b_hour( Time_Stamp, A_1501, A_1503 )
SELECT date_format( Time_Stamp, '%Y-%m-%d %H:00:00' ) AS HOUR , avg( A_1501 ) , avg( A_1503 ) FROM log_analog_generator_b WHERE Time_Stamp >= date_format( date_sub( '2009-10-01 08:00:00' , INTERVAL 10 HOUR ) , '%Y-%m-%d 00:00:00' ) AND Time_Stamp < date_format( '2009-10-01 08:00:00' , '%Y-%m-%d 00:00:00' )
GROUP BY HOUR;

 

Prøvde meg med denne på et datasett fra 2009-09-30 08:00:00 til 2009-10-01 08:00:00 med da få jeg aggregert data fra 2009-09-30 09:00:00 - 2009-09-30 23:00:00 når jeg egentlig skulle fått fra 2009-09-30 10:00:00 til 2009-10-01 08:00:00. Er det noe jeg har gjort feil her?

[JohndoeMAKT]

Hvorfor bruker du f.eks DATE_SUB når du vet de absolutte tidene du skal bruke?

 

Ønsker du 2009-09-30 10:00:00 til 2009-10-01 08:00:00 skiver du dette:

 

INSERT IGNORE INTO log_analog_generator_b_hour( Time_Stamp, A_1501, A_1503 )
SELECT date_format( Time_Stamp, '%Y-%m-%d %H:00:00' ) AS HOUR , avg( A_1501 ) , avg( A_1503 ) FROM log_analog_generator_b WHERE Time_Stamp >= '2009-10-01 10:00:00' AND Time_Stamp < '2009-10-01 08:00:00' GROUP BY HOUR;