Vraag Hoe kunt u 7,3 miljard rijen marktgegevens opslaan (geoptimaliseerd om te worden gelezen)?


Ik heb een dataset van 1 minuut gegevens van 1000 aandelen sinds 1998, die in totaal ongeveer (2012-1998)*(365*24*60)*1000 = 7.3 Billion rijen.

De meeste (99,9%) van de tijd zal ik alleen uitvoeren lezen verzoeken.

Wat is de beste manier om deze gegevens op te slaan in een database?

  • 1 grote tafel met rijen van 7,3B?
  • 1000 tabellen (één voor elk aandeelensymbool) met elk 7.3M rijen?
  • enige aanbeveling van database-engine? (Ik ben van plan Amazon RDS 'MySQL te gebruiken)

Ik ben niet gewend om dergelijke datasets te verwerken, dus dit is een uitstekende gelegenheid voor mij om te leren. Ik zal veel van je hulp en advies op prijs stellen.

Bewerk:

Dit is een voorbeeldrij:

'XX', 20041208, 938, 43.7444, 43.7541, 43.735, 43.7444, 35116.7, 1, 0, 0

Kolom 1 is het voorraadsymbool, kolom 2 is de datum, kolom 3 is de minuut, de rest zijn open-hoog-laag-dicht prijzen, volume en 3 integer kolommen.

De meeste vragen zullen zijn als "Geef mij de prijzen van AAPL tussen 12 april 2012 12:15 en 13 april 2012 12:52"

Over de hardware: ik ben van plan Amazon RDS te gebruiken, dus daar ben ik flexibel in


72
2018-03-22 01:26


oorsprong


antwoorden:


Vertel ons over de vragen en uw hardware-omgeving.

Ik zou erg geneigd zijn om te gaan NoSQL, gebruik makend van Hadoop of iets dergelijks, zolang je maar kunt profiteren van parallellisme.

Bijwerken

Oké, waarom?

Merk allereerst op dat ik naar de vragen heb gevraagd. Je kunt - en we kunnen zeker niet - deze vragen beantwoorden zonder te weten hoe de workload eruit ziet. (Ik zal toevallig een artikel over dit binnenkort verschijnen, maar ik kan het vandaag niet koppelen.) Maar het schaal van het probleem doet me denken aan het verlaten van een Big Old Database omdat

  • Mijn ervaring met vergelijkbare systemen suggereert dat de toegang groot sequentieel is (het berekenen van een soort van tijdreeksanalyse) of zeer zeer flexibele datamining (OLAP). Sequentiële gegevens kunnen achtereenvolgens beter en sneller worden verwerkt; OLAP betekent veel en veel indices berekenen, die ofwel veel tijd of veel ruimte in beslag nemen.

  • Als je doet wat effectief grootschalig is tegen veel gegevens in een OLAP-wereld, is een kolomgerichte aanpak misschien het beste.

  • Als u willekeurige query's wilt uitvoeren, met name het maken van kruisvergelijkingen, kan een Hadoop-systeem effectief zijn. Waarom? Omdat

    • je kunt parallellisme beter gebruiken op relatief kleine grondstoffenhardware.
    • u kunt ook betere betrouwbaarheid en redundantie implementeren
    • veel van die problemen lenen zich natuurlijk voor het MapReduce-paradigma.

Maar het is een feit dat, totdat we weten wat je werkdruk is, het onmogelijk is om iets definitiefs te zeggen.


25
2018-03-22 01:30



Databases zijn dus voor situaties waarin je een groot gecompliceerd schema hebt dat voortdurend verandert. Je hebt maar één "tafel" met een hand vol eenvoudige numerieke velden. Ik zou het op deze manier doen:

Bereid een C / C ++ struct voor om het recordformaat te behouden:

struct StockPrice
{
    char ticker_code[2];
    double stock_price;
    timespec when;
    etc
};

Bereken vervolgens sizeof (StockPrice [N]) waarbij N het aantal records is. (Op een 64-bits systeem) Het zou maar een paar honderd jol moeten zijn en passen op een harde schijf van $ 50.

Kopieer vervolgens een bestand naar dat formaat en mmap (op linux of gebruik CreateFileMapping op vensters) in het geheugen:

//pseduo-code
file = open("my.data", WRITE_ONLY);
truncate(file, sizeof(StockPrice[N]));
void* p = mmap(file, WRITE_ONLY);

Werp de mmaped-wijzer naar StockPrice * en maak een vulling van uw gegevens die de array invullen. Sluit de mmap en nu heb je je gegevens in één grote binaire array in een bestand dat later opnieuw kan worden mmaped.

StockPrice* stocks = (StockPrice*) p;
for (size_t i = 0; i < N; i++)
{
    stocks[i] = ParseNextStock(stock_indata_file);
}
close(file);

U kunt het nu opnieuw alleen lezen vanuit elk programma en uw gegevens zijn direct beschikbaar:

file = open("my.data", READ_ONLY);
StockPrice* stocks = (StockPrice*) mmap(file, READ_ONLY);

// do stuff with stocks;

Dus nu kun je het behandelen als een in-memory array van structs. U kunt verschillende soorten indexgegevensstructuren maken, afhankelijk van wat uw "query's" zijn. De kernel zal omgaan met het transparant transponeren van de data van / naar schijf, dus het zal waanzinnig snel zijn.

Als u verwacht een bepaald toegangspatroon te hebben (bijvoorbeeld aaneengesloten datum), is het het beste om de array in die volgorde te sorteren, zodat deze de schijf sequentieel raakt.


44
2018-03-31 04:27



Ik heb een dataset van 1 minuut gegevens van 1000 [...] voorraden (99,9%) van de tijd die ik alleen zal uitvoeren lezen verzoeken.

Eén keer opslaan en vele malen tijdgebaseerde numerieke gegevens lezen is een use-case genaamd "tijdreeks". Andere veel voorkomende tijdreeksen zijn sensorgegevens in het internet der dingen, servercontrolestatistieken, toepassingsevents, enz.

Deze vraag werd in 2012 gesteld en sindsdien hebben verschillende databasemotoren functies ontwikkeld speciaal voor het beheer van tijdreeksen. Ik heb geweldige resultaten behaald met de InfluxDB, dat is open source, geschreven in Go en MIT-licentie.

InfluxDB is specifiek geoptimaliseerd voor het opslaan en opvragen van tijdreeksgegevens. Veel meer dan Cassandra, dat vaak wordt aangeprezen als geweldig voor het opslaan van tijdreeksen:

InfluxDB vs Cassandra query speed

Optimalisatie voor tijdreeksen hield bepaalde afwegingen in. Bijvoorbeeld:

Updates van bestaande gegevens zijn zeldzaam en er gebeuren nooit controversiële updates. Tijdreeksgegevens zijn overwegend nieuwe gegevens die nooit worden bijgewerkt.

Pro: als u de toegang tot updates beperkt, kunnen de query- en schrijfprestaties worden verhoogd

Con: Update-functionaliteit is aanzienlijk beperkt

In openstaande benchmarks,

InfluxDB presteerde in alle drie de tests beter dan MongoDB met een 27x grotere schrijfsnelheid, terwijl 84 keer minder schijfruimte werd gebruikt en de prestaties van de query met betrekking tot de querysnelheid relatief hoog waren.

InfluxDB vs. MongoDB on-disk storage requirements and compression

Query's zijn ook heel eenvoudig. Als je rijen eruit zien <symbol, timestamp, open, high, low, close, volume>, met InfluxDB kunt u alleen dat opslaan en vervolgens gemakkelijk opvragen. Zeg, voor de laatste 10 minuten aan gegevens:

SELECT open, close FROM market_data WHERE symbol = 'AAPL' AND time > '2012-04-12 12:15' AND time < '2012-04-13 12:52'

Er zijn geen ID's, geen sleutels en geen joins te maken. Je kunt er veel van doen interessante aggregaties. Dat hoeft niet verticaal de tabel verdelen zoals met PostgreSQLof contort je schema in rijen van seconden zoals bij MongoDB. InfluxDB comprimeert ook heel goed, terwijl PostgreSQL kan geen compressie uitvoeren op het type gegevens dat u heeft.


23
2017-09-13 17:00



Oké, dus dit is iets verwijderd van de andere antwoorden, maar ... het lijkt me alsof je de gegevens in een bestandssysteem hebt (één bestand per bestand, misschien) met een vaste recordgrootte, je kunt bij de gegevens komen werkelijk eenvoudig: als u een vraag voor een bepaalde voorraad en een bepaald tijdbereik krijgt, kunt u naar de juiste plaats zoeken, alle benodigde gegevens ophalen (u weet precies hoeveel bytes), transformeert u de gegevens naar het formaat dat u nodig hebt (wat kan zijn erg snel, afhankelijk van je opslagformaat) en je bent weg.

Ik weet niets over Amazon-opslag, maar als je niet zoiets als directe toegang tot bestanden hebt, zou je eigenlijk blobs kunnen hebben - je zou grote blobs in evenwicht moeten brengen (minder records, maar waarschijnlijk meer data lezen dan je elk nodig hebt) tijd) met kleine blobs (meer records geven meer overhead en waarschijnlijk meer verzoeken om ze te pakken te krijgen, maar minder nutteloze gegevens worden elke keer geretourneerd).

Vervolgens voeg je caching toe - ik zou voorstellen om verschillende servers verschillende aandelen te geven om bijvoorbeeld te behandelen - en je kunt vrijwel gewoon vanuit het geheugen serveren. Als u genoeg geheugen op genoeg servers kunt veroorloven, omzeil dan het deel "load on demand" en laad gewoon alle bestanden bij het opstarten. Dat zou de zaken vereenvoudigen, ten koste van een langzamere opstart (wat uiteraard gevolgen heeft voor failover, tenzij u het zich kunt veroorloven altijd te hebben twee servers voor een bepaalde voorraad, wat handig zou zijn).

Merk op dat dat niet nodig is op te slaan het voorraadsymbool, de datum of de minuut voor elk record - omdat ze impliciet zijn in het bestand dat u laadt en de positie in het bestand. Je moet ook overwegen welke nauwkeurigheid je nodig hebt voor elke waarde, en hoe je dat efficiënt kunt opslaan - je hebt 6SF gegeven in je vraag, die je in 20 bits zou kunnen opslaan. Bewaar mogelijk drie 20-bits gehele getallen in 64 bits opslag: lees het als een long (of wat je 64-bits geheel getal dan ook is) en gebruik maskeren / verschuiven om het terug te halen naar drie gehele getallen. Je moet natuurlijk weten welke schaal je moet gebruiken, wat je waarschijnlijk zou kunnen coderen in de reserve 4 bits, als je het niet constant kunt maken.

Je hebt niet gezegd wat de drie andere gehele kolommen zijn, maar als je ook voor die drie 64 bits zou kunnen krijgen, zou je een heel record in 16 bytes kunnen opslaan. Dat is slechts ~ 110 GB voor de hele database, wat niet echt veel is ...

EDIT: Het andere ding om te overwegen is dat vermoedelijk de voorraad niet verandert tijdens het weekend - of zelfs 's nachts. Als de aandelenmarkt slechts 8 uur per dag, 5 dagen per week open is, dan heb je slechts 40 waarden per week nodig in plaats van 168. Op dat moment zou je kunnen eindigen met slechts ongeveer 28 GB aan gegevens in je bestanden ... wat klinkt een stuk kleiner dan je waarschijnlijk aanvankelijk dacht. Het hebben van zoveel gegevens in het geheugen is heel redelijk.

EDIT: Ik denk dat ik de uitleg van heb gemist waarom deze aanpak past hier goed: u hebt een zeer voorspelbaar aspect voor een groot deel van uw gegevens - de aandelentikker, datum en tijd. Door de ticker uit te drukken een keer (als de bestandsnaam) en de datum / tijd volledig impliciet achterlaten in de positie van de gegevens, je verwijdert een hele hoop werk. Het lijkt een beetje op het verschil tussen een String[] en een Map<Integer, String> - wetende dat uw array-index altijd begint bij 0 en omhoog gaat in stappen van 1 tot aan de lengte van de array, zorgt voor snelle toegang en efficiëntere opslag.


15
2018-03-24 09:27



Het is mijn begrip dat hdf5 werd specifiek ontworpen met de time-series opslag van voorraadgegevens als één potentiële toepassing. Medestapelaars hebben aangetoond dat HDF5 goed is voor grote hoeveelheden gegevens: chromosomen, fysica.


14
2018-03-22 01:37



Hier is een poging om een ​​Market Data Server te maken bovenop de Microsoft SQL Server 2012-database die goed moet zijn voor OLAP-analyse, een gratis open source-project:

http://github.com/kriasoft/market-data


4
2017-12-11 18:43



Ten eerste zijn er geen 365 handelsdagen in het jaar, met feestdagen 52 weekends (104) = zeg 250 x de werkelijke uren van de dagmarkt wordt geopend zoals iemand zei, en het symbool gebruiken als de primaire sleutel is geen goed idee aangezien symbolen veranderen, gebruik een k_equity_id (numeriek) met een symbool (char) omdat symbolen kunnen zijn zoals deze A, of GAC-DB-B.TO, dan in je gegevenstabellen van prijsinformatie, die je hebt, dus je schatting van 7.3 miljard is enorm overschat, want het is slechts ongeveer 1,7 miljoen rijen per symbool voor 14 jaar.

k_equity_id k_date k_minute

en voor de EOD-tabel (die 1000x boven de andere gegevens wordt bekeken)

k_equity_id k_date

Ten tweede, sla uw OHLC niet op per minuut in dezelfde DB-tabel als en EOD-tabel (einde van de dag), want iedereen die een pnf- of lijndiagram gedurende een jaarperiode wil bekijken, heeft geen interesse in de de minuut informatie.


4
2017-08-13 19:54



Laat me adviseren dat je een kijkje neemt apache solr, waarvan ik denk dat het ideaal is voor jouw specifieke probleem. In principe zou u eerst uw gegevens indexeren (elke rij is een "document"). Solr is geoptimaliseerd voor zoeken en ondersteunt native bereikreeksen op datums. Uw nominale vraag,

"Give me the prices of AAPL between April 12 2012 12:15 and April 13 2012 12:52"

zou vertalen naar iets als:

?q=stock:AAPL AND date:[2012-04-12T12:15:00Z TO 2012-04-13T12:52:00Z]

Ervan uitgaande dat "voorraad" de voorraadnaam is en "datum" een "datumveld" is gemaakt op basis van de kolommen "datum" en "minuut" van uw invoergegevens over indexering. Solr is ongelooflijk flexibel en ik kan echt niet genoeg goede dingen zeggen. Als u bijvoorbeeld de velden in de oorspronkelijke gegevens wilt behouden, kunt u waarschijnlijk een manier vinden om het 'DateField' dynamisch te maken als onderdeel van de query (of filter).


3
2018-03-27 15:37



Vergelijk de trage oplossingen met een eenvoudig geoptimaliseerd in-geheugenmodel. Ongecomprimeerd past het in een 256 GB ram-server. Een momentopname past in 32 K en u indexeert het gewoon positioneel op datetime en voorraad. Dan kun je gespecialiseerde momentopnames maken, omdat een open ervan vaak gelijk is aan het sluiten van de vorige.

[bewerken] Waarom denk je dat het zinvol is om een ​​database te gebruiken (rdbms of nosql)? Deze gegevens veranderen niet en passen in het geheugen. Dat is geen use-case waarbij een dbms waarde kan toevoegen.


3
2018-03-30 09:28



Ik denk dat elk groot RDBMS dit aankan. Op atomair niveau lijkt een tabel met correcte partitionering redelijk (partitie op basis van uw datagebruik als dit is vastgelegd - dit is waarschijnlijk een symbool of een datum).

U kunt ook kijken naar het samenstellen van geaggregeerde tabellen voor snellere toegang boven het atomaire niveau. Als uw gegevens bijvoorbeeld overdag zijn, maar u vaak gegevens terugkrijgt op het wekk- of zelfs het maandniveau, dan kan dit vooraf worden berekend in een verzameltabel. In sommige databases kan dit worden gedaan via een cacheweergave (verschillende namen voor verschillende DB-oplossingen - maar in feite is het een weergave van de atomaire gegevens, maar eenmaal uitgevoerd wordt de weergave in de cache opgeslagen / gehard in een vaste temp-tabel - die wordt opgevraagd voor daaropvolgende overeenkomende query's Dit kan met tussenpozen worden verwijderd om geheugen / schijfruimte vrij te maken).

Ik denk dat we je meer kunnen helpen met een idee over het datagebruik.


2
2018-03-30 05:51