Database Queries

Querying the Database and Exporting Results

This guide explains how to execute database queries in GLUE and export the results. We'll cover how to use the list command for core and custom tables, specify conditions with the where clause, and configure console output settings for exporting data.

1. Listing Data Using the list Command

The list command retrieves data from GLUE's core tables or custom tables defined within a project. The basic structure is as follows

list <tableName> <field1> <field2> ... [options]

Example

In the Dengue-GLUE project, the following command lists the sequence ID, source name, and genotype of sequences from a specific source:

list sequence sequenceID source.name genotype -w "source.name = 'ncbi-nuccore-short'"

Options for the list Command:

• -w "<whereClause>" : Applies filters (explained below).
• -p <pageSize> : Defines how many results to display per page.
• -l <fetchLimit> : Limits the total number of records fetched.
• -o <fetchOffset> : Skips a number of records.
• -s <sortProperties> : Sorts results by the specified field(s).

Using the where Clause

The where clause specifies conditions to filter results. It must be enclosed in double quotes (" "). If filtering by a text field (e.g., varchar type), values must be enclosed in single quotes (' '), within the double-quoted clause.

Example:

To list sequences from the ncbi-nuccore-short source:

-w "source.name = 'ncbi-nuccore-short'"

Command Completion for Table Fields

You can use command completion in the GLUE console to see the fields that can be listed. Type the list command followed by the table name, and press Tab for auto-completion of fields.

GLUE> list sequence 

Listing Custom Table Rows

Custom tables extend the core database schema to fit the needs of a specific project. Rows from these tables can be listed similarly to core tables.

For example, to list rows from the isolate_data custom table:

list custom-table-row isolate_data

You can also retrieve custom table rows when querying core tables, linking fields via a period (.):

list sequence sequenceID source.name isolate_data.country isolate_data.collection_year

Note: Command completion does not list available custom table row fields when listing fields from core tables.

2. Exporting Results

GLUE allows you to export query results in various formats. You can set the output format and specify whether to export to a file or display results in the console.

Configuring Console Output

Use the console set commands to adjust how output is handled. For example, you can set the output format for commands:

console set cmd-output-file-format tab

This specifies that the next command's output will be tab-delimited. Other options include csv, json, text, and xml.

console set cmd-result-format text

To output results directly to a file, use:

console set next-cmd-output-file denv-genotypes-fragment-seqs.tsv

Now, the next executed command will save its results to the file specified.

Example Interaction

GLUE> console set cmd-output-file-format tab
GLUE> console set next-cmd-output-file denv-genotypes-fragment-seqs.tsv
GLUE> list sequence sequenceID source.name genotype isolate_data.country isolate_data.collection_year  -w "source.name = 'ncbi-nuccore-short'"

In this example, the output is saved in tab-delimited format to denv-genotypes-fragment-seqs.tsv.

3. Understanding the Output Format

The output format can be set for both the console and export files. For console output, the text format generates a human-readable table, as shown below:

The output format can be set for both the console and export files. For console output, the text format generates a human-readable table, as shown below:

+============+====================+==========+==========+===============+
| sequenceID |    source.name     | genotype | country  | collection_year |
+============+====================+==========+==========+===============+
| A91810     | ncbi-nuccore-short | 2        | -        | 2005          |
| AB003090   | ncbi-nuccore-short | 1I       | Japan    | 1998          |
+============+====================+==========+==========+===============+

When exporting to a file, the format (e.g., csv, tab) will affect how fields are separated, enabling easier downstream use in other tools.

---

Summary

• Use the list command to query both core and custom tables, applying filters with the where clause.
• Set up console output for readable display or exporting results in formats like csv or tab.
• Leverage custom tables and period notation to link data across the database schema.