<html><head><meta http-equiv="Content-Type" content="text/html charset=windows-1252"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">hi WIll, <div>Could you please check the DBI->connect statement<div>I have an error message, I suspect the dbname is not correct, I used variation as dbname.</div><div><br></div><div>error message</div><div>******</div><div>DBI connect('dbname=variation;host=<a href="http://ensembldb.ensembl.org">ensembldb.ensembl.org</a>;port=5306','anonymous',...) failed: Unknown database 'variation' at ./start_test2.pl line 11.</div><div><div>******</div><div><br></div><div>my statement</div><div>*****</div><div>my $geno_dbh = DBI->connect( 'dbi:mysql:dbname=variation;host=<a href="http://ensembldb.ensembl.org">ensembldb.ensembl.org</a>;port=5306', 'anonymous', '', {InactiveDestroy => 1, RaiseError => 1, PrintError => 1} ) || die "Database connection not made: $DBI::errstr";</div><div>*****</div><div><br></div><div>thanks</div><div><div>On 7 Oct 2014, at 16:47, Will McLaren <<a href="mailto:wm2@ebi.ac.uk">wm2@ebi.ac.uk</a>> wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On 7 October 2014 16:34, Nathalie Conte <span dir="ltr"><<a href="mailto:nconte@ebi.ac.uk" target="_blank">nconte@ebi.ac.uk</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div style="word-wrap:break-word"><br><div><span class=""><div>On 7 Oct 2014, at 16:00, Will McLaren <<a href="mailto:wm2@ebi.ac.uk" target="_blank">wm2@ebi.ac.uk</a>> wrote:</div><br></span><blockquote type="cite"><div dir="ltr">Hi Nathalie,<div><br></div><span class=""><div>Yes you could use MySQL, though again you may have memory issues (either on the server or client side), and we prefer not to have long running or very large queries running on our public database server.</div><div><br></div><div>mysql -<a href="http://hensembldb.ensembl.org/" target="_blank">hensembldb.ensembl.org</a> -uanonymous -e"SELECT name, somatic FROM variation LIMIT 10;" homo_sapiens_variation_77_38</div><div><br></div><div>should do it (obviously without the LIMIT 10), though without wanting to test this myself I think it would be best to avoid using this.</div><div><br></div><div>You could wrap that statement in a Perl DBI call with {'MYSQL_USE_RESULT' => 1} to avoid killing the server memory, though I imagine it will still take a long time.</div></span></div></blockquote><div><br></div>thanks, would this work to connect to the database in a perl DBI call ?</div><div><div>my $geno_dbh = DBI->connect( 'dbi:mysql:dbname=ensembl;host=<a href="http://ensembldb.ensembl.org/" target="_blank">ensembldb.ensembl.org</a>;port=5306', 'anonymous', {InactiveDestroy => 1, RaiseError => 1, PrintError => 1, MYSQL_USE_RESULT => 1} ) || die "Database connection not made: $DBI::errstr";</div><div>this would need a password for anonymous user?</div></div></div></blockquote><div><br></div><div>There's no password for the anonymous user.</div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div style="word-wrap:break-word"><div><br></div><div><div>my $sql = "SELECT name, somatic FROM variation LIMIT 10 "; </div><div>my $geno_sth = $geno_dbh->prepare($sql);</div><div>$geno_sth->execute() or die "SQL Error: $DBI::errstr\n"; …..</div></div></div></blockquote><div><br></div><div>The {mysql_use_result => 1} should go in your prepare statement, so</div><div><br></div><div>my $geno_sth = $geno_dbh->prepare($sql, {mysql_use_result => 1});<br></div><div><br></div><div>Will</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div style="word-wrap:break-word"><div><div><br></div><blockquote type="cite"><span class=""><div dir="ltr"><div><br></div><div>You shouldn't have issues with memory using the FTP (you could just wget the whole file first before parsing), and you can expect the latest VCF files to appear in the same address structure every Ensembl release. You can find the latest Ensembl release programmatically through our REST API, see <a href="http://rest.ensembl.org/documentation/info/software" target="_blank">http://rest.ensembl.org/documentation/info/software</a></div><div><br></div><div>Will</div><div><div><br></div><div><br></div></div></div></span><div class="gmail_extra"><br><div class="gmail_quote"><span class="">On 7 October 2014 15:45, Nathalie Conte <span dir="ltr"><<a href="mailto:nconte@ebi.ac.uk" target="_blank">nconte@ebi.ac.uk</a>></span> wrote:<br></span><div><div class="h5"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div style="word-wrap:break-word">HI Will, <div>Thanks for your answer.<div>I would like to have programmatic access to this data as this will be saved in a data warehouse containing other data pulled from other databases which would be updated automatically in the future.To manually retrieve the data using ftp may not be sustainable for these reasons - I have tried using FTP but as you predicted , I have issues with memory and this is clearly not optimal. Would a direct sql query to ensembl database work?</div><div>Thanks for any advice </div><span><font color="#888888">Nathalie</font></span><div><div><br></div><div><br><div><div>On 7 Oct 2014, at 13:52, Will McLaren <<a href="mailto:wm2@ebi.ac.uk" target="_blank">wm2@ebi.ac.uk</a>> wrote:</div><br><blockquote type="cite"><div dir="ltr">Hi Nathalie,<div><br></div><div>We wouldn't recommend using the API to retrieve all of the rsIDs; there are >60million and the API is not optimised for retrieving the whole dataset in this way.</div><div><br></div><div>Instead I'd recommend you extract the IDs from one of our dump files; probably VCF or GVF would be the easiest to work with:</div><div><br></div><div>curl <a href="ftp://ftp.ensembl.org/pub/release-77/variation/vcf/homo_sapiens/Homo_sapiens.vcf.gz" target="_blank">ftp://ftp.ensembl.org/pub/release-77/variation/vcf/homo_sapiens/Homo_sapiens.vcf.gz</a> | zcat | grep -v # | cut -f 3 | head<br></div><div><br></div><div>(remove the head and redirect to a file to get all of them).</div><div><br></div><div>The somatic mutations are in a separate file, <a href="ftp://ftp.ensembl.org/pub/release-77/variation/vcf/homo_sapiens/Homo_sapiens_somatic.vcf.gz" target="_blank">ftp://ftp.ensembl.org/pub/release-77/variation/vcf/homo_sapiens/Homo_sapiens_somatic.vcf.gz</a></div><div><br></div><div>To answer your question, to fetch somatic mutations use fetch_all_somatic() (see <a href="http://www.ensembl.org/info/docs/Doxygen/variation-api/classBio_1_1EnsEMBL_1_1Variation_1_1DBSQL_1_1VariationAdaptor.html#a22e69dacdd77542463320a1ef16b151f" target="_blank">http://www.ensembl.org/info/docs/Doxygen/variation-api/classBio_1_1EnsEMBL_1_1Variation_1_1DBSQL_1_1VariationAdaptor.html#a22e69dacdd77542463320a1ef16b151f</a>)</div><div><br></div><div>Regards</div><div><br></div><div>Will McLaren</div><div>Ensembl Variation</div></div><div class="gmail_extra"><br><div class="gmail_quote">On 7 October 2014 10:38, Nathalie Conte <span dir="ltr"><<a href="mailto:nconte@ebi.ac.uk" target="_blank">nconte@ebi.ac.uk</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">Hi,<br>
I would like to get all variation ID (ie rs1822893 )from ensembl, I am using the variation API to do so.<br>
Is it the best way? the fetch_all method seems to get all germline variation, is there another method for somatic ones?<br>
<br>
my $vf_adaptor = Bio::EnsEMBL::Registry->get_adaptor('human', 'variation', 'variationfeature');<br>
my @vfs = @{$vf_adaptor->fetch_all()};<br>
foreach my $vf(@vfs){<br>
if ($vf){<br>
my $varID=defined($vf->variation_name) ? $vf->variation_name :'No_variation';<br>
if ($varID) {<br>
print "$varID\n";<br>
}<br>
}<br>
}<br>
<br>
<br>
_______________________________________________<br>
Dev mailing list <a href="mailto:Dev@ensembl.org" target="_blank">Dev@ensembl.org</a><br>
Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev" target="_blank">http://lists.ensembl.org/mailman/listinfo/dev</a><br>
Ensembl Blog: <a href="http://www.ensembl.info/" target="_blank">http://www.ensembl.info/</a><br>
</blockquote></div><br></div>
_______________________________________________<br>Dev mailing list <a href="mailto:Dev@ensembl.org" target="_blank">Dev@ensembl.org</a><br>Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev" target="_blank">http://lists.ensembl.org/mailman/listinfo/dev</a><br>Ensembl Blog: <a href="http://www.ensembl.info/" target="_blank">http://www.ensembl.info/</a><br></blockquote></div><br></div></div></div></div><br>_______________________________________________<br>
Dev mailing list <a href="mailto:Dev@ensembl.org" target="_blank">Dev@ensembl.org</a><br>
Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev" target="_blank">http://lists.ensembl.org/mailman/listinfo/dev</a><br>
Ensembl Blog: <a href="http://www.ensembl.info/" target="_blank">http://www.ensembl.info/</a><br>
<br></blockquote></div></div></div><br></div><div><div class="h5">
_______________________________________________<br>Dev mailing list <a href="mailto:Dev@ensembl.org" target="_blank">Dev@ensembl.org</a><br>Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev" target="_blank">http://lists.ensembl.org/mailman/listinfo/dev</a><br>Ensembl Blog: <a href="http://www.ensembl.info/" target="_blank">http://www.ensembl.info/</a><br></div></div></blockquote></div><br></div><br>_______________________________________________<br>
Dev mailing list <a href="mailto:Dev@ensembl.org">Dev@ensembl.org</a><br>
Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev" target="_blank">http://lists.ensembl.org/mailman/listinfo/dev</a><br>
Ensembl Blog: <a href="http://www.ensembl.info/" target="_blank">http://www.ensembl.info/</a><br>
<br></blockquote></div><br></div></div>
_______________________________________________<br>Dev mailing list <a href="mailto:Dev@ensembl.org">Dev@ensembl.org</a><br>Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev">http://lists.ensembl.org/mailman/listinfo/dev</a><br>Ensembl Blog: <a href="http://www.ensembl.info/">http://www.ensembl.info/</a><br></blockquote></div><br></div></div></body></html>