Package {datapond}

datapond: Query Curated DuckDB Databases Built from Public Data

Description

Connects to the datapond registry of curated DuckDB databases built from public government and research data (immigration courts, campaign finance, clinical trials, Medicare, and more). Databases are attached remotely over HTTP so only the byte ranges a query touches are transferred, or downloaded once for local use. Returns standard 'DBI' connections that work with 'dbplyr'.

Author(s)

Maintainer: Ian Nason ign.nason@gmail.com

Authors:

• Ian Nason ign.nason@gmail.com

See Also

Useful links:

• https://github.com/datapond-db/datapond-r

• https://datapond-db.github.io/website/

• Report bugs at https://github.com/datapond-db/datapond-r/issues

Connect to one or more datapond databases

Description

Opens an in-memory DuckDB connection and attaches each requested database read-only, either remotely from its attach_url (the default; DuckDB's httpfs extension fetches only the byte ranges a query touches, so there is no full download) or from a local copy made with dp_download().

Usage

dp_connect(id, local = FALSE, quiet = FALSE)

Arguments

Details

With a single id the database is made the default catalog (USE), so tables can be referenced unqualified. With several ids, qualify tables with the database id, double-quoting ids that contain a hyphen: ⁠"cms-medicare".physician_summary⁠.

Value

A duckdb_connection (a DBIConnection). Close it with dp_disconnect().

Examples

## Not run: 
con <- dp_connect("eoir")
DBI::dbGetQuery(con, "SELECT * FROM proceedings LIMIT 5")
dp_disconnect(con)

con <- dp_connect(c("cms-medicare", "openpayments"))
DBI::dbGetQuery(con, 'SELECT * FROM "cms-medicare".physician_summary LIMIT 5')
dp_disconnect(con)

## End(Not run)

Local directories used by datapond

Description

The registry is cached under tools::R_user_dir("datapond", "cache") and downloaded databases live under tools::R_user_dir("datapond", "data"). Override either with options(datapond.cache_dir = ...) / options(datapond.data_dir = ...). Setting options(datapond.data_dir = "~/.datapond") shares downloads with the Python package, which names local files the same way (⁠<id>.duckdb⁠).

Usage

dp_data_dir()

dp_cache_dir()

Value

A path (character scalar).

Examples

dp_data_dir()

Explore a database's data dictionary

Description

Every datapond database ships ⁠_metadata⁠ (one row per table) and ⁠_columns⁠ (types, null rates, example values, join hints). dp_describe() reads them and falls back to information_schema when they are absent.

Usage

dp_describe(x, table = NULL, search = NULL)

Arguments

Value

A data frame (a tibble when the tibble package is installed).

Examples

## Not run: 
dp_describe("eoir")
dp_describe("eoir", table = "proceedings")
dp_describe("eoir", search = "judge")

## End(Not run)

Close a datapond connection

Description

Close a datapond connection

Usage

dp_disconnect(con)

Arguments

Value

TRUE, invisibly.

Download a database for local use

Description

Downloads the full .duckdb file so later queries run at disk speed. Files are saved as ⁠<id>.duckdb⁠ under dp_data_dir() (or path), which is where dp_connect(id, local = TRUE) looks for them.

Usage

dp_download(id, path = NULL, quiet = FALSE, resume = TRUE)

Arguments

Value

The local path, invisibly.

Examples

## Not run: 
dp_download("dol-visas")
con <- dp_connect("dol-visas", local = TRUE)

## End(Not run)

Information about one database

Description

Information about one database

Usage

dp_info(id)

Arguments

Value

The registry entry (a list of class datapond_db), printed in a readable layout.

Examples

## Not run: 
dp_info("eoir")

## End(Not run)

List available databases

Description

List available databases

Usage

dp_list()

dp_databases()

Value

dp_list() returns a character vector of database ids; dp_databases() returns one row per database with its registry fields.

Examples

## Not run: 
dp_list()
dp_databases()[, c("id", "rows", "size_gb")]

## End(Not run)

Path of a locally downloaded database

Description

Path of a locally downloaded database

Usage

dp_local_path(id, must_exist = FALSE)

Arguments

Value

A path (character scalar).

Examples

dp_local_path("eoir")

The datapond registry

Description

Fetches registry.json from GitHub (cached for an hour under dp_cache_dir(); a stale cache is used if the network is unavailable).

Usage

dp_registry(refresh = FALSE)

Arguments

Value

The parsed registry: a list with a databases element, one list per database.

Examples

## Not run: 
reg <- dp_registry()
length(reg$databases)

## End(Not run)

Update a local database if the registry has a newer version

Description

Compares the registry's updated date with the local file's modification time and re-downloads when the registry is newer.

Usage

dp_update(id, quiet = FALSE)

Arguments

Value

The local path, invisibly.