Tables

Tutorial purpose

In this vignette you will:

1. Create a Table and load it with data
2. Query your Table
3. Update your Table
4. Change Table structure
5. Delete Table rows and Tables
6. Work with dates and datetimes

Prerequisites

1. Make sure that you have completed the Project tutorial.
2. The tutorial assumes you have data in a dataframe and/or CSV you want to query in a SQL like interface in Synapse

Initial setup

library(synapser)

synLogin()

project <- Project(
  name = paste0(
    "My uniquely named project about Alzheimer's Disease",
    paste(sample(c(letters, LETTERS, 0:9), 8, replace = TRUE), collapse = "")
  )
) |> synStore()
cat("Created project:", project$id, "\n")

Example data

First, let’s load some data. Let’s say we had a file, genes.csv:

genes_csv <- tempfile(fileext = ".csv")
writeLines(
  c(
    "Name,Chromosome,Start,End,Strand,TranscriptionFactor",
    "foo,1,12345,12600,+,False",
    "arg,2,20001,20200,+,False",
    "zap,2,30033,30999,-,False",
    "bah,1,40444,41444,-,False",
    "bnk,1,51234,54567,+,True",
    "xyz,1,61234,68686,+,False"
  ),
  genes_csv
)

Loading data

• Creating a table from a CSV without specifying initial columns. In older versions of synapser, this was implemented using synBuildTable().

table <- Table(
  name = "My Favorite Genes_no_initial_columns",
  parent_id = project$id
) |> synStore()

table |> synStoreRows(
  values = genes_csv,
  schema_storage_strategy = "INFER_FROM_DATA"
)

synStoreRows() accepts data from several sources:

• A string holding the path to a CSV file. If the schema_storage_strategy is set to NULL the data will be uploaded as is. If schema_storage_strategy is set to INFER_FROM_DATA, the code makes assumptions about the format of the columns in the CSV.

• A named list where each name is a column name and each value is one or more values.

• An R data frame.

• Creating a table from a CSV with specified columns

columns <- list(
  Column(name = "Name", column_type = "STRING", maximum_size = 20),
  Column(name = "Chromosome", column_type = "STRING", maximum_size = 20),
  Column(name = "Start", column_type = "INTEGER"),
  Column(name = "End", column_type = "INTEGER"),
  Column(name = "Strand", column_type = "STRING", enum_values = list("+", "-"), maximum_size = 1),
  Column(name = "TranscriptionFactor", column_type = "BOOLEAN")
)
table <- Table(
  name = "My Favorite Genes_with_columns",
  columns = columns,
  parent_id = project$id
) |> synStore()
table |> synStoreRows(values = genes_csv)

• Creating a table from a data frame

genes <- tibble::tibble(
  Name = c("foo", "arg", "zap", "bah", "bnk", "xyz"),
  Chromosome = c("1", "2", "2", "1", "1", "1"),
  Start = c(12345L, 20001L, 30033L, 40444L, 51234L, 61234L),
  End = c(12600L, 20200L, 30999L, 41444L, 54567L, 68686L),
  Strand = c("+", "+", "-", "-", "+", "+"),
  TranscriptionFactor = c(FALSE, FALSE, FALSE, FALSE, TRUE, FALSE)
)

table <- Table(
  name = "My Favorite Genes_from_dataframe",
  parent_id = project$id
) |> synStore()
table |> synStoreRows(values = genes, schema_storage_strategy = "INFER_FROM_DATA")

Appending new rows

Appending new rows is fairly straightforward. To continue the previous example, we might add some new genes from another file. Lets take this new data in more_genes.csv.

The more_genes.csv:

• Using a CSV

more_genes_csv <- tempfile(fileext = ".csv")
writeLines(
  c(
    "Name,Chromosome,Start,End,Strand,TranscriptionFactor",
    "Qux1,4,201001,202001,+,False",
    "Qux2,4,203001,204001,+,False"
  ),
  more_genes_csv
)

table <- Table(
  name = "My Favorite Genes_from_dataframe",
  parent_id = project$id
) |> synGet()
table |> synStoreRows(values = more_genes_csv)

• Using a data frame If a column is not given, NA values are added automatically.

moreGenes <- tibble::tibble(
  Name = c("Qux3", "Qux4"),
  Chromosome = c("4", "4"),
  Start = c(201001L, 203001L),
  End = c(202001L, 204001L),
  Strand = c("+", "+"),
  TranscriptionFactor = c(FALSE, FALSE)
)
table |> synStoreRows(values = moreGenes)

Updating existing rows

Updating the existing table requires querying the rows you want to change first, since Synapse needs the ROW_ID and ROW_VERSION for each row being modified — synQuery() includes these columns by default. Minimizing changesets to contain only rows that actually change will make processing faster.

For example, let’s update the names of some of our favorite genes:

results <- synQuery(sprintf("SELECT * FROM %s WHERE Chromosome = '1'", table$id))
results$Name <- c("rzing", "zing1", "zing2", "zing3")
table |> synStoreRows(values = results)

Now, query the table again to see your changes:

synQuery(sprintf("SELECT * FROM %s LIMIT 10", table$id))

Upserting (Update or Insert) rows

If your data has a primary key, you can use the upsert functionality (synUpsertRows) to update existing rows or insert new rows based on this primary key. This way you won’t have to query for the existing data to update your table. This function does not do deletions.

toUpsert <- tibble::tibble(
  Name = c("Qux3", "Qux5"),
  Chromosome = c("4", "4"),
  Start = c(201001L, 203001L),
  End = c(202001L, 204001L),
  Strand = c("-", "+"),
  TranscriptionFactor = c(TRUE, FALSE)
)
# Qux3 will be updated, Qux5 will be inserted
table |> synUpsertRows(values = toUpsert, primary_keys = list("Name"))

Working with dates and datetimes

Synapse DATE columns store an exact moment in time, represented as the number of milliseconds since 1970-01-01 00:00:00 UTC. Before upload, the client converts the datetime values in your data to that representation, and how a value is converted depends on whether it carries timezone information. The examples below create a small table with a DATE column and walk through the different ways datetime data can be stored and queried.

table <- Table(
  name = "Sample Collection Dates",
  parent_id = project$id,
  columns = c(
    Column(name = "sample_id", column_type = "STRING", maximum_size = 20),
    Column(name = "collected_on", column_type = "DATE")
  )
)
table |> synStore()

Storing timezone-aware datetimes (recommended)

A timezone-aware datetime is converted to UTC exactly — the stored value does not depend on the timezone settings of the machine performing the upload or on the date the upload is run. Localizing with a zone name like "America/Los_Angeles" interprets each value using the UTC offset in effect on that value’s own date, so values on either side of a daylight saving switch are both stored correctly.

df <- data.frame(
  sample_id = c("S1", "S2"),
  collected_on = as.POSIXct(
    c(
      "2017-02-14 11:23:00", # winter in Los Angeles (PST, UTC-8)
      "2018-10-01 09:30:00" # summer in Los Angeles (PDT, UTC-7)
    ),
    tz = "America/Los_Angeles"
  )
)

table |> synStoreRows(values = df)

Storing naive datetimes

A naive datetime (one without tzinfo) is assumed to be in the local timezone of the machine at the time of upload, and that single UTC offset is applied to every value in the upload.

naive_df <- data.frame(
  sample_id = c("S3"),
  collected_on = as.POSIXct(
    c("2017-02-14 11:23:00")
  )
)

table |> synStoreRows(values = naive_df)

This is convenient when your data and your machine share a timezone and daylight saving period, but any value whose date falls in a different daylight saving period than the upload date is stored shifted by one hour. Here, 2017-02-14 11:23 is a winter (PST, UTC-8) wall-clock time; uploading it from a machine currently on summer time (PDT, UTC-7) stores 18:23 UTC instead of the correct 19:23 UTC. Midnight values deserve extra care: the same one-hour shift can move a naive midnight to 11 PM of the previous day, changing the calendar date the value displays as. To avoid all of this, localize your data first as shown in the previous example.

Storing dates without a time of day

Plain R Date values (as opposed to datetime values like POSIXct) are treated as midnight on that date and converted the same way timezone-naive datetimes are: using the local timezone of the machine at upload time, with the same daylight-saving-shift risk described above.

date_df <- data.frame(
  sample_id = "S4",
  collected_on = as.Date("2017-02-14") # a date, not a datetime
)

table |> synStoreRows(values = date_df)

query_results <- synQuery(
  sprintf("SELECT sample_id, collected_on FROM %s ORDER BY collected_on", table$id),
  convert_to_datetime = TRUE
)
print(query_results)

Loading date columns from a CSV

When storing rows from a CSV file, use date_columns and date_format to tell the client which columns hold formatted date strings and how to parse them. Including the UTC offset in the strings (parsed by %z) keeps the values timezone-aware, with the same benefits as localizing a DataFrame column.

writeLines(
  c(
    "sample_id,collected_on",
    "S5,2017-02-14 11:23 -0800",
    "S6,2018-10-01 09:30 -0700"
  ),
  "collection_dates.csv"
)

table |> synStoreRows(
  values = "collection_dates.csv",
  date_columns = c("collected_on"),
  date_format = "%Y-%m-%d %H:%M %z"
)

Querying datetime data back

By default, querying a table returns DATE columns as raw millisecond timestamps rather than R date/time objects.

Don’t try to reconstruct a date from that raw value yourself. Synapse stores DATE values as 64-bit epoch-millisecond integers, but R has no native 64-bit integer type, and converting a raw integer that large from Python to R isn’t a safe, bounds-checked cast the way as.integer() is — the value can silently wrap around to a different, still plausible-looking integer with no warning that anything went wrong.

Instead, pass convert_to_datetime=TRUE so DATE columns are converted to timezone-aware datetimes in UTC on the Python side:

results <- synQuery(
  sprintf("SELECT * FROM %s ORDER BY collected_on", table$id),
  convert_to_datetime = TRUE
)
results

The collected_on column above may still print as a plain number rather than a date — that’s misleading, not wrong. Each converted value crosses from Python into R individually rather than as one vectorized column, so collected_on comes back as an R list-column: one correct POSIXct object per cell rather than a single date/time vector. R’s default data.frame printing silently drops the class when displaying a list-column, showing the raw epoch-seconds number underneath instead. Flatten it into a normal POSIXct vector with c() before using it:

results$collected_on <- do.call(c, results$collected_on)
results

DATE columns are returned as UTC datetimes. Set the display timezone to view the same instants on another wall clock:

attr(results$collected_on, "tzone") <- "America/Los_Angeles"
results

Note how the timezone-aware rows (S1, S2, S5, S6) round-trip back to the exact wall-clock times they were created with, while the naive datetime row S3 and date row S4 were converted using that same upload-time PDT offset. For S4, that offset is applied to midnight because dates have no time of day.

To filter on a DATE column in a query, compare it against epoch milliseconds:

# In a WHERE clause, compare a DATE column against epoch milliseconds:
# Example: filter for rows where 'collected_on' is on or after January 1, 2018
cutoff <- as.numeric(as.POSIXct("2018-01-01 00:00:00", tz = "UTC")) * 1000 # as double
recent <- synQuery(
  sprintf("SELECT sample_id FROM %s WHERE collected_on >= %.0f", table$id, cutoff),
  include_row_id_and_row_version = FALSE
)
recent

For the full details of how datetime values are interpreted during upload, see the synStoreRows reference documentation.