Getting Ucsc Data Via Mysql

Question

[jvijai]

This may be a completely naive question. But having seen multiple questions answered in a flash using the tables generated by Pierre (http://bit.ly/ibz71P)and others by querying UCSC using sql, I just had to ask this one. There was even mention of using DAS server by Pierre in one thread(http://bit.ly/hKDlr0). However, it was not explained in detail since the OP was also a very advanced user. Yes, it is very neat, super cool, but will you be kind to explain it?

How do I set up mySQL to get UCSC data, for e.g. like the coordinates of genes, or other annotations. The UCSC Help page is somewhat useful, but lacks the starting point of configuring it.

Would be very helpful to see a short tutorial including the installation of mySQL, configuration and finally performing a simple query and exporting results. Also realize that this is a quite broad request with no mention of OS or softwares etc.,

Like I mentioned, if this seems like a dumb request, kindly ignore/delete.

Answer 1

[Aaronquinlan]

UCSC has a public MYSQL interface that one can log into. See this link for info on connecting to it. This mitigates the need to have a local instance of the UCSC tables. However, if you plan to build analysis pipelines around these tables, etc., it might be wise to go through the (minimal) pain of setting it up locally.

[jvijai]

I also found this link which shows using perl http://lh3lh3.users.sourceforge.net/ucsc-mysql.shtml

[jvijai]

Managed to install and connect to ucsc and do an example from here! Very happy :)

Answer 2

[Maximilian Haeussler]

Local setup is really not difficult, just takes some space.

First install Mysql, there are tons of tutorials on the web for this, e.g. for Windows http://www.youtube.com/watch?v=oylVvq-3Tfk.

Then use rsync (included in Mac and Linux, part of cygwin.com for Windows), go to your mysql data directory (For Windows: Usually C:\Program Files\MySQL\MySQL Server 5.0\data) and execute a command like this:

rsync -avzP rsync://hgdownload.cse.ucsc.edu/mysql/hg18/ ./hg18/

hg18 is for human. Replace hg18 with other assemblies, e.g. mm9 for mouse or dm3 for drosophila, if you need other databases.

Once the download is finished (beware: hg18 is ~ 830 GB, dm3 is 18 GB), you can start to run mysql commands on the UCSC database by using this database in your commands ("use hg18").

Note: The trailing slashes in the rsync command are important.

(thx to joachim, I would have missed this question)

[jvijai]

Finally did that ! Installed and worked with mysql at UCSC ! Thank you all.

[Thaman]

Did you download 830GB?