Experimental lifecycle

These functions provide a framework for updating data in existing tables. Unlike compute(), copy_to() or copy_dm_to(), no new tables are created on the database. All operations expect that both existing and new data are presented in two compatible dm objects on the same data source.

The functions make sure that the tables in the target dm are processed in topological order so that parent (dimension) tables receive insertions before child (fact) tables.

These operations, in contrast to all other operations, may lead to irreversible changes to the underlying database. Therefore, in-place operation must be requested explicitly with in_place = TRUE. By default, an informative message is given.

dm_rows_insert() adds new records via rows_insert(). The primary keys must differ from existing records. This must be ensured by the caller and might be checked by the underlying database. Use in_place = FALSE and apply dm_examine_constraints() to check beforehand.

dm_rows_update() updates existing records via rows_update(). Primary keys must match for all records to be updated.

dm_rows_patch() updates missing values in existing records via rows_patch(). Primary keys must match for all records to be patched.

dm_rows_upsert() updates existing records and adds new records, based on the primary key, via rows_upsert().

dm_rows_delete() removes matching records via rows_delete(), based on the primary key. The order in which the tables are processed is reversed.

dm_rows_truncate() removes all records via rows_truncate(), only for tables in dm. The order in which the tables are processed is reversed.

dm_rows_insert(x, y, ..., in_place = NULL)

dm_rows_update(x, y, ..., in_place = NULL)

dm_rows_patch(x, y, ..., in_place = NULL)

dm_rows_upsert(x, y, ..., in_place = NULL)

dm_rows_delete(x, y, ..., in_place = NULL)

dm_rows_truncate(x, y, ..., in_place = NULL)

Arguments

x

Target dm object.

y

dm object with new data.

...

Must be empty.

in_place

Should x be modified in place? This argument is only relevant for mutable backends (e.g. databases, data.tables).

When TRUE, a modified version of x is returned invisibly; when FALSE, a new object representing the resulting changes is returned.

Value

A dm object of the same dm_ptype() as x. If in_place = TRUE, the underlying data is updated as a side effect, and x is returned, invisibly.

Examples

# Establish database connection: sqlite <- DBI::dbConnect(RSQLite::SQLite()) # Entire dataset with all dimension tables populated # with flights and weather data truncated: flights_init <- dm_nycflights13() %>% dm_zoom_to(flights) %>% filter(FALSE) %>% dm_update_zoomed() %>% dm_zoom_to(weather) %>% filter(FALSE) %>% dm_update_zoomed() # Target database: flights_sqlite <- copy_dm_to(sqlite, flights_init, temporary = FALSE) print(dm_nrow(flights_sqlite))
#> airlines airports flights planes weather #> 16 1458 0 3322 0
# First update: flights_jan <- dm_nycflights13() %>% dm_select_tbl(flights, weather) %>% dm_zoom_to(flights) %>% filter(month == 1) %>% dm_update_zoomed() %>% dm_zoom_to(weather) %>% filter(month == 1) %>% dm_update_zoomed() print(dm_nrow(flights_jan))
#> flights weather #> 932 72
# Copy to temporary tables on the target database: flights_jan_sqlite <- copy_dm_to(sqlite, flights_jan) # Dry run by default: dm_rows_insert(flights_sqlite, flights_jan_sqlite)
#> Not persisting, use `in_place = FALSE` to turn off this message.
#> ── Table source ──────────────────────────────────────────────────────────────── #> src: sqlite 3.30.1 [] #> ── Metadata ──────────────────────────────────────────────────────────────────── #> Tables: `airlines`, `airports`, `flights`, `planes`, `weather` #> Columns: 53 #> Primary keys: 3 #> Foreign keys: 3
print(dm_nrow(flights_sqlite))
#> airlines airports flights planes weather #> 16 1458 0 3322 0
# Explicitly request persistence: dm_rows_insert(flights_sqlite, flights_jan_sqlite, in_place = TRUE) print(dm_nrow(flights_sqlite))
#> airlines airports flights planes weather #> 16 1458 932 3322 72
# Second update: flights_feb <- dm_nycflights13() %>% dm_select_tbl(flights, weather) %>% dm_zoom_to(flights) %>% filter(month == 2) %>% dm_update_zoomed() %>% dm_zoom_to(weather) %>% filter(month == 2) %>% dm_update_zoomed() # Copy to temporary tables on the target database: flights_feb_sqlite <- copy_dm_to(sqlite, flights_feb) # Explicit dry run: flights_new <- dm_rows_insert( flights_sqlite, flights_feb_sqlite, in_place = FALSE ) print(dm_nrow(flights_new))
#> airlines airports flights planes weather #> 16 1458 1761 3322 144
print(dm_nrow(flights_sqlite))
#> airlines airports flights planes weather #> 16 1458 932 3322 72
# Check for consistency before applying: flights_new %>% dm_examine_constraints()
#> ! Unsatisfied constraints:
#> Table `flights`: foreign key tailnum into table `planes`: 273 entries (15.5%) of `flights$tailnum` not in `planes$tailnum`: N725MQ (6), N537MQ (5), N722MQ (5), N730MQ (5), N736MQ (5), …
# Apply: dm_rows_insert(flights_sqlite, flights_feb_sqlite, in_place = TRUE) print(dm_nrow(flights_sqlite))
#> airlines airports flights planes weather #> 16 1458 1761 3322 144
DBI::dbDisconnect(sqlite)