Synapse Tables are Synapse tables are used to create tabular data that is organize web-accessible, sharable, and queryable . Table columns have a user-specified, structured schema. You can define Table columns to contain common primitive data types (text, numbers, and dates) or references to other Synapse objects, like Files and Folders.Tables may . Tables may be queried and edited through with the web Synapse UI, as well as through with the Synapse programmatic clients. Unlike most NoSQL systems, the data in Synapse Tables is strongly consistent, not eventually consistent. This is an important design consideration for scientific data processing, as analysis on eventually-consistent data sources can limit the types of analysis performed, and may require special coding strategies to ensure reasonable accuracy.To learn more about working with tables using one of our programmatic clients, including code examples for the below tasksThis article guides you through the process of creating a table in Synapse.
For information about how to query a table, see Querying Tables, Views, and Datasets.
To learn more about creating tables and creating queries using one of the Synapse programmatic clients, see:
Tables in Python Docs.
Tables in R Docs.
Creating a New Table From a File
Tables require structured data , such as that contained in a .csv or .tsv file. If your structured data is saved in an Excel format such as .xls or .xlsx, please you must “save-as” to convert the file to a .csv to before you proceed in Synapse. Navigate to the Tables tab on your project. You have the option to create an empty table by clicking on the Add Table button under the Tools menu, or to upload your existing data in .csv or .tsv format by clicking the in your project and select Upload a Table button. If you create an empty table, you’ll need to start by defining your schema (see below). If you create a Table by uploading an existing file, first attach your file, click “Save”, and then you will be presented with a preview of your data and some advanced options for defining separators and declaring whether or not your first line is a header. Synapse will automatically attempt to detect the table schema based on these options.
After clicking on “Next”, you will see the option to rename your Table and, optionally, define your schema more specifically.
Defining a Schema
Synapse Tables require that you specify the types of data in each column included in the Table. If you are creating an empty Table, these columns can be specified manually by clicking on Add Column and selecting the column type you wish to add. If you are uploading an existing file, Synapse will recommend column types during upload. If you allow Synapse to recommend column types, be sure to verify that these column types represent your data well. You will be presented with a best guess under the button labeled “Schema Options” during import.
Synapse currently supports several kinds of text columns (STRING, LARGE TEXT), dates (as TIMESTAMP), and a variety of numeric columns (INTEGER, DOUBLE), and various Synapse identifiers (ENTITYID, USER). For a complete list of column types and definitions, please see the REST docs on columnTypes.
...
Note: Each programmatic client has a utility function to create columns from a data frame. Please see the Python Docs or R Docs for examples.
Adding Data to Tables
If you have imported an existing .csv or .tsv, all of your data will be imported at once. provide a .csv or .tsv file, Synapse will infer your table schema based on the column headers. You may further customize the schema by selecting Schema Options.
For very large files, it may take a while time for the Table table to be built and indexed completely before it can be usedviewed. You can may navigate away from the Tables table once you have clicked “Create” Create, and you will not lose any data.
If you have created an empty Table, you will need to click on “Add Data to Table” to upload a .csv or .tsv file, or add rows individually through the web UI. To add rows, click on the Edit Query Results icon to the right of the query button to get to the Edit Rows pop-up. From there, you can add rows by clicking the + at the top.
...
Deleting rows
To delete rows, query for the rows you want to delete and use the delete function on the results:
Click on the Edit icon to the right of the query button to get to the Edit Rows pop-up. From there, you can delete rows by checking the boxes of the rows you would like to delete and then clicking the Trash Can icon.
...
Modifying existing rows
To modify row entries, click on the Edit icon to the right of the Query button. In the resulting pop-up, you can adjust each entry as you please. In this example, the first and last entry of row two have been updated.
...
Modifying Tables
Changing or Adding Data
Once the schema is settled, changes to the data can be made by adding, appending, and deleting.
When updating, begin by querying the table to ensure you have the latest schema and values.
Updating existing values
Click on the edit icon to the right of the Query button to update table values.
...
Changing or Adding Columns
To add columns, click on the Schema button. From there, select the Edit Schema button and then add columns using the Add Column button located at the bottom of the pop-up.
...
Creating an Empty Table
You have the option to create an empty table by clicking on the Tables Tools menu and Add Table. To build a new table, you must specify the table structure, column by column. Select Add Column to specify each column’s properties.
Column Name | Choose a name for the column that will appear in the header. Column names must be 256 characters or less. There are three reserved words that cannot be used for Column Name: ROW_ID, ROW_VERSION, ROW_ETAG (case insensitive). |
To delete columns, click on the Schema button. From there, click the Edit Schema button and then select the columns you would like to delete and delete them by clicking the trash can icon at the top.
...
To modify information in a column, first begin by adding a new column, then copy the data from the column you would like to change into the newly created column, make the changes in the new column, and delete the old one. In this example, we are changing the Column Type of Header_1 into Boolean and setting the Default Value to true. This is also true if you are changing a Table Column Type from representing a single value to multiple values, such as needing to change a Column Type from String to StringList or similar.
...
Deleting the Whole Table
To delete the entire Table, click on Tools and then select Delete Table from the resulting dropdown.
...
Querying Table Data
The data contained within a Synapse Table can be retrieved by using a SQL-like query language either through the web portal or through the analytical clients. See the API docs for an enumeration of the types of queries that can be performed.
Selecting and Filtering Data
To get all of the columns from a Table with id syn3079449, the following query would be used:
| Code Block |
|---|
SELECT * FROM syn3079449
|
To get only the two columns called “age” and “gender”:
| Code Block |
|---|
SELECT age, gender FROM syn3079449
|
To get all columns, but only rows where age is greater that 50:
| Code Block |
|---|
SELECT * FROM syn3079449 WHERE age > 50
|
To get all columns, but only rows where age is greater that 50 - and sort by treatmentArm:
| Code Block |
|---|
SELECT * FROM syn3079449 WHERE age > 50 ORDER BY "treatmentArm" ASC
|
Using Advanced SQL Queries to Aggregate Data
More advanced SQL functions are also supported, such as COUNT, SELECT AS, and GROUP_CONCAT statements. Please see the API docs for an enumeration of the types of queries that can be performed.
To count the number of rows:
| Code Block |
|---|
SELECT count(*) FROM syn3079449
|
To select and rename a subset of columns for use in Wiki widgets:
| Code Block |
|---|
SELECT age AS "Age at Diagnosis", gender AS "Gender" FROM syn3079449
|
To find out how many distinct treatment arms were studied, by gender:
| Code Block |
|---|
SELECT count(distinct(treatmentArm)) AS "Number of Treatments", gender FROM syn3079449 group by gender
|
To list out the distinct treatent arms that were studied, by gender:
| Code Block |
|---|
SELECT GROUP_CONCAT(distinct(treatmentArm) SEPARATOR ', ') AS "Available Treatments", gender as "By Gender" FROM syn3079449 group by gender
|
Using Table Facets
The faceted navigation on Tables (also known as simple search) can be used to simplify your search without having to use SQL-like queries. Simple search uses radio buttons and sliders to show all available facets in a menu to the left of the Table whereas advanced search employs a SQL-like query to filter the Table. To use table facets, navigate to a Table or a File View. Simple and advanced search both allow you to query for features of interest in aTable using different methods.
Set Facets
In order to use simple search, you must first set columns to be facets in the schema editor. Select Schema in the upper right of your table and click on Edit Schema. In the resulting pop-up, select Values or Range from the dropdowns under the Facet option. Values can be thought of as categories whereas Range is a date or number.
...
Note: If you change Column Type in the schema, you have to set its facet selection again.
To see all the facets, click on Show simple search found above the SQL-query bar:
...
Select the features you are interested in to filter the table.
...
Toggling between Simple and Advanced Search
You can toggle from the simple search to the advanced search without losing the query results. For example, if in the simple search you had selected treatmentArm A, age of 23:64, and gender as female, the query will be preserved in the advanced search bar. However, this is unidirectional because the advanced search allows parameters that are not available with facets. Therefore switching from advanced to simple search will result in resetting the search query.
Note: The slider for range in simple search is inclusive.
...
Warning: When toggling back to simple search, the query will be reset.
...
Working with Files in a Table
Synapse Tables support a special column type called File which contain a file handle, an identifier of a file stored in Synapse. Here’s an example of how to upload files into Synapse, associate them with a table and read them back later.
First, add a new column for files in the table we’re currently working with. To add columns, click on the Schema button. From there, select the Edit Schema button and then add columns using the Add Column button located at the bottom of the pop-up and set the Column Type as File.
...
Next, retrieve the most current table and save as a data frame. Click Save to save your latest schema.
...
Next, upload the files; click on the Edit icon to the right of the Query button. In the resulting pop-up, you can upload files by clicking the Upload icon then Browse and selecting the file from your local directory. Save the new table.
...
Finally, query the table and download the album cover files. Clicking on any file will download it.
...
See Also
Annotations and Queries, Downloading Data, Files and Versioning
...
Column Type | Select the type of data that will be entered into this column. For detailed descriptions of each column type, see the REST docs. |
Size | For certain column types, such as String or Link, you must specify the maximum size of a single value. The default value is 50 characters, but you can limit the maximum size to be between 1 and 1000 characters. For column types that are lists (such as StringList or IntegerList), this number specifies the character maximum for all values in the column. |
Max List Length | For some list column types (such as StringList or IntegerList), you must specify a maximum list length. This number describes the maximum number of values that can appear in your list. For example, a Max List Length of 3 means that you may enter a list of up to three items. |
Default Value (optional) | Choose a default value to pre-populate in every new row of the column. Leave this field blank if you do not want to specify a default. |
Restrict Values (optional) | If you want to restrict the values for a particular column, enter the list of allowed values to create a dropdown menu. You can then select entries from this menu when adding table rows. Leave this field blank if you do not want to restrict the values. |
Facet | Select columns to be included in a faceted search to the left of your table. Choose Values to filter from a list of all possible entries for this column. Choose Range to filter with a slider, which is recommended for numeric values. Select a blank field to remove this column from the faceted search. |
After you create an empty table, select Table Tools and Upload Data to Table to upload data from a .csv or .tsv file. The first line of your file must match the table structure specified.
Alternatively, you may add rows and table data manually. To add, delete, or modify existing rows, click on Bulk Edit Table Cell Values (pencil icon) to edit rows.
...
Click the + sign to add rows. To delete rows, check the boxes of the rows and click the Trash Can icon.
Modifying a Table Schema
Select Table Tools, Show Table Schema, and then select the Edit Schema button to modify the existing table structure. From Edit Schema, you can delete columns, add new columns, and modify existing columns.
Searching a Table
You can search for data within a table in two ways. The default search is a simple search menu to the left of your table. Use the facets to filter your dataset and narrow down your search. Table data can also be retrieved by using a SQL-like query language either through the web portal or through the analytical clients.
See Querying Tables, Views, and Datasets for more information.
Deleting a Table
To delete the entire table, click on the Table Tools menu and then select Delete Table. If you do not see this option, you do not have permission to delete the table. Contact an administrator for the project to get permission.
Learn more about permissions at Sharing Settings, Permissions, and Conditions for Use.
Adding Files to a Table
In addition to structured data, you can also add individual files to a table in Synapse. In the example below, this feature is used to add image files to a table containing histology data.
First, navigate to a table and add a new column for files. To add columns, click Table Tools, Show Table Schema, and then Edit Schema. From the pop-up window, click the Add Column button and set the Column Type as File. Click Save to exit from the Edit Columns window.
Next, click the Edit Query Results button (the pencil icon). In the column you just created, click the upload icon to add a file from your local computer.
...
Versioning a Table
You can create a version history for any table in Synapse. Versioning helps you keep a record of what changes you made to the table and when you made them.
For more information on versioning a table, see Versioning Tables, Views, and Datasets.