Creative Commons License
This blog by Tommy Tang is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

My github papge

Saturday, March 30, 2013

download data from UCSC database to local drive

I wanted to download UCSC refGene table to my local computer.  http://genome.ucsc.edu/goldenPath/help/mysql.html
However, it looks like it is not possible to direct pull data from UCSC to a local host Database.
on linux command line, mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A
SHOW Databases;
USE hg19;
SHOW tables like "%ref%";


+------------------------+

| Tables_in_hg19 (%ref%) |
+------------------------+
| kgXref                 |
| kgXrefOld5             |
| refFlat                |
| refGene                |
| refLink                |
| refSeqAli              |
| refSeqStatus           |
| refSeqSummary          |
+------------------------+
8 rows in set (1.31 sec)

select * from refGene into outfile "/export.txt";
ERROR 1045 (28000): Access denied for user 'genome'@'%' (using password: NO)

I got this error, and I googled it found that
"The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected 

rows to a file. The file is created on the server host, so you must have 
the FILE privilege to use this syntax.

If you want to create the resulting file on some client host other than 
the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you 
should instead use a command such as

mysql -e "SELECT ..." >  file_name 

to generate the file on the client host." https://lists.soe.ucsc.edu/pipermail/genome/2009-August/019892.html


So, instead on linux command line, I typed :
mysql --user=genome --host=genome-mysql.cse.ucsc.edu hg19 -A -sre 'SELECT * from refGene' > ~/Desktop/refGene.hg19



and now it works!

No comments:

Post a Comment