[seek-dev] SQL db candidates for data query
Peter McCartney
peter.mccartney at asu.edu
Wed Jun 23 14:47:21 PDT 2004
i think the extensions in hsqldb for importing from ascii files make it
very well suited for this purpose.
On Wed, 2004-06-23 at 14:38, Jing Tao wrote:
> Hi, everyone:
>
> Sorry for previous email which I sent out accidently :)
>
> As Peter mentioned, my purpose to start this thread is found a suitable
> tool for dynamic loading data which normally stored in text file into
> relational db and user can query it through sql command.
>
> So far, I am thinking hsql is a best candidate. Because it doesn't need
> installation (just put a jar file into class path), it supports sql
> command and JDBC, it supports CSV or other delimited text file as data
> source.
>
> I also took a look to Berkeley DB Java Edition from sleepycat. It is good
> db engine too. But it seems it doesn't support sql at all(at least, I
> didn't find it in the documentation). In this db, records are stored as
> pairs - key/value, both key and value will be stored as byte array. User
> search a db through the key value. It works like a hashtable.
>
> I didn't find much info about Sparrow DB. Bertram, would you please point
> out a url to it?
>
> Thanks.
>
> Jing
>
>
> On Wed, 23 Jun 2004, Peter McCartney wrote:
>
> > Date: Wed, 23 Jun 2004 10:18:10 -0700
> > From: Peter McCartney <peter.mccartney at asu.edu>
> > To: Bertram Ludaescher <ludaesch at sdsc.edu>, Matt Jones <jones at nceas.ucsb.edu>
> > Cc: Jing Tao <tao at nceas.ucsb.edu>, seek-dev at ecoinformatics.org
> > Subject: RE: [seek-dev] SQL db candidates for data query
> >
> > This thread has listed a number of cool products that vary in features,
> > but its not clear to me that everyone's contributiion is motivated by
> > exactly the same intended use. I understood Jing's original queston to
> > be about a suitable tool for dynamically loading data that are normally
> > stored as ascii files into a relational database so that they may be
> > queryied. For that I think products like th hsqldb (or PointBase which
> > was a commercial java db shipped with forte for a while), are ideal
> > because they are exposed as jdbc connections and thus will work with any
> > code you've already written to work with existing sql data. Exist is an
> > xpath/xquery engin and berkelydb seems to be somewhat proprietary
> > (although I didn't really look at it). Thus with those tools, you don't
> > get the immediate benefit of yourexisting sql code.
> >
> > We had been thinking we would do this in our project using mysql or
> > postgres, but both of those involve an installation and configuration
> > step inorder to make them accessible. A javabased db avoides that
> > neatly, albeit at the expense of performance.
> >
> > Peter McCartney (peter.mccartney at asu.edu)
> > Center for Environmental-Studies
> > Arizona State University
> >
> >
> >
> > > -----Original Message-----
> > > From: seek-dev-admin at ecoinformatics.org
> > > [mailto:seek-dev-admin at ecoinformatics.org] On Behalf Of
> > > Bertram Ludaescher
> > > Sent: Wednesday, June 23, 2004 1:41 AM
> > > To: Matt Jones
> > > Cc: Jing Tao; seek-dev at ecoinformatics.org
> > > Subject: Re: [seek-dev] SQL db candidates for data query
> > >
> > >
> > >
> > > Hi all:
> > >
> > > Sorry that I might have missed the beginning of this thread..
> > >
> > > There is also Sparrow DB ;-)
> > >
> > > We have done some experiments with storing a simple
> > > relational query engine close to the data. It's a 100KB
> > > runtime overhead and gives you relational and recursive
> > > queries, possibly in the future some XML querying
> > > capabilities as well. Right now, not much is available or
> > > checked in, but the local SMSers will provide more info once
> > > we're back in town and can actually work on this =B-)
> > >
> > > Bertram
> > >
> > > PS I don't want to get into a XML vs. relational debate right
> > > now. The
> > > short answer: there a good arguments for each of them..
> > >
> > >
> > >
> > > >>>>> "MJ" == Matt Jones <jones at nceas.ucsb.edu> writes:
> > > MJ>
> > > MJ> Hi Jing,
> > > MJ> Also, you might consider this Java version of Berkeley DB from
> > > MJ> Sleepycat.
> > > MJ>
> > > MJ> http://www.sleepycat.com/products/je.php?src=javaed
> > > MJ>
> > > MJ> I'm not sure about its features, particularly sql support, but it
> > > MJ> seems
> > > MJ> like a good potential system given the excellence of the
> > > underlying
> > > MJ> berkeley db product.
> > > MJ>
> > > MJ> Matt
> > > MJ>
> > > MJ>
> > > MJ> Jing Tao wrote:
> > > >> Hi, Serguei:
> > > >>
> > > >> Actually the query is base on sql. Now we are thinking about the
> > > >> issue
> > > >> that user don't want a entire data object(i.e. data tables
> > > or text files)
> > > >> but part of this data object which match a sql query.
> > > >> One approach to achieve this purpose is to load text files into a
> > > >> relational db and it is easy to run a sql query against
> > > the db. We are
> > > >> think this approach can be done in both ecogrid server
> > > side and kepler
> > > >> client side.
> > > >> Of course, postsql, oracle and other one are good
> > > candidates as a sql
> > > >> engine. But they are too huge to redistribution with
> > > kepler. So we are looking for a light
> > > >> weight java relational db.
> > > >>
> > > >> Thanks.
> > > >>
> > > >> Jing
> > > >>
> > > >> On Thu, 17 Jun 2004, Serguei Krivov wrote:
> > > >>
> > > >>
> > > >>> Date: Thu, 17 Jun 2004 22:10:39 -0400
> > > >>> From: Serguei Krivov <Serguei.Krivov at uvm.edu>
> > > >>> To: 'Jing Tao' <tao at nceas.ucsb.edu>, seek-dev at ecoinformatics.org
> > > >>> Subject: RE: [seek-dev] SQL db candidates for data query
> > > >>>
> > > >>> Hi All,
> > > >>> I did not attend the last meeting and I do not know much
> > > about the
> > > >>> requirements for ql. Yet , before opting for sql db it is good to
> > > >>> know if sql support (not XQuery and friends) is really the main
> > > >>> requirement. In fact, should we abandon the world of well
> > > >>> established sql rdbms (e.g postgresql, oracle) and switch to new
> > > >>> java databases, then we shall have a wide vistas of options that
> > > >>> include native xml databases and a lot of other things.
> > > Ferdinando
> > > >>> has installed one here at
> > > >>> http://ecoinformatics.uvm.edu:8080/exist/index.xml
> > > >>> There are a lot of others as well, see:
> > > >>>
> > > http://www.garshol.priv.no/download/xmltools/cat_ix.html#SC_XMLDBMS
> > > >>>
> > > >>> In fact I wonder if there is a DB specifically designed
> > > for DL( or
> > > >>> may be we can write one ;-) ) But surely, if the target query
> > > >>> language is not sql, then why do not to consider non sql
> > > dbs? Ciao,
> > > >>> serguei
> > > >>>
> > > >>>
> > > >>>
> > > >>>
> > > >>>
> > > >>> -----Original Message-----
> > > >>> From: seek-dev-admin at ecoinformatics.org
> > > >>> [mailto:seek-dev-admin at ecoinformatics.org] On Behalf Of Jing Tao
> > > >>> Sent: Wednesday, June 16, 2004 6:46 PM
> > > >>> To: seek-dev at ecoinformatics.org
> > > >>> Subject: [seek-dev] SQL db candidates for data query
> > > >>>
> > > >>> Hi, everyone:
> > > >>>
> > > >>> I am eveluating the sql db candidates for data query. It
> > > turns out
> > > >>> that
> > > >>> the following ones are pretty good: hsqldb and Mckoi.
> > > >>>
> > > >>> Here is the features both of them share:
> > > >>> 1)Open source
> > > >>> 2)Write in pure java and everything is in jar files.
> > > >>> 3)Have server/client and stand-alone mode.
> > > >>> 4)Have JDBC implementation.
> > > >>> 5)Support Linux, Windows.
> > > >>>
> > > >>> Moreover, hsqldb has a good feature that support CSV (Comma
> > > >>> Separated
> > > >>> Value) or other delimited text file as the source of
> > > their data. So user
> > > >>>
> > > >>> don't need use sql command to insert data into db and
> > > only tell the
> > > >>> text
> > > >>>
> > > >>> file location and the sperator. It even can ommit the first line
> > > >>> when it
> > > >>>
> > > >>> is a column name. It pretty matches eml semantic.
> > > >>> Except pipe(|), comma(,) and period(.), HSQLDB also recognises the
> > > >>> following special indicators for separators:
> > > >>> \semi - semicolon
> > > >>> \quote - quote
> > > >>> \space - space character
> > > >>> \apos - apostrophe
> > > >>> \n - newline - Used as an end anchor (like $ in regular
> > > expressions)
> > > >>> \r - carriage return
> > > >>> \t - tab
> > > >>> \\ - backslash
> > > >>> \u#### - a Unicode character specified in hexadecimal
> > > >>>
> > > >>> This feature is every good for us to load data into db.
> > > So I prefer
> > > >>> to
> > > >>> use hsqldb.
> > > >>>
> > > >>> Any comments, suggestions are apprecaited.
> > > >>>
> > > >>> Jing
> > > >>>
> > > >>>
> > > >>
> > > >>
> > > MJ>
> > > MJ> --
> > > MJ>
> > > -------------------------------------------------------------------
> > > MJ> Matt Jones
> > > jones at nceas.ucsb.edu
> > > MJ> http://www.nceas.ucsb.edu/ Fax: 425-920-2439 Ph:
> > > 907-789-0496
> > > MJ> National Center for Ecological Analysis and Synthesis (NCEAS)
> > > MJ> University of California Santa Barbara
> > > MJ> Interested in ecological informatics?
> > > http://www.ecoinformatics.org
> > > MJ>
> > > -------------------------------------------------------------------
> > > MJ> _______________________________________________
> > > MJ> seek-dev mailing list
> > > MJ> seek-dev at ecoinformatics.org
> > > MJ> http://www.ecoinformatics.org/mailman/listinfo/seek-dev
> > > _______________________________________________
> > > seek-dev mailing list
> > > seek-dev at ecoinformatics.org
> > > http://www.ecoinformatics.org/mailman/listinfo> /seek-dev
> > >
> >
More information about the Seek-dev
mailing list