Behandling av tidserie data i SQL database

[norsemanGrey]

Hei

 

Jeg har en liten utfordring jeg kunne trenge litt hjelp med. Jeg har en MySQL database hvor jeg logger data hvert sekund for en rekke tags. Hver time ønsker jeg å beregne gjennomsnittet av alle loggede verdier for hvert tag over den siste timen og putte disse verdiene i en ny database. Hva er den beste måten å gå frem på for å få til dette?

 

Takker på forhånd for input

[siDDis]

Bruk vanleg fil-IO for logging. Så går du igjennom verdiene og putter dei inn i databasen etterpå.

[blackbrrd]

Hvis du ikke er interessert i rådataene hadde jeg bare holdt dataene i minnet på webserveren (som jeg antar du bruker). Det skal da være en smal sak å oppdatere databasen med disse dataene en gang i timen.

 

Hvis du faktisk skal ha svar som er 100% relevant for din problemstilling så er det nyttig å forklare problemet fullt ut, ikke bare et lite utdrag.

Endret 28. september 2009 av blackbrrd

[norsemanGrey]

Hvis du ikke er interessert i rådataene hadde jeg bare holdt dataene i minnet på webserveren (som jeg antar du bruker). Det skal da være en smal sak å oppdatere databasen med disse dataene en gang i timen.

 

Hvis du faktisk skal ha svar som er 100% relevant for din problemstilling så er det nyttig å forklare problemet fullt ut, ikke bare et lite utdrag.

 

Nei det var kanskje ikke så godt beskrevet. Skal forklare nærmere....

 

Jeg populerer en tabell i en MySQL database med verdier fra et HMI program hvert sekund. Det er alt fra 100 til 300 kolonner med data. Jeg ønsker hver time å beregne gjennomsnittet av all data i hver kolonne samlet den siste timen og legge dette inn i en ny tabell. Det jeg trenger hjelp til er å lage et SQL script for å få til dette.

[blackbrrd]

Hvordan henter du dataene?

 

Er du interessert i å bruke dataene til noe, eller er det bare for å vedlikeholde statistikken?

[kaffenils]

Det du sannsynligvis er ute etter er en tidsserie-database, ikke en relasjonsdatabase. Se her: http://en.wikipedia.org/wiki/Time_series_database

[norsemanGrey]

Det du sannsynligvis er ute etter er en tidsserie-database, ikke en relasjonsdatabase. Se her: http://en.wikipedia.org/wiki/Time_series_database

 

Ja det hadde absolutt vært optimalt, men tror ikke det er noe som kan brukes i denne sammenhengen. Jeg kan ikke så mye om tidsserie-databaser, men jeg tror kanskje ikke HMI applikasjonen jeg bruker kan interface med en slik database. Jeg kan ta feil. Er det noen gratis tidsserie-databaser som det går ann å eksperimentere med?

[kaffenils]

Er det noen gratis tidsserie-databaser som det går ann å eksperimentere med?

Ikke som jeg kjenner til. Som regel betaler du for antall tags du vil lagre.

Vi (ikke jeg, andre i bedriften jeg jobber i) har prøvd å lagre tidserier (ca 500 tags med logging hvert sekund) i relasjonsdatabaser. Selve lagringen går greit, men uthenting og aggregering over tidsenheter blir "umulig" uten veldig kraftig maskinvare. Kostnaden av ekstra maskinvare overstiger langt prisen av en tidsseriedatabase.

[JohndoeMAKT]

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;

[siDDis]

Kvifor ikkje bare bruke vanleg fil-IO, det er superraskt og enkelt? Man trenger ikkje ein relasjonsdatabase i absolutt alle tilfeller. Å lagre masse data kvart sekund i ein relasjonsdatabase er jo heilt idiotisk.

[blackbrrd]

Hvis du kun er ute etter de aggregerte dataene så er det da fullt mulig å aggregere i en applikasjon underveis med minimalt ressursbruk.

[kaffenils]

Hvis du kun er ute etter de aggregerte dataene så er det da fullt mulig å aggregere i en applikasjon underveis med minimalt ressursbruk.

Jeg tror "problemet" til trådstarter er at en ikke alltid vet hvilke aggregeringer en ønsker, og hvilke perioder en vil gruppere på. Det hjelper lite å lagre AVG aggregeringer per time når en pluselig finner ut at en også vil ha MIN, MAX, STDEV per dag eller uke eller mnd eller per 12 time.

Jeg har lært at en ting er sikkert, og det er at ingenting er sikkert. Om kunden sier at de kun trenger aggregeringer per time, så vil de om ett år si at de ønsker å kunne definere intervallet for aggregering selv og at de vil se på andre aggregeringsfunksjoner enn de de opprinngelig ønsket. Da er du relativt fortapt hvis du har valgt en relasjonsdatabase.

[blackbrrd]

Veldig sant det du sier kaffenils, men det høres ut som rådataene allerede ligger lagret et annet sted...?

[norsemanGrey]

Hvordan henter du dataene?

 

Er du interessert i å bruke dataene til noe, eller er det bare for å vedlikeholde statistikken?

 

HMI applikasjonen er bygd opp rundt en sentral tag database som oppdaterer og håndterer alle tags i applikasjonen (det kan være tags kun brukt internt i applikasjonen og tags som får verdier fra PLCer gjennom modbus). Verdiene vi ønsker å ta vare på blir logget i SQL databasen ved bruk av VBScript og SQL. Har skrevet en funksjon i VBScrip som bygger en SQL query til databasen hvert sekund med verdiene til tag'ene.

 

Vi lagrer verdiene for at vi skal ha mulighet til å vise dem i en trend i HMI applikasjonen. Slik at hvis vi vil se på verdiene til 10 forskjellige sensorer over en periode på 1 mnd kan vi få dem opp i trend vinduet. Men når vi skal se på data over lengre tidsrom er det veldig mye data som skal hentes opp. Data hvert sekund blir da mye høyere oppløsningen enn det vi trenger. Derfor vil vi ha en database gjennomsnitt for hver time som vi bruker når vi vil se på data over et lengre tidsrom.

 

Er det noen gratis tidsserie-databaser som det går ann å eksperimentere med?

Ikke som jeg kjenner til. Som regel betaler du for antall tags du vil lagre.

Vi (ikke jeg, andre i bedriften jeg jobber i) har prøvd å lagre tidserier (ca 500 tags med logging hvert sekund) i relasjonsdatabaser. Selve lagringen går greit, men uthenting og aggregering over tidsenheter blir "umulig" uten veldig kraftig maskinvare. Kostnaden av ekstra maskinvare overstiger langt prisen av en tidsseriedatabase.

 

Er det noen tidsserie-databaser du kan anbefale å ta en titt på? Bruker man vanlig SQL språk for å skrive inn og hente ut data fra slike databaser?

 

Kvifor ikkje bare bruke vanleg fil-IO, det er superraskt og enkelt? Man trenger ikkje ein relasjonsdatabase i absolutt alle tilfeller. Å lagre masse data kvart sekund i ein relasjonsdatabase er jo heilt idiotisk.

 

Jeg er ikke helt sikker på hva du mener med fil-IO? Kan du forklare det litt nærmere?

 

Hvis du kun er ute etter de aggregerte dataene så er det da fullt mulig å aggregere i en applikasjon underveis med minimalt ressursbruk.

 

Er det ikke nesten enklere å gjøre det med dataene man har tatt vare på i databasen hver time?

[kaffenils]

Er det noen tidsserie-databaser du kan anbefale å ta en titt på? Bruker man vanlig SQL språk for å skrive inn og hente ut data fra slike databaser?

Jeg har såvidt brukt Proficy Historian (http://www.gefanuc.com/products/2420), men kun til uthenting av data. Spørring mot data likner veldig på SQL, så det vil du lære raskt.

Lagring av data er jeg ikke særlig inne på, men den gang (tre år siden) så kunne du ikke lagre data ved hjelp av SQL. Jeg er usikker på hvordan det ble gjort i praksis, men jeg mener å huske at det bl.a finnes et C/C++ API.

 

Et annet system er PI (http://www.osisoft.com/software-support/what-is-pi/Interfaces.aspx), men jeg husker at dette vær endel dyrere enn Proficy Historian.

[blackbrrd]

"Data hvert sekund blir da mye høyere oppløsningen enn det vi trenger"

 

Ikke lagre dem i databasen hvert sekund da, men lagre aggregatet en gang i timen, eller en gang hvert 15. min, eller tilsvarende.

 

Du kan gjerne hente ut dataene en gang i sekundet, men du behøver ikke lagre dataene hvis du ikke skal bruke dem til noe.

[siDDis]

Kvifor ikkje bare bruke vanleg fil-IO, det er superraskt og enkelt? Man trenger ikkje ein relasjonsdatabase i absolutt alle tilfeller. Å lagre masse data kvart sekund i ein relasjonsdatabase er jo heilt idiotisk.

 

Jeg er ikke helt sikker på hva du mener med fil-IO? Kan du forklare det litt nærmere?

 

Det er så enkelt som å skrive data til ein vanleg tekst fil, f.eks:

2005-03-19 15:10:26,618 - Vann - 12
2005-03-19 15:10:26,620 - Pumpe - 2120
2005-03-19 15:10:26,695 - Trykk - 21
2005-03-19 15:10:26,697 - Temperatur - 214
2005-03-19 15:10:26,720 - Pumpe - 2121
2005-03-19 15:10:26,773 - Trykk - 22
2005-03-19 15:10:26,818 - Vann - 11

 

Det er lynkjapt i forhald til å legge det inn i ein relasjonsdatabase og rimeleg enkelt å legge inn i ein relasjonsdatabase som du vil med AVG/MIN/MAX verdier seinare, f.eks kvar time/dag osv..

[norsemanGrey]

"Data hvert sekund blir da mye høyere oppløsningen enn det vi trenger"

 

Ikke lagre dem i databasen hvert sekund da, men lagre aggregatet en gang i timen, eller en gang hvert 15. min, eller tilsvarende.

 

Du kan gjerne hente ut dataene en gang i sekundet, men du behøver ikke lagre dataene hvis du ikke skal bruke dem til noe.

 

Jeg prøver å begrunne hvorfor vi trenger å få et gjennomsnitt av dataen hver time. Vi må selvfølgelig ha høyere oppløsning (sekund) når vi ser over et kort tidsrom. Det er det vi vanligvis gjør, og derfor "sampler" vi hvert sekund.

[norsemanGrey]

Kvifor ikkje bare bruke vanleg fil-IO, det er superraskt og enkelt? Man trenger ikkje ein relasjonsdatabase i absolutt alle tilfeller. Å lagre masse data kvart sekund i ein relasjonsdatabase er jo heilt idiotisk.

 

Jeg er ikke helt sikker på hva du mener med fil-IO? Kan du forklare det litt nærmere?

 

Det er så enkelt som å skrive data til ein vanleg tekst fil, f.eks:

2005-03-19 15:10:26,618 - Vann - 12
2005-03-19 15:10:26,620 - Pumpe - 2120
2005-03-19 15:10:26,695 - Trykk - 21
2005-03-19 15:10:26,697 - Temperatur - 214
2005-03-19 15:10:26,720 - Pumpe - 2121
2005-03-19 15:10:26,773 - Trykk - 22
2005-03-19 15:10:26,818 - Vann - 11

 

Det er lynkjapt i forhald til å legge det inn i ein relasjonsdatabase og rimeleg enkelt å legge inn i ein relasjonsdatabase som du vil med AVG/MIN/MAX verdier seinare, f.eks kvar time/dag osv.

 

Det var jeg ikke klar over. Hvorfor er dette raskere? Er det noen ulemper i forhold til å bruke en database? Større filer eller liknende?

[blackbrrd]

Problemet med å skrive til fil er bl.a. dataintegritet og en felles, kjent måte å hente dataene på.