Synapse Tables are used to create tabular data that is organize web-accessible, sharable, and queryable data. Table columns have a user-specified, structured schema. Tables may be queried and edited with the Synapse web client, as well as with the Synapse programmatic clients. This article guides you through the process of creating a Table. For information about searching a Table, see Search Tables and Views.
You can define Table columns to contain common primitive data types (like text, numbers, and dates) or , or you can define columns to include references to other Synapse objects, like Files and Folders.Tables may be queried and edited through the web as well as through 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 creating Tables and creating queries using one of our the Synapse programmatic clients, including code examples for the below tasks, see:
Tables in Python Docs.
Tables in R Docs.
...
What is a
...
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 “save-as” a .csv
to proceed.
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 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
...
schema?
A schema is the structure of your dataset. In a Table, a schema defines the structure by specifying the column names, types, and sizes. A schema may also define the values allowed in each column.
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
...
Creating a New Table from a File
Tables require structured data contained in a .csv
or .tsv
file. If your structured data is saved in an Excel format such as .xls
or .xlsx
, you must “save-as” to convert the file to a .csv
before you proceed in Synapse. Navigate to the Tables tab in your Project and select Upload a Table. If you 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 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.
Create 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 set the Column Name, Column Type, Size, Default Value, and Restrict Values.
If you have created an empty Table, you will need to click on “Add Table Tools and Upload Data to Table” Table to upload a data from a .csv
or or .tsv
file, or add rows individually through the web UI. To add 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 the 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 icon to Edit Rows.
...
Click the + sign to add rows. To delete rows, check the boxes of the rows you would like to delete and then clicking click 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.
...
Note |
---|
Warning: Column names must be 256 characters or less. There are three reserved words that cannot be used: 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
...
Modify Table Schema
Click Table Tools, Show Table Schema and Edit Schema to modify the existing Table structure. From Edit Schema, you can delete columns, add new columns, modify existing columns, define default column values, restrict column values, and add facet values.
Search 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 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 See the Search Tables and Views page for more information.
Delete a Table
To delete the entire Table, click on the Tools menu and then select Delete Table.
Add Files to a Table
Synapse Tables support a special column type called File
that contains a file handle, an identifier of a file File stored in Synapse. Here’s an example of how to upload files into Synapse, associate them with a table and read them back laterIn 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 in the table we’re currently working withFiles. To add columns, click on the Schema button. From there, select the Edit Schema button and then add columns using Click Table Tools, Show Table Schema and Edit Schema. From the pop-up window, click 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.
...
Click Save to exit from the Edit Columns window.
Next, click the Edit Query Results button (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 the Versioning Tables and Views page.
See Also
Annotations and Queries, Downloading Data, Files and Versioning
...