How to mySQL repeatmasker with a list in R

Question

[sebastiz]

I have a list of repeat members and I would like to look up which repeat Family they belong to. I believe I can do this with a mysql query to UCSC but I'm not sure exactly how. So far I have managed to perform a basic UCSC query with

mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A -D hg19 -e 'select chrom,chromStart,chromEnd,name,repClass from nestedRepeats ' > RptDivergence.txt

but how do I add the WHERE clause for a list?

Thanks

[Pierre Lindenbaum]

What you want to do in the 'where' clause?

[sebastiz]

Is it possible to load a text file with the list in it into the where clause?

Thanks

[Pierre Lindenbaum]

NO you can't do that with the UCSC mysql server; you usually create the WHERE clause using something like awk:

awk '{printf(" OR name=\"%s\" ",$1);}' name.txt

or download+import the table+file on a private sql engine and use a sql join statement.

Answer 1

[dariober]

Just a guess of what you want to do:

mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A -D hg19 -e "
SELECT chrom, chromStart, chromEnd, name, repClass, repFamily
FROM nestedRepeats
WHERE name IN ('MLT1E1A', 'L1MC4a')
LIMIT 10"
+-------+------------+----------+---------+----------+-----------+
| chrom | chromStart | chromEnd | name    | repClass | repFamily |
+-------+------------+----------+---------+----------+-----------+
| chr1  |      38255 |    40294 | MLT1E1A | LTR      | ERVL-MaLR |
| chr1  |      71368 |    78235 | L1MC4a  | LINE     | L1        |
| chr1  |     445842 |   447510 | L1MC4a  | LINE     | L1        |
| chr1  |     814631 |   823247 | L1MC4a  | LINE     | L1        |
| chr1  |    1704017 |  1704907 | L1MC4a  | LINE     | L1        |
| chr1  |    1797851 |  1799394 | L1MC4a  | LINE     | L1        |
| chr1  |    2495194 |  2495515 | L1MC4a  | LINE     | L1        |
| chr1  |    3810125 |  3810678 | L1MC4a  | LINE     | L1        |
| chr1  |    4339311 |  4339795 | MLT1E1A | LTR      | ERVL-MaLR |
| chr1  |    4909326 |  4909557 | MLT1E1A | LTR      | ERVL-MaLR |
+-------+------------+----------+---------+----------+-----------+