SelfSync

Explore your database with R

The duckdb R package implements the standard DBI interface. Run SQL and get data frames back, or use dplyr and let dbplyr translate your pipelines into SQL executed by DuckDB.

Runs on
macOS, Windows, Linux
Price
Free, open source
Website
duckdb.org

Get started

  1. Install the packages

    From R or RStudio.

    install.packages(c("duckdb", "dplyr", "dbplyr", "ggplot2"))

  2. Open the database

    read_only = TRUE guarantees nothing gets modified.

    library(duckdb)

con <- dbConnect(duckdb(), dbdir = "selfsync-db.duckdb", read_only = TRUE)
dbGetQuery(con, "FROM z_fit_session_mesgs LIMIT 5")

  3. Use dplyr and plot

    dplyr verbs run inside DuckDB, only the result is collected into R. Here, the kilometres you ran each year.

    library(dplyr)
library(ggplot2)

tbl(con, "z_fit_session_mesgs") |>
  filter(z_sport == "running") |>
  group_by(year = year(z_start_time)) |>
  summarise(km = sum(z_total_distance, na.rm = TRUE) / 1000) |>
  collect() |>
  ggplot(aes(year, km)) +
  geom_col()

Good to know

  • Close the connection with dbDisconnect(con, shutdown = TRUE) when you are done.
  • Positions are stored in semicircles: multiply by 180 / 2^31 to get degrees before drawing a map.

Other tools

Wondering what a table or a column holds? Browse the FIT file reference.