Creating database tables
Database tables are SQL tables that are stored within the workspace database. Any tabular data, e.g. a CSV file, can be converted to a database table.
There are multiple benefits to doing this. Metadata can be added to a table to ensure that it follows the FAIR data principles. Built-in tools are also available for working with the datasets, such as a point and click SQL for joining tables, rapid data visualisation functionality and a table editor. To find out more about these tools, see articles in the Managing data section.
Creating a new database table
To create a new table from scratch within your workspace, go to the Database tab and select ‘New database table’. Name the new table, click ‘Create table’ and an Add Column window will open.
You can either close the window or add columns by typing a column name and its data type.
Converting a CSV to a database table
Database tables can be easily created within the workspace by converting a CSV file. From the Files tab, choose a CSV file and select the option 'Convert to table' in the option menu. A New table tab will open in the workspace.
The top panel in the New table tab gives you the following options to customise how the data is converted to a data table:
- Data table name: the name of the new table.
- Include header row: specifies whether the data file includes a header row. If this checkbox is selected, the items in the first line of the file will be used as column names in the data table.
- Delimiter: the character used as the delimiter to separate data items in the file. Text qualifier: the character used to distinguish where the text begins and ends. The default is a double quote.
- Null qualifier: the string used to represent null values in the file. Such data items will appear as no value fields in the table. Example null qualifiers: NULL, null, 0.
- Encoding: the character encoding used in the file. The default is UTF-8.
At the bottom of the New table tab, the preview shows the first 10 rows of the data table. The preview automatically updates the data depending on the parameters set.
The panel above the preview lets you edit the header:
Column names must satisfy the restrictions outlined below, otherwise, the field will be highlighted in red, indicating an error. Column name requirements:
- must start with a letter
- can only contain lowercase letters, numbers and underscores
- must be less than 65 characters
- cannot be a reserved SQL word (e.g. GROUP)
Data types of the columns (selected from a dropdown list). Data tables currently support the following data types: text, integer, decimal, boolean, date, datetime, datetime with time zone, time, time with time zone.
The primary key of the table (more information about the primary keys in the next section).
Which columns to exclude from the table (done by clicking the checkbox beside the column name).
Below is an example of a new dataset conversion from a CSV file. Airport name was selected as the primary key of the table, and iata column was excluded from the table.
When happy with the data preview, you can select the ‘Confirm’ button which closes the current tab and opens a new Data Preview tab. For larger data tables, you will see a progress bar at the bottom. Once the table is fully uploaded, you can access the data as normal. Next, you might also want to add metadata to your newly created table.
Workspaces do not enforce a Primary Key on all tables which are created using this method. By default, a new column is added to all tables in the New table tab called id. This column is an index which is the default Primary Key for any new table.
The Primary Key can easily be changed at this stage by selecting another column which has no duplicate values in the first 10GB. Note, if another column is selected as the Primary Key, the auto-generated index column is no longer added to the table.
For all file to data table conversions, a report is generated. This report is accessible from the Activity tab as well as from the Activity section for the table, which can be found in the sidebar of any successfully created data table.
If the workspace could not create the data table, a report is generated and shown in the Activity tab. This report contains a list of the errors encountered while trying to create the data table, including a reference to the line or character whenever possible.
The image below shows an example report which indicates that the table creation failed because the primary key column contained duplicate values.
I can't find the 'Convert to table' button
This action is only available for files ending in .csv (or .CSV). Make sure that you are trying to make a conversion from a CSV file.
My data table is taking a long time to show up
Large tables can take quite a few minutes to convert. This is longer when you choose to exclude certain columns from their table or when multiple users are carrying out the action at once.