Hello, The location of the file is in here: http://hgdownload.cse.ucsc.edu/goldenPath/hg18/database/
For all databases, the tables in the mySQL database are available as text files in the Downloads area (browser home page -> left blue navigation bar link "Downloads"). One at the main page, there are links by species, down to assembly, followed by a series of directories. The "Annotation Database" link is where the mySQL tables are located. The file names are the same as the table names. Files ending with .sql are the schema and with .txt are the actual data. http://genome.ucsc.edu/FAQ/FAQdownloads#download1 Creating a utility in another programming language (C, shel, perl) to pull data from the text file is one option. Creating a utility that pulls the exact data from the mySQL database is also possible. To do that, your utility would also likely be written in another programming language that incorporates a call to mySQL to fetch data, followed by some parsing. I didn't send this before, but for examples of these kinds of programs, you can examine the utilities in the kent source tree for query examples. This is a list of most commons ones: http://genomewiki.cse.ucsc.edu/index.php/Kent_source_utilities Hope this helps, Jennifer ------------------------------------------------ Jennifer Jackson UCSC Genome Bioinformatics Group ----- "Justin Fincher" <[email protected]> wrote: > From: "Justin Fincher" <[email protected]> > To: "Jennifer Jackson" <[email protected]> > Cc: [email protected] > Sent: Thursday, September 10, 2009 10:58:32 AM GMT -08:00 US/Canada Pacific > Subject: Re: [Genome] building MySQL queries (examples or techniques) > > Thank you so much for your help. I do have a specific question that > I > was hoping you could help me with. I am trying to pull values for > different ranges from the uwNucOccA375 table. From the description, > it seems that I can't pull the individual values for a range, but > must > do it myself from the data file. Is this correct? And if it is, > could you help me with the absolute path for the file designated in > the table? It gives a relative path, but I'm not sure how to > actually > access the file. Thank you again for your help. > > - Fincher > > > On Wed, Sep 2, 2009 at 16:54, Jennifer Jackson <[email protected]> > wrote: > > Hello, > > > > The queries for the Table browser can all be recreated in regular > SQL, but the method used by the Table browser will not always be most > desirable query for a user. However, the schema information provided > by the Table browser will definately be necessary when constructing > your own query. For our example, I am assuming that you are using our > public mySQL server (not Oracle or Sybase which have their own > syntax). > > > > For mySQL syntax, try a google search. There are several good > sites. > > > > To learn the table structure, using the Table browser, bring up the > track of interest. The default table is the primary table. Click on > the view schema button next to the table. This page has three > components. Top = the table schema with examples and data definitions. > Middle = Linked tables, the actual table name and the key (field) to > link between in the "where" clause of your mySQL statement. Bottom = > (For primary tables only) The track description. > > > > The middle tables are interactive, meaning that you can click on > them to view their schema and related tables below. > > > > A general query would be something like this: > > > > select > > table1.field1, > > table3.field2 > > from > > table1, > > table3 > > where > > table1.field3 = table2.field4; > > > > The select and from clauses are required. The where clause is > optional but is where the actual filtering or joining between data > keys occurs. This can get complicated. Then there are all sorts of > other options, "sort by" and such. There are rules for using quotes > and rules for when a "=" should be used (for numbers) rather than an > "equal to" should be used (for non-numbers). Wildcards are possible in > some types of matches. And of course the whole thing can be wrapped up > into a shell, perl, c or other script with the right libraries > installed. > > > > Here is the link to our public server mySQL help page, that includes > some help regarding set up: > http://genome.ucsc.edu/FAQ/FAQdownloads#download29 > > > > If you find yourself stuck with a particular query, we can offer > some help to fine tune or offer advice, > > Thanks, Jennifer > > > > > > ------------------------------------------------ > > Jennifer Jackson > > UCSC Genome Bioinformatics Group > > > > ----- "Justin Fincher" <[email protected]> wrote: > > > >> From: "Justin Fincher" <[email protected]> > >> To: [email protected] > >> Sent: Wednesday, September 2, 2009 11:55:49 AM GMT -08:00 US/Canada > Pacific > >> Subject: [Genome] building MySQL queries (examples or techniques) > >> > >> Pardon if this is a remedial question, but I am just getting > started > >> working with using MySQL queries to extract information from the > >> database. I was wondering if there was a resource with some > example > >> queries or if there was a basic "formula" for understanding how to > >> convert what is done in the table browser to a database query. I > >> only > >> have a little experience with MySQL and am finding browsing > through > >> the tables to be less beneficial than I would hope. Thank you for > >> your help. > >> > >> - Fincher > >> _______________________________________________ > >> Genome maillist - [email protected] > >> https://lists.soe.ucsc.edu/mailman/listinfo/genome > > > > -- > > This message has been scanned for viruses and > > dangerous content by MailScanner, and is > > believed to be clean. > > > > _______________________________________________ Genome maillist - [email protected] https://lists.soe.ucsc.edu/mailman/listinfo/genome
