All tables created within Base are gathered on the Tables page. New tables can be created and existing tables can be updated or deleted here.
To create a new table, click + New table on the Tables page. Tables can be created from scratch or from a template that was previously saved. Once a table is saved it is no longer possible to edit the schema, only new fields can be added. To edit an existing schema, the original schema can be copied as text and pasted into a new empty table where the necessary changes can be made before saving this new table.
To create a table from scratch, complete all fields as indicated below in the following sections and click the Save button. Once saved, a job will be created to create the table. To view table creation progress, navigate to the Activity page.
The table name is a required field and must be a unique name. The first character of the table must be a letter followed by letters, numbers or underscores. The description is optional.
Including or excluding references can be done by checking or un-checking the Include reference checkbox. By including references, additional columns will be added to the schema (see next paragraph) which can contain references to the data on the platform:
- data_reference: reference to the data element in the Illumina platform from which the record originates
- data_name: original name of the data element in the Illumina platform from which the record originates
- sample_reference: reference to the sample in the Illumina platform from which the record originates
- sample_name: name of the sample in the Illumina platform from which the record originates
- pipeline_reference: reference to the pipeline in the Illumina platform from which the record originates
- pipeline_name: name of the pipeline in the Illumina platform from which the record originates
- execution_reference: reference to the pipeline execution in the Illumina platform from which the record originates
- account_reference: reference to the account in the Illumina platform from which the record originates
- account_name: name of the account in the Illumina platform from which the record originates
In an empty table, users can create a schema by adding a field for each column of the table and defining it. The + Add field button can be found in the upper, right-hand corner of the schema. At any time during the creation process it is possible to switch to the ‘edit as text’ mode and back. The text mode shows the JSON code, whereas the original view shows the fields in a table.
Each field requires:
- a name – this has to be unique
- a type
- String – collection of characters
- Bytes – raw binary data
- Integer – whole numbers
- Float – fractional numbers
- Numeric – any number
- Boolean – only options are “true” or “false”
- Timestamp - Stores number of (milli)seconds passed since the Unix epoch
- Date - Stores date in the format YYYY-MM-DD
- Time - Stores time in the format HH:MI:SS
- Datetime - Stores date and time information in the format YYYY-MM-DD HH:MI:SS
- Record – has a child field
- Variant - can store a value of any other type, including OBJECT and ARRAY
- a mode
- Required - Mandatory field
- Nullable - Field can have no value
- Repeated - Multiple values are allowed in this field (will be recognized as array in Snowflake)
- it is also possible (but not required) to indicate whether the value of the field matches with a database id from the drop-down list. This will create a hyperlink that will bring you to the database item.
Users can create their own template by making a schema (by creating a new empty table) and clicking “Save as template”. An overview of all saved table templates in your account can be found on the Base Management page.
If a template is created and available/active, it is possible to create a new table based on this template. The table information and references follow the rules of the empty table but in this case the schema will be pre-filled. It is possible to still edit the schema that is based on the template.
The status of a table can be found on the Tables page. The possible statuses are:
- Available: Ready to be used, both with or without data
- Pending: The system is still processing the table, there is probably a process running to fill the table with data
- Deleted: The table is deleted functionally; it still exists and can be shown in the list again by clicking the “Show deleted tables” button
- Tables created from empty data or from a template are “Available” faster.
- When copying a table with data, it takes longer. These can remain in a “Pending” state for longer periods of time.
- Clicking on the page's refresh button will update the list.
For any available table, the following details can be found:
- Table information: Name, description, number of records and data size
- Schema definition: An overview of the table schema, also available in text. Fields can be added to the schema but not deleted. For deleting fields: copy the schema as text and paste in a new empty table where the schema is still editable.
- Preview: A preview of the table for the 50 first rows (when data is uploaded into the table)
- Data: the files that are currently uploaded into the table.
From within the details of a table it is possible to perform the following actions related to the table:
- Copy: Create a copy from this table in the same or a different project. In order to copy to another project, data sharing of the original project should be enabled in the details of this project. The user also has to have access to both original and target project.
- Export as file: Export this table as a CSV, JSON or PARQUET file. The exported file can be found in a project where the user has the access to download it.
- Save as template: Save the schema or an edited form of it as a template.
- Add data: Load additional data into the table manually. This can be done by selecting data files previously uploaded to the project, or by dragging and dropping files directly into the popup window for adding data to the table. It’s also possible to load data into a table manually or automatically via a pre-configured job. This can be done on the Schedule page.
- Delete: Delete the table.
Base Table schema definitions do not include an array type, but arrays can be ingested using either the
Repeatedmode for arrays containing a single type (ie, String), or the