Table Browser User's Guide

Contents

Introduction
About the Table Browser databases and tables
Getting started - simple queries
Filtering output by constraining field values
Intersecting data from multiple tables
Correlating data from two tables
Output formats
Video examples of Table Browser queries

Search the Genome Browser help pages:  

Questions and feedback are welcome.

Introduction

The Table Browser provides a powerful and flexible graphical interface for querying and manipulating the Genome Browser annotation tables. Because the Table Browser uses the same database as the Genome Browser, the two views are always consistent.

Using the Table Browser, you can:

This User's Guide is aimed at both the novice Table Browser user as well the advanced user. If you are new to the Table Browser, read the Getting started section to learn about browser basics and try some simple queries. Advanced users may want to proceed directly to the section that addresses a particular area of functionality in detail.

Although the Table Browser provides sufficient flexibility to satisfy the needs of most users, some advanced users may require the ability to run SQL commands directly on the Genome Browser database. UCSC provides two public MariaDB servers: (1) genome-mysql.soe.ucsc.edu (US West Coast), (2) genome-euro-mysql.soe.ucsc.edu (Europe). More information can be found on our MariaDB Access page. Alternatively, the database may be downloaded to a local computer for MariaDB access. See the mirror site documentation for information on setting up a local copy of the database.

About the Table Browser databases and tables

The Table Browser is built on top of the Genome Browser database, which actually consists of several separate databases, one for each genome assembly.

Tables within the databases may be differentiated by whether the data are based on genome start-stop coordinates (positional tables) or are independent of position (non-positional tables).Some output formats and query options are applicable only to positional tables, hence the distinction.

Positional tables

Positional tables contain data associated with specific locations in the genome, such as mRNA alignments, gene predictions, cross-species alignments, and other annotations. Each of the annotation tracks displayed in the Genome Browser is based on a positional table. In some instances, data from other positional and non-positional tables may also be incorporated into the track. Data associated with custom annotation tracks active within the user's Table Browser session are also available as positional tables.

Positional tables can be further subdivided into several categories based on the type of data they describe. Alignment data can be best described by using a block structure to represent each element. Other tables require only start and end coordinate data for each element. Some tables specify a translation start and end in addition to the transcription start and end. Some tables contain strand information, others don't. Most tables, but not all, specify a name for each element. Based on the format of the data described by a table, different query and output formatting options may be offered.

Non-positional tables

Non-positional tables contain data not tied to genomic location, for example a table that correlates a Known Gene ID with a RefSeq accession ID. Some non-positional tables relate internal numeric mRNA IDs to extended information such as author, tissue, or keyword. Some "meta" tables in this category contain information about the structure of the database itself or describe external files containing sequence data.

Getting started - simple queries

In its most basic form, the Table Browser can be used to retrieve a specific subset of records from a track or positional table in a selected genome assembly. The query may be based on a specific position or a set of one or more identifiers.

This section describes the steps required to conduct basic simple data queries using the Table Browser. Once you have mastered the basic Table Browser functionality, refer to subsequent sections for information about generating more complex queries that use filters, intersections, and alternative data output formats.

Simple position-based query

Follow these steps to display a list of records that lie within a specific position in a table:

Step 1. Pick a genome assembly
Specify the genome assembly from which you'd like to retrieve the data by choosing the appropriate organism in the genome list, then selecting the assembly version from the assembly list. Note that the assembly list refreshes each time a different option is selected in the genome list. Assemblies are typically named after the first three characters of an organism's genus and species names.

Step 2. Pick an annotation track
The group list shows all the annotation track groups available in the selected genome assembly. The names correspond to the groupings displayed at the bottom of the Genome Browser annotation tracks page. When a group is selected from the list, the track list automatically updates to show all the annotation tracks available within that group.

Step 3. Pick a table
The table list shows all tables (both positional and non-positional) associated with the currently-selected track. By default, it displays the primary table for the track, i.e. the table containing the data shown in the Genome Browser annotation track. Other tables in the list are linked to the primary table by a common field and may provide supporting data used in constructing the annotation.

Step 4. Pick a genomic region (positional tables only)
By default, the Table Browser region is set to genome, which will display all the data records in the selected table.

Step 5. Display the output
Click the Get Output button to display the results of the query. By default, the Table Browser outputs the data from all fields in the selected table as tab-separated text on the screen. See the Output formats section for information on configuring the query output.

Example:
Here is an example of a simple query that retrieves all the RefSeq Genes records in the position range chr7:26906938-26940301 on the May 2004 human genome assembly.

  1. Select the Human option in the genome list.
  2. Select the May 2004 option in the assembly list.
  3. Select the Genes and Gene Prediction Tracks option in the group list.
  4. Select the RefSeq Genes option in the track list.
  5. Type chr7:26906938-26940301 in the position box (the Table Browser will automatically select the position option button).
  6. Click the Get Output button.

The Table Browser will display the records for the RefSeq accessions NM_005522, NM_153620, NM_006735, NM_153632, NM_030661, and NM_153631.

Batch query using identifiers

In many cases, you may want to retrieve data based on a list of one or more accessions, IDs, or names, rather than querying by genomic position. Many tracks in the Table Browser, such as those in the Genes and Gene Prediction or Variationtrack groups, support identifier queries. The identifier type used in the query must match the kind of identifiers present in the track data, e.g., mRNA accession IDs must be used to query the mRNA table and rsIDs must match those in the dbSNP table.

Follow these steps to display a list of records that correspond to a set of accessions or names entered as query input.

Step 1. Pick the genome assembly, track, and table

Step 2. Select the genome region setting

Step 3. Load the identifiers into the browser
Click the Paste List button to type or paste in the identifiers or the Upload List button to load the data from a file existing on your local computer.

Step 4. Click the Get Output button
See the Output formats section for information about configuring the query output.

Batch query from positions

If you have a list of genomic positions and want to retrieve information about their properties, you can use the Define Regions button to input multiple positions to query a chosen table. In this example, you want to determine the dbSNP rsID names for your list of positions.

Step 1. Select genome assembly and track
To determine dbSNP rsIDs we will be using Human genome hg38 and dbSNP153.

Step 2. Select the define regions button, enter regions
You can find the define regions button under the Define region of interest section. Upload, type, or paste in your regions of interest, making sure they are in the desired 0/1 base notation. They will only be accepted in BED or positional format.

Step 3. Select output format and get output
If you want all data from a table, you need not change the output format from the default. If you want only particular columns from the table, you can change it to selected fields from primary and related tables. Once you hit the get output button, you will be redirected to a column selection page or if you did not change the output format, your output data itself.

Get gene symbols in a query

Follow the example below to obtain gene symbols in your query:

Filtering output by constraining field values

The Table Browser filter option can be used to:

Filtering on fields from a single table

Follow these steps to create a filter on one or more fields in a single table:

Step 1. Select the assembly, track, and region

Step 2. Click the Create button on the filter line

Step 3. Add the filter constraints
One or more of the fields in the currently selected table may be filtered by typing constraints into the corresponding text boxes.

Step 4. Click the Submit button to apply the filter

Once a filter has been created on a table, it will persist for the duration of the Table Browser session or until it has been cleared. Only one filter can exist for a table at a time, but multiple filters may exist in one session if they are applied on different tables. To modify an existing filter, click the Edit button on the filter line. To remove a filter, click the Clear button.

Filtering on fields from multiple tables

A Table Browser filter may include constraints on fields from tables related to the primary table. To create a filter composed of fields from multiple tables:

Step 1. Select the assembly, track, and region

Step 2. Click the Create button on the filter line
Note: If a filter already exists on the table, click the Edit button to modify it or the Clear button to remove it.

Step 3. Select the tables to include in the filter
Scroll down to the Linked Tables section of the page. The tables listed in this section are linked to the selected table by one or more common fields (typically a name, accession, or ID field). Click the boxes in front of the table(s) whose fields you wish to include in the filter, then click the Allow Filtering Using Field in Checked Tables button. The fields of the selected tables will be displayed in the top portion of the page.

Step 4. Add the filter constraints

Step 5. Click the Submit button to apply the filter

Note: In the current implementation of the Table Browser, the selected fields from primary and related tables output format option must be used when including fields from multiple tables in a filter. Check the boxes for all tables in the Linked Tables list on which filter constraints have been applied, then click the Allow Selection From Checked Tables button to include them in the output.

Filter constraints

Strings
Text fields are compared to words or patterns containing wildcard characters. Valid wildcards are i "*" (matches 0 or more characters) and "?" (matches a single character). Each space-separated word or pattern in a text field box is matched against the value of that field in each record. If any word or pattern matches the value, then the record meets the constraint on that field.

Numbers
Numeric fields are compared to table data using an operator such as <, >, != (not equals) followed by a number. To specify a range, enter two numbers (start and end) separated by white space and/or a comma.

Free-form queries
When the filters on individual fields aren't sufficiently flexible, the free-form query text box allows the application of more complex constraints that typically relate two or more field names of the selected table. Valid free-form queries use the syntax of the SQL where clause (using wildcards as defined above).

Free-form queries combine simple constraints with AND, OR, and NOT using parentheses as needed for clarity. A simple constraint consists of a table field name, a comparison operator (see below), and a value: a number, string, wildcard value (see below), or another field name. In place of a field name, you may use an arithmetic expression of numeric field names.

Example:
The following examples show free-form queries applied to the human refGene table).

Intersecting data from multiple tables

It is often interesting to compare the positions of features in different annotation tracks to identify points of overlap. The Table Browser intersection utility can be used to generate various position-based comparisons of track features. Using the intersection utility, you can:

An intersection may be expanded to include additional tables by using the Table Browser custom track feature.

Note: The intersection utility can be used only on positional tables. To generate intersections incorporating data in non-positional tables, use the Table Browser filter utility. See the Filtering on fields from multiple tables section for more information.

Intersecting data from two tables

Follow these steps to configure and generate an intersection between two positional tables:

Step 1. Select the assembly, track, table, and region for the primary table
Note: Only positional tables may be used in an intersection.

Step 2. Click the Create button on the intersection line
Note: If an intersection already exists on the table, click the Edit button to modify it or the Clear button to remove it.

Step 3. Select the secondary track to include in the filter
Select a group in the group list, then select a track from the track list. To view all the tracks available, regardless of group, select the All Tracks option in the group list.

Step 4. Select a combination method
The Table Browser provides two major types of comparisons:

Click the circle in front of a combination method to select it. Only one method may be selected from the two sets of methods. For more information about the individual combination options, see the Intersection Options section.

Step 5. (optional) Select the complement options
Check the box in front of one or both tables to complement the feature data in the The complement options allow you to invert the set of positions covered by one or both tables. For example, if you choose to complement the primary track, any position covered by the that track's features will be considered not covered, and vice versa. This option provides more flexibility in comparing track positions.

Step 6. Click the Submit button to apply the intersection
Once an intersection has been created on a table, it will persist for the duration of the Table Browser session or until it has been cleared. Only one intersection may exist at a time. To modify an existing intersection, click the Edit button on the intersection line. To remove an intersection, click the Clear button.

Intersecting data from more than two tables

The Table Browser intersection utility limits combinations to only two tables. An existing intersection may be expanded to include additional tables by using the Table Browser custom track utility. To create an intersection on multiple tables:

Step 1. Set up an intersection between two tables
See the Intersecting data from two tables section for more information.

Step 2. Save the intersection data in a custom track
See the Saving data as a custom track section for information on generating a custom track. Note: In the current implementation of the Table Browser, you must use the Get Custom Track button on the custom track page to add the custom track to the Table Browser track list.

Step 3. Select the newly-generated custom track
Select the Custom Tracks option in the group list, then select the newly created custom track from the track list.

Step 4. Create an intersection with another track
Follow the steps in the Intersecting data from two tables section to intersect the custom track with another track.

Intersection options

Feature-by-feature comparisons
Some comparisons preserve the primary table's gene and alignment structure, if it exists. For example, if the refGene table (human RefSeq Genes track) is combined with another table using one of these comparisons, the resulting output data will describe exon structure (unless you choose an output format in which the structure is lost). Primary table features are kept or discarded based on the amount of positional overlap with the features in the table underlying the secondary track. The Table Browser offers the following options in this category:

Note: If the primary table has an exon/block structure, only those bases located in exons and/or blocks will be counted.

Base-by-base comparisons
In these combination options, the positions of the primary and secondary table features are compared one base position at a time. When applying base-by-base comparisons, the structure of the primary table is not preserved. For example, if the refGene table (from the human RefSeq Genes track) is compared with a secondary table using these comparisons, the resulting output data will not describe exon structure. Instead, only position ranges will be returned; the exon/block structure, strand, and translation region information will be discarded. The Table Browser provides the following base-by-base combination options:

Note: If the primary table has an exon/block structure, only base positions located in exons and/or blocks will be counted.

Base-by-base complement (NOT)
Before the Table Browser applies a feature-by-feature or base-by-base comparison to the table data, the set of positions covered by one or both tables can be inverted (complemented). When the data set of a table is complemented, any position covered by the table's features in the original data will be considered not covered in the inverted data, and vice versa. This option gives the user more flexibility in comparing table positions.

Correlating data from two tables

The Table Browser Correlation function creates a scatter plot of the data points of two tables as well as provides individual histograms of the data points from both tables. Additionally, it will also show a plot of the Residuals vs. Fitted which can be used to detect non-linearity, unequal error variances and outliers.

The correlation function uses Pearson's correlation, which is optimized to work with continuous data such as wiggle tracks. For tracks that do not have data values such as gene-structured tracks, the data value used in the calculation is 1.0 for bases covered by exons and 0.0 at all other positions in the region.

Due to memory and processing limitations, the number of data points that can be plotted is limited to 300,000,000. The "Window data to" function allows you to smooth out your plot by taking the average of the number of data points specified (defaults to 1). The total number of bases analyzed is independent of the data window. There is currently no way to output the results of the Correlation function.

Output formats

The data resulting from a Table Browser query may be configured in a number of different ways:

The output options available for a specific query may vary depending on the table(s) selected. For example, non-positional table data cannot be organized in a position-based format, but instead may be displayed only in tab-separated format. The Table Browser will automatically update the options on the output format list to show only those available for the current query.

Displaying all fields in a table

To display all the fields of the records in the query output in tab-separated format, select the all fields from primary table option.

Displaying selected fields from one or more tables

To restrict the query output to a subset of the fields in a table, choose the selected fields from primary and related tables option. You will be prompted to pick the table fields to display. Click the box in front of the fields you would like to see in the query output (or click the Check All button to select all the fields), then click the Get Fields button.

To include data fields from other tables linked to the selected table, choose the selected fields from primary and related tables option, then scroll down to the Linked Tables section of the page. The tables listed in this section are linked to the selected table by one or more common fields (typically a name, accession, or ID field). Click the boxes in front of the table(s) whose fields you wish to include in the query output, then click the Allow Selection From Checked Tables. The fields of the selected tables will be displayed in the top portion of the page. Click the boxes in front of the fields that you wish to include in the query output, then click the Get Fields button underneath any of the field lists to generate tab-separated output that includes data from all the selected fields. Note that the Get Fields and Cancel buttons apply globally to all the selected tables, but the Check All and Clear All buttons apply only to the fields listed directly above the buttons.

Displaying sequence (FASTA) data (positional tables only)

To display the genomic sequence underlying the query results, select the sequence option in the output format list. The Table Browser will present you with several options to configure the output display. When you have completed the configuration, click the Get Sequence button. When displaying sequence data for gene prediction tracks, you will also be offered the option to view the protein and mRNA sequence as extracted from the data source in addition to the genomic sequence.

Displaying CDS FASTA alignments (genePred tables only)

The CDS FASTA alignments are created from a Multiple Alignment File (MAF) in combination with a genePred table. The UCSC MAF format stores multiple alignments at the DNA level between entire genomes. You can use the Table Browser to return FASTA alignments of coding regions in nucleotide-space or translated into amino acid-space. However, it is worth noting that the initial MAF files are all created by aligning genomes at the DNA level.

Genome-wide CDS FASTA alignments
Note that when using the Table Browser to fetch CDS FASTA output, it is best to restrict your query to a reasonable-sized position range rather than requesting output from the entire genome. A genome-wide query will take a substantial amount of compute time, and it is likely that your Internet browser will time out and disconnect. If you would like to download genome-wide CDS FASTA output for any of several model organisms, you can do so from the download server.

Creating CDS FASTA alignments using the Table Browser
To display FASTA multiple alignments for the CDS regions of genes, select the CDS FASTA alignment from multiple alignment option in the output format list. In order to see this output format option, you must have a genePred table selected. If you limit your search to a certain position range within the genome (rather than searching the entire genome), the tool will return FASTA alignments for all genes that overlap the position for which you are searching. The Table Browser will present you with a configuration page. On this page, you can select options for your output.

First, select your MAF table. This is the table from which the multiple alignments will be extracted for the CDS regions of your gene track. If you do not know the name of the MAF table that corresponds to the Conservation track, you can find it in the Genome Browser by following these instructions.

Then select any of the following choices:

Finally, from the list of species, select those that you would like included in the FASTA multiple alignment output. Press the "get output" button to view the output.

Explanation of CDS FASTA header format
Whole gene format: geneName_assemblyName peptideLength location

Exon format: geneName_assemblyName_exonNum_totalExons exonLength inFrame outFrame location

Here are the descriptions for each field name:

Explanation of CDS FASTA sequence format
As noted above, the CDS FASTA output files can be in either DNA-space or protein-space.

In some instances, there is a dash ("–") in the sequence portion of the CDS FASTA file. Dashes are used in several circumstances. They indicate missing sequence for the aligning genome, as well as deletions in the aligning genome or insertions in the base genome.

Because the CDS FASTA alignments are based on one reference genome, any amino acids or nucleotides that are not in the reference genome are not displayed. Consequently the peptides shown for aligning genomes are not necessarily the peptide that the gene of the other organism would generate. Any sequence inserted in an aligning genome or deleted in the base genome will not be present in the alignment. We represent this condition with an orange bar in the Genome Browser display, but the CDS FASTA alignments silently ignore this issue.

Nucleotide CDS FASTA sequence:
Consider the example below that shows the FASTA sequence for four species aligned with the first exon of the human gene PLEKHO1 (UCSC Gene: uc001ett.1). Note that the rat (rn4) row is missing the first three nucleotides. This could be due to a lineage-specific insertion between the rat and human genomes, or a lineage-specific deletion between the human and rat genomes. Note also that the Zebrafish (danRer4) row contains only dashes. This could be due to excessive evolutionary distance between the zebrafish and human, missing data in the zebrafish, or independent indels in the region in both species. Sometimes it is helpful to view the Conservation track in the Genome Browser in this area to clarify the exact meaning of the dashes.

>uc001ett.1_hg18_1_6 30 0 0 chr1:148389072-148389101+
ATGATGAAGAAGAACAAcode
>uc001ett.1_panTro2_1_6 30 0 0 chr1:129156502-129156531+
ATGATGAAGAAGAACAAcode
>uc001ett.1_rn4_1_6 30 0 0 chr2:190795892-190795918-
---ATGAAGAAGAGCGGCTCCGGCAAGCGG
>uc001ett.1_danRer4_1_6 30 0 0
------------------------------
>uc001ett.1_oryLat2_1_6 30 0 0 chr11:3404940-3404969-
AGGATGAAGAAAAGCAACCAGAGCAGGCGG 

Amino Acid CDS FASTA sequence:

Saving query results in GTF or BED format (positional tables only)

To format the query results using GTF or BED conventions, select the corresponding option in the output format list. Note that when you select GTF, the table browser translates the output into this format. For tables that lack feature designations, all records are arbitrarily assigned the feature "exon" to conform to GTF specifications. If you select BED format, you will be presented with the option to include and configure a custom track header and options for organizing the data. When you have finished the configuration -- or to accept the default options -- click the Get BED button at the bottom of the window. To understand the name column in the BED format, see this FAQ.

Saving data to a file

By default, the Table Browser displays query results directly in your internet browser window. To redirect the data to a file, type a file name into the output file box before starting the query. The Table Browser will prompt you for the location of this file on your local disk while processing the query.

Saving data as a custom track (positional tables only)

Query output may be saved in a format that can be displayed as a custom annotation track in the Genome Browser. Custom tracks created during a Table Browser session may also be used for subsequent queries and intersections in the same session. For more information on custom tracks, see the Genome Browser User's Guide.

To save query data in custom track format, select the custom track option in the output format list. When the query is executed, the Table Browser will prompt you to customize the track header and configure the record layout of the data. The configuration is optional; the Table Browser automatically sets up a default track configuration. Click the Custom track link for more information on custom track syntax and format.

When you have finished configuring the custom track -- or to accept the default configuration -- click one of the buttons at the bottom of the window to create the custom annotation track.

Displaying query results as Genome Browser hyperlinks (positional tables only)

To examine the records in the query output individually in the Genome Browser, select the hyperlinks to Genome Browser output option. The Table Browser will display a list of one or more hyperlinks corresponding to the individual records in the output data. Click a link to open up the Genome Browser display to the item and position shown on the hyperlink.

Displaying a statistical summary of query data (positional tables only)

To generate a statistical summary of the query output data, the region covered by the query, and the CPU time required to process the query, click the Summary/Statistics button.

Video examples of Table Browser queries

Finding a list of genes in a genomic region

Obtaining coordinate sequences for a gene exon

Finding all the SNPs in a gene

Finding SNPs upstream of a gene

Visit our YouTube channel for more instructional videos.