Table : upsert_rows
Table_UpsertRows.RdThis method allows you to perform an upsert (Update and Insert) for row(s).
This means that you may update a row with only the data that you want to change.
When supplied with a row that does not match the given primary_keys a new
row will be inserted.
Using the primary_keys argument you may specify which columns to use to
determine if a row already exists. If a row exists with the same values in the
columns specified in this list the row will be updated. If a row does not exist
it will be inserted.
Limitations:
- The request to update, and the request to insert data does not occur in a
single transaction. This means that the update of data may succeed, but the
insert of data may fail. Additionally, as noted in the limitation below, if
data is chunked up into multiple requests you may find that a portion of
your data is updated, but another portion is not.
- The number of rows that may be upserted in a single call should be
kept to a minimum (< 50,000). There is significant overhead in the request
to Synapse for each row that is upserted. If you are upserting a large
number of rows a better approach may be to query for the data you want
to update, update the data, then use the synStoreRows() function to
update the data in Synapse. Any rows you want to insert may be added
to the data frame that is passed to the synStoreRows() function.
- When upserting many rows the requests to Synapse will be chunked into smaller
requests. The limit is 2MB per request. This chunking will happen
automatically and should not be a concern for most users. If you are
having issues with the request being too large you may lower the
number of rows you are trying to upsert, or note the above limitation.
- The primary_keys argument must contain at least one column.
- The primary_keys argument cannot contain columns that are a LIST type.
- The primary_keys argument cannot contain columns that are a JSON type.
- The primary_keys columns must be present in the data being upserted. An
error is raised if a primary key column is missing.
- The values in the primary_keys columns cannot be null. A null value
cannot be used to match an existing row, so an error is raised if any row
has a null value in a primary key column.
- The values used as the primary_keys must be unique in the table. If there
are multiple rows with the same values in the primary_keys the behavior
is that an exception will be raised.
- The columns used in primary_keys cannot contain updated values. Since
the values in these columns are used to determine if a row exists, they
cannot be updated in the same transaction.
The following is a Sequence Diagram that describes the upsert process at a high level:
sequenceDiagram
participant User
participant Table
participant Synapse
User->>Table: upsert_rows()
loop Query and Process Updates in Chunks (rows_per_query)
Table->>Synapse: Query existing rows using primary keys
Synapse-->>Table: Return matching rows
Note Over Table: Create partial row updates
loop For results from query
Note Over Table: Sum row/chunk size
alt Chunk size exceeds update_size_bytes
Table->>Synapse: Push update chunk
Synapse-->>Table: Acknowledge update
end
Table->>Table: Add row to chunk
end
alt Remaining updates exist
Table->>Synapse: Push final update chunk
Synapse-->>Table: Acknowledge update
end
end
alt New rows exist
Table->>Table: Identify new rows for insertion
Table->>Table: Call synStoreRows() function
end
Table-->>User: Upsert complete
Usage
synUpsertRows(instance, values, primary_keys, dry_run=FALSE, rows_per_query=50000L, update_size_bytes=1992294.4, insert_size_bytes=943718400L, job_timeout=600L, date_columns=NULL, date_format=NULL, synapse_client=NULL)Arguments
- instance
(Table) The Table instance to operate on.
- values
(Union[str, dict[str, Any], object]) Supports storing data from the following sources:
- A string holding the path to a CSV file. The data will be read into a Pandas DataFrame. The code makes assumptions about the format of the columns in the CSV as detailed in the csv_to_pandas_df function.
- A named list where the key is the column name and the value is one or more values. The values will be wrapped into a Pandas DataFrame. You may pass in additional arguments to the pandas DataFrame constructor by passing them in as keyword arguments to this function. Read about the available arguments in the Pandas DataFrame documentation.
- A Pandas DataFrame- primary_keys
(list[str]) The columns to use to determine if a row already exists. If a row exists with the same values in the columns specified in this list the row will be updated. If a row does not exist it will be inserted.
- dry_run
(bool) If set to TRUE the data will not be updated in Synapse. A message will be printed to the console with the number of rows that would have been updated and inserted. If you would like to see the data that would be updated and inserted you may set the
dry_runargument to TRUE.- rows_per_query
(int) The number of rows that will be queried from Synapse per request. Since we need to query for the data that is being updated this will determine the number of rows that are queried at a time. The default is 50,000 rows.
- update_size_bytes
(int) The maximum size of the request that will be sent to Synapse when updating rows of data. The default is 1.9MB.
- insert_size_bytes
(int) The maximum size of the request that will be sent to Synapse when inserting rows of data. The default is 900MB.
- job_timeout
(int) The maximum amount of time to wait for a job to complete. This is used when inserting, and updating rows of data. Each individual request to Synapse will be sent as an independent job. If the timeout is reached a timeout error will be raised. The default is 600 seconds
- date_columns
(Optional[list[character]]) (CSV file only) The names of columns in your CSV file that contain dates or datetimes stored as formatted strings (e.g.
"2024-01-15"or"01/15/2024 13:30"). The columns are parsed withpandas.to_datetimeand converted to epoch time in milliseconds before the data is uploaded, which is the format Synapse requires forDATEcolumns.- date_format
(Optional[character or named list]) (CSV file only) How the strings in
date_columnsare formatted — a strftime format string (e.g."%m/%d/%Y") applied to every column, or a named list mapping column names to their formats. Supply this so that ambiguous dates (e.g."01/02/2024") are not silently misinterpreted and to optimize the data upload performance. If the values in a column do not match the format an error is raised.- synapse_client
(Optional[Synapse]) If not passed in, the last created instance from the Synapse class constructor will be used.
Note
When values is a path to a CSV file, you may pass additional keyword arguments to the
csv_to_pandas_df
function by adding them as extra named arguments to synUpsertRows(),
e.g. separator = ";" or lines_to_skip = 1L.
Examples
if (FALSE) { # \dontrun{
## Example 1: Updating 2 rows and inserting 1 row
# In this given example we have a table with the following data:
# | col1 | col2 | col3 |
# |------|------| -----|
# | A | 1 | 1 |
# | B | 2 | 2 |
# The following code will update the first row's col2 to 22, update the
# second row's col3 to 33, and insert a new row:
library(synapser)
synLogin()
# Also works with Dataset
table = Table(id = "syn123") |> synGet(table_options = TableOptions(include_columns = TRUE))
df = data.frame(
col1 = c("A", "B", "C"),
col2 = c(22, 2, 3),
col3 = c(1, 33, 3)
)
table |> synUpsertRows(values = df, primary_keys = list("col1"))
# The resulting table will look like this:
# | col1 | col2 | col3 |
# |------|------| -----|
# | A | 22 | 1 |
# | B | 2 | 33 |
# | C | 3 | 3 |
## Example 2: Deleting data from a specific cell
# In this given example we have a table with the following data:
# | col1 | col2 | col3 |
# |------|------| -----|
# | A | 1 | 1 |
# | B | 2 | 2 |
# The following code will clear the value in the first row's col2, and the
# second row's col3:
library(synapser)
synLogin()
# Also works with Dataset
table = Table(id = "syn123") |> synGet(table_options = TableOptions(include_columns = TRUE))
data_to_upsert = data.frame(
col1 = c("A", "B"),
col2 = c(NA, 2),
col3 = c(1, NA)
)
table |> synUpsertRows(values = data_to_upsert, primary_keys = list("col1"))
# The resulting table will look like this:
# | col1 | col2 | col3 |
# |------|------| -----|
# | A | | 1 |
# | B | 2 | |
## Example 3: Upserting data with date columns
# In this given example we have a table with the following data:
# | col1 | date_col |
# |------|------------|
# | A | 2024-01-15 |
# | B | 2024-02-20 |
# Suppose we have a CSV file with the following data that we want to upsert:
# | col1 | date_col |
# |------|------------|
# | A | 03/10/2024 |
# | C | 04/01/2024 |
# The date_columns/date_format arguments tell the client how to parse
# the formatted date strings in the CSV file so that they can be compared
# against, and stored in, the table's DATE column. The following code
# will update row A's date_col and insert a new row for C:
library(synapser)
synLogin()
# Also works with Dataset
Table(id = "syn123") |> synUpsertRows(
values = "path/to/file.csv",
primary_keys = list("col1"),
date_columns = list("date_col"),
date_format = "%m/%d/%Y"
)
# The resulting table will look like this:
# | col1 | date_col |
# |------|------------|
# | A | 2024-03-10 |
# | B | 2024-02-20 |
# | C | 2024-04-01 |
} # }