O'Reilly logo

Discovering SQL: A Hands-On Guide for Beginners by Alex Kriegel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

ONE HAPPY FAMILY: WORKING IN HETEROGENEOUS ENVIRONMENTS

So far, we've been working within the confines of a single database. All objects were created and managed by a specific RDBMS, be it Oracle, IBM DB2, Microsoft SQL Server, PostgreSQL, MySQL, MS Access, or HSQLDB. What if you have several different RDBMSs in your environment and need to use data from all of them?

One way is to transfer data among different RDBMSs in a process called extract, transform, and load (ETL) in database parlance. First, the data are extracted from the source database(s); second, it is transformed according to some rules; and third, it is loaded to a different RDBMS for querying. This is the bread and butter of a data warehousing operations. Some RDBMS vendors bundle ETL capabilities into their products (for example, Microsoft SQL Server); some rely on third-party companies, both open source and proprietary, to assist with the process.

Another approach is to use distributed querying capabilities offered by some RDBMSs. For instance, Microsoft SQL Server allows you to create a “linked server” from a variety of heterogeneous data sources (including Oracle, DB2, and MySQL, among others) that then can be included into an SQL query. The complexity of executing queries across these databases and returning a single result set is hidden in the layers of functionality built into the software.

There is a thriving market specializing in software to make distributed heterogeneous queries possible. Chances are that ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required