73 억 행의 시장 데이터를 저장하는 방법 (읽기 위해 최적화 됨)?

73 억 행의 시장 데이터를 저장하는 방법 (읽기 위해 최적화 됨)?

---

1998 년 이후로 1,000 개의 주식에 대한 1 분 데이터의 데이터 세트가 있는데, 그 합계는 (2012-1998)*(365*24*60)*1000 = 7.3 Billion행 주변 입니다.

대부분 (99.9 %)의 경우 읽기 요청 만 수행 합니다.

이 데이터를 db에 저장하는 가장 좋은 방법은 무엇입니까?

• 7.3B 행이있는 큰 테이블 1 개?
• 각각 730 만 개의 행이있는 1000 개의 테이블 (각 주식 기호에 대해 하나씩)?
• 데이터베이스 엔진에 대한 권장 사항이 있습니까? (Amazon RDS의 MySQL을 사용할 계획입니다)

저는 이렇게 큰 데이터 세트를 다루는 데 익숙하지 않기 때문에 이것은 제가 배울 수있는 좋은 기회입니다. 많은 도움과 조언에 감사드립니다.

편집하다:

다음은 샘플 행입니다.

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

열 1은 주식 기호, 열 2는 날짜, 열 3은 분, 나머지는 시가 고가-저가 종가, 거래량 및 3 개의 정수 열입니다.

대부분의 쿼리는 "2012 년 4 월 12 일 12:15에서 2012 년 4 월 13 일 12:52 사이에 AAPL 가격을 알려주세요"와 같습니다.

하드웨어 정보 : Amazon RDS를 사용할 계획이므로 유연하게 사용할 수 있습니다.

---

쿼리 및 하드웨어 환경에 대해 알려주십시오.

나는 아주 아주 갈 유혹 될 수 없는 NoSQL을 사용하여, 하둡 만큼 당신이 병렬 처리를 활용할 수있는, 또는 비슷한.

최신 정보

좋아, 왜?

우선, 내가 질문에 대해 물었다는 것을 주목하십시오. 워크로드가 어떤 것인지 모른 채 이러한 질문에 답할 수는 없습니다. (이에 대한 기사가 곧 나타날 것입니다.하지만 오늘은 연결할 수 없습니다.)하지만 문제 의 규모 로 인해 Big Old Database에서 벗어나는 것을 생각하게됩니다.

• 유사한 시스템에 대한 경험에 따르면 액세스는 큰 순차 (일종의 시계열 분석 계산)이거나 매우 유연한 데이터 마이닝 (OLAP)이 될 것입니다. 순차적 데이터는 순차적으로 더 좋고 더 빠르게 처리 할 수 ​​있습니다. OLAP는 많은 시간이나 많은 공간이 필요한 많은 인덱스를 계산하는 것을 의미합니다.

• 그러나 OLAP 세계의 많은 데이터에 대해 효과적으로 큰 실행을 수행하는 경우 열 지향 접근 방식이 가장 좋습니다.

• 무작위 쿼리, 특히 교차 비교를 수행하려는 경우 Hadoop 시스템이 효과적 일 수 있습니다. 왜? 때문에

  • 비교적 작은 상용 하드웨어에서 병렬 처리를 더 잘 활용할 수 있습니다.
  • 또한 높은 안정성과 중복성을 더 잘 구현할 수 있습니다.
  • 이러한 문제 중 다수는 자연스럽게 MapReduce 패러다임에 적합합니다.

하지만 사실은 우리가 당신의 작업량을 알 때까지 확실한 말을 할 수 없다는 것입니다.

---

따라서 데이터베이스는 지속적으로 변경되는 크고 복잡한 스키마가있는 상황을위한 것입니다. 간단한 숫자 필드로 가득 찬 하나의 "테이블"만 있습니다. 나는 이렇게 할 것이다 :

레코드 형식을 보유 할 C / C ++ 구조체를 준비합니다.

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

그런 다음 sizeof (StockPrice [N])를 계산합니다. 여기서 N은 레코드 수입니다. (64 비트 시스템에서) 몇 백 기가 여야하고 50 달러 HDD에 맞습니다.

그런 다음 파일을 해당 크기로 자르고 mmap (Linux의 경우 또는 Windows의 경우 CreateFileMapping 사용)을 메모리에 저장합니다.

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

mmaped 포인터를 StockPrice *로 캐스팅하고 배열을 채우는 데이터를 전달합니다. mmap을 닫으면 나중에 다시 mmap 할 수있는 파일에 하나의 큰 이진 배열에 데이터가 있습니다.

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

이제 모든 프로그램에서 다시 읽기 전용으로 mmap 할 수 있으며 데이터를 쉽게 사용할 수 있습니다.

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

// do stuff with stocks;

이제 메모리 내 구조체 배열처럼 처리 할 수 ​​있습니다. "쿼리"에 따라 다양한 종류의 인덱스 데이터 구조를 만들 수 있습니다. 커널은 디스크와의 데이터 교환을 투명하게 처리하므로 엄청나게 빠릅니다.

특정 액세스 패턴 (예 : 연속적인 날짜)이있을 것으로 예상되는 경우 배열을 순서대로 정렬하여 디스크에 순차적으로 도달하도록하는 것이 가장 좋습니다.

---

나는 읽기 요청 만 수행 할 시간의 대부분 (99.9 %)의 1000 주식 [...]의 1 분 데이터 데이터 세트를 가지고 있습니다 .

한 번 저장하고 여러 번 시간 기반 숫자 데이터를 읽는 것은 "시계열"이라는 사용 사례입니다. 다른 일반적인 시계열은 사물 인터넷의 센서 데이터, 서버 모니터링 통계, 애플리케이션 이벤트 등입니다.

이 질문은 2012 년에 제기되었으며 그 이후로 여러 데이터베이스 엔진이 시계열 관리를위한 기능을 특별히 개발해 왔습니다. 오픈 소스이고 Go로 작성되었으며 MIT 라이선스를받은 InfluxDB를 사용하여 훌륭한 결과를 얻었습니다 .

InfluxDB는 시계열 데이터를 저장하고 쿼리하도록 특별히 최적화되었습니다. 시계열을 저장하는 데 매우 유용하다고 종종 선전되는 Cassandra보다 훨씬 더 많습니다.

시계열 최적화에는 특정 장단점이 포함되었습니다. 예를 들면 :

기존 데이터에 대한 업데이트는 거의 발생하지 않으며 논쟁적인 업데이트는 발생하지 않습니다. 시계열 데이터는 주로 업데이트되지 않는 새로운 데이터입니다.

장점 : 업데이트에 대한 액세스를 제한하면 쿼리 및 쓰기 성능이 향상됩니다.

단점 : 업데이트 기능이 크게 제한됨

에서 열린 소싱 벤치 마크 ,

InfluxDB는 27 배 더 큰 쓰기 처리량으로 세 가지 테스트 모두에서 MongoDB를 능가하는 반면, 84 배 더 적은 디스크 공간을 사용하고 쿼리 속도면에서 비교적 동일한 성능을 제공했습니다.

Queries are also very simple. If your rows look like <symbol, timestamp, open, high, low, close, volume>, with InfluxDB you can store just that, then query easily. Say, for the last 10 minutes of data:

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

There are no IDs, no keys, and no joins to make. You can do a lot of interesting aggregations. You don't have to vertically partition the table as with PostgreSQL, or contort your schema into arrays of seconds as with MongoDB. Also, InfluxDB compresses really well, while PostgreSQL won't be able to perform any compression on the type of data you have.

---

Okay, so this is somewhat away from the other answers, but... it feels to me like if you have the data in a file system (one stock per file, perhaps) with a fixed record size, you can get at the data really easily: given a query for a particular stock and time range, you can seek to the right place, fetch all the data you need (you'll know exactly how many bytes), transform the data into the format you need (which could be very quick depending on your storage format) and you're away.

I don't know anything about Amazon storage, but if you don't have anything like direct file access, you could basically have blobs - you'd need to balance large blobs (fewer records, but probably reading more data than you need each time) with small blobs (more records giving more overhead and probably more requests to get at them, but less useless data returned each time).

Next you add caching - I'd suggest giving different servers different stocks to handle for example - and you can pretty much just serve from memory. If you can afford enough memory on enough servers, bypass the "load on demand" part and just load all the files on start-up. That would simplify things, at the cost of slower start-up (which obviously impacts failover, unless you can afford to always have two servers for any particular stock, which would be helpful).

Note that you don't need to store the stock symbol, date or minute for each record - because they're implicit in the file you're loading and the position within the file. You should also consider what accuracy you need for each value, and how to store that efficiently - you've given 6SF in your question, which you could store in 20 bits. Potentially store three 20-bit integers in 64 bits of storage: read it as a long (or whatever your 64-bit integer value will be) and use masking/shifting to get it back to three integers. You'll need to know what scale to use, of course - which you could probably encode in the spare 4 bits, if you can't make it constant.

You haven't said what the other three integer columns are like, but if you could get away with 64 bits for those three as well, you could store a whole record in 16 bytes. That's only ~110GB for the whole database, which isn't really very much...

EDIT: The other thing to consider is that presumably the stock doesn't change over the weekend - or indeed overnight. If the stock market is only open 8 hours per day, 5 days per week, then you only need 40 values per week instead of 168. At that point you could end up with only about 28GB of data in your files... which sounds a lot smaller than you were probably originally thinking. Having that much data in memory is very reasonable.

EDIT: I think I've missed out the explanation of why this approach is a good fit here: you've got a very predictable aspect for a large part of your data - the stock ticker, date and time. By expressing the ticker once (as the filename) and leaving the date/time entirely implicit in the position of the data, you're removing a whole bunch of work. It's a bit like the difference between a String[] and a Map<Integer, String> - knowing that your array index always starts at 0 and goes up in increments of 1 up to the length of the array allows for quick access and more efficient storage.

---

It is my understanding that HDF5 was designed specifically with the time-series storage of stock data as one potential application. Fellow stackers have demonstrated that HDF5 is good for large amounts of data: chromosomes, physics.

---

Here is an attempt to create a Market Data Server on top of the Microsoft SQL Server 2012 database which should be good for OLAP analysis, a free open source project:

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

---

First, there isn't 365 trading days in the year, with holidays 52 weekends (104) = say 250 x the actual hours of day market is opened like someone said, and to use the symbol as the primary key is not a good idea since symbols change, use a k_equity_id (numeric) with a symbol (char) since symbols can be like this A , or GAC-DB-B.TO , then in your data tables of price info, you have, so your estimate of 7.3 billion is vastly over calculated since it's only about 1.7 million rows per symbol for 14 years.

k_equity_id k_date k_minute

and for the EOD table (that will be viewed 1000x over the other data)

k_equity_id k_date

Second, don't store your OHLC by minute data in the same DB table as and EOD table (end of day) , since anyone wanting to look at a pnf, or line chart, over a year period , has zero interest in the by the minute information.

---

Let me recommend that you take a look at apache solr, which I think would be ideal for your particular problem. Basically, you would first index your data (each row being a "document"). Solr is optimized for searching and natively supports range queries on dates. Your nominal query,

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

would translate to something like:

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

Assuming "stock" is the stock name and "date" is a "DateField" created from the "date" and "minute" columns of your input data on indexing. Solr is incredibly flexible and I really can't say enough good things about it. So, for example, if you needed to maintain the fields in the original data, you can probably find a way to dynamically create the "DateField" as part of the query (or filter).

---

You should compare the slow solutions with a simple optimized in memory model. Uncompressed it fits in a 256 GB ram server. A snapshot fits in 32 K and you just index it positionally on datetime and stock. Then you can make specialized snapshots, as open of one often equals closing of the previous.

[edit] Why do you think it makes sense to use a database at all (rdbms or nosql)? This data doesn't change, and it fits in memory. That is not a use case where a dbms can add value.

---

I think any major RDBMS would handle this. At the atomic level, a one table with correct partitioning seems reasonable (partition based on your data usage if fixed - this is ikely to be either symbol or date).

You can also look into building aggregated tables for faster access above the atomic level. For example if your data is at day, but you often get data back at the wekk or even month level, then this can be pre-calculated in an aggregate table. In some databases this can be done though a cached view (various names for different DB solutions - but basically its a view on the atomic data, but once run the view is cached/hardened intoa fixed temp table - that is queried for subsequant matching queries. This can be dropped at interval to free up memory/disk space).

I guess we could help you more with some idea as to the data usage.

---

If you have the hardware, I recommend MySQL Cluster. You get the MySQL/RDBMS interface you are so familiar with, and you get fast and parallel writes. Reads will be slower than regular MySQL due to network latency, but you have the advantage of being able to parallelize queries and reads due to the way MySQL Cluster and the NDB storage engine works.

Make sure that you have enough MySQL Cluster machines and enough memory/RAM for each of those though - MySQL Cluster is a heavily memory-oriented database architecture.

Or Redis, if you don't mind a key-value / NoSQL interface to your reads/writes. Make sure that Redis has enough memory - its super-fast for reads and writes, you can do basic queries with it (non-RDBMS though) but is also an in-memory database.

Like others have said, knowing more about the queries you will be running will help.

---

You will want the data stored in a columnar table / database. Database systems like Vertica and Greenplum are columnar databases, and I believe SQL Server now allows for columnar tables. These are extremely efficient for SELECTing from very large datasets. They are also efficient at importing large datasets.

A free columnar database is MonetDB.

---

If your use case is to simple read rows without aggregation, you can use Aerospike cluster. It's in memory database with support of file system for persistence. It's also SSD optimized.

If your use case needs aggregated data, go for Mongo DB cluster with date range sharding. You can club year vise data in shards.

참고 URL : https://stackoverflow.com/questions/9815234/how-to-store-7-3-billion-rows-of-market-data-optimized-to-be-read