Skip to contents

A Table represents the metadata of a table.

Usage

Table(id=NULL, name=NULL, parent_id=NULL, activity=NULL, version_number=NULL, description=NULL, columns=list(), etag=NULL, created_on=NULL, created_by=NULL, modified_on=NULL, modified_by=NULL, version_label=NULL, version_comment=NULL, is_latest_version=NULL, is_search_enabled=NULL, annotations=list())

Format

An S3-style class object.

Arguments

id

(Optional[str]) The unique immutable ID for this table. A new ID will be generated for new Tables. Once issued, this ID is guaranteed to never change or be re-issued

name

(Optional[str]) The name of this table. Must be 256 characters or less. Names may only contain: letters, numbers, spaces, underscores, hyphens, periods, plus signs, apostrophes, and parentheses

parent_id

(Optional[str]) The ID of the Entity that is the parent of this table.

activity

(Optional[Activity]) The Activity model represents the main record of Provenance in Synapse. It is analygous to the Activity defined in the W3C Specification on Provenance. Activity cannot be removed during a store operation by setting it to None. You must use: synDelete or synDisassociateActivityFromEntity.

version_number

(Optional[int]) (Read Only) The version number issued to this version on the object. Use this synSnapshot() function to create a new version of the table.

description

(Optional[str]) The description of this entity. Must be 1000 characters or less.

columns

(Optional[Union[list[Column], OrderedDict[str, Column], dict[str, Column]]]) The columns of this table. This is an ordered named list where the key is the name of the column and the value is the Column object. When creating a new instance of a Table object you may pass any of the following types as the columns argument:

- A list of Column objects
- A named list where the key is the name of the column and the value is the Column object

The order of the columns will be the order they are stored in Synapse. If you need to reorder the columns the recommended approach is to use the synReorderColumn() function. Additionally, you may add, and delete columns using the synAddColumn(), and synDeleteColumn() functions on your Table instance.

You may modify the attributes of the Column object to change the column type, name, or other attributes. For example suppose I'd like to change a column from a INTEGER to a DOUBLE. I can do so by changing the column type attribute of the Column object. The next time you store the table the column will be updated in Synapse with the new type.


library(synapser)
synLogin()

table = Table(id = "syn1234") |> synGet()
table$columns[["my_column"]]$column_type = "DOUBLE"
table = table |> synStore()

Note that the keys in this named list should match the column names as they are in Synapse. However, know that the name attribute of the Column object is used for all interactions with the Synapse API. The named list key is purely for the usage of this interface. For example, if you wish to rename a column you may do so by changing the name attribute of the Column object. The key in the named list does not need to be changed. The next time you store the table the column will be updated in Synapse with the new name and the key in the named list will be updated.

etag

(Optional[str]) Synapse employs an Optimistic Concurrency Control (OCC) scheme to handle concurrent updates. Since the E-Tag changes every time an entity is updated it is used to detect when a client's current representation of an entity is out-of-date.

created_on

(Optional[str]) The date this table was created.

created_by

(Optional[str]) The ID of the user that created this table.

modified_on

(Optional[str]) The date this table was last modified. In YYYY-MM-DD-Thh:mm:ss.sssZ format

modified_by

(Optional[str]) The ID of the user that last modified this table.

version_label

(Optional[str]) (Read Only) The version label for this table. Use the synSnapshot() function to create a new version of the table.

version_comment

(Optional[str]) (Read Only) The version comment for this table. Use the synSnapshot() function to create a new version of the table.

is_latest_version

(Optional[bool]) (Read Only) If this is the latest version of the object.

is_search_enabled

(Optional[bool]) When creating or updating a table or view specifies if full text search should be enabled. Note that enabling full text search might slow down the indexing of the table or view.

annotations

(Optional[dict[str, Union[list[str], list[bool], list[float], list[int], list[date], list[datetime]]]]) Additional metadata associated with the table. The key is the name of your desired annotations. The value is an object containing a list of values (use empty list to represent no values for key) and the value type associated with all values in the list. To remove all annotations set this to an empty named list list() or NULL and store the entity.

Value

NULL

Methods

  • Table(id=NULL, name=NULL, parent_id=NULL, activity=NULL, version_number=NULL, description=NULL, columns=list(), etag=NULL, created_on=NULL, created_by=NULL, modified_on=NULL, modified_by=NULL, version_label=NULL, version_comment=NULL, is_latest_version=NULL, is_search_enabled=NULL, annotations=list()): Constructor for Table

  • synAddColumn(instance, column, index=NULL): Add column(s) to the table. Note that this does not store the column(s) in Synapse. You must call the synStore() function on this Table instance to store the column(s) in Synapse. This is a convenience function to eliminate the need to manually add the column(s) to the $columns list.

    This function will add an item to the $columns attribute of the Table instance. $columns is a named list where the key is the name of the column and the value is the Column object.

  • synBindSchema(instance, json_schema_uri, enable_derived_annotations=FALSE, synapse_client=NULL): Bind a JSON schema to the entity.

  • synDeleteColumn(instance, name): Mark a column for deletion. Note that this does not delete the column from Synapse. You must call the synStore() function on this Table instance to delete the column from Synapse. This is a convenience function to eliminate the need to manually delete the column from the named list and add it to the $_columns_to_delete attribute.

  • synDeletePermissions(instance, include_self=TRUE, include_container_content=FALSE, recursive=FALSE, target_entity_types=NULL, dry_run=FALSE, show_acl_details=TRUE, show_files_in_containers=TRUE, synapse_client=NULL): Delete the entire local Access Control List (ACL) for a given Entity. This is not scoped to a specific user or group, but rather removes all permissions associated with the Entity. After this operation, the Entity will inherit permissions from its benefactor, which is typically its parent entity or the Project it belongs to.

    In order to remove permissions for a specific user or group, you should use the synSetPermissions() function with the access_type set to an empty list.

    By default, Entities such as FileEntity and Folder inherit their permission from their containing Project. For such Entities the Project is the Entity's 'benefactor'. This permission inheritance can be overridden by creating an ACL for the Entity. When this occurs the Entity becomes its own benefactor and all permission are determined by its own ACL.

    If the ACL of an Entity is deleted, then its benefactor will automatically be set to its parent's benefactor.

    Special notice for Projects: The ACL for a Project cannot be deleted, you must individually update or revoke the permissions for each user or group.

  • synDeleteRows(instance, query=NULL, df=NULL, job_timeout=600L, synapse_client=NULL): Delete rows from a table given a query or a data frame to select rows. The query at a minimum must select the ROW_ID and ROW_VERSION columns. If you want to inspect the data that will be deleted ahead of time you may use the synQuery() function to get the data. The data frame must at least contain the ROW_ID and ROW_VERSION columns. And ROW_ETAG column is also required if the entity is one of the following: EntityView, Dataset, DatasetCollection, or SubmissionView. If both query and df are provided, the query will be used.

  • synGetAcl(instance, principal_id=NULL, check_benefactor=TRUE, synapse_client=NULL): Get the ACL that a user or group has on an Entity.

  • synGetPermissions(instance, synapse_client=NULL): Get the permissions that the caller has on an Entity.

  • synGetSchema(instance, synapse_client=NULL): Get the JSON schema bound to the entity.

  • synGetSchemaDerivedKeys(instance, synapse_client=NULL): Retrieve derived JSON schema keys for the entity.

  • synListAcl(instance, recursive=FALSE, include_container_content=FALSE, target_entity_types=NULL, log_tree=FALSE, synapse_client=NULL): List the Access Control Lists (ACLs) for this entity and optionally its children.

    This function returns the local sharing settings for the entity and optionally its children. It provides a mapping of all ACLs for the given container/entity.

    Important Note: This function returns the LOCAL sharing settings only, not the effective permissions that each Synapse User ID/Team has on the entities. More permissive permissions could be granted via a Team that the user has access to that has permissions on the entity, or through inheritance from parent entities.

  • synQuery(query, include_row_id_and_row_version=TRUE, convert_to_datetime=FALSE, download_location=NULL, quote_character="\"", escape_character=r"(\)", line_end="\n", separator=",", header=TRUE, timeout=250L, synapse_client=NULL): Query for data on a table stored in Synapse. The results 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 data frame or downloading the results to a file.

  • synQueryPartMask(query, part_mask, timeout=250L, synapse_client=NULL): Query for data on a table stored in Synapse. This is a more advanced use case of the synQuery() function that allows you to determine what additional metadata about the table or query should also be returned. If you do not need this additional information then you are better off using the synQuery() function.

    The query for this method uses this Rest API:
    https://rest-docs.synapse.org/rest/POST/entity/id/table/query/async/start.html

  • synReorderColumn(instance, name, index): Reorder a column in the table. Note that this does not store the column in Synapse. You must call the synStore() function on this Table instance to store the column in Synapse. This is a convenience function to eliminate the need to manually reorder the named list stored in the $columns attribute.

    You must ensure that the index is within the bounds of the number of columns in the table. If you pass in an index that is out of bounds the column will be added to the end of the list.

  • synSetPermissions(instance, principal_id=NULL, access_type=NULL, modify_benefactor=FALSE, warn_if_inherits=TRUE, overwrite=TRUE, synapse_client=NULL): Sets permission that a user or group has on an Entity. An Entity may have its own ACL or inherit its ACL from a benefactor.

  • synSnapshot(instance, comment=NULL, label=NULL, include_activity=TRUE, associate_activity_to_new_version=TRUE, synapse_client=NULL): Request to create a new snapshot of a table. The provided comment, label, and activity will be applied to the current version thereby creating a snapshot and locking the current version. After the snapshot is created a new version will be started with an 'in-progress' label.

  • synStoreRows(instance, values, schema_storage_strategy=NULL, column_expansion_strategy=NULL, dry_run=FALSE, additional_changes=NULL, insert_size_bytes=943718400L, csv_table_descriptor=NULL, date_columns=NULL, date_format=NULL, read_csv_kwargs=NULL, to_csv_kwargs=NULL, job_timeout=600L, synapse_client=NULL): Add or update rows in Synapse from the sources defined below. In most cases the result of this function call will append rows to the table. In the case of an update this method works on a full row replacement. What this means is that you may not do a partial update of a row. If you want to update a row you must pass in all the data for that row, or the data for the columns not provided will be set to null.

    If you'd like to update a row see the example Updating rows in a table below.

    If you'd like to perform an upsert or partial update of a row you may use the synUpsertRows() function. See that function for more information.

    Note the following behavior for the order of columns:

    - If a column is added via the synAddColumn() function it will be added at the index you specify, or at the end of the columns list.
    - If column(s) are added during the construction of your Table instance, ie. Table(columns = list(Column(name = "foo"))), they will be added at the beginning of the columns list.
    - If you use the synStoreRows() function and the schema_storage_strategy is set to INFER_FROM_DATA the columns will be added at the end of the columns list.

    How datetime values are interpreted:

    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, datetime values are converted to that representation as follows:

    - Timezone-aware datetimes (POSIXct values created with a tz, e.g. as.POSIXct("2017-02-14 11:23", tz = "America/Los_Angeles")) are converted to UTC exactly. This is the recommended way to pass datetime data: the stored value does not depend on the timezone settings of the machine performing the upload or on the date the upload is run.
    - Naive datetimes (POSIXct values without a tz) are assumed to be in the local timezone of the machine at the time of upload. That single UTC offset is applied to every value, including values whose dates fall in a different daylight saving period, which will be stored shifted by one hour. For example, uploading 2017-02-14 11:23 (a PST date, UTC-8) from a machine currently on PDT (UTC-7) stores 2017-02-14 18:23 UTC instead of the correct 19:23 UTC. To avoid this, create your datetimes with a timezone, e.g. as.POSIXct(x, tz = "America/Los_Angeles").
    - Plain R Date values (as opposed to datetimes) are treated as midnight of that date and converted the same way naive datetimes are: using the local timezone of the machine at the time of upload, with the same daylight-saving-shift risk described above.
    - Midnight values deserve extra care: the one-hour daylight saving shift described above can move a naive midnight datetime — or a Date, which is treated as midnight — to 11 PM of the previous day, changing the calendar date the value displays as.

    Why timezone-aware values are recommended:

    A naive datetime such as 2017-02-14 11:23 is only "what a clock on the wall said." Before it can be stored as an exact moment in time, something has to answer: a clock where? Timezone-aware values answer that question in the data itself; naive values leave the client to guess, and it guesses the uploading machine's current timezone.

    A zone name like "America/Los_Angeles" does not mean a fixed offset such as UTC-8. It means "a Los Angeles wall clock" — and those clocks move twice a year: UTC-7 in summer (PDT), UTC-8 in winter (PST). When you create datetimes with as.POSIXct(x, tz = "America/Los_Angeles"), R looks up what LA clocks were set to on each value's own date:

    - 2017-02-14 → winter → interpreted using UTC-8
    - 2018-10-01 → summer → interpreted using UTC-7

    When the data is queried back with synQuery(..., convert_to_datetime = TRUE), DATE columns are returned as datetimes in UTC. See vignette("tables", package = "synapser") for how to work with the returned values and view them in another timezone.

    Limitations:

    - Synapse limits the number of rows that may be stored in a single request to a CSV file that is 1GB. If you are storing a CSV file that is larger than this limit the data will be chunked into smaller requests. This process is done by reading the file once to determine what the row and byte boundries are and calculating the MD5 hash of that portion, then reading the file again to send the data to Synapse. This process is done to ensure that the data is not corrupted during the upload process, in addition Synapse requires the MD5 hash of the data to be sent in the request along with the number of bytes that are being sent.
    - The limit of 1GB is also enforced when storing a named list or a data frame. The data will be converted to a CSV format using the to_csv() pandas function. If you are storing more than a 1GB file it is recommended that you store the data as a CSV and use the file path to upload the data. This is due to the fact that the data frame chunking process is slower than reading portions of a file on disk and calculating the MD5 hash of that portion.

    The following is a Sequence Diagram that describes the process noted in the limitation above. It shows how the data is chunked into smaller requests when the data exceeds the limit of 1GB, and how portions of the data are read from the CSV file on disk while being uploaded to Synapse.

    
    sequenceDiagram
        participant User
        participant Table
        participant FileSystem
        participant Synapse
    
        User->>Table: store_rows(values)
    
        alt CSV size > 1GB
            Table->>Synapse: Apply schema changes before uploading
            note over Table, FileSystem: Read CSV twice
            Table->>FileSystem: Read entire CSV (First Pass)
            FileSystem-->>Table: Compute chunk sizes & MD5 hashes
    
            loop Read and Upload CSV chunks (Second Pass)
                Table->>FileSystem: Read next chunk from CSV
                FileSystem-->>Table: Return bytes
                Table->>Synapse: Upload CSV chunk
                Synapse-->>Table: Return file_handle_id
                Table->>Synapse: Send 'TableUpdateTransaction' to append/update rows
                Synapse-->>Table: Transaction result
            end
        else
            Table->>Synapse: Upload CSV without splitting & Any additional schema changes
            Synapse-->>Table: Return file_handle_id
            Table->>Synapse: Send 'TableUpdateTransaction' to append/update rows
            Synapse-->>Table: Transaction result
        end
    
        Table-->>User: Upload complete
    

    The following is a Sequence Diagram that describes the process noted in the limitation above for data frames. It shows how the data is chunked into smaller requests when the data exceeds the limit of 1GB, and how portions of the data are read from the data frame while being uploaded to Synapse.

    
    sequenceDiagram
        participant User
        participant Table
        participant MemoryBuffer
        participant Synapse
    
        User->>Table: store_rows(DataFrame)
    
        loop For all rows in DataFrame in 100 row increments
            Table->>MemoryBuffer: Convert DataFrame rows to CSV in-memory
            MemoryBuffer-->>Table: Compute chunk sizes & MD5 hashes
        end
    
    
        alt Multiple chunks detected
            Table->>Synapse: Apply schema changes before uploading
        end
    
        loop For all chunks found in first loop
            loop for all parts in chunk byte boundry
                Table->>MemoryBuffer: Read small (< 8MB) part of the chunk
                MemoryBuffer-->>Table: Return bytes (with correct offset)
                Table->>Synapse: Upload part
                Synapse-->>Table: Upload response
            end
            Table->>Synapse: Complete upload
            Synapse-->>Table: Return file_handle_id
            Table->>Synapse: Send 'TableUpdateTransaction' to append/update rows
            Synapse-->>Table: Transaction result
        end
    
        Table-->>User: Upload complete
    
  • synUnbindSchema(instance, synapse_client=NULL): Unbind the JSON schema bound to the entity.

  • 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): This 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
    
  • synValidateSchema(instance, synapse_client=NULL): Validate the entity against the bound JSON schema.

Examples

if (FALSE) { # \dontrun{
## Example 1: Create a table with data without specifying columns
    # This API is setup to allow the data to define which columns are created on the
    # Synapse table automatically. The limitation with this behavior is that the
    # columns created will only be of the following types:

    # - STRING
    # - LARGETEXT
    # - INTEGER
    # - DOUBLE
    # - BOOLEAN
    # - DATE

    # The determination of the column type is based on the data that is passed in
    # using the pandas function
    # infer_dtype (https://pandas.pydata.org/docs/reference/api/pandas.api.types.infer_dtype.html).
    # If you need a more specific column type, or need to add options to the columns
    # follow the examples below.
    library(synapser)
    synLogin()

    my_data = data.frame(
        my_string_column = c("a", "b", "c", "d"),
        my_integer_column = c(1, 2, 3, 4),
        my_double_column = c(1.0, 2.0, 3.0, 4.0),
        my_boolean_column = c(TRUE, FALSE, TRUE, FALSE)
    )

    table = Table(
        name = "my_table",
        parent_id = "syn1234"
    ) |> synStore()

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

    # Prints out the stored data about this specific column
    table$columns[["my_string_column"]]

## Example 2: Rename an existing column
    # This examples shows how you may retrieve a table from synapse, rename a column,
    # and then store the table back in synapse.
    library(synapser)
    synLogin()

    table = Table(
        name = "my_table",
        parent_id = "syn1234"
    ) |> synGet()

    # You may also get the table by id:
    table = Table(id = "syn4567") |> synGet()

    table$columns[["my_old_column"]]$name = "my_new_column"

    # Before the data is stored in synapse you'll still be able to use the old key to access the column entry
    table$columns[["my_old_column"]]

    table = table |> synStore()

    # After the data is stored in synapse you'll be able to use the new key to access the column entry
    table$columns[["my_new_column"]]

## Example 3: Create a table with a list of columns
    # A list of columns may be passed in when creating a new table. The order of the
    # columns in the list will be the order they are stored in Synapse. If the table
    # already exists and you create the Table instance in this way the columns will
    # be appended to the end of the existing columns.
    library(synapser)
    synLogin()

    columns = list(
        Column(name = "my_string_column", column_type = "STRING"),
        Column(name = "my_integer_column", column_type = "INTEGER"),
        Column(name = "my_double_column", column_type = "DOUBLE"),
        Column(name = "my_boolean_column", column_type = "BOOLEAN")
    )

    table = Table(
        name = "my_table",
        parent_id = "syn1234",
        columns = columns
    )

    table |> synStore()

## Example 4: Creating a table with a named list of columns
    # When specifying a number of columns via a named list setting the name attribute
    # on the Column object is optional. When it is not specified it will be
    # pulled from the key of the named list.
    library(synapser)
    synLogin()

    columns = list(
        my_string_column = Column(column_type = "STRING"),
        my_integer_column = Column(column_type = "INTEGER"),
        my_double_column = Column(column_type = "DOUBLE"),
        my_boolean_column = Column(column_type = "BOOLEAN")
    )

    table = Table(
        name = "my_table",
        parent_id = "syn1234",
        columns = columns
    )

    table |> synStore()
} # }