SQL Query Browser

2024-04-28

Browse and Navigate the Data Model
Show All Columns vs. Columns in the Default Grid View
Validate Queries
Generate Schema Export / Migrate Data to Another Schema

The schema browser is the dashboard for browsing all the database data in a LabKey Server folder. It also provides access to key schema-related functionality. (A schema is a named collection of tables and queries.) Using the schema browser, you can:

Browse the tables and queries
Add new SQL queries
Discover table relationships to help write queries
Define external schemas to access new data
Generate scripts for bulk insertion of data into a new schema

Browse and Navigate the Data Model

To open the Query Schema Browser go to Admin > Developer Links > Schema Browser.

The browser displays a list of the available schemas, including external schemas and data sources you have added. Each schema contains a collection of queries and tables. User-defined queries are grouped together separately from the built-in queries. The image below shows the queries and tables in the issues schema (circled).

Schemas live in a particular folder on LabKey Server, but can be marked as inheritable, in which case they are accessible in child folders. (For more information on controlling schema heritability in child folders, see Query Metadata.)

You can browse column names by clicking on a particular table or query. The image below shows how to discover the column names of the Comments table.

Note that the tables and queries displayed are filtered for your permissions within a folder.

Show All Columns vs. Columns in the Default Grid View

For a particular table or query, the browser shows two separate lists. The first list (labeled All columns in this table) shows all of the columns in the table/query, while the second list (labeled Columns in your default view of this query) shows the columns in the default grid view of the table/query. The second list may contain only a subset of all the available columns, or it may contain columns from other related tables.

Validate Queries

When you upgrade to a new version of LabKey, change hardware, or database software, you may want to validate your SQL queries. You can perform a validation check of your SQL queries by pressing the Validate Queries button, on the top row of buttons in the Query Schema Browser. Validation runs against all queries in the current folder and checks to see if the SQL queries parse and execute without errors.

Generate Schema Export / Migrate Data to Another Schema

If you wish to move data from one LabKey Server schema to another LabKey Server schema, you can do so by generating a migration script. The system will read the source schema and generate:

a set of tab-separated value (TSV) files, one for each table in the source. (Each TSV file is packaged as a .tsv.gz file)
a script for importing these tables into a target schema.

Note that the script only copies data, it does not create the target schema itself. The target schema must already exist for the import script to work. Also, the script must be used as an update script included in a module.

To generate the TSV files and the associated script:

Go the Schema Browser: Admin > Developer Links > Schema Browser.
Click Generate Schema Export.

Select the data source and schema name.
Enter a directory path where the script and TSV files will be written, for example: C:\temp\. Note that this directory must already exist on your machine for the export to succeed.
Click Export.
The file artifacts will be written to the path you specified.

Field Descriptions

Field Name	Description
Source Data Source	The data source where the data to export resides.
Source Schema	The schema you want to export.
Target Schema	The schema where you want to import the data.
Path in Script	Optional. If you intend to place the import script and the data files in separate directories, specify a path so that the import script can find the data.
Output Directory	Directory on your local machine where the import script and the data files will be written. This directory must already exist on your machine, it will not be created for you.

The generated script consists of a series of bulkImport calls that open the .tsv.gz data files and insert them into the target schema, in this case the target schema is 'assaydata'.

SELECT core.bulkImport('assaydata', 'c17d97_pcr_data_fields', 'dbscripts/assaydata/c17d97_pcr_data_fields.tsv.gz');
SELECT core.bulkImport('assaydata', 'c15d80_rna_data_fields', 'dbscripts/assaydata/c15d80_rna_data_fields.tsv.gz');
SELECT core.bulkImport('assaydata', 'c2d326_test_data_fields', 'dbscripts/assaydata/c2d326_test_data_fields.tsv.gz');
SELECT core.bulkImport('assaydata', 'c15d77_pcr_data_fields', 'dbscripts/assaydata/c15d77_pcr_data_fields.tsv.gz');

Now you can re-import the data by adding the generated .sql script and .tsv.gz files to a module as a SQL upgrade script. For details on adding SQL scripts to modules, see Modules: SQL Scripts.