What is Query Cloud?
In short: This feature allows to combine data from multiple Caché Servers, Caché Namespaces but also other database vendors are supported. Combine tables from Microsoft Access, Microsoft SQL Server or other database vendors or from simple CSV Files by writing a simple sql query.
All this without any installation on Server Side!
Before we look at the Query Cloud lets see what options we have to query data from different places without to move the data before and what are the differences between all of them
Linked Server
Several database products, such Microsoft Access, Microsoft SQL Server, Sybase and more have the ability to “link” a table into the current server instance. With that you can write queries as if that table was actually stored on the particular server. This is a very useful feature that will often allow you to avoid loading data into another source while still being able to execute a query.
Caché SQL Gateway
The Caché SQL Gateway provides access from Caché to external databases via JDBC\ODBC. So this is a similar technique like the Linked Servers in Microsoft SQL Server.
To linking to a Table\View from a external source you have to create a Gateway Connection. After that you have to use a wizard from Caché Management Portal to link to an external table in an ODBC- or JDBC-compliant database. Link means here that there is a class created that redirect all calls to the external table. Now you can query this table within Caché.
The drawback of that feature: The user need to be autorized to use the SQL Gateway, create connections, create tables and more.
All the tables listed in the FROM clause of an SQL query must come from the same data source. Queries that join data from heterogeneous data sources are not allowed.
Caché Monitor Mapped-Tables for InterSystems Caché
Caché Monitor Mapped-Tables feature is something like the Caché SQL Gateway and the Caché Link Table Wizard in one. This features is limited to InterSystems Caché and allows to map\link the data storage of a class\table into another namespace. With that you can combine and query the data in a sql statement as if that table was actually stored in same namespace like the other tables.
The drawback of that feature: Mapped-Tables only works for Caché and need be configure on server side. The User need to be very carefully while execute delete operations on Mapped-Tables! Cause the original table and the Mapped-Table(s) are use the same data storage.
What these techniques have in common?
All the work is done on Serverside by the database server. The query optimizer can choose the right plan to get the data as fast as possible and only the result of the query is send back to the client (Caché Monitor). So the client has nothing to do just show the result. This is quit good from performance perspective.
BUT if you only want to do some adhoc queries across multible databases for analysis purposes you have to do a lot of configuration first.
With the Query Cloud the user can combine data from all sources they have access to. Only a working connection from Caché Monitor is required. The User don’t need any special rights to that neither any configuration on server side.
This techniques has also a drawback:
All the work is done on client side. So the client need more ressources (memory\cpu) to do the job. So mayby this is not the right way to work with very large tables with millions of rows.
But it is fantastic feature to make some fast ad hoc analysis on the data without moving them.
