GMOD Malaysia 2014/Chado Tutorial

The most recent Chado tutorial can be found at the Chado Tutorial page.

This tutorial uses the AWS AMI ' named ' in the '.

Chado is the database schema of the GMOD project. This session introduces database concepts, and then provides an overview of Chado's design and architecture, and then goes into detail about how to use a Chado database.

SQL

SQL is a standardized query language for defining and manipulating databases. Chado uses it. SQL is supported by all major DBMSs.

FlyBase Field Mapping Tables shows some example SQL that queries the FlyBase Chado database. (Caveat: FlyBase sometimes uses Chado in ways that no other organizations do.)

Will SQL be on the test?

No, we aren't going to teach in-depth SQL in this course but we will use it in examples and show how to write queries in Chado.

You can do basics with Chado without knowing SQL. Many common tasks already have scripts written for them. However, as you get more into using Chado, you will find that a working knowledge of SQL is necessary.

Chado Architecture: Modules

The Chado schema is built with a set of modules. A Chado module is a set of database tables and relationships that stores information about a well-defined area of biology, such as sequence or attribution.

Plus

Module Caveats

All modules are blessed, but some modules are more blessed than others.

The General, CV, Publication, Organism, Sequence and Companalysis modules are all widely used and cleanly designed. After that modules become less frequently used (Stock, Expression, Phenotype, Mage). Also several modules are not as cleanly separated as we would like them to be. Phenotypic data is spread over several modules. Organism and Phylogeny overlap. CMap is all about maps, but it does not use the Map module.

From Jeff Bowes, at XenBase:

As for Chado, we are more Chadoish than exactly Chado. We use the core modules with few changes - feature, cv, general, analysis. Although I prefer to add columns to tables when it is reasonable and limit the use of property tables (too many left outer joins). We use a slightly modified version of the phylogeny module. We have developed completely different modules for community, literature, anatomy and gene expression. If there is a PATO compatible Chado Phenotype solution we'd prefer to go with that. Although, it might cause problems that we have a separate anatomy module as opposed to using cvterm to store anatomy.

Exploring the schema

Rather than simply listing out the modules and what is stored in them, we'll take a data-centric view and imagine what we want to store in our database, then learn the Chado way of storing it.

During this course you'll be working with genome annotation data from MAKER. We'll simplify this and start by considering that we have annotation on chromosomes that we want to store in our database. These are the sort of things we want to store:

chromosomes

genes

gene predictions

tRNAs

BLAST matches

You may have worked with databases in the past where each type of thing you want to store is given its own table. That is, we'd have a table for genes, one for chromosomes, tRNAs, etc. The problem with this sort of design is that, as you encounter new types of things, you have to create new tables to store them. Also, many of these 'thing' tables are going to look very much alike.

Chado is what is a generic schema which, in effect, means that data are abstracted wherever possible to prevent duplication in both the design and data itself. So, instead of one table for each type of 'thing', we just have one table to hold 'things', regardless of their types. In the Chado world these are known as 'features'.

This brings us to the Sequence Module, which contains the central feature table.

Sequence Module

Features

Chado defines a feature to be a region of a biological polymer (typically a DNA, RNA, or a polypeptide molecule) or an aggregate of regions on this polymer. A region can be an entire chromosome, or a junction between two bases. Features are typed according to the Sequence Ontology (SO), they can be localized relative to other features, and they can form part-whole and other relationships with other features.

Within this feature table we can store all types of features and keep track of their type with the type_id field. It's conceivable to store the named value of each type in this field, like 'gene', 'tRNA', etc. but this would be prone to things like spelling errors, not to mention disagreement of the definition of some of these terms.

So solve this, all features are linked to a specific type in a controlled vocabulary or ontology. These are stored in the cv module.

CV (Controlled Vocabularies) Module

The CV module implements controlled vocabularies and their more complex cousins, ontologies.

Controlled Vocabularies

A controlled vocabulary (CV) is a list of terms from which a value must come. CVs are widely used in all databases, not just biological ones. Pull down menus are often used to present CVs to users in query or annotation interfaces.

Ontologies

Controlled vocabularies are simple lists of terms. Ontologies are terms plus rules and relationships betwen the terms. The Gene Ontology (GO) and Sequence Ontology (SO) are the two best known ontologies, but there are many more available from OBO.

Ontologies can be incredibly complex with many relationships between terms. Representing them and reasoning with them is non-trivial, but the CV module helps with both.

CVs and Ontologies in Chado

Every other module depends on on the CV module. CVs and ontologies are central to Chado's design philosophy. Why?

Data Integrity

Using CVs (and enforcing their use as Chado does) ensures that your data stays consistent. For example, in the most simple case it prevents your database from using several different values all to mean the same thing (e.g., "unknown", "unspecified", "missing", "other", " ",...), and it prevents misspellings ("sagital" instead of "sagittal") and typos.

Data Portability and Standardization

If you are studying developmental processes and you use the Gene Ontology's biological process terms then your data can be easily shared and integrated with data from other researchers. If you create your set of terms or just enter free text (egads!), then it will require a lot of human intervention to convert your data to a standard nomenclature so it can be integrated with others.

Using an established ontology when one exists usually involves some compromises, but it greatly increases the usability of your data to others (and to yourself).

Complexity

Controlled vocabularies are not particularly complex - they are just lists of terms. Ontologies, however, can be very complex, as shown by the GO example above. This complexity could be ignored. You could, for example, convert GO to a controlled vocabulary - a very long list of terms. You would still have data integrity and portability, and it wouldn't be as complex.

It also would not be as powerful. Ontologies support reasoning about the terms in them and this can be very useful. With GO, for example, you can ask

Show me all genes involved in anatomical structure development

and get back genes directly tagged with anatomical structure development, plus any genes tagged with any of that term's sub-terms, from organ development to regulation of skeletal muscle regeneration. If you convert GO to just a list of terms, you can no longer answer that question.

The Chado CV Module supports such complex queries with ontologies by pre-calculating the transitive closure of all terms in an ontology. There is a great explanation of transitive closure on the Chado CV Module page. Also see the description of these 3 tables:

Opening our sample database

$ psql chado

Our first example query

Up to this point we've seen how to store features using the feature table as well as rigidly define what types of things they are using the cv module tables. Here is an SQL example of how to query some very basic information about all gene features in our database:

Lists of things are great, but we're going to need to do a lot more with our genomic data than keep lists of features. First, we need to be able to identify them properly. This may seem straightforward, but creating one column per ID type in feature would be a bad idea, since any given feature could have dozens of different identifiers from different data sources. The General Module helps resolve this.

General Module

The General module is about identifying things within this DB to the outside world, and identifying things from the outside world (i.e., other databases) within this database.

IDs in Chado

items defined in other databases, that are used or referenced in this database.

In fact, those two classes of IDs are defined in exactly the same way, in the dbxref table.

In Chado every table (in every module) defines its own private IDs.

Properties

So far we've only seen very basic information for each feature stored. Because the feature table was designed to be very generic and store all feature types, attributes specific to only some types can't be stored there. It wouldn't make sense, for example, to have a column called 'gene_product_name', since that column would be empty for all the features, like chromosomes, that aren't gene products.

These feature-specific attributes are known as 'properties' of a feature in Chado. They are stored in a table called featureprop.

Table: featureprop

featureprop_id

feature_id

type_id

value

rank

You may have noticed that the featureprop table shares a 'type_id' column with the feature table. Properties of features are typed according to a controlled vocabulary just as the features themselves are. This helps to ensure that anyone using the same vocabularies are encoding their property assertions in the same way.

Using feature properties we can now describe our features as richly as needed, but our features are still independent of one another. Because many of the features we're storing have some sort of relationship to one another (such as genes and their polypeptide end-products), our schema needs to accommodate this.

Relationships

Features can be arranged in graphs, e.g. "exon part_of transcript part_of gene"; If type is thought of as a verb, then each arc or edge makes a statement:

Subject verb Object, or

Child verb Parent, or

Contained verb Container, or

Subfeature verb Feature

Again, notice the use of controlled vocabularies (type_id) to define the relationship between features.

One might think of a relationship between a gene and chromosome as 'located_on', and store that as a feature_relationship entry between the two. If you did, pat yourself on the back for Chado-ish thinking, but there's a better way to handle locatable features, since a relationship entry alone wouldn't actually say WHERE the feature was located, only that it was.

Locations

Location describes where a feature is/comes from relative to another feature. Some features such as chromosomes are not localized in sequence coordinates, though contigs/assemblies which make up the chromosomes could be.

Locations are stored in the featureloc table, and a feature can have zero or more featureloc records. Features will have either

one featureloc record, for localized features for which the location is known, or

zero featureloc records, for unlocalized features such as chromosomes, or for features for which the location is not yet known, such as a gene discovered using classical genetics techniques.

Features with multiple featurelocs are explained below.

(For a good explanation of how features are located in Chado see Feature Locations. This explanation is excerpted from that.)

Interbase Coordinates

A featureloc record specifies an interval in interbase sequence coordinates, bounded by the fmin and fmax columns, each representing the lower and upper linear position of the boundary between bases or base pairs (with directionality indicated by the strand column).

Interbase coordinates were chosen over the base-oriented coordinate system because the math is easier, and it cleanly supports zero-length features such as splice sites and insertion points.

Location Chains

Chado supports location chains. For example, locating an exon relative to a contig that is itself localized relative to a chromosome. The majority of Chado instances will not require this flexibility; features are typically located relative to chromosomes or chromosomes arms.

The ability to store such localization networks or location graphs can be useful for unfinished genomes or parts of genomes such as heterochromatin, in which it is desirable to locate features relative to stable contigs or scaffolds, which are themselves localized in an unstable assembly to chromosomes or chromosome arms.

Localization chains do not necessarily only span assemblies - protein domains may be localized relative to polypeptide features, themselves localized to a transcript (or to the genome, as is more common). Chains may also span sequence alignments.

How do we represent these exons, mRNAs, proteins, and there relationships between them?

Table: feature

feature _id

name

uniquename

type_id

is_ analysis

10456

exon 1

genex_exon1

(exon)

false

10457

exon 2

genex_exon2

(exon)

false

10458

exon 3

genex_exon3

(exon)

false

10459

exon 4

genex_exon4

(exon)

false

10460

exon 5

genex_exon5

(exon)

false

10461

exon 6

genex_exon6

(exon)

false

10462

exon 7

genex_exon7

(exon)

false

10463

mRNA-A

genex_mRNA-A

(mRNA)

false

10464

mRNA-B

genex_mRNA-B

(mRNA)

false

10465

protein-p2

genex_protein-p2

(polypeptide)

false

10466

protein-p1

genex_protein-p1

(polypeptide)

false

10467

gene X

genex

(polypeptide)

false

Table: feature_relationship

feature_ relationship _id

subject_id

object_id

type_id

rank

589

(exon 1)

(mRNA-A)

(part_of)

0

590

(exon 2)

(mRNA-A)

(part_of)

0

591

(exon 3)

(mRNA-A)

(part_of)

0

592

(exon 4)

(mRNA-A)

(part_of)

0

593

(exon 5)

(mRNA-A)

(part_of)

0

594

(exon 2)

(mRNA-B)

(part_of)

0

595

(exon 4)

(mRNA-B)

(part_of)

0

596

(exon 6)

(mRNA-B)

(part_of)

0

597

(exon 7)

(mRNA-B)

(part_of)

0

598

(protein-p2)

(mRNA-A)

(derives_from)

0

599

(protein-p1)

(mRNA-B)

(derives_from)

0

600

(mRNA-A)

(gene X)

(part_of)

0

601

(mRNA-B)

(gene X)

(part_of)

0

Example: Computational Analysis

Note: This is example is based on an example by Scott Cain from an earlier Chado workshop.

You can store the results of computational analysis such as BLAST or BLAT runs in the sequence module. Here's an example of you could store a BLAST highest scoring pair (HSP) result.

With HSP results there are two reference sequences, which means two entries in the featureloc table. (The analysis and analysisfeature tables, in the Companalysis module, are used to store information about how the analysis was done and what scores resulted.)

Every horizontal line becomes a record in the feature table, and every vertical line becomes a record in the feature_relationship table.

Other Feature Annotations

Extending Chado: Properties tables and new modules

Chado was built to be easily extensible. New functionality can be added by adding new modules. Both the Mage and the Stock modules were added after Chado had been out for a while. They both addressed needs that were either not addressed, or were inadequately addressed in the original release.

Chado can be tailored to an individual organization's needs by using property tables. Property tables are a means to virtually add new columns without having to modify the schema. Property tables are included in many modules.

Property tables are incredibly flexible, and they do make Chado extremely extensible, but they do so at a cost.

The SQL to get attributes from property tables is much more complex, and

It is more work to enforce constraints on data in properties tables then in regular columns.

Practice

Prerequisites

PostgreSQL

Already installed PostgreSQL 8.4 via apt-get.

Edit config files

sudo su
cd /etc/postgresql/9.1/main/
less pg_hba.conf

At the bottom of the file, we've already changed ident sameuser to trust. This means that anyone who is on the local machine is allowed to connect to any database. If you want to allow people to connect from other machines, their IP address or a combo of IP address and netmasks can be used to allow remote access.

If we'd have edited the pg_hba.conf file, we'd have to (re)start the database server:

/etc/init.d/postgresql restart
exit # to get out of su

Create a gmod user

Normally, we'd have to switch to the postgres user to create a new database user, but the ubuntu user is already a superuser. Create a new user named "gmod":

In the previous course, this was installed by hand, and then in a subsquent course it was pre-installed; now were back to installing it by hand. It is moderately tricky, because it is difficult to install by CPAN when using sudo cpan but is easy to install "by hand" or using cpan.

Installing Chado

This allows you to specify Pythium when you are asked for a default organism.

> "chado" for database name
> "gmod" for database username
> "Pythium" for the default organism
> "public" for the schema Chado will reside in

Just hit enter when it asks for a password for user gmod.

This Makefile.PL does quite a bit of stuff to get the installation ready. Among other things it:

Sets up a variety of database user parameters

Prepares the SQL files for installation

It can rebuild Class::DBI api for Chado, but isn't now (they are prebuilt for the default schema)

All the 'normal' stuff that would happen when running Makefile.PL, like copying lib and bin files to be ready for installation

make
sudo make install
make load_schema
make prepdb

At this point, the installer checks the database to see if the organism we put in as our default organism is present in the database. Since it isn't, it will ask us to add it. It does this by automatically executing a script that comes with Chado, gmod_add_organism.pl. You can execute this script any time you want to add organisms to your database:

You can pick any ontologies you want, but GO will take over an hour to install, and it doesn't matter to much what you pick, because we are going to be blowing this database away soon anyway.

Saving your progress to this point

It is generally a good idea to save your progress when you are done loading ontologies before you've attempted to load any other data. That way, if something goes wrong, it is very easy to restore to this point. To make a db dump, do this:

pg_dump chado | bzip2 -c --best > db_w_ontologies.bz2

The -c tells bzip2 to take input on standard in and spit it out on stdout. To restore from a dump, drop and recreate the database and then uncompress the dump into it, like this:

A Note about Redos

If at some point you feel like you want to rebuild your database from scratch, you need to get rid of the temporary directory where ontology files are stored. You can either do this with rm -rf ./tmp(be very careful about that ./ in front of tmp) or with a make target that was designed for this: make rm_locks, which only gets rid of the lock files but leaves the ontology files in place.

Large files (more than 3-500,000 rows) can cause headaches for the GFF3 bulk loader, but genome annotation GFF3 files can frequently be millions of lines. What to do? The script gmod_gff3_preprocessor.pl will help with this, both by splitting the files in to reasonable size chunks and sorting the output so that it make sense.

Note that if your files are already sorted (and in this case, that means that all parent features come before their child features and lines that share IDs (like CDSes sometimes do) are together), then all you need to do is split your files. Frequently files are already sorted and avoiding sorting is good for two reasons:

It takes a long time to do the sort compared to the split (it parses every line, loads it into temporary tables and pulls out lines via query to rebuild the GFF file)

The sorting process makes it more difficult to read the resulting GFF, since the parent feature will no longer be near the child features in the GFF file. Chado doesn't care about that but you might.

Working with the large number of files that come out of the preprocessor can be a bit of a headache, so I've 'developed' a few tricks. Basically, I make a bash script that will execute all of the loads at once. The easy way (for me) to do this is:

ls *.gff3 > load.sh
vi load.sh

and then use vim regex goodness to write the loader commands into every line of the file:

:% s/^/gmod_bulk_load_gff3.pl --analysis -g /

which puts the command at the beginning every line. The --analysis tells the loader that it is working with analysis results, so the scores need to be store in the companalysis module. While the genes file are not really analysis results, I faked them to look like FgenesH results so that Apollo would have something to work with. Then I manually edit the file to add and remove the few modifications I need:

Add --noexon to the genes file (since it contains both CDS and exon features, I don't want the loader to create exon features from CDS features).

Then I add #!/bin/bash at the top of the file (which isn't actually needed) and run

bash load.sh

and it loads all of the files sequentially. I have done this with 60-70 files at a time, letting the loader run for a few days(!)

Capturing the output to check for problems

If you are going to let a load run a very long time, you probably should capture the output to check for problems. There are two ways to do this:

run inside the screen command:

screen -S loader

which creates a new 'screen' separate from your login. Then execute the load command in the screen. To exit the screen but let your load command continue running in it, type ctrl-a followed by a d (to detach) and you get your original terminal back. To reconnect to the loader screen, type

screen -R loader

capture stdout and stderr to a file

When you run the load command, you can use redirection to collect the stdout and stderr to a file:

There are both CDS and exon features in this file, but
you did not set the --noexon option, which you probably want.
Please see `perldoc gmod_bulk_load_gff3.pl for more information.

Argh! Now the loader is pointing out that this GFF file has both exons and CDS features and Chado prefers something a little different. While the loader will load this data as written, it won't be "standard." Instead, we'll add the --noexon option (which tells the loader not to create exon features from the CDS features, since we already have them). One (at least) more time:

Loading other data

Chado for Expression, Genotype, Phenotype, and Natural Diversity

This section is about some of the lesser used Chado Modules.

Expression

From an organism database point of view, expression is about turning this:

Fig. S5 Synergistic effect of zdia2 sMO and profilin I tMO. Embryos were injected with 8 ng zdia2 sMO (A and B) or co-injected with 4 ng zdia2 sMO and 4 ng profilin I tMO (C and D). Embryos were incubated until tail bud stage (10 hpf), fixed and stained with ntl and gsc riboprobe. Photographs were taken for side view (A and C) and dorsal view (B and D) after WISH.

This would be implemented in the expression module by linking the dpp transcript feature to expression via feature_expression. We would then link the following cvterms to the expression using
expression_cvterm:

embryonic stage 13 where the cvterm_type would be stage and the rank=0

embryonic stage 14 where the cvterm_type would be stage and the rank=1

embryonic stage 15 where the cvterm_type would be stage and the rank=1

cephalic segment where the cvterm_type would be anatomy and the rank=0

in situ hybridization where the cvterm_type would be assay and the rank=0

Translation

In FlyBase, this would be a single expression record, with 5 Ontology/CV terms attached to it.

Spatially distinguishable group. group can be used for distinguishing the chromosomal groups, for example (RNAi products and so on can be treated as different groups, as they do not fall on a particular chromosome).

Table: environment_cvterm

Phenotype, Natural Diversity and Atlas Support

Phenotypes, natural diversity, and atlas support are all areas of future work in Chado. Chado does have a phenotype module, but it has not aged as well as other modules. Its support for natural diversity is limited to what is implemented in the genotype, environment, and phenotype modules. This is not robust enough to deal with studie that are frequently done in the plant community, and are increasingly done in animal communities as well.

To address this there are several efforts currently underway. The Aniseed project includes 4 dimensional anatomy, expression, and cell fate graphical atlases. Aniseed is currently in the process of reimplementing itself to use Chado. This work is likely to lead to contributions back to GMOD (both in Chado and a web interface) to better support these types of atlases.

Better natural diversity support will be added in the coming year. NESCent has developed a prototype natural diversity Chado module based on the GDPDM, that will added robust support for natural diversity data.