From: Rob Sargent <robjsargent@gmail.com>
To: Viktor Bojović <viktor.bojovic@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: large xml database
Date: Sun, 31 Oct 2010 15:26:01 -0600
Message-ID: <4CCDDEE9.9010803@gmail.com> (raw)
In-Reply-To: <AANLkTimQC=tg6g3vsYb5X55MUHcaAOmMH_xZskxQNFBz@mail.gmail.com>
References: <AANLkTi=ZEO1W=FOxdH+S3twj_A1VCpyyzes7HFghJ7T-@mail.gmail.com>
<m3r5f76wbm.fsf@jhcloos.com>
<AANLkTinZqmQA5hovJNAdRoTTu3mjzfRQdw1=cmwVMFEk@mail.gmail.com>
<4CCDD4B0.7030605@gmail.com>
<AANLkTimQC=tg6g3vsYb5X55MUHcaAOmMH_xZskxQNFBz@mail.gmail.com>
Viktor Bojović wrote:
>
>
> On Sun, Oct 31, 2010 at 9:42 PM, Rob Sargent <robjsargent@gmail.com
> <mailto:robjsargent@gmail.com>> wrote:
>
>
>
>
> Viktor Bojovic' wrote:
>
>
>
> On Sun, Oct 31, 2010 at 2:26 AM, James Cloos
> <cloos@jhcloos.com <mailto:cloos@jhcloos.com>
> <mailto:cloos@jhcloos.com <mailto:cloos@jhcloos.com>>> wrote:
>
> >>>>> "VB" == Viktor Bojovic' <viktor.bojovic@gmail.com
> <mailto:viktor.bojovic@gmail.com>
>
> <mailto:viktor.bojovic@gmail.com
> <mailto:viktor.bojovic@gmail.com>>> writes:
>
> VB> i have very big XML documment which is larger than 50GB and
> want to
> VB> import it into databse, and transform it to relational
> schema.
>
> Were I doing such a conversion, I'd use perl to convert the
> xml into
> something which COPY can grok. Any other language, script
> or compiled,
> would work just as well. The goal is to avoid having to
> slurp the
> whole
> xml structure into memory.
>
> -JimC
> --
> James Cloos <cloos@jhcloos.com <mailto:cloos@jhcloos.com>
> <mailto:cloos@jhcloos.com <mailto:cloos@jhcloos.com>>>
>
> OpenPGP: 1024D/ED7DAEA6
>
>
> The insertion into dabase is not very big problem.
> I insert it as XML docs, or as varchar lines or as XML docs in
> varchar format. Usually i use transaction and commit after
> block of 1000 inserts and it goes very fast. so insertion is
> over after few hours.
> But the problem occurs when i want to transform it inside
> database from XML(varchar or XML format) into tables by parsing.
> That processing takes too much time in database no matter if
> it is stored as varchar lines, varchar nodes or XML data type.
>
> --
> ---------------------------------------
> Viktor Bojovic'
>
> ---------------------------------------
> Wherever I go, Murphy goes with me
>
>
> Are you saying you first load the xml into the database, then
> parse that xml into instance of objects (rows in tables)?
>
>
> Yes. That way takes less ram then using twig or simple xml, so I tried
> using postgre xml functions or regexes.
>
>
>
> --
> ---------------------------------------
> Viktor Bojović
> ---------------------------------------
> Wherever I go, Murphy goes with me
Is the entire load a set of "entry" elements as your example contains?
This I believe would parse nicely into a tidy but non-trivial schema
directly without the "middle-man" of having xml in db (unless of course
you prefer xpath to sql ;) )
The single most significant caveat I would have for you is Beware:
Biologists involved. Inconsistency (at least overloaded concepts)
almost assured :). EMBL too is suspect imho, but I've been out of that
arena for a while.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robjsargent@gmail.com, viktor.bojovic@gmail.com
Subject: Re: large xml database
In-Reply-To: <4CCDDEE9.9010803@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox