Improve your Galaxy text life: The Query Tabular Tool

Implementation

Although described as a single tool, Query Tabular is comprised of several modules which carry out different functions. These modules use Python applications to read and filter tabular files, and the Python package sqlite to create and query a SQLite database. There are 3 main functions performed within the tool:

The query_tabular.py application can perform all three of the steps above. However, the query can be omitted when the SQLite database is the only desired output. The sqlite_to_tabular.py application only performs the query function given an existing SQLite database as input. This can be useful when one needs to perform several queries on the same database. The filter_tabular.py application performs the line filtering function to directly produce a tabular file. This can be sufficient for simple selection of rows and columns from a single file.

The main Query Tabular tool has been developed in Galaxy under the name “Query Tabular”, which carries out the three functions described above. The other modules within Query Tabular are also available as standalone Galaxy tools, called“SQLite to Tabular”, and “Filter Tabular”,running the sqlite_to_tabular.py and filter_tabular.py applications, respectively. The Query Tabular Galaxy tool provides aweb form for a user to specify input files and settings for line filters, table and column names, and a SQL query. The Galaxy framework makes it easy to link these tools with other software and processing steps, creating multi-step workflows.

Operation

Figure 1 shows a screenshot of the Galaxy-based Query Tabular tool, including an expanded view of the Filtering and Table options within the Galaxy interface. The Query Tabular Galaxy tool loads any number of tabular datasets into a new or existing SQLite database allowing the full power of a SQL query to produce a new tabular output. Long, complicated workflows of Galaxy text manipulation tools can be replaced by Query Tabular in a single step.

Figure 1. The Galaxy user interface for the Query Tabular tool.

A) The main interface for the Query Tabular tool. The user can select the tabular data which acts as input to be converted to a SQLite database table. The interface also provides a field to define the query for the SQLite database that will be carried out, along with options for displaying results from the query in the tabular output. B) Expanded view of the the input filtering function. Any number of line filters can be sequentially applied as the tabular data is being read. Line filters can remove comment lines. They can add columns, either incrementing index, or text. Line filters can also “normalize” list columns within a tabular input line, producing one output line for each item in the list of the selected columns. The Query Tabular tool help section provides examples of using line filters for common situations. In this example, the input tabular data is passed through one line filter: “select columns”, and the user chose to keep data columns 3 and 2 in that order. C) Expanded view of the table options function. In this example, the user named the table: “psm”, and the columns: “Sequence” and “Proteins”. This can make the SQL query much more readable, especially when loading in multiple tabular files and joining the resulting database tables.

The Query Tabular tool provides default names for tables - t1, t2, etc. - and columns - c1, c2, etc. - but a user can specify more specific and meaningful names for tables and columns. We have successfully utilized Query Tabular on large datasets containing millions of rows and columns, where annotating rows and columns becomes important. When column names are specified in the first row of the tabular file, the user has the option to use those names when selecting the columns to be loaded into the SQLite database.

Regex functions, which apply regular expressions, are added to sqlite connections so that re.search, re.match, and re.replace functions are available for use in the SQL query. Line filters can apply regular expressions while reading tabular input files to include, exclude, or modify lines before entering the values as rows in the database table. A column replace line filter can use a regex function to change, for example, a date value to the SQLite recognized format. A normalize filter can convert list fields in the input to first normal form with an individual list item per row; when several fields are specified in a normalization filter, an input line having lists of length n in the specified columns results in n output row, each with one respective pair of values from the specified fields.

Proteogenomics

A common task in a proteogenomics data analysis is to match MS/MS fragmentation spectra to variant peptide sequences, which derive from genomic mutations, expression from genomic regions thought to be non-coding or silenced, or unexpected RNA splicing events¹⁰. The veracity of putative variant sequences matched to MS/MS spectra must be confirmed, which can be accomplished by querying the variant peptide sequences against NCBI’s non-redundant (nr) protein database using the BLASTP tool, which is implemented in Galaxy⁴. Those peptides which do not have a 100% alignment and sequence match to known sequences within the database qualify as verified variant sequences, which are then passed on for further analysis^3,4.

For the purposes of illustrating the simplification offered by Query Tabular, steps from two different workflows for carrying out this analysis of putative variant peptide sequences is shown in Figure 2. The purpose of this workflow is to take as input the peptide spectrum matches (PSMs) containing matches to putative variant amino acid sequences, and analyzes these using BLASTP, producing a list of verified PSMs to true variant sequences. Figure 2A outlines the initial workflow, which contained 9 total steps and required multiple text manipulations with Galaxy tools. The text manipulations format the input tabular file for BLASTP analysis, extracting and re-formatting information from the PSM input. A number of manipulations are also required on the BLASTP alignments: querying the tabular files for peptides with alignment identities less than 100%, those with any gaps in the sequence alignment or those which lacked full-length matching of the known peptides to the putative variant sequence.

Figure 2. A proteogenomics workflow using Query Tabular.

This workflow takes as input peptide spectrum matches (PSMs) of putative variant peptide sequences and further analyzes them using BLASTP to verify sequences which are truly variants compared to the reference proteome. A) The initial workflow comprised of nine total steps, including multiple text manipulation steps in Galaxy; B) The simplified workflow when using Query Tabular, which reduces the number of steps to 4 to obtain the same results. The figure also displays the structure of the tabular input file containing putative variant peptide sequences, and the tabular output file containing verified variant peptide sequences. The inset boxes also show the two SQL queries used in this workflow, made possible by Query Tabular.

When Query Tabular is used, the individual text manipulation steps are not needed, and the number of steps is reduced from 9 to 4 (Figure 2B). We have made this workflow available for demonstration purposes at z.umn.edu/proteogenomicsgateway. Supplementary File 1 provides instructions on accessing and using this workflow.

Metaproteomics

Metaproteomic workflows seek to identify peptide sequences expressed by a community of microorganisms, usually bacteria. These sequences are further analyzed to characterize the taxonomic distributions of the bacteria present in the community; the peptides are also mapped to protein groups which have known biochemical functions, such that the peptides can be indicators of specific functional responses of the community to external perturbations^11,12.

In one established metaproteomics Galaxy workflow⁶, the microbial peptides must be verified by matching to the NCBI nr database, using the BLASTP tool. A number of text manipulation steps are required to make the file of identified peptide sequences compatible with BLASTP. The BLASTP-aligned sequences are outputted in a tabular file, and this file must be further manipulated via several steps in order to create a tabular file in correct format for downstream functional and taxonomic analysis. For the purposes of showing the simplification offered by Query Tabular, Figure 3 shows the steps comprising two different workflows to generate desired results from input files. Figure 3A highlights the numerous manipulation steps required when employing standard text manipulation tools available in Galaxy.

Figure 3. A metaproteomics workflow using Query Tabular.

This workflow verifies the presence of detected microbial peptides by matching peptides against the NCBI nr protein sequence database using the BLASTP tool. (A) Using conventional Galaxy text manipulation tools, the workflow requires 17 steps to achieve desired outputs. (B) When utilizing Query Tabular, desired results are obtained in seven steps. The figure also displays the structure of the tabular input file containing peptide sequences putatively from microbes in the sample, and the tabular output file which contains verified microbial peptide sequences. One output contains peptides between 8 and 30 amino acids (a.a.) in length, while the other contains peptides 30 a.a. or greater in length, due to BLASTP requiring separate analysis based on length of peptide sequences. The two inset boxes show the two SQL queries used in this workflow, made possible by Query Tabular.

Query Tabular greatly simplifies this metaproteomics workflow. As shown in Figure 3B, use of Query Tabular eliminates many of the initial steps required to generate a tabular input compatible with BLASTP. It also greatly simplifies the second part of the workflow where the BLASTP outputs are further manipulated to generate a tabular file which is required for further taxonomic and functional analysis. In all, using Query Tabular reduced the length of the workflow from 17 steps to 7. We have made this workflow available for demonstration purposes at z.umn.edu/metaproteomicsgateway. Supplementary File 1 provides instructions on accessing and using this workflow.

Metabolomics

A Galaxy-based metabolomics workflow provides an example where Query Tabular was used to enable efficient data correction and analysis that was not possible with other existing Galaxy tools. This workflow utilizes VKMZ, a metabolomics tool under development which predicts and plots metabolites from liquid chromatography (LC)-MS data. Metabolite predictions are made by comparing the neutral mass of observed signals to a dictionary of known mass-formulas. When a signal’s neutral mass is within a given mass error range of a known mass, a prediction is made.

For the use-case presented here, targeted metabolomics data were collected on a low resolution LC-MS instrument. Low mass standards in the data, used to provide more accurate mass assignments to observed signals, had a systematic mass shift caused by using an instrument calibration method for high mass molecules. Figure 4 shows the two-part SQL query inputted in the Query Tabular tool and used to correct this shift, operating on the tabular data generated from MS data by VKMZ, which assumes charge (z) is 1. The inner-query determines the average relative mass error for molecules with low mass-to-charge (mz) values (molecular mass <250 Daltons) in the data. The outer-query adjusts all detected molecules within this same mz range by the average mass error. Before making mass adjustment with Query Tabular, VKMZ was able to predict 85.7% of the features for the standards. After the mass adjustment, VKMZ was able to correctly predict all features for the standards. This two-step manipulation, with dependency of the outer-query on the result from the inner-query, is concise and would require generation of a nested, multiple step workflow within the larger workflow if using existing text manipulation tools in Galaxy. We have made this workflow available for demonstration purposes at z.umn.edu/metaproteomicsgateway. Supplementary File 1 provides instructions on accessing and using this workflow.

Figure 4. Example query utilizing the Query Tabular tool for a metabolomics data analysis workflow.

The two part SQL query corrects mass errors in low resolution MS-based metabolomics data, using an inner- and outer-query. The inner-query (lines 9–11) determine the average mass error for mz values of detected molecules below 250 Daltons. The outer-query (all other lines) adjusts all mz values in this range based on the determined mass error. Chromatographic retention time (rt) and signal intensities are also assigned values for the molecules detected by LC-MS.