We will be storing the candlestick data from the Binance API in a local Redis database. The most common query to be performed will be loading up candle data within a date range, so we'll need the Redis sorted set (ZADD, ZRANGEBYSCORE) to store the candle timestamps of the start time. The actual price data for the candle will be stored as a Redis hash map (HMSET, HMGET) with a key that incorporates the timestamp. Inserting and retrieving data will be a two step process.
I don't know if this is the best way to store the data in Redis, but it seems like a natural first attempt. Let me know if you have a better schema that would load faster. Since there are 525,000 minutes per year, it would take about 500,000 queries to load up a year's worth of data. I guess we could use Redis pipelining though. Anyway, I'm open to hearing your ideas. Maybe you can open an issue in the project if you would like to discuss.
- ZADD the start time in milliseconds
- HSET the key-value pairs.
- ZRANGEBYSCORE <start_time_ms> <stop_time_ms>
- For each timestamp, HMGET <key_prefix>: <field_0> [field_1 [field_2] ... ]
<key_prefix> looks like candles:::. See the example below.
The Redis hash map fields roughly correspond to the Binance API:
| field | data type |
|---|---|
| open_time | Unixtime (ms) |
| open | float |
| high | float |
| low | float |
| close | float |
| volume | float |
| quote_volume | float |
| num_trades | int |
| base_taker_buy_volume | float |
| quote_taker_buy_volume | float |
HSET candles:<exchange>:<pair>:<period>:<unix_time_ms> time <unixtime_ms> open <float> high <float> low <float> close <float> volume <float> quote_volume <float> num_trades <int> base_taker_buy_volume <float> quote_taker_buy_volume <float>
HSET candles:binance:btc/usdt:1m:1650499200000 time 1650499200000 open 41273.42 high 41299.88 low 41101.5 close 41.200.01 volume 23814.2 quote_volume 10013456.342 num_trades 232 base_taker_buy_volume 12584.3 quote_taker_buy_volume 513487239.43
To a range of dates on available unixtime_ms, insert unix time of candle as the score. When exchanges go down for maintenace there will be gaps more than the length of the period in ms.
ZADD candles:<exchange>:<pair>:<period> <unix_timestamp_ms>
ZADD candles:binance:btc/usdt:1m 1650499200000 1
ZRANGEBYSCORE candles:<exchange>:<pair>:<period> <unixtime_ms_start> <unixtime_ms_stop> WITHSCORES
ZRANGEBYSCORE candles:binance:btc/usdt:1m 1650499200000 1660499200000 WITHSCORES
HMGET candle:binance:btc/usdt:1m open high low close volume
HMGET candle:binance:btc/usdt:1m open high low close volume