---
title: "Tables"
date: "`r Sys.Date()`"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Tables}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---
## Tables

Synapse Tables enable storage of tabular data in Synapse in a form that can be queried using a SQL-like query language.

A table has a schema and holds a set of rows conforming to that schema. A schema defines a series of `Column`s of the following types: `STRING`, `DOUBLE`, `INTEGER`, `BOOLEAN`, `DATE`, `ENTITYID`, `FILEHANDLEID`, `LINK`, `LARGETEXT`, `MEDIUMTEXT`, `USERID`, `SUBMISSIONID`, `EVALUATIONID`, `JSON`, `STRING_LIST`, `INTEGER_LIST`, `BOOLEAN_LIST`, `DATE_LIST`, `ENTITYID_LIST`, `USERID_LIST`.

### 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

## 1. Creating a table

### Initial setup

```{r collapse=TRUE, eval=FALSE}
library(synapser)
```

```{r collapse=TRUE, eval=FALSE}
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:

| 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               |

```{r collapse=TRUE, eval=FALSE}
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()`.

```{r collapse=TRUE, eval=FALSE}
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
```{r collapse=TRUE, eval=FALSE}
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
```{r collapse=TRUE, eval=FALSE}
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")
```

## 2. Querying for data

The query language is quite similar to SQL select statements, except that joins are not supported. The documentation for the Synapse API has lots of [query examples](https://rest-docs.synapse.org/rest/org/sagebionetworks/repo/web/controller/TableExamples.html). Let's try to query:

```{r collapse=TRUE, eval=FALSE}
results <- synQuery(sprintf("SELECT * FROM %s WHERE Chromosome = '1' AND Start < 41000 AND \"End\" > 20000", table$id))
results
```

The `synQuery()` will always be returned as a data frame unless you specify a `download_location` in which case the results will be downloaded to that location. There are a number of arguments that you may pass to this function depending on if you are getting the results back as a DataFrame or downloading the results to a file.

## 3. Changing Data

Once the schema is settled, changes come in two flavors: appending new rows and updating existing ones. 
One other piece of information required for making changes to tables is the `etag`, which is used by the Synapse server to prevent concurrent users from making conflicting changes through a technique called optimistic concurrency. This comes as a result of running `synQuery`. In the example above, you could see the etag by running `results$etag` - but you should never have to use it directly. In case of a conflict, your update may be rejected. You then have to do another query and try your update again.

### 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:

| Name | Chromosome | Start  | End    | Strand | TranscriptionFactor |
|------|------------|--------|--------|--------|---------------------|
| Qux1 | 4          | 201001 | 202001 | +      | False               |
| Qux2 | 4          | 203001 | 204001 | +      | False               |

* Using a CSV
```{r collapse=TRUE, eval=FALSE}
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.
```{r collapse=TRUE, eval=FALSE}
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:

```{r collapse=TRUE, eval=FALSE}
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:

```{r collapse=TRUE, eval=FALSE}
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.

```{r collapse=TRUE, eval=FALSE}
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"))
```

## 4. Changing Table Structure

* Adding columns
```{r collapse=TRUE, eval=FALSE}
table |> synAddColumn(
  Column(name = "Expression", column_type = "STRING")
)
table |> synStore()
```

* Renaming or modifying a column
Renaming or otherwise modifying a column is done by changing the attributes on the `Column` object accessed through `columns`, then storing the table again:

```{r collapse=TRUE, eval=FALSE}
table$columns[["Expression"]]$name <- "Expression2"
table$columns[["Expression"]]$column_type <- "INTEGER"
table |> synStore()
```

* Removing a column
```{r collapse=TRUE, eval=FALSE}
table |> synDeleteColumn(name = "Expression2")
table |> synStore()
```

## 5. Deleting Table Rows & Tables

Deleting specific rows — query for the rows you want to delete and call `synDeleteRows()` on the results: 

```{r collapse=TRUE, eval=FALSE}
table |> synDeleteRows(query = sprintf("SELECT * FROM %s WHERE Strand = '+'", table$id))
```

Rows can also be deleted based on a data.frame, where the `ROW_ID` and `ROW_VERSION` columns specify the rows to be deleted from the table. In this example, rows 2 and 3 are deleted. See this document that describes the expected columns: <https://rest-docs.synapse.org/rest/org/sagebionetworks/repo/model/table/Row.html>. Note: `ROW_VERSION` begins at 1 upon row creation and increases by one with every subsequent update.

```{r collapse=TRUE, eval=FALSE}
table |> synDeleteRows(df = data.frame(ROW_ID = c(2L, 3L), ROW_VERSION = c(1L, 1L)))
```

Deleting the whole table deletes the table and all of its rows:

```{r collapse=TRUE, eval=FALSE}
synDelete(table)
```

### 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.

```{r collapse=TRUE, eval=FALSE}
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.

```{r collapse=TRUE, eval=FALSE}
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.

```{r collapse=TRUE, eval=FALSE}
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.

```{r collapse=TRUE, eval=FALSE}
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.

```{r collapse=TRUE, eval=FALSE}
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:

```{r collapse=TRUE, eval=FALSE}
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:

```{r collapse=TRUE, eval=FALSE}
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:

```{r collapse=TRUE, eval=FALSE}
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:

```{r collapse=TRUE, eval=FALSE}
# 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.

## Clean Up

Delete the project and all its contents when done:

```{r collapse=TRUE, eval=FALSE}
synDelete(project)
```