Tidy Finance migrates from SQLite to Parquet

We recently updated our data handling procedures from SQLite to Parquet files. This change was made to improve performance and cross-programming language consistency. However, we understand that some users may still need access to the legacy SQLite database. Therefore, the following blog post contains the code snippets and (in a condensed form), the previous explanations on the use of SQLite.

There are many ways to set up and organize a database, depending on the use case. For our purpose we used an SQLite-database, which is the C-language library that implements a small, fast, self-contained, high-reliability, full-featured SQL database engine. Note that SQL (Structured Query Language) is a standard language for accessing and manipulating databases.

There are two packages that make working with SQLite in R very simple: RSQLite (Müller et al. 2022) embeds the SQLite database engine in R, and dbplyr (Wickham et al. 2022) is the database back-end for dplyr. These packages allow to set up a database to remotely store tables and use these remote database tables as if they are in-memory data frames by automatically converting dplyr into SQL. Check out the RSQLite and dbplyr vignettes for more information.

library(RSQLite)
library(dbplyr)

An SQLite database is easily created - the code below is really all there is. You do not need any external software. Note that we use the extended_types = TRUE option to enable date types when storing and fetching data. Otherwise, date columns are stored and retrieved as integers. We will use the file tidy_finance_r.sqlite, located in the data subfolder, to retrieve data for all subsequent chapters. The initial part of the code ensures that the directory is created if it does not already exist.

if (!dir.exists("data")) {
  dir.create("data")
}

tidy_finance <- dbConnect(
  SQLite(),
  "data/tidy_finance_r.sqlite",
  extended_types = TRUE
)

For our example, we rely on some data retrieved by the tidyfinance package:

library(tidyfinance)

factors_ff3_monthly <- download_data(
  type = "factors_ff_3_monthly",
  start_date = start_date,
  end_date = end_date
)

Next, we create a remote table with the monthly Fama-French factor data. We do so with the function dbWriteTable(), which copies the data to our SQLite-database.

dbWriteTable(
  tidy_finance,
  "factors_ff3_monthly",
  value = factors_ff3_monthly,
  overwrite = TRUE
)

We can use the remote table as an in-memory data frame by building a connection via tbl().

factors_ff3_monthly_db <- tbl(tidy_finance, "factors_ff3_monthly")

All dplyr calls are evaluated lazily, i.e., the data is not in our R session’s memory, and the database does most of the work. You can see that by noticing that the output below does not show the number of rows. In fact, the following code chunk only fetches the top 10 rows from the database for printing.

factors_ff3_monthly_db |>
  select(date, rf)

If we want to have the whole table in memory, we need to collect() it. You will see that we regularly load the data into the memory in the next chapters.

factors_ff3_monthly_db |>
  select(date, rf) |>
  collect()

The last couple of code chunks is really all there is to organizing a simple database! You can also share the SQLite database across devices and programming languages.

From now on, all you need to do to access data that is stored in the database is to follow three steps: (i) Establish the connection to the SQLite database, (ii) call the table you want to extract, and (iii) collect the data. For your convenience, the following steps show all you need in a compact fashion.

library(tidyverse)
library(RSQLite)

tidy_finance <- dbConnect(
  SQLite(),
  "data/tidy_finance_r.sqlite",
  extended_types = TRUE
)

factors_ff3_monthly_db <- tbl(tidy_finance, "factors_ff3_monthly_db")
factors_ff3_monthly_db <- factors_ff3_monthly_db |> collect()

In Python, the workflow is similar:

import tidyfinance as tf
import pandas as pd
import sqlite3

factors_ff3_monthly = tf.download_data(
    domain="famafrench",
    dataset="F-F_Research_Data_Factors",
    start_date=start_date,
    end_date=end_date,
)

(
    factors_ff3_monthly.to_sql(
        name="factors_ff3_monthly",
        con=tidy_finance,
        if_exists="replace",
        index=False,
    )
)

tidy_finance = sqlite3.connect(database="data/tidy_finance_python.sqlite")

factors_q_monthly = pd.read_sql_query(
    sql="SELECT * FROM factors_q_monthly",
    con=tidy_finance,
    parse_dates={"date"},
)

Managing SQLite Databases

Finally, at the end of our data chapter, we revisit the SQLite database itself. When you drop database objects such as tables or delete data from tables, the database file size remains unchanged because SQLite just marks the deleted objects as free and reserves their space for future uses. As a result, the database file always grows in size.

To optimize the database file, you can run the VACUUM command in the database, which rebuilds the database and frees up unused space. You can execute the command in the database using the dbSendQuery() function.

res <- dbSendQuery(tidy_finance, "VACUUM")
res

The VACUUM command actually performs a couple of additional cleaning steps, which you can read about in this tutorial.

Python offers similar functionalities

tidy_finance.execute("VACUUM")

We store the result of the above query in res because the database keeps the result set open. To close open results and avoid warnings going forward, we can use dbClearResult().

dbClearResult(res)

Apart from cleaning up, you might be interested in listing all the tables that are currently in your database. You can do this via the dbListTables() function.

dbListTables(tidy_finance)

This function comes in handy if you are unsure about the correct naming of the tables in your database.

References

Müller, Kirill, Hadley Wickham, David A. James, and Seth Falcon. 2022. RSQLite: SQLite interface for R. https://CRAN.R-project.org/package=RSQLite.

Wickham, Hadley, Maximilian Girlich, and Edgar Ruiz. 2022. dbplyr: A ’dplyr’ back end for databases. https://CRAN.R-project.org/package=dbplyr.