rsmb7z
commented
1 year ago Hi @limx0 This is indeed long awaited. While we need rich features for data analysis and easy read/write, the main chocking point is Historical data - mainly not because of data size but because of number of files involved. Problem with the DuckDB and DataFusion for this exists although we get SQL queries benefit, although there is some discussion on the fix for it.
My suggestion will be as follows.
- Historical Data (Reading), we have fixed data types (Ticks and Bars). These are huge in data and the core requirement for Backtest to function. For this part instead of waiting and relying on third-party tools, what we have already and performs well, reading through Pandas we simply have to add the partition path in this case
instrument_idand Pandas can directly read from there. The typical filtering required here is the bar_type, start_time, and end_time. I have uploaded the modified Catalog https://github.com/rsmb7z/nautilus_trader/tree/catalog_pandas which works like this. It is not the clean code but does the job and can give idea. - Historical Data (Writing), pandas can directly handle partitioning without any issue. So we simply convert the Nautilus objects to dataframe and let Pandas do the rest - with the simpler helper function to add the Partition columns i.e. instrument_id.
- Any other data types can be read/written like above as well however filtering may be bit complex for user as compared to queries, so on top of it we leverage here the DuckDB or DataFusion. As we introduce this, the user can read/write any data including Bars/Ticks using queries but the default method to read/load historical data can be as in step 1.
Some feedback and example usage for the modified catalog using Pandas as follows. It has some extra helper functions which may be added as feature in new catalog.
bar_type = BarType.from_str('EUR/USD.IDEALPRO-1-MINUTE-MID-EXTERNAL')
start_time = pd.Timestamp("2005-01-01")
end_time = pd.Timestamp("2022-12-31")
as_nautilus = False
# Load Bars for given BarType
perf_start = time.perf_counter()
bars = catalog.bars(
instrument_ids=[bar_type.instrument_id.value],
filter_expr=("bar_type", "=", str(bar_type)),
as_nautilus=as_nautilus,
start=start_time,
end=end_time,
)
print(f"{as_nautilus=}, loaded {len(bars)} Bars, in {time.perf_counter() - perf_start:0.4f} seconds")
# RESULT: as_nautilus=False, loaded 6564442 Bars, in 8.4057 seconds
# RESULT: as_nautilus=True, loaded 6564442 Bars, in 89.0130 seconds
# Loads Bars parquet as QuoteTicks (loads BID and ASK bars and then convert to ticks)
perf_start = time.perf_counter()
ticks_data = catalog.bars_to_ticks(
instrument_ids=[bar_type.instrument_id.value],
start=start_time,
end=end_time,
)
print(f"as_nautilus=True, loaded {len(ticks_data)} Ticks, in {time.perf_counter() - perf_start:0.4f} seconds")
# RESULT: as_nautilus=True, loaded 26252848 Ticks, in 117.1738 seconds
# Load Bars to given BarType from smaller timeframe, for directly loading to Backtest engine or other analysis
perf_start = time.perf_counter()
bar_type = BarType.from_str('EUR/USD.IDEALPRO-5-MINUTE-MID-EXTERNAL')
bars = catalog.bars_resample(
instrument_ids=[bar_type.instrument_id.value],
price_type=bar_type.spec.price_type,
bar_delta=bar_type.spec.timedelta,
as_nautilus=as_nautilus,
start=start_time,
end=end_time,
raise_on_empty=False,
source_bar_delta=pd.Timedelta(minutes=1)
)
print(f"{as_nautilus=}, loaded {len(bars)} Bars, in {time.perf_counter() - perf_start:0.4f} seconds")
# RESULT: as_nautilus=False, loaded 1312949 Bars, in 9.0769 seconds
# RESULT: as_nautilus=True, loaded 1312949 Bars, in 28.1769 seconds
limx0
Harnas
Troubladore
TianQingX
bramo0
OnlyC
Background
I'd like to propose a refactor of the data catalog, focusing on a couple of goals:
I'm opening this issue to engage our community and encourage users to share their opinions or ideas on the current implementation of the Data Catalog. Fundamentally, the goal of the catalog is to allow users to store their data in a simple format that can be queried in a performant way to load data into the backtest engine for backtesting. It should be relatively easy for users to get their data into the catalog. A secondary goal is storing data for research purposes.
I should also note there is on-going work to "rustify" the catalog, but this is mostly related to parsing/deserializing the data retreived from the catalog and will continue / won't be affected by this issue.
Current State
Our (mostly my) initial implementation of the catalog has a couple of issues:
All of this has made the maintenance burden quite high, and hacking on the catalog quite tough. We should consider if this is a core competency of the platform or if we can leverage other projects to achieve our desired outcome.
Proposal
The catalog has two main functions:
This seems like a common use case in the broader python community currently, and I am quite confident we can achieve our stated goals above by leveraging some existing packages in the python/rust ecosystem. I'll be splitting the writing/reading of the parquet files as existing packages seem to split in a similar way.
Writing data
Users need to be able to take raw data (tick or bar data typically) from an external source (recorded / purchased / downloaded) and write it into one or many parquet files somewhere (local or remote storage) in the correct format to ensure they can be read again in a performant way for backtesting or research.
At a minimum, I believe we need the following features:
We currently leverage pyarrow datasets for writing, which offers partitioning and writing to remote storage, but leaves some of the management of the catalog to us.
I am considering using something like plateau (built on top of pyarrow) which offers a few more features for working with datasets and builds on real world expertise working with datasets similar to ours. There are a couple of appealing features we don't currently have (or have attempting to implement but not very well) (taken from https://plateau.readthedocs.io/en/latest/spec/format_specification.html):
Plateau currently only works with DataFrames, so we would need to do some work to integrate with Nautilus objects, but this may be worth doing to get the above "for free".
Reading Data
For reading data, we are looking for the most performant way to read from a series of parquet files from a storage backend. Because streaming is a strong use case for backtesting, we ideally need to be able to also stream data from our storage, deserialize it and pass it into the engine. Currently we implement this reading/querying in the data catalog, but there are several projects of interest doing similar things that we could leverage. We need the following features:
The projects that come to mind are:
Each of these libraries are trying to achieve similar-ish goals to us, and could potentially be leveraged on the querying side. Ideally we would maintain a simple API over the top of one of these libraries, benefiting from their continued development and improvements to their querying framework. The idea being, we would ship off a query off to one of the libraries, which would then handle querying/filtering and return some sort of iterator of raw arrow bytes that we could convert (via rust) into nautilus objects.
Other considerations
I'm keen to hear any thoughts or ideas on the above, or anything I may have missed or misunderstood.