delta_loading: Import and export a data-chunk with history from historical data

Description

delta_export() exports data from tables created with update_snapshot() in chunks to allow for faster migration of data between sources.

delta_load() import deltas created by delta_export() to rebuild a historical table.

Usage

delta_export(conn, db_table, timestamp_from, timestamp_until = NULL)
delta_load(conn, db_table, delta, logger = NULL)

Value

delta_export() returns a lazy-query containing the data (and history) in the source to be used in conjunction with delta_load().

This table is a temporary table that may need cleaning up.

delta_load() returns NULL (called for side effects).

Arguments

conn

(DBIConnection(1))
Connection object.

db_table

(id-like object(1))
A table specification (coercible by id()).

timestamp_from

(POSIXct(1), Date(1), or character(1))
The timestamp describing the start of the export (including).

timestamp_until

(POSIXct(1), Date(1), or character(1))
The timestamp describing the end of the export (not-including).

If NULL (default), all history after timestamp_from is exported.

delta

.data (data.frame(1), tibble(1), data.table(1), or tbl_dbi(1))
A "delta" exported from delta_export() to load.

A list of "deltas" can also be supplied.

logger

(Logger(1))
A configured logging object. If none is given, one is initialized with default arguments.

Details

This pair of functions is designed to facilitate easy migration or incremental backups of a historical table (created by update_snapshot()).

To construct the basis of incremental backups, delta_export() can be called with only timestamp_from at the desired frequency (weekly etc.)

To migrate a historical table in chunks, delta_export() can be called with timestamp_until to constrain the size of the delta.

In either case, the table can then be re-constructed by "replaying" the deltas with delta_load(). The order the deltas are replayed does not matter, but all have to be replayed to achieve the same state as the source table.

Examples

Run this code

if (FALSE) { # requireNamespace("RSQLite", quietly = TRUE)
  conn <- get_connection()

  data <- dplyr::copy_to(conn, mtcars)

  # Copy the first 3 records
  update_snapshot(
    head(data, 3),
    conn = conn,
    db_table = "test.mtcars",
    timestamp = "2020-01-01"
  )

  # Create a delta with the current state
  delta <- delta_export(
    conn,
    db_table = "test.mtcars",
    timestamp_from = "2020-01-01"
  )

  # Update with the first 5 records
  update_snapshot(
    head(data, 5),
    conn = conn,
    db_table = "test.mtcars",
    timestamp = "2021-01-01"
  )

  dplyr::tbl(conn, "test.mtcars")

  # Create a backup using the delta
  delta_load(
    conn = conn,
    db_table = "test.mtcars_backup",
    delta = delta
  )

  dplyr::tbl(conn, "test.mtcars_backup")

  close_connection(conn)
}