SQLite: Handling Large Structured Data

Overview

Storing your data in SQLite, format allows you to get benefits of database, and at the same time simple and storage of data in a file on a disk.

From developer's website: "SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day. The SQLite file format is stable, cross-platform, and backwards compatible and the developers pledge to keep it that way through at least the year 2050."

Some use-cases

• • You think you need MySQL, PostreSQL, etc for your ML project. Usually you don't

  • You have to deal with hundreds of GB of table-stuctured data (or larger) and your script (for whatever reason) can't be made parallel.

  • You would request a lot of RAM and work with data slowly.....  This would be a waste of RAM.

  • It is better in this case to request smaller amount of RAM and read data (efficiently) from disk - for example using SQLite

Benefits:

• • You are not limited by RAM any longer

  • comparing to other file formats SQLite is very good in selecting certain lines (especially if you use indexing)

  • you can use familiar dplyr syntax or execute SQL queries directly

    • dplyr is an interface for working with data in a database, not for modifying remote tables.

    • DBI package allows to both read and modify tables

  • SQLite is actually faster for common data analysis tasks than other popular databases.

  • You can have multiple threads accessing an SQLite database simultaneously (for read operations. Writing is more tricky)

  • Merging/Joining datasets on disk

Major benefits of SQLite comparing to MySQL (PostgreSQL, etc)

• • You control your own data (sqlite file). You don't depend on any service like MySQL

  • You can copy a file to your own laptop and work with it

  • Again, SQLite is faster!

Limits

• • SQLite has some limitations in terms of concurrency, which usually don't apply for typical ML/AI jobs.

  • Read more here.

Command line (CLI) example

Create environment

Then follow this SQLite example.

Now Close session (Ctrl-D).

Reopen session to check if changes are saved

R example

Install

Here we use conda, as a great way to keep everything isolated and reproducible.

Note: conda will install pre-compiled packages. Which is good (faster) and bad (not fully optimized for a specific hardware)

Alternative: install packages to a local directory or use renv as described here

Note:

window functions (row_number in particular) require newer version of rsqlite

(https://github.com/r-dbi/RSQLite/issues/268)

Save list of installed packages for reproducibility

Use

Many examples can be found here:

• • dplyr syntax https://db.rstudio.com/dplyr/

  • SQL syntax https://db.rstudio.com/databases/sqlite/

Copy data frame to database (dplyr)

Or copy data to database using DBI

Connect to a specific table

Subset

Save as feather

Alternative: read csv file to SQLite directly

If you already have a large csv file on disk, and you don't want to read it to RAM, you can read it to SQLite file directly

UI for SQLite - SQLiteStudio

Once you have SQLite file, you can easily transfer it to your own laptop and explore it using SQLiteStudio, if you like to use UI instead of terminal