Kysymys soveltuvasta SQL-arkkitehtuurista

Liittynyt
02.01.2017
Viestejä
87
Taustana sen verran, että Pythonilla on joitain pienempiä projekteja tullut tehtyä mutta virallista koulutusta näistä aihealueista ei löydy.

Nyt on työn alla oma prokkis, jota varten tarvitsen tietokannan. Tietokanta on neliulotteinen; x- ja y-koordinaatti, aika sekä parametrit. Ulottuvuuksilla on kokoa noin 200 x 200 x 262800 x 5, tässä järjestyksessä. Tietokannan kasaamisen jälkeen sitä ei enää tulla muokkaamaan. Jokaisessa solussa (cell?) on yksi arvo. Nopean laskutoimituksen mukaan tämän pitäisi mahtua alle 100GB tilaan. Tietokannasta tehdään ensisijaisesti kutsuja koordinaatien ja ajan suhteen sekä lasketaan keskiarvoja, jakaumia yms. eri parametreista.

Olen tässä pari iltaa lueskellut MySQL:stä, mutta en millään saa päätettyä miten tämä kannattaisi rakentaa. Kannattaako jokaiselle dimensiolle ja parametrille luoda oma table ja linkittää niitä toisiinsa dimensoiden mukaan? Ainoastaan x, y ja aika dimensiot ja siihen sisään sanakirja parametreja varten? Onko jotain muuta tietokantakieltä, millä onnistuisi tehokkaammin?

Lähinnä haen vertaistukea sekä vinkkejä, mistä ja millä sanoilla tätä kannattaa lähteä purkamaan. Ongelma ei varmasti ole millään tavalla uniikki, mutta google-fu pettää nyt pahasti, sillä termit ovat (selvästikin) hakusessa. Yleisimmät tutorialit netissä tuntuvat olevan asiakastietokantoja yms. joista en keksi ainakaan suoraan vastausta ongelmaani.

Jossain vaiheessa olisi mukava testata myös tätä AWS:ssä omakustanteisesti, kunhan hinnat pysyvät edes jotakuinkin kohtuullisena.
 
Liittynyt
17.10.2016
Viestejä
10 363
But why? Miksi tarvitset puolen vuoden minuuttitarkan datan?

Tee tuollainen taulu päivätasolle, opettele vähän tilastotiedettä ja laske ne loppulaskennan tarvittavat arvot yöajossa päivätasolle. Tallenna pelkkä päivätaso kantaan. Kanta on kohtuullisen kokoinen.

Ei ne hinnat ole kohtuullisia, kun sensoridata ja tuollaiset 52.5 miljardin rivin datat menee kätevimmin Redshiftiin, eikä Auroraan. Siistimällä tietokantaa saat sen kohtuullisen veloituksen piiriin.

Tietenkin jos tuo on jatkuvasti tulevaa sensoridataa, eikä sinulla ole sitä vielä, niin sitten on Kinesis ja TimeStream, mutta vähän hankalasti oli kuvattu se data. Ja nuo on satoja euroja tuolla määrällä dataa kuukaudessa.

T: AWS Certified Solutions Architect
 
Viimeksi muokattu:
Liittynyt
31.08.2018
Viestejä
92
Voi olla että ymmärsin ongelman asettelun väärin, mutta eikö tuo koko homma mene yhteen tauluun? Koordinaatit, aika ja parametrit olisivat jokainen oma kenttänsä.

Vaikka rivejä on kymmeniä miljardeja, voisi olla ns. kotikoneella kokeiltava juttu. Kannan luonti voi kestää mutta kyselyt toimia ihan järkevässä ajassa, varsinkin jos indeksit ovat kunnossa.
 
Liittynyt
02.01.2017
Viestejä
87
"But why" on erittäin hyvä kysymys ja tässä vaiheessa kun sitä asiantuntemusta ei tästä aiheesta löydy pätkän vertaa niin olen lähtenyt näkökulmasta "why not".

Kyseessä on siis 30 vuoden globaalia säädataa tunnin intervallilla ja 0.5deg koordinaattiresoluutiolla. Mielikuvissa dataa voitaisiin kutsua mielivaltaisesti rajatulla alueella, yksittäisillä pisteillä taikka esimerkiksi reitiltä pisteiden väliltä, joten tässä vaiheessa kuvittelen ainakin raaka-datan olevan fiksumpi lähestymistapa ilman, että tarkkuus kärsii. Käytännön rajoitteitahan en tällaiselle järjestelmälle tunne.
 
Liittynyt
02.01.2017
Viestejä
87
Voi olla että ymmärsin ongelman asettelun väärin, mutta eikö tuo koko homma mene yhteen tauluun? Koordinaatit, aika ja parametrit olisivat jokainen oma kenttänsä.

Vaikka rivejä on kymmeniä miljardeja, voisi olla ns. kotikoneella kokeiltava juttu. Kannan luonti voi kestää mutta kyselyt toimia ihan järkevässä ajassa, varsinkin jos indeksit ovat kunnossa.
Voipi olla. Yksinkertaisimmillaan siis, että yksi taulu pystyyn mistä löytyy esimerkiksi kentät [x-coord, y-coord, time, para1, para2, para3] ja riveille juokseva primary key? Jos rivejä on ns. "perkeleesti", niin venyvätkö kyselyt poskettomaksi jos haluaa vaikka kaikki arvot tietyltä päivämäärältä ja tiettyjen koordinaattien sisältä? Eikös tällä tavalla rakennettuna esimerkiksi aika-tieto ole ympäriinsä epäjärjestyksessä eikä kaikki saman koordinaatin ja aikaleiman tiedot ole relaatioilla "yhdessä paikassa"?

Vai mietinkö tätä liian munimutkaisesti?
 
Liittynyt
17.10.2016
Viestejä
10 363
"But why" on erittäin hyvä kysymys ja tässä vaiheessa kun sitä asiantuntemusta ei tästä aiheesta löydy pätkän vertaa niin olen lähtenyt näkökulmasta "why not".

Kyseessä on siis 30 vuoden globaalia säädataa tunnin intervallilla ja 0.5deg koordinaattiresoluutiolla. Mielikuvissa dataa voitaisiin kutsua mielivaltaisesti rajatulla alueella, yksittäisillä pisteillä taikka esimerkiksi reitiltä pisteiden väliltä, joten tässä vaiheessa kuvittelen ainakin raaka-datan olevan fiksumpi lähestymistapa ilman, että tarkkuus kärsii. Käytännön rajoitteitahan en tällaiselle järjestelmälle tunne.
Ehkä nyt sitten kannattaa tehdä se kanta kotikoneelle ja miettiä sen jälkeen optimointeja ja tilankulutusta, kun reunaehdot on selvillä.
 
Liittynyt
17.10.2016
Viestejä
10 363
Voipi olla. Yksinkertaisimmillaan siis, että yksi taulu pystyyn mistä löytyy esimerkiksi kentät [x-coord, y-coord, time, para1, para2, para3] ja riveille juokseva primary key? Jos rivejä on ns. "perkeleesti", niin venyvätkö kyselyt poskettomaksi jos haluaa vaikka kaikki arvot tietyltä päivämäärältä ja tiettyjen koordinaattien sisältä? Eikös tällä tavalla rakennettuna esimerkiksi aika-tieto ole ympäriinsä epäjärjestyksessä eikä kaikki saman koordinaatin ja aikaleiman tiedot ole relaatioilla "yhdessä paikassa"?

Vai mietinkö tätä liian munimutkaisesti?
Jos muutenkin heitetään ne tietokantojen suunnitteluperiaatteet roskiin, niin mihin tarvitset sitä primary keytä?

Nopeutusta varten @woodeye ehdotti indeksejä, joita kannattaa sitten tehdä x,y-pareille ja ajalle. Saa nähdä, nopeuttavatko nekään tarpeeksi.
 
Liittynyt
31.08.2018
Viestejä
92
Voipi olla. Yksinkertaisimmillaan siis, että yksi taulu pystyyn mistä löytyy esimerkiksi kentät [x-coord, y-coord, time, para1, para2, para3] ja riveille juokseva primary key? Jos rivejä on ns. "perkeleesti", niin venyvätkö kyselyt poskettomaksi jos haluaa vaikka kaikki arvot tietyltä päivämäärältä ja tiettyjen koordinaattien sisältä? Eikös tällä tavalla rakennettuna esimerkiksi aika-tieto ole ympäriinsä epäjärjestyksessä eikä kaikki saman koordinaatin ja aikaleiman tiedot ole relaatioilla "yhdessä paikassa"?

Vai mietinkö tätä liian munimutkaisesti?
Ensinnäkin, riippumatta siitä onko sulla 10 vai 10 miljardia riviä, kyselyt ovat samanlaisia. Toiseksi voit ajaa tiedot kantaa millaisessa järjestyksessä tahansa eikä sillä ole merkitystä.

Tietyltä ajanjaksolta ja tiettyjen koordinaattien sisältä tehty haku on todella yksinkertainen, jotain tyyliin

SELECT arvo FROM taulu WHERE xcoord > X1 AND xcoord < X2 AND ycoord > Y1 and ycoord < Y2 and aika > '2019-09-01' and aika < '2019-09-12'

Tämä nyt olettaen ettei ole mitään joka monimutkaistaa asetelmaa. Usein kannoissa on myös laajennuksia joilla voi tehdä aitoja maantieteellisiä koordinaatteja ja kehittyneempiä kyselyjä kuin tuo.
 
Liittynyt
17.01.2018
Viestejä
643
Ehdottomasti! AWS oli lähinnä jos ehkä joskus-heitto noin huvin, urheilun ja oppimisen takia
Kun rivejä tulee paljon niin partitiointi auttaa (vähän riippuen kannasta).
Esimerkiksi sql serverillä voi tehdä oman datatiedoston/partitio ja yhteen partitioon vaikka vuoden data. Tiedostoja tulee paljon mutta vuoden sisällä tapahtuva haku nopeutuu kummasti kun ei olekaan niin paljoa kahlattavaa.
Oracle osaa myös, mysql:n partitiointia en ole koskaan käyttänyt.

100M on vielä aika pieni, asenna kanta omalle koneelle ja testaa. Todennäköisesti et tarvitse muuta kuin muutaman indeksin jotka tukevat hakuja.

Mysql:ssä kannattaa valita sellainen storage engine joka ei lokita kaikkia kyselyitä (siis insert/update/delete) transaktiolokiin koska et niitä kuitenkaan tarvitse yhdellä syöttökerralla ja useilla hauilla. Perinteinen MyISAM toimii todennköisesti tässä paremmin kuin InnoDB.

Käytä oikeita datatyyppejä (siis ei stringejä joka paikassa) niin kyselyt nopeutuu ja tilaa kuluu vähemmän.
Jos päivämäärien ei tarvitse olla päivämääriä niin joissakin tilanteissa kokonaisluku muodossa yyyymmdd antaa paremman suorituskyvyn ja säilyttää ajan mukaisen vertailun oikein.

Jos nuo parametrit ovat tekstiä ja erillisiä arvoja on paljon vähemmän kuin datarivejä niin sitten omaan tauluun voi olla parempi ainakin tilankäytön kannalta. Kyselyteknisesti rankasti karkeistaen "sitä huonompi tehokkuus mitä enemmän tauluja kyselyssä on mukana"
 
Liittynyt
19.10.2016
Viestejä
1 150
Käytä oikeita datatyyppejä (siis ei stringejä joka paikassa) niin kyselyt nopeutuu ja tilaa kuluu vähemmän.
Jos päivämäärien ei tarvitse olla päivämääriä niin joissakin tilanteissa kokonaisluku muodossa yyyymmdd antaa paremman suorituskyvyn ja säilyttää ajan mukaisen vertailun oikein.
Mahtaneekohan tässä tulla mitään oikeaa voittoa? Vähän nimittäin näyttäisi sille, että MySQL:n DATE-tietotyyppi tallennetaan kolmetavuisena eli kaiketi jonkinlaisena kokonaislukuna. Tässä valossa kuulostaa vähän liiankin "fiksulta" optimoinnilta lähteä itse säätämään tyypin kanssa - vai onko tässä kuitenkin jokin järki, jota en näe?
 
Liittynyt
31.08.2018
Viestejä
92
Mahtaneekohan tässä tulla mitään oikeaa voittoa? Vähän nimittäin näyttäisi sille, että MySQL:n DATE-tietotyyppi tallennetaan kolmetavuisena eli kaiketi jonkinlaisena kokonaislukuna. Tässä valossa kuulostaa vähän liiankin "fiksulta" optimoinnilta lähteä itse säätämään tyypin kanssa - vai onko tässä kuitenkin jokin järki, jota en näe?
Nykyään tietokantamoottorit ovat niin hyvin tehtyjä ettei tuollaisella kikkailulla taida voittaa yhtään mitään.

Mutta päivä+aika -kenttien kanssa tuleekin toinen ongelma jos meillä on miljardi riviä aikaan sidottuja tapahtumia, ja hakuja tehdään ajan perusteella. Nimittäin aikapohjaista kenttää on paha indeksoida jos mennään minuutti- ja sekuntitasolle, ehkä tuntitasollakin tulee vaikeuksia valtavan datamäärän kanssa. Indeksit kasvavat niin isoiksi että haut alkavat kestää. Tätä pulmaa voisi kiertää tekemällä apukentän, jossa on tapahtuman ajasta vain päivä ja indeksoimalla sen. Sitten haut kohdistetaan ensin apukenttään ja sen lisäksi vielä varsinaiseen pvm-aikakenttään.

Sama juttu parametrien kanssa, aloittaja ei tainnut sanoa mitä parametrit ovat mutta jos säädatasta on kyse niin varmaan jotain float -tyyppisiä (lämpötila 17.8 jne) jotka on myös vähän paha indeksoida. Tässäkin voisi auttaa arvojen kopioiminen kokonaislukuina apukenttiin ja tekemällä niistä indeksoituja hakuja.
 
Liittynyt
17.10.2016
Viestejä
10 363
Nykyään tietokantamoottorit ovat niin hyvin tehtyjä ettei tuollaisella kikkailulla taida voittaa yhtään mitään.

Mutta päivä+aika -kenttien kanssa tuleekin toinen ongelma jos meillä on miljardi riviä aikaan sidottuja tapahtumia, ja hakuja tehdään ajan perusteella. Nimittäin aikapohjaista kenttää on paha indeksoida jos mennään minuutti- ja sekuntitasolle, ehkä tuntitasollakin tulee vaikeuksia valtavan datamäärän kanssa. Indeksit kasvavat niin isoiksi että haut alkavat kestää. Tätä pulmaa voisi kiertää tekemällä apukentän, jossa on tapahtuman ajasta vain päivä ja indeksoimalla sen. Sitten haut kohdistetaan ensin apukenttään ja sen lisäksi vielä varsinaiseen pvm-aikakenttään.

Sama juttu parametrien kanssa, aloittaja ei tainnut sanoa mitä parametrit ovat mutta jos säädatasta on kyse niin varmaan jotain float -tyyppisiä (lämpötila 17.8 jne) jotka on myös vähän paha indeksoida. Tässäkin voisi auttaa arvojen kopioiminen kokonaislukuina apukenttiin ja tekemällä niistä indeksoituja hakuja.
Indeksien lisäksi nuo apukentät vaan kasvattaa sitä kokoa ja tätä ei enää saa minnekään pilveen halvalla.

Tosin hieno harjoitus ja samalla tulee testattua se indeksointi + partitiointi.
 
Liittynyt
31.08.2018
Viestejä
92
Indeksien lisäksi nuo apukentät vaan kasvattaa sitä kokoa ja tätä ei enää saa minnekään pilveen halvalla.

Tosin hieno harjoitus ja samalla tulee testattua se indeksointi + partitiointi.
Jos vaihtoehtoina on fyysisesti pienempi tietokanta, jossa haut kestävät liian kauan valmistua ja isompi kanta jossa pystyy tekemään järjellisiä hakuja niin aika selvä kumpi kannattaa tehdä?

Tämä tehtävänasettelu haiskahtaa siltä että ainakin testiversion pystyy toteuttamaan ihan peruskoneella jos viitsii vähän miettiä suorituskykyongelmia etukäteen.
 

nnaku

I'm object-oriented!
Tukijäsen
Liittynyt
28.11.2016
Viestejä
759
Ompa kiinnostava kysymys ja vielä kiinnostavampi datasetti. Ehkä lähtisin toteuttamaan ideksejä kerroksina koodinaateille ja/tai osiointia aikaleiman mukaa. Laitas data jakoon jos on avointa. :tup:
 
Liittynyt
17.01.2018
Viestejä
643
Mahtaneekohan tässä tulla mitään oikeaa voittoa? Vähän nimittäin näyttäisi sille, että MySQL:n DATE-tietotyyppi tallennetaan kolmetavuisena eli kaiketi jonkinlaisena kokonaislukuna. Tässä valossa kuulostaa vähän liiankin "fiksulta" optimoinnilta lähteä itse säätämään tyypin kanssa - vai onko tässä kuitenkin jokin järki, jota en näe?
Kuten sanoin "joissakin tapauksissa". Monet optimointivaihtoehdot riippuvat valitusta kannasta ja myös siitä miten dataa käytetään.
On tullut vastaan tapauksia joissa esim date/datetime käyttö hakuehtona/joinissa on ollut hitaampi kuin pieni kokonaisluku.

Joka tapauksessa pitää käyttää tarkoitukseen sopivia datatyyppejä ja välttää stringejä jos massaa on tiedossa paljon, tämä ei muutu mihinkään kannasta riippumatta.
On myös ollut tapauksia joissa apukentät auttavat vaikka massa kasvaa, esimerkiksi minuutti/sekuntidata jonka seuraksi tehdään vuosikk tai päivä. Näihin joudutaan yleensä vain silloin kun rivejä on oikeasti paljon.

Esimerkkinä eräässä järjestelmässä on pääasiassa tuntitason dataa jota käsitellään ja myös näytetään käyttöliittymän kautta. Dataa on nyt noin 23M riviä ja taulu vie tilaa levyltä melkein 3G, kantana sql server 2014. Koska käyttö on pääasiassa kuukausitasolla niin vuosikk- kentän lisäys ja partitiointi sen avulla tuottaa hyvän nopeutuksen hakuihin. Sen indeksoinnillakin pärjää jo pitkälle.
Minuutti/sekuntitason päivämääräkenttää en ehkä lähtisi indeksoimaan ollenkaan.

Mutta kuten jo aiemmin sanoin, kannattaa kokeilla ensin ja sen perusteella sitten näkee pitääkö jotain säätää.

AWS- maailmassa pääsee aika hyvään tulokseen kun tallentaa datan parquet:iin, tallennus S3 ja luku Athenan kautta. S3 ja Athena on suhteellisen halpoja moneen muuhun verrattuna ja parkettien luku nopeaa.
Kirjoitus vaikka spark-shellin kautta käsin omalla koneella.

Ompa kiinnostava kysymys ja vielä kiinnostavampi datasetti. Ehkä lähtisin toteuttamaan ideksejä kerroksina koodinaateille ja/tai osiointia aikaleiman mukaa. Laitas data jakoon jos on avointa. :tup:
Tämä, vain näkemällä mitä pitää tehdä voi tehdä jotain oikeasti. Ilman aitoa dataa voi vain spekuloida kaikilla mahdollisilla malleilla ja arvailla:D
 
Liittynyt
17.10.2016
Viestejä
229
Itse lähtisin kokeilemaan ihan SQLitellä ja vaikka pythonilla miten taipuu, flatti taulu vaan johon kaikki datat ja kun datasetti on staattinen niin indeksoit vaan columnit joista haet, katso miten toimii. Tuollaista 100 gigan datasettiä pyörittelee kotikoneellakin aivan hyvin kunhan vaan valitsee oikeat työkalut, ellet nyt jotain todella monimutkaista tee sillä datalla. Unohda ainekin aluksi redshiftit ja pilvi DWH kilkkeet, niihin uppoaa fyrkkaa suuria määriä todella nopeasti eikä tuollainen 100 gigan datasetti vielä tee järkeväksi noiden järeiden työkalujen käyttöä kun niitä tosiaan pyörittelee vaikka sillä kuuluisalla omalla läppärillä.

Lisätään nyt vielä senverrran, että jos näitä datoja haluaa pilveen viedä niin varmaan tässä casessa edullisinta olisi katsoa Googlen BigQuery:ä, siellä saa partitioinnit tehtyä helposti ajan perusteella ja hintakaan ei päätä huimaa tuolla datamäärällä kunhan pitää ne kyselyt järkevinä.
 
Viimeksi muokattu:
Liittynyt
06.11.2018
Viestejä
264
Iso osa vanhempaa SQL dokumentaatiota perustuu normalisointiin jossa pyritään välttämään duplikaattia tietoa (siis lähinnä tilaa) optimoimalla rakennetta. Tämä voisi esim tarkoittaa, että yhdessä taulussa olisi koordinaatit ja id joka olisi toisessa taulussa foreign key.

Tuolla datalla voit päästä samaan suorituskykyyn helpommin yhdellä taululla riippuen lähinnä itse kyselyistä (ovatko koosteet koko datasta vai vain tietyistä koordinaateista / ajalta).

Sitä en alkuperäisestä viestistä ymmärtänyt, että onko 200 lokaatiota (uniikkia x/y paria) vai tosiaan 200x200, ja onko jokaisella ajalla mitattu jokaisella paikalla 5 eri arvoa?
 
Liittynyt
17.01.2018
Viestejä
643
Ja kuten tuolla aiemmin jo todettiin niin näyttämällä edes vähän dataa saa parempia vastauksia...

Tuo normalisointi on juuri noin, se on hyvä jos tietoja päivitellään paljon, siis yleensä jonkun softan takana.
Mitä lähemmäs raportointia mennään sitä mieluummin dataa kannattaa yhdistää/monistaa järjestellä niin että rakenteet tukee lukemista yleensä tilan kustannuksella.
 
Liittynyt
17.10.2016
Viestejä
19
Jos dataa ei tosiaan tulla muokkaamaan, niin minusta järkevin ratkaisu olisi tuupata se yhteen columnstore-tauluun. Itsellä kokemusta vain MSSQL:n toteutuksesta, mutta uskoisin että clustered CS indeksit löytyvät myös opensource tietokannoista.

Klusteroidulla columnstore indeksillä tuollainen data pakkaantunee kymmenesosaan rowstoreen verrattuna ja perus select -kyselyt where -ehdoilla on usein kymmeniä kertoja nopeampia, koska io:ta ei ole läheskään niin paljon indeksien skannauksissa.
 
Liittynyt
28.12.2016
Viestejä
484
Numpy ja memmap käyttöön. Tietokoneeseen mahdollisimman nopea ssd (tai yli 100gb muisti). Varmasti nopein ja helpoin tapa analysoida dataa.
 
Viimeksi muokattu:
Liittynyt
29.10.2016
Viestejä
489
Joku GIS-laajennos MySQL/MariaDB kantaan, tai jos tietokantahommat ovat uusia niin miksei suoraan PostgreSQL. Jos XY-koordinaatit olisivat vaikka WellKnownBinary-muodossa, niin sen saa indeksoitua aika hyvin. Aika jossain tunnetussa formaatissa ja päälle attribuutit.
Ainakin PostgreSQL PostGIS laajennoksella osaa aika hyvin nuo vertailut reitin ja kiinteiden pisteiden välillä. Aika hyvin löytyy Googlellakin neuvoja
 
Toggle Sidebar

Statistiikka

Viestiketjuja
81 024
Viestejä
1 649 987
Jäsenet
36 346
Uusin jäsen
kauppias

Hinta.fi

Ylös Bottom