This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Installing/Recreating Ucsc Annotation Database Locally

I want to download the rattus norvegicus annotation database so that my program can do lookups quickly and often without pestering the UCSC MySQL server.

Problem is, the download is just very many .sql files and .gz files: http://hgdownload.cse.ucsc.edu/goldenPath/rn4/database/

Eg.

  affyAllExonProbes.sql                      15-Mar-2009 14:44  1.5K  
  affyAllExonProbes.txt.gz                   15-Mar-2009 14:44   13M  
  affyExonTissues.sql                        15-Mar-2009 14:42  1.8K  
  affyExonTissues.txt.gz                     15-Mar-2009 14:42  254M  
  ...

There are no instructions on how to use this to recreate the database locally. Is it possible to do in one, or a few bash commands?

Ps. downloading them all in one go isn't a problem.

ucsc annotation database
• 2,869 views • link
updated by Maximilian Haeussler Maximilian Haeussler • written by Click downvote Click downvote

2 answers

pipe the sql files in mysql

gunzip the *.txt.gz files

the loop over the txt files an invoke 'mysql local load data infile ' http://dev.mysql.com/doc/refman/5.1/en/load-data.html

mysql -u login -p -e "LOAD DATA LOCAL INFILE 'affyExonTissues.txt' INTO TABLE affyExonTissues" -D rr5
• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

Thank you. Will accept when I get to try it out. Much appreciated.

• 0 views • link
written by Click downvote Click downvote

you might have to create the mysql database before everything: mysql -u root -p -e 'create database rr5'

• 0 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

It might be easier to download the raw mysql data files, that's just one rsync command: Assuming that your mysql dataDir directory is /var/lib/mysql the command would go like this: rsync -avzP --delete --max-delete=20 rsync://hgdownload.cse.ucsc.edu/mysql/rn3/ /var/lib/mysql/rn3/

For details see http://genomewiki.ucsc.edu/index.php/Browser_Installation#Download_assembly_database_tables

• 347 views • link
written by Maximilian Haeussler Maximilian Haeussler

Log in to answer this question.

More posts like this