Repository navigation
Home
adameur edited this page Jul 14, 2016
·
82 revisions
This section describes how to download and install CanvasDB on your local computer. Make sure that MySQL, R and ANNOVAR are running on your computer before starting the installation.
Step 1. Download code from github
$ git clone https://github.com/UppsalaGenomeCenter/CanvasDB.git
$ cd CanvasDB
Step 2. Set the current path to 'rootDir' in canvasDB.R
Use any text editor for this. Change the following line to the path
to the current working directory (i.e. where the code is located):
rootDir <- "/Volumes/Data/canvasDB/" # Path to this directory, where R code is located
Step 3. Set paths to local ANNOVAR installation
On my computer, ANNOVAR is installed under /Volumes/Data/tools/annovar/
Open the canvasDB_config.R file in an editor and set the full paths
to ANNOVAR executables (variable 'ANNOVARpath') and ANNNOVAR hg19
database (variable 'ANNOVARpathDB'). This means changing the two lines:
ANNOVARpath <- "/opt/annovar/annotate_variation.pl"
and
ANNOVARpathDB <- "/Volumes/Data/tools/annovar/humandb/"
Step 4. Create the CanvasDB variant database and annotation database
This step creates databases called 'canvasdb_test' & 'canvasdb_test_annot_hg19'
The annotation database ('canvasdb_test_annot_hg19') will contain general
annotations and can be used by multiple databases containing variation data.
The user ('user') must have administrative privileges for the mysql-server.
$ mysql -u 'user' -p
mysql> create database canvasdb_test;
mysql> create database canvasdb_test_annot_hg19;
Check that the databases are present,
mysql> show databases like 'canvas%';
This should produce something like this:
+--------------------------+
| Database (canvas%) |
+--------------------------+
| canvasdb_test |
| canvasdb_test_annot_hg19 |
+--------------------------+
2 rows in set (0,00 sec)
Quit mysql,
mysql> exit
Step 5. Download hg19 annotation file and load into local database
$ gunzip canvasdb_annot_hg19.sql.gz
$ mysql -u 'user' -p canvasdb_test_annot_hg19 < canvasdb_annot_hg19.sql
Note that this is a substantial amount of data, and the import can take around 1h.
You can then check the imported data:
$ mysql -u 'user' -p
mysql> USE canvasdb_test_annot_hg19;
mysql> SHOW TABLES;
+------------------------------------+
| Tables_in_canvasdb_test_annot_hg19 |
+------------------------------------+
| annot_score |
| annot_snp137_indels |
| annot_snp137_indels_common |
| annot_snp137_single |
| annot_snp137_single_common |
+------------------------------------+
5 rows in set (0,00 sec)
mysql> SELECT * FROM `annot_score` LIMIT 0,5;
+----------------+------+----------+--------+------+------------+-------+
| SNP_id | sift | polyphen | phylop | lrt | mut_taster | gerp |
+----------------+------+----------+--------+------+------------+-------+
| chr1|69091|A|C | 0.06 | 0.82 | 0.01 | 0.29 | 0.98 | -4.04 |
| chr1|69091|A|G | 0.06 | 0.00 | 0.00 | 0.29 | 0.98 | -4.04 |
| chr1|69091|A|T | 0.06 | 0.82 | 0.01 | 0.29 | 0.98 | -4.04 |
| chr1|69092|T|A | 0.24 | 1.00 | 0.53 | 0.29 | 0.99 | 0.79 |
| chr1|69092|T|C | 0.24 | 1.00 | 0.15 | 0.29 | 0.98 | 0.79 |
+----------------+------+----------+--------+------+------------+-------+
5 rows in set (0,04 sec)
mysql> quit
Step 6. Configure .my.cnf file and test RMySQL-package.
In the directory where you downloaded the canvasDB-files, there should be a file
called '.my.cnf'. This file is used by the R-scripts to access login-information
to the canvas-databases.
The user/password used in this step need not be the same user as the one that
created the databases in Step 4, but will need to have read/write permissions on
the mysql-databases created in that step.
Open .my.cnf file in a text editor and modify the MySQL user name and password.
Start R and make sure that the needed R-packages are installed. Type the following:
$ R
> require("RMySQL")
If this doesn't produce something like:
Loading required package: RMySQL
Loading required package: DBI
>
You need to install RMySQL. Please refer to the R-manual for this, but for me,
>install.packages("RMySQL")
usually does the trick. After successful installation, continue with:
> drv <- dbDriver("MySQL")
> con <- dbConnect(drv, default.file = "/Volumes/Data/canvasdb/.my.cnf", group = "canvasdb_test")
> dbGetQuery(con,"SHOW DATABASES LIKE 'canvas%';")
Database (canvas%)
1 canvasdb_test
2 canvasdb_test_annot_hg19
> dbDisconnect(con)
> q()
Step 7. Create empty CanvasDB database from within R
$ R
> source("canvasDB.R")
> initDB()
> q()
Using the mysql-client, check so that the 4 expected tables were created:
$ mysql -u 'user' -p
mysql> use canvasdb_test;
mysql> show tables;
+-------------------------+
| Tables_in_canvasdb_test |
+-------------------------+
| indel_summary |
| runs |
| samples |
| snp_summary |
+-------------------------+
4 rows in set (0,00 sec)
mysql> quit
Step 1. Download a test dataset containing chr21 from the 1000Genomes project
The above link contains SNP and indel data for chr21 from 1092 individuals from
the 1000 Genomes Project, formatted in a way the can be imported into CanvasDB.
$ tar xzf testdata_1000G.tar.gz
Step 2. Download meta data file for test samples
The annotation file '1000g_samples_chr21.txt' contains meta information about the
1000 genomes test data and paths to variant data files. in a way the can be
imported into CanvasDB.
Step 3. Populate database using batchImport() function
The commands below loads all the test data into the new installation of CanvasDB:
$ R
> source("canvasDB.R")
> batchImport("1000g_samples_chr21.txt")
The test data samples for chr21 are named '1000G_1' to '1000G_1092'. Below are
some examples of filtering analyses that can be performed on this test data:
# Ex1: find amino acid changing SNPs present only in '1000G_2'**
> snps <- filterSNPs(c("1000G_2"))
Total time for filtering: 0.635s
> snps
sample_name SNP_id class chr pos ref alt snp137 gene heterozygous sift polyphen phylop lrt mut_taster gerp coverage ref_counts ref_starts alt_counts alt_starts details
1 1000G_2 chr21|40568528|T|C nonsynonymous chr21 40568528 T C rs2234548 BRWD1 0 0.16 1 0.71 0.99 0.02 -0.08 0 0 0 0 0 BRWD1:NM_018963:exon41:c.A6467G:p.K2156R,BRWD1:NM_033656:exon41:c.A6467G:p.K2156R,
# Ex2: Find amino acid changing SNPs present in at least one of the samples '1000G_1' to '1000G_5', but not in any other samples **
> snps <- filterSNPs(c("1000G_1","1000G_2","1000G_3","1000G_4","1000G_5"), minIn=1)
Total time for filtering: 2.059s
> snps
sample_name SNP_id class chr pos ref alt snp137 gene heterozygous sift polyphen phylop lrt mut_taster gerp coverage ref_counts ref_starts alt_counts alt_starts details
2 1000G_3 chr21|33651170|C|A nonsynonymous chr21 33651170 C A rs181198941 MIS18A 0 0.97 0.88 0.99 0.39 0.02 2.46 0 0 0 0 0 MIS18A:NM_018944:exon1:c.G156T:p.W52C,
1 1000G_2 chr21|40568528|T|C nonsynonymous chr21 40568528 T C rs2234548 BRWD1 0 0.16 1 0.71 0.99 0.02 -0.08 0 0 0 0 0 BRWD1:NM_018963:exon41:c.A6467G:p.K2156R,BRWD1:NM_033656:exon41:c.A6467G:p.K2156R,
# Ex3: Find amino acid changing SNPs present in at least one of the samples '1000G_1' to '1000G_5', and at most 2 of the other samples in the database **
> snps <- filterSNPs(c("1000G_1","1000G_2","1000G_3","1000G_4","1000G_5"), minIn=1, maxOthers=2)
Total time for filtering: 0.931s
> snps
sample_name SNP_id class chr pos ref alt snp137 gene heterozygous sift polyphen phylop lrt mut_taster gerp coverage ref_counts ref_starts alt_counts alt_starts details
2 1000G_1 chr21|30698953|T|G nonsynonymous chr21 30698953 T G rs61735774 BACH1 0 0.98 0.55 1 1 0.9 3.99 0 0 0 0 0 BACH1:NM_206866:exon3:c.T808G:p.C270G,BACH1:NM_001186:exon3:c.T808G:p.C270G,
3 1000G_32 chr21|30698953|T|G nonsynonymous chr21 30698953 T G rs61735774 BACH1 0 0.98 0.55 1 1 0.9 3.99 0 0 0 0 0 BACH1:NM_206866:exon3:c.T808G:p.C270G,BACH1:NM_001186:exon3:c.T808G:p.C270G,
4 1000G_45 chr21|30698953|T|G nonsynonymous chr21 30698953 T G rs61735774 BACH1 0 0.98 0.55 1 1 0.9 3.99 0 0 0 0 0 BACH1:NM_206866:exon3:c.T808G:p.C270G,BACH1:NM_001186:exon3:c.T808G:p.C270G,
7 1000G_3 chr21|33651170|C|A nonsynonymous chr21 33651170 C A rs181198941 MIS18A 0 0.97 0.88 0.99 0.39 0.02 2.46 0 0 0 0 0 MIS18A:NM_018944:exon1:c.G156T:p.W52C,
8 1000G_3 chr21|34950728|G|A nonsynonymous chr21 34950728 G A rs141237312 DONSON 0 1 0.97 0.01 0.99 0 5.2 0 0 0 0 0 DONSON:NM_017613:exon10:c.C1586T:p.T529I,
9 1000G_919 chr21|34950728|G|A nonsynonymous chr21 34950728 G A rs141237312 DONSON 0 1 0.97 0.01 0.99 0 5.2 0 0 0 0 0 DONSON:NM_017613:exon10:c.C1586T:p.T529I,
10 1000G_1046 chr21|34950728|G|A nonsynonymous chr21 34950728 G A rs141237312 DONSON 0 1 0.97 0.01 0.99 0 5.2 0 0 0 0 0 DONSON:NM_017613:exon10:c.C1586T:p.T529I,
11 1000G_2 chr21|40568528|T|C nonsynonymous chr21 40568528 T C rs2234548 BRWD1 0 0.16 1 0.71 0.99 0.02 -0.08 0 0 0 0 0 BRWD1:NM_018963:exon41:c.A6467G:p.K2156R,BRWD1:NM_033656:exon41:c.A6467G:p.K2156R,
12 1000G_2 chr21|43327167|T|G nonsynonymous chr21 43327167 T G rs138967523 C2CD2 0 1 1 1 1 1 4.53 0 0 0 0 0 C2CD2:NM_199050:exon9:c.A787C:p.T263P,C2CD2:NM_015500:exon10:c.A1252C:p.T418P,
13 1000G_430 chr21|43327167|T|G nonsynonymous chr21 43327167 T G rs138967523 C2CD2 0 1 1 1 1 1 4.53 0 0 0 0 0 C2CD2:NM_199050:exon9:c.A787C:p.T263P,C2CD2:NM_015500:exon10:c.A1252C:p.T418P,
14 1000G_1008 chr21|43327167|T|G nonsynonymous chr21 43327167 T G rs138967523 C2CD2 0 1 1 1 1 1 4.53 0 0 0 0 0 C2CD2:NM_199050:exon9:c.A787C:p.T263P,C2CD2:NM_015500:exon10:c.A1252C:p.T418P,
1 1000G_1 chr21|45941804|G|A stopgain chr21 45941804 G A rs201663789 TSPEAR 0 0.81 0.89 0.7 1 1 1.96 0 0 0 0 0 TSPEAR:NM_144991:exon9:c.C1528T:p.R510X,
5 1000G_4 chr21|45941804|G|A stopgain chr21 45941804 G A rs201663789 TSPEAR 0 0.81 0.89 0.7 1 1 1.96 0 0 0 0 0 TSPEAR:NM_144991:exon9:c.C1528T:p.R510X,
6 1000G_16 chr21|45941804|G|A stopgain chr21 45941804 G A rs201663789 TSPEAR 0 0.81 0.89 0.7 1 1 1.96 0 0 0 0 0 TSPEAR:NM_144991:exon9:c.C1528T:p.R510X,
15 1000G_5 chr21|46047920|C|T nonsynonymous chr21 46047920 C T rs113691502 KRTAP10-9 0 0.86 0.94 1 0.95 0.73 0.06 0 0 0 0 0 KRTAP10-9:NM_198690:exon1:c.C832T:p.R278C,
16 1000G_163 chr21|46047920|C|T nonsynonymous chr21 46047920 C T rs113691502 KRTAP10-9 0 0.86 0.94 1 0.95 0.73 0.06 0 0 0 0 0 KRTAP10-9:NM_198690:exon1:c.C832T:p.R278C,
17 1000G_227 chr21|46047920|C|T nonsynonymous chr21 46047920 C T rs113691502 KRTAP10-9 0 0.86 0.94 1 0.95 0.73 0.06 0 0 0 0 0 KRTAP10-9:NM_198690:exon1:c.C832T:p.R278C,