Accessing Clinvar Table through mySQL

[Gabriel Goncalves]

Hi,

I'm trying to access the ClinVar (clinvarMain and clinvarRaw) tables using the ucsc mysql server (genome-mysql.cse.ucsc.edu) but I'm getting the following results:

>  mysql --user=genome --host=genome-mysql.cse.ucsc.edu -NA

> USE hg19;

> DESCRIBE clinvarMain;
+----------+--------------+----+--+------+--+
| fileName | varchar(255) | NO |  | NULL |  |
+----------+--------------+----+--+------+--+
1 row in set (0.19 sec)

> DESCRIBE clinvarCnv;
+----------+--------------+----+--+------+--+
| fileName | varchar(255) | NO |  | NULL |  |
+----------+--------------+----+--+------+--+
1 row in set (0.18 sec)

> SELECT * FROM clinvarMain LIMIT 5;
+---------------------------------------+
| /gbdb/hg19/bbi/clinvar/clinvarMain.bb |
+---------------------------------------+

1 row in set (0.19 sec)

> SELECT * FROM clinvarCnv LIMIT 5;
+--------------------------------------+
| /gbdb/hg19/bbi/clinvar/clinvarCnv.bb |
+--------------------------------------+
1 row in set (0.18 sec)

I tested with other tables like (COSMIC, snp150, etc) and it's working properly.

> SELECT * FROM cosmic LIMIT 5;
+-----+------+-------+-------+-------------+
| 585 | chr1 | 69344 | 69345 |  COSM911918 |
| 585 | chr1 | 69522 | 69523 |  COSM426644 |
| 585 | chr1 | 69537 | 69538 |   COSM75742 |
| 585 | chr1 | 69538 | 69539 | COSM1343690 |
| 585 | chr1 | 69539 | 69540 | COSM1560546 |
+-----+------+-------+-------+-------------+
5 rows in set (0.18 sec)

Why am I not getting the information from the columns from Clinvar tables?

Att

Gabriel

[Jairo Navarro Gonzalez]

Hello Gabriel,

Thank you for using the UCSC Genome Browser and your inquiry.

The reason you are not getting information from the Clinvar tables is that we use a MySQL table to reference the underlying bigBed file, which contains the data on a different server. All the files referenced by the Genome Browser tables are stored on the /gbdb fileserver and you can find these files at the following location:

http://hgdownload.soe.ucsc.edu/gbdb/hg19/bbi/clinvar/

You can extract the data from these files by using our bigBed utilities, which can be obtained from the utilities directory on our download server. For example, you can use the following bigBed tools to extract information from these bigBed files:

• bigBedToBed
• bigBedSummary

As a reminder, each of our tools can be run without any arguments to get the usage statement, i.e.:

> bigBedToBed
bigBedToBed v1 - Convert from bigBed to ascii bed format.
usage:
   bigBedToBed input.bb output.bed
options:
   -chrom=chr1 - if set restrict output to given chromosome
   -start=N - if set, restrict output to only that over start
   -end=N - if set, restrict output to only that under end
   -maxItems=N - if set, restrict output to first N items
   -udcDir=/dir/to/cache - place to put cache for remote bigBed/bigWigs

You can also use the URL to the bigBed file in our tools. For example, you can run the following command to get the underlying data for the ClinVar CNVs track:

> bigBedToBed -udcDir=. http://hgdownload.soe.ucsc.edu/gbdb/hg19/bbi/clinvar/clinvarCnv.bb clinvarCnv.bed

I hope this is helpful. If you have any further questions, please reply to gen...@soe.ucsc.edu.
All messages sent to that address are archived on a publicly-accessible Google Groups forum.
If your question includes sensitive data, you may send it instead to genom...@soe.ucsc.edu.

Jairo Navarro 
UCSC Genomics Institute

--
You received this message because you are subscribed to the Google Groups "UCSC Genome Browser Confidential Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to genome-www+unsubscribe@soe.ucsc.edu.
To post to this group, send email to genom...@soe.ucsc.edu.