Migrate from SQLite database to Parquet files

christophscheuch commented 2 months ago

Goal is to replace the SQLite database with parquet files that are shared between R and Python scripts, so that we have interoperability of data and remove redundancy.

To achieve this goal we need to do the following:

Organize tables in subdirectories (which mirror logic in tidyfinance package), e.g.: data/ ├── wrds/ │ ├── crsp_monthly.parquet │ ├── compustat.parquet ├── factors/ │ ├── ff5_monthly.parquet │ ├── q_daily.parquet
Use schemas to keep column types in sync across R and Python (in particular dates and datetimes).
For R decide between arrow and nanopartquet
For Python decide between pyarrow and fastparquet

DayleLee commented 1 month ago

Can we still view the SQLite version after this migrate? It's very convenient to use SQLite database with python, and I can use DBeaver link to SQLite to see those data. Strongly recommend keep the SQLite version! Thanks!

christophscheuch commented 1 month ago

@DayleLee we have not decided yet how to approach this topic and will take your feedback into consideration.

christophscheuch commented 3 weeks ago

I just leave it here for future discussions: we might alternatively use duckdb since it has reached version 1.0.0: https://duckdb.org/why_duckdb. It also works nicely with arrow / parquet: https://r4ds.hadley.nz/arrow#using-duckdb-with-arrow. DuckDB can also be inspected with DBeaver.

tidy-finance / website

Migrate from SQLite database to Parquet files #122