Import UCSC Annotation Data into Local MySQL Databse

1. Download and install it From here (Just download dmg if you are lazy🙂 )

2. make database called hg19

3. download wanted annotation file from here

download all

wget 'ftp://hgdownload.cse.ucsc.edu/goldenPath/hg19/database/*'

download kgXref (or whatever db you want)

wget 'ftp://hgdownload.cse.ucsc.edu/goldenPath/hg19/database/kgXref*'

4. Create table based on the downloaded sql file

mysql -u myid -p mypasswd < kgXref.sql

5. Insert gzipped txt file into table (You do not need to unzip txt file. Use gzcat or zcat)

gzcat kgXref.txt.gz | mysql -u myid -p mypasswd hg19 --local-infile=1  -e 'LOAD DATA LOCAL INFILE "/dev/stdin" INTO TABLE kgXref;'

6. check your db

mysql -u myid -p mypasswd hg19 -e 'describe kgXref;'
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| kgID        | varchar(255) | NO   | MUL | NULL    |       |
| mRNA        | varchar(255) | NO   | MUL | NULL    |       |
| spID        | varchar(255) | NO   | MUL | NULL    |       |
| spDisplayID | varchar(255) | NO   | MUL | NULL    |       |
| geneSymbol  | varchar(255) | NO   | MUL | NULL    |       |
| refseq      | varchar(255) | NO   | MUL | NULL    |       |
| protAcc     | varchar(255) | NO   | MUL | NULL    |       |
| description | longblob     | NO   |     | NULL    |       |
| rfamAcc     | varchar(255) | NO   | MUL | NULL    |       |
| tRnaName    | varchar(255) | NO   | MUL | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

mysql -u myid -p mypasswd hg19 -e 'select kgID, refseq, description from kgXref;'
                                                                                                                                                           |
| uc022cer.1 |              | Rfam model RF00001 hit found at contig region AL450472.14/87738-87621                                                                                                                                                                                                                                                                                                         |
| uc022cey.1 |              | Rfam model RF00026 hit found at contig region AL683813.10/545-651                                                                                                                                                                                                                                                                                                             |
| uc022cez.1 |              | Rfam model RF00026 hit found at contig region AL591398.2/79459-79356                                                                                                                                                                                                                                                                                                          |
| uc022cfd.1 |              | Rfam model RF01061 hit found at contig region AL033403.1/72536-72619                                                                                                                                                                                                                                                                                                          |
| uc022cff.1 |              | Rfam model RF00066 hit found at contig region AL121875.10/53331-53391                                                                                                                                                                                                                                                                                                         |
| uc022cfk.1 |              | Rfam model RF00100 hit found at contig region AL022720.1/24706-25036                                                                                                                                                                                                                                                                                                          |
| uc022cgh.1 |              | Rfam model RF00001 hit found at contig region L29074.1/109999-109884                                                                                                                                                                                                                                                                                                          |
| uc022cha.1 |              | Rfam model RF00026 hit found at contig region AF282854.4/53205-53101                                                                                                                                                                                                                                                                                                          |
| uc022chb.1 |              | Rfam model RF00670 hit found at contig region AC108171.3/12729-12651                                                                                                                                                                                                                                                                                                          |
| uc022chc.1 |              | Rfam model RF00670 hit found at contig region AC108171.3/12954-12871                                                                                                                                                                                                                                                                                                          |
| uc022che.1 |              | Rfam model RF00670 hit found at contig region AC108171.3/13432-13352                                                                                                                                                                                                                                                                                                          |
| uc022cpe.1 |              | Rfam model RF00026 hit found at contig region AC013734.4/10137-10240                                                                                                                                                                                                                                                                                                          

7. If you want to import bunch of databases starting with ‘knownGene..’

# download all of schema and txt.gz file started with 'knownGene'
wget 'ftp://hgdownload.cse.ucsc.edu/goldenPath/hg19/database/knownGene*'
# using for loop, create databse of all of knownGene*.sql file
for f in knownGene*.sql; do mysql -u id -p yourpasswd hg19 < $f;echo $f; done
# same trick. using for loop, all of file were inserted into database. 
for f in knownGene*.txt.gz; do t=${f%.txt.gz};gzcat $f | mysql -u id -p yourpasswd hg19 --local-infile=1  -e "LOAD DATA LOCAL INFILE '/dev/stdin' INTO TABLE $t;"; done
# t=${f%.txt.gz} means strip .txt.gz extension (It is your table name) 
# Note that double quotation used for the command line option of mysql to pass variable $t

8. Same with GO databases

mysql -u suk -e "create database mygo;"
for f in *.sql; do mysql -u suk mygo < $f;echo $f; done
for f in *.txt; do t=${f%.txt};echo $f;cat $f | mysql -u suk mygo --local-infile=1  -e "LOAD DATA LOCAL INFILE '/dev/stdin' INTO TABLE $t;"; done


Good introduction for Database noob :)(Some examples for the remote access of UCSC MySQL db)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s