SQLite Driver for Intake Data Catalogs

This package provides a (very) thin wrapper around the more general intake-sql driver, which can be used to generate Intake data catalogs from SQL databases.

The intake-sql driver takes an SQL Alchemy database URL and uses it to connect to and extract data from the database. This works with just fine with SQLite databases, but only when the database file is stored locally and can be referenced with a simple path.

For example this path:

/home/zane/code/catalyst/pudl-work/sqlite/pudl.sqlite

would correspond to this SQL Alchemy database URL:

sqlite:///home/zane/code/catalyst/pudl-work/sqlite/pudl.sqlite

But you can’t access a remote SQLite DB this way.

Why access a remote SQLite DB?

• SQLite databases are great standalone, standardized containers for relational data, that can be accessed using a huge variety of tools on almost any computer platform. They are even accepted as an archival format by the US Library of Congress!

• Data evolves over time, and it’s often useful to have easy access to several different versions of it, and to know exactly which version you’re working with.

• Cloud object storage is extremely cheap and convenient, and makes it easy to publish and store historical file-based data releases.

• Managing your own bespoke local filesystem hierarchy filled with data – and coordinating with colleagues so that everyone is using the same filesystem organizational scheme – is a pain.

• Intake catalogs can provide easy access to metadata and let you manage data versions just like software versions. Installing a new version of the data catalog points you at the new version of the data.

• The overhead and cost associated with setting up and maintaining a database that uses a client-server model is relatively large compared to distributing a few files that change infrequently, are essentially read-only resources, and only take up a few gigabytes of space.

How does it work?

Rather than using an SQL Alchemy database URL to reference the SQLite DB, this intake driver takes a local path or a remote URL, like:

• ../pudl-work/sqlite/pudl.sqlite

• https://global-power-plants.datasettes.com/global-power-plants.db

• s3://cloudy-mc-cloudface-databucket/v1.2.3/mydata.db

For local paths, it resolves the path and prepends sqlite:// before handing it off to intake-sql to do all the hard work.

For remote URLs it uses fsspec to cache a local copy of the database, and then gives intake-sql a database URL that points to the cached copy.

import intake_sqlite

gpp_cat = intake_sqlite.SQLiteCatalog(
    urlpath="https://global-power-plants.datasettes.com/global-power-plants.db",
    storage_options={"simplecache": {"cache_storage": "/home/zane/.cache/intake"}},
)

list(gpp_cat)

# ['global-power-plants',
#  'global-power-plants_fts',
#  'global-power-plants_fts_config',
#  'global-power-plants_fts_data',
#  'global-power-plants_fts_docsize',
#  'global-power-plants_fts_idx']

About Catalyst Cooperative

Catalyst Cooperative is a small group of data wranglers and policy wonks organized as a worker-owned cooperative consultancy. Our goal is a more just, livable, and sustainable world. We integrate public data and perform custom analyses to inform public policy (Hire us!). Our focus is primarily on mitigating climate change and improving electric utility regulation in the United States.