nodalpoint.org - Medline XML to database parser? - Comments

LingPipe's MEDLINE XML parser + DB + search demo

Bob Carpenter — Mon, 09 Oct 2006 18:18:09 -0400

LingPipe (which can be downloaded with source from our site -- see links below) contains a parser for the 2006 MEDLINE distribution. It works through a document-object model, creating Java objects for MEDLINE citations. It covers the entire MEDLINE DTD. We'll update it as soon as the 2007 data replaces the 2006 data; the format changes slightly every year.

We also provide a program that will automatically download MEDLINE from NLM's FTP site. It checks the checksums and retries if necessary. It can be run as a scheduled job to get the daily updates, too; it only downloads what doesn't already match the checksums.

We have a tutorial on using the parser to create a Lucene (search engine) index from the data, and another tutorial on using the parser to create a MySQL DB. The latter includes complete from-scratch MySQL installation instructions.

In our tutorial on MEDLINE, we show how to load data from MEDLINE into MySQL using our parser. It's easily extendible, but if you're looking for something that off-the-shelf that puts all of MEDLINE data into a search engine or DB, this only cherry-picks the most useful information.

The DB tutorial also shows how to run some of our natural language processing tools to do things like break the text into sentences and find references to entities of various kinds (e.g. protein subdomains, malignancies, variations such as SNPs or mutations, organisms, cell lines, etc.)

Here are the links:

LingPipe Home Page (Description and download links)

LingPipe Tutorial: MEDLINE Parsing and Indexing (Download MEDLINE, parse MEDLINE and index MEDLINE in Lucene)

LingPipe DB Text Mining Tutorial (Parse MEDLINE, load MEDLINE into MySQL and run natural language text mining tools over MEDLINE)

This is quite late reply

smarko — Fri, 14 Jul 2006 08:31:07 -0400

This is quite late reply from me, but I wanted to add that I wasn't referring to Sedna with that "Usual weaknessies" -list, but to some others yes. I've never had out of memory problems with Sedna, for example.

XML database performance

Maxim — Mon, 19 Jun 2006 07:37:33 -0400

Hi, I'm a Sedna developer.

Marko, thank you for reporting on Sedna!

Theoretically, Sedna can store data sets of unlimited size. Practically, we have tested it successfully with 150 Gb of XML data as a single document (!) and as collections of documents.

As concerns speed of query execution, it should be ok for 50+ Gb if you configure indexes properly. Sedna is not too fast for data loading but it usually works very good for querying. It would be interesting to find out why Marko found Sedna awfully slow for indexing and querying.

Errors like "out of memory" might happen but they are bugs (not fundamental Sedna limitations) which we will fix if you contact us. We are always avaliable for support, questions and bugfixes.

XML database performance is

FiReaNG3L — Fri, 16 Jun 2006 14:13:31 -0400

XML database performance is very meh I think, and I need split second fast queries. Right now I'm having fun with PHP::SimpleXML. It loads XML as an object in memory and then you can do fun things like foreach ($xml->MedlineCitation as $citation) { do stuff here }. Works wonderfully. 30000 records inserts in mySQL in 25 seconds... so it should take 5-10 hours loading the entire thing in. And then maybe a day to index it?

Sedna, a native XML database

smarko — Thu, 15 Jun 2006 17:07:01 -0400

I guess I could add my experiences with Medline database here, too. My website's (ebounce.info) PubMedTool -tool is currently using E-utilities offered by NCBI for querying and fetching data, but offline I've been making tests with native XML databases.

Most of the native XML databases practically choke, because of the huge amount of data. Sedna is one of those, which I found to handle things nicely. I mean, if one doesn't require immediate results and have time to wait for few minutes.

Usual weaknessies, which become clear in first 5 minutes:
- out of memory errors
- not possible to join databases or query multiple databases at the same time
- (awful) slowness of indexing of xml-files and actual querying
- ..

So far, I've mostly been using Sedna for playing with XQueries. However, Sedna doesn't support all the XQuery-functions yet, but so far it hasn't been a big problem. Also, while learning more about Sedna, I've started to miss different kind of indexing methods, XSLT support and few other things. It does have a API for few languages (C, Java, Python..).

Sedna is a good choice, if you don't need to query Medline in full, but maybe just last 10 years or so. Otherwise you'd get bored of waiting. And for querying smaller databases like MeSH (240Mb), it is a really good choice.

I've heard that Berkeley DB XML might also be worth a try, so I'll try it next. Again. Earlier I had some problems with it (I've already forgot, what it was).

PyLucene

alf — Sun, 11 Jun 2006 20:21:32 -0400

I have a Lucene index of the MEDLINE data, created with PyLucene - it's what HubMed uses for relevance-ranked searches. If you still want some help with the indexing let me know.

XML looks easy

FiReaNG3L — Fri, 09 Jun 2006 22:38:06 -0400

It's for my next web project (as biologynews.net is kinda finished, for now). I'm building something useful (I hope) for the biologists community. Still in the preliminary stages, but big databases don't scare me :) It could take time to build as I'm still busy with my PhD (wetlab and all, microarrays and HIV, tons of fun) and can only work by night / weekends on it. My goal is to have something functional by the end of the year.

Mapping XML2RDB

Neil — Fri, 09 Jun 2006 08:32:56 -0400

There are a few Perl tools around for parsing XML and mapping it to a RDB. XML.com is a good place to start.

Are you sure that you really want to do this? 50 GB of Medline XML sounds like a living hell to me. I assume this is some sort of text mining effort?

about the file size

lindenb — Wed, 07 Jun 2006 11:08:39 -0400

Oh, Sorry, I thought the post was about chunks of pubmed articles (such as the one you can fetch via EUtils).

I remember one day I inserted some data from dbSNP in a database. This time I used a lex/yacc (the grammar was trivial) to parse the XML and to quickly insert the records.

Pierre

My mistake (slap me if you

Greg — Wed, 07 Jun 2006 11:00:22 -0400

My mistake (slap me if you will), my memory must be declining with age.

Rohan Williams

chris — Wed, 07 Jun 2006 10:44:34 -0400

Rohan Williams (R Teasdale is at the IMB). Last I heard, that copy was lying around on a hard drive somewhere - the paperwork to get it released from USyd to UNSW across the city was stupendous!

The medline database is

FiReaNG3L — Wed, 07 Jun 2006 10:22:29 -0400

The medline database (in XML format) is broken in ~150 megs chunks, so it's not that bad I guess.

Yeah you have to sign a

FiReaNG3L — Wed, 07 Jun 2006 10:19:25 -0400

Yeah you have to sign a license agreement to get it; you even have to provide a specific ip adress to access their ftp. DLT tapes are an option, but next year it will be ftp only; no DVD format :( The database compressed is only 7 GB though, so on a good connection it took about a day.

I guess I'll roll my own solution :) I have yet to try Lingpipe to automatically download daily updates.

I almost used the forum, but the 'hey there's only 1 post there no one might read it' syndrome kicked in, so I blogged about it :) I will next time for sure.

Thanks for posting the code

Greg — Wed, 07 Jun 2006 10:18:47 -0400

Thanks for posting the code snippet, however the code formatting for the comments is less than ideal. If you want to preserve this maybe post it to the wiki, the syntax highlighting feature is infinitely better...

While XSLT maybe your weapon of choice for the small chunks of XML that get returned from NCBI's EUtils, 50GB of data is probably going to make it explode (XSLT builds an in memory model of the XML file, right ?). I've tried running XSLT on the XML serialization of dbSNP, with less than spectacular results. That is unless the Medline database is split into smaller chunks ? Maybe I'm just missing the point here...

The largest amount of XML formatted data I've had to deal with was the UniProt database serialized as RDF/XML (10 GB). At the time I used the XMLReader interface (via python) from libxml2 to extract a subset of the data for this article (which is poorly written I might add and needs updating).

I would use XSLT

lindenb — Wed, 07 Jun 2006 08:08:12 -0400

Most time I need to parse this kind of XML, I use a XSLT transformation (either directly from xsltproc or using the JAVA transformation API or PHP).
For example to insert a Medline in a DB I would use:

<?xml version='1.0' ?> <xsl:stylesheet xmlns:xsl='http://www.w3.org/1999/XSL/Transform' version='1.0'> <xsl:output method='text'/>


<xsl:template match="/">

<xsl:apply-templates/>

</xsl:template>
<!-- =================================================================== -->

<!-- =================================================================== -->

<!-- =================================================================== -->
<xsl:template match="PubmedArticleSet">

use database DB;

<xsl:apply-templates select='PubmedArticle'/>

</xsl:template>
<!-- =================================================================== -->

<!-- =================================================================== -->

<!-- =================================================================== -->
<xsl:template match="PubmedArticle">

insert ignore into article(PMID,title) values

(

<xsl:value-of select='MedlineCitation/PMID'/>,

&apos;<xsl:value-of select="MedlineCitation/Article/ArticleTitle"/>&apos;

);
set @pmid:= select uid from article where pmid=<xsl:value-of select='MedlineCitation/PMID'/>;

<xsl:apply-templates select='MedlineCitation/Article/AuthorList' />

</xsl:template>
<!-- =================================================================== -->

<!-- =================================================================== -->

<!-- =================================================================== -->

<xsl:template match="AuthorList">

<xsl:apply-templates select='Author' />

</xsl:template>
<!-- =================================================================== -->

<!-- =================================================================== -->

<!-- =================================================================== -->

<xsl:template match="Author">

insert ignore into author(forename,lastname) values

(

&quot;<xsl:value-of select='ForeName' />&quot;,

&quot;<xsl:value-of select='LastName' />&quot;

);
set @authorid:= select uid from author where

        forename=&quot;<xsl:value-of select='ForeName' />&quot; and

        lastname=&quot;<xsl:value-of select='LastName' />&quot;

        ;
insert into paper2author(pmid,athorid) values (@pmid,@authorid);

</xsl:template>
Not checked, not SQL-escaped but you get the idea...

Pierre

Medline XML to database parser?

FiReaNG3L — Wed, 07 Jun 2006 00:23:06 -0400

I recently downloaded Medline in XML format - the goal is to load it in a relational database (like mySQL), index it, and then somehow save the world with the data. I'm pretty sure tons (relatively speaking) of people have done the same thing before (except maybe the save the world part), and I'd prefer not to reinvent the wheel if I don't have to.

Anyone know of a good XML->database parser for Medline? If not I guess I'll code one myself! Indexing tips / advice would also be appreciated (first time I'm playing with a 50+ GB database). I heard Lucene is the bomb for indexing such a large database...