update_snapshot {SCDB}

Update a historical table

Description

update_snapshots makes it easy to create and update a historical data table on a remote (SQL) server. The function takes the data (.data) as it looks on a given point in time (timestamp) and then updates (or creates) an remote table identified by db_table. This update only stores the changes between the new data (.data) and the data currently stored on the remote. This way, the data can be reconstructed as it looked at any point in time while taking as little space as possible.

See vignette("basic-principles") for further introduction to the function.

Usage

update_snapshot(
  .data,
  conn,
  db_table,
  timestamp,
  filters = NULL,
  message = NULL,
  tic = Sys.time(),
  logger = NULL,
  enforce_chronological_order = TRUE
)

Arguments

.data

(data.frame(1), tibble(1), data.table(1), or tbl_dbi(1))
Data object.

conn

(DBIConnection(1))
Connection object.

db_table

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

timestamp

(POSIXct(1), Date(1), or character(1))
The timestamp describing the data being processed (not the current time).

filters

(data.frame(1), tibble(1), data.table(1), or tbl_dbi(1))
A object subset data by. If filters is NULL, no filtering occurs. Otherwise, an inner_join() is performed using all columns of the filter object.

message

(character(1))
A message to add to the log-file (useful for supplying metadata to the log).

tic

(POSIXct(1))
A timestamp when computation began. If not supplied, it will be created at call-time (used to more accurately convey the runtime of the update process).

logger

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

enforce_chronological_order

(logical(1))
Are updates allowed if they are chronologically earlier than latest update?

Value

No return value, called for side effects.

See Also

filter_keys

Examples

  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 = Sys.time()
  )

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

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

  close_connection(conn)