Cross-Database query engine?

Greetings.

We've been stuck for quite a while between SQL Server and MySQL, and
while doing cross-database queries is drop-dead simple in MS Access,
it's relatively painful in Perl.

Are there any solutions at present for running queries across different
database servers? If not, I may well write one ...

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

Re: Cross-Database query engine?

At 9:43 AM +1000 7/25/07, Daniel Kasak wrote:
>We've been stuck for quite a while between SQL Server and MySQL, and
>while doing cross-database queries is drop-dead simple in MS Access,
>it's relatively painful in Perl.
>
>Are there any solutions at present for running queries across different
>database servers? If not, I may well write one ...

Please give more detail of what you actually want to do, perhaps with
an example, so it is easier to answer the question. -- Darren Duncan

Re: Cross-Database query engine?

On 7/24/07, Daniel Kasak wrote:
> doing cross-database queries is drop-dead simple in MS Access,

really? please explain.

Re: Cross-Database query engine?

On Tue, 2007-07-24 at 22:12 -0500, David Nicol wrote:

> On 7/24/07, Daniel Kasak wrote:
> > doing cross-database queries is drop-dead simple in MS Access,
>
> really? please explain.

Hmmm. Did any thought go into this? OK then.

Link some tables from one database server.
Link some tables from another database server.

Go to the query builder. Add tables from both database servers. Join
tables where appropriate. Execute query.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

Re: Cross-Database query engine?

I'm no database expert but I believe I can answer your question.

On 7/24/07, Daniel Kasak wrote:

> Link some tables from one database server.
> Link some tables from another database server.
>
> Go to the query builder. Add tables from both database servers. Join
> tables where appropriate. Execute query.

So you're saying that Access abstracts the handles to the multiple databases
in such a way that they appear to you as a single database, and you can use the
tools the way you would use on a single database on the combination of the
multiple databases.

I do not recall seeing such a tool discussed on this mailing list. If
I were tasked
with such a situation I would unfold the various queries, possibly using one of
the perly abstractions such as Tie::DBI or DBIx::SimplePerl and using keys or
each to iterate. At some level, Access must be doing that for you.

The closest thing a cursory CPAN search revealed was
http://search.cpan.org/~dwright/DBD-Multi-0.10/lib/DBD/Multi .pm
which appears to be concerned with load-balancing against data
sources containing identical data rather than abstracting multiple data sources
in a way that the database driver takes apart the queries and sends the various
pieces to the various databases.

Without knowing for certain that nobody has done what you are looking
for already,
it sounds to me like it would be a welcome addition to the DBI tool kit.

Go for it. I expect that the devil will be in the optimization.

Re: Cross-Database query engine?

At 10:44 PM -0500 7/24/07, David Nicol wrote:
>So you're saying that Access abstracts the handles to the multiple databases
>in such a way that they appear to you as a single database, and you
>can use the
>tools the way you would use on a single database on the combination of the
>multiple databases.

If that's the case, making several dbs look like one, then some DBMS
can already do that internally. At the very least, Oracle, MySQL,
and SQLite can all do that, afaik.

Since MySQL is what you say you want to use, I recommend looking into
the "Federated" storage engine that MySQL offers, which is in the
current 5.0.x series and later; tables of that engine are proxies for
tables under some other server. Not all SQL features are supported
with them, but the ones that are may be enough.

http://dev.mysql.com/tech-resources/articles/mysql-federated -storage.html

-- Darren Duncan

Re: Cross-Database query engine?

On Tue, 2007-07-24 at 21:36 -0700, Darren Duncan wrote:

> At 10:44 PM -0500 7/24/07, David Nicol wrote:
> >So you're saying that Access abstracts the handles to the multiple databases
> >in such a way that they appear to you as a single database, and you
> >can use the
> >tools the way you would use on a single database on the combination of the
> >multiple databases.
>
> If that's the case, making several dbs look like one, then some DBMS
> can already do that internally. At the very least, Oracle, MySQL,
> and SQLite can all do that, afaik.
>
> Since MySQL is what you say you want to use, I recommend looking into
> the "Federated" storage engine that MySQL offers, which is in the
> current 5.0.x series and later; tables of that engine are proxies for
> tables under some other server. Not all SQL features are supported
> with them, but the ones that are may be enough.
>
> http://dev.mysql.com/tech-resources/articles/mysql-federated -storage.html
>
> -- Darren Duncan

Thanks for the link.

Unfortunately MySQL can only connect to other MySQL servers ( ie not
other database servers ).

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

Re: Cross-Database query engine?

At 9:43 AM +1000 7/25/07, Daniel Kasak wrote:
>Are there any solutions at present for running queries across different
>database servers? If not, I may well write one ...

Another thing I'll say is that my Muldis::DB project, currently on
CPAN, has what you're talking about as a standard feature-to-be, by
way of its native paradigm that makes all data stores look like
they're all in one query environment, which is analagous to Perl's
tie mechanism making foreign variables look like native local
variables. -- Darren Duncan

Re: Cross-Database query engine?

On Tue, 2007-07-24 at 22:44 -0500, David Nicol wrote:

> I'm no database expert but I believe I can answer your question.
>
> On 7/24/07, Daniel Kasak wrote:
>
> > Link some tables from one database server.
> > Link some tables from another database server.
> >
> > Go to the query builder. Add tables from both database servers. Join
> > tables where appropriate. Execute query.
>
> So you're saying that Access abstracts the handles to the multiple databases
> in such a way that they appear to you as a single database, and you can use the
> tools the way you would use on a single database on the combination of the
> multiple databases.

That's it exactly. What's more, if you look at MySQL's query log as you
run one of these cross-database queries, you quickly get an idea of how
it works :)

> I do not recall seeing such a tool discussed on this mailing list. If
> I were tasked
> with such a situation I would unfold the various queries, possibly using one of
> the perly abstractions such as Tie::DBI or DBIx::SimplePerl and using keys or
> each to iterate. At some level, Access must be doing that for you.

I'll look into these, thanks.

> The closest thing a cursory CPAN search revealed was
> http://search.cpan.org/~dwright/DBD-Multi-0.10/lib/DBD/Multi .pm
> which appears to be concerned with load-balancing against data
> sources containing identical data rather than abstracting multiple data sources
> in a way that the database driver takes apart the queries and sends the various
> pieces to the various databases.

That also sounds interesting.

> Without knowing for certain that nobody has done what you are looking
> for already,
> it sounds to me like it would be a welcome addition to the DBI tool kit.
>
> Go for it. I expect that the devil will be in the optimization.

Yes I already have some ideas here too.

Firstly I'll be doing some testing with using SQLite as a local storage
engine. I'll pull the query apart, grab bits of stuff from various DB
servers, and then do the final select against the SQLite DB.

Secondly, from some very limited testing, it looks like I might get a
decent performance boost by creating temporary tables on the DB servers,
inserting a list of keys, and then running queries against these tmp
tables ( as opposed to just sending a huge 'where' clause with all the
keys ).

Thirdly, I'm also investigating using a separate thread to do all
querying, which will make my apps feel much more responsive :)

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

Re: Cross-Database query engine?

On Tue, 2007-07-24 at 21:55 -0700, Darren Duncan wrote:

> At 9:43 AM +1000 7/25/07, Daniel Kasak wrote:
> >Are there any solutions at present for running queries across different
> >database servers? If not, I may well write one ...
>
> Another thing I'll say is that my Muldis::DB project, currently on
> CPAN, has what you're talking about as a standard feature-to-be, by
> way of its native paradigm that makes all data stores look like
> they're all in one query environment, which is analagous to Perl's
> tie mechanism making foreign variables look like native local
> variables. -- Darren Duncan

I see. That *IS* interesting then. I'll check it out. Thanks.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

Re: Cross-Database query engine?

Daniel Kasak wrote:

> On Tue, 2007-07-24 at 22:44 -0500, David Nicol wrote:
>
>> I'm no database expert but I believe I can answer your question.
>>
>> On 7/24/07, Daniel Kasak wrote:
>>
>>> Link some tables from one database server.
>>> Link some tables from another database server.
>>>
> That's it exactly. What's more, if you look at MySQL's query log as you
> run one of these cross-database queries, you quickly get an idea of how
> it works :)

Hi Daniel!

Could you post an extract of these logs?
I'm curious about it...

--
Cosimo

Re: Cross-Database query engine?

On Wed, Jul 25, 2007 at 03:02:35PM +1000, Daniel Kasak wrote:
> On Tue, 2007-07-24 at 21:55 -0700, Darren Duncan wrote:
>
> > At 9:43 AM +1000 7/25/07, Daniel Kasak wrote:
> > >Are there any solutions at present for running queries across different
> > >database servers? If not, I may well write one ...
> >
> > Another thing I'll say is that my Muldis::DB project, currently on
> > CPAN, has what you're talking about as a standard feature-to-be, by
> > way of its native paradigm that makes all data stores look like
> > they're all in one query environment, which is analagous to Perl's
> > tie mechanism making foreign variables look like native local
> > variables. -- Darren Duncan
>
> I see. That *IS* interesting then. I'll check it out. Thanks.

Personally I think this is the killer-app for Muldis::DB, if the query
optimization can be made smart enough.

Tim.

p.s. No need to reply to this. It's just an observation.

Re: Cross-Database query engine?

On 25 Jul 2007 at 9:43, Daniel Kasak wrote:
> Greetings.
>
> We've been stuck for quite a while between SQL Server and MySQL, and
> while doing cross-database queries is drop-dead simple in MS Access,
> it's relatively painful in Perl.
>
> Are there any solutions at present for running queries across
> different database servers? If not, I may well write one ...

What about linking all those tables into an MS Access database and
then
accessing that from
Perl?

Jenda

===== Jenda@Krynicky.cz === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery

Re: Cross-Database query engine?

On Wed, 2007-08-01 at 22:13 +0200, Jenda Krynicky wrote:

> On 25 Jul 2007 at 9:43, Daniel Kasak wrote:
> > Greetings.
> >
> > We've been stuck for quite a while between SQL Server and MySQL, and
> > while doing cross-database queries is drop-dead simple in MS Access,
> > it's relatively painful in Perl.
> >
> > Are there any solutions at present for running queries across
> > different database servers? If not, I may well write one ...
>
> What about linking all those tables into an MS Access database and
> then
> accessing that from
> Perl?

:)

Nice suggestion ( we do this from Excel sometimes ), but I'm actually
planning on *competing* with Access, and not using it to do the heavy
lifting for me. ie this is for a generic query engine, and not for a
specific task I have in mind.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

most common kinds of cross-database queries

At 9:03 AM +1000 8/2/07, Daniel Kasak wrote:
>Nice suggestion ( we do this from Excel sometimes ), but I'm actually
>planning on *competing* with Access, and not using it to do the heavy
>lifting for me. ie this is for a generic query engine, and not for a
>specific task I have in mind.

As a tangent to that other cross-database thread, I'm hoping for some
input that can help me determine priorities in my own implementation
under Muldis DB.

What do you think would be the most common scenarios of a cross-database query?

Or why would data be in multiple databases, and what kinds of ways
are most likely for it to be brought together in a common query?

For example, is it more common for the multiple databases with the
same schema but different data, such as to implement partitioning or
clusters, or are they all full replication for redundancy and
performance, or do they tend to be all different from each other and
just associate at their edges?

For example, what relational operations tend to be the most common
between databases, eg: unions, joins to display data from both, using
one to filter data from the other.

In common scenarios, is usually the same DBMS product normally used
for all the databases, or are they more likely to be different
products driving each one?

For those replying, please just answer the question in a generic
sense for now, without regard for particulars of my project.

Thank you. -- Darren Duncan

Re: most common kinds of cross-database queries

------=_Part_61969_26377565.1186019202383
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 02/08/07, Darren Duncan wrote:
>
> What do you think would be the most common scenarios of a cross-database
> query?


Speaking only theoretically for now but one "use case" I can vaguely imagine
would be something we are working on right now:

We have a large SQL Server database with many individual records and we are
now building a PostgresQL 8.1 database on another machine to contain
statistics about these records (e.g. for each hour in the life of the
database, how many records of each type were created or updated). One is
derived from the other and contains aggregates of records but maybe when
we'll get the web interface to dig the stats database we might want to be
able to say "list all records in the main db which were counted by a
particular record in the stats DB".

HTH,

--Amos

------=_Part_61969_26377565.1186019202383--

Re: most common kinds of cross-database queries

On Wed, 2007-08-01 at 18:29 -0700, Darren Duncan wrote:

> What do you think would be the most common scenarios of a cross-database query?
>
> Or why would data be in multiple databases, and what kinds of ways
> are most likely for it to be brought together in a common query?

In our case, we are migrating from a legacy SQL Server database to
MySQL. We're doing it in bits and pieces. MS Access is making this quite
easy for us ( because of it's cross-database query support ).

I think our situation wouldn't be too uncommon, particularly amongst
people who use open-source software ( ie I assume there's a general
trend to migrate from the big commercial DB servers to open-source
solutions ).

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

Re: most common kinds of cross-database queries

--Qxx1br4bt0+wmkIi
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2007-08-01 18:29:23 -0700, Darren Duncan wrote:
> As a tangent to that other cross-database thread, I'm hoping for some inp=
ut=20
> that can help me determine priorities in my own implementation under Muld=
is DB.
>=20
> What do you think would be the most common scenarios of a cross-database =
query?
>=20
> Or why would data be in multiple databases, and what kinds of ways are mo=
st=20
> likely for it to be brought together in a common query?
>=20
> For example, is it more common for the multiple databases with the same s=
chema=20
> but different data, such as to implement partitioning or clusters, or are=
they=20
> all full replication for redundancy and performance, or do they tend to b=
e all=20
> different from each other and just associate at their edges?

I think a very common problem is that you have multiple databases in an
organisation, which were created by different people at different times
which the RDBMS they were familiar with. And then you need to combine
some data from accounting (commercial software using Oracle), assets
management (developed in-house using MS-Access), the intranet CMS (open
source, uses MySQL) and a list the secretary maintains in an Excel
spreadsheet. Different schemas, different data, different RDBMS, but
you want to do a join over all of them.

hp

--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--Qxx1br4bt0+wmkIi
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGsYnNMdFfQa64PCwRArGjAKCcN7PG7TbYLiL+CY80LBS12YgRqwCe OUa3
Lr2dRvqv67jkuNW/McnPuVE=
=Pmz4
-----END PGP SIGNATURE-----

--Qxx1br4bt0+wmkIi--