Thanks for your feedback!
Need to tell us more? Click here or use the Feedback button.

Is this page helpful?

Contents

Introduction to Foreign Tables
Creating a Foreign Table
Querying a Foreign Table
Deleting a Foreign Table

Defining Foreign Tables

InterSystems SQL enables you to define a foreign table, which projects data from an external data source into InterSystems IRIS® data platform and allows you to query such external data alongside data stored in InterSystems IRIS.

Introduction to Foreign Tables

For various reasons, it may not be feasible or reasonable for you to load data directly into InterSystems IRIS. For example, perhaps a data file is extremely large and will not be queried often enough to justify the storage cost of loading it into an InterSystems IRIS table. A foreign table is a projection of data managed by another system that can be queried and accessed alongside data that is managed and stored within an instance of InterSystems IRIS.

Creating a Foreign Table

Prior to creating a foreign table, you must define a foreign server to determine how InterSystems IRIS will interact with the external data source. Once you have defined a foreign server, you can then define one or more foreign tables that represent data in the external source by specifying column names and types, as well as any other details required to map fields in the external data source to columns within InterSystems IRIS.

Step 1: Define a Foreign Server

Before you can define a foreign table, you must define a foreign server and specify which foreign data wrapper to use. To do so, you should use the CREATE FOREIGN SERVER command.

The CREATE FOREIGN SERVER command requires you to specify a foreign data wrapper. Foreign data wrappers determine how InterSystems IRIS interacts with a specific type of data source. Within a CREATE FOREIGN SERVER command, you will need to specify both a foreign data wrapper and the metadata that the foreign data wrapper requires. At present, the InterSystems SQL supports two foreign data wrappers: CSV and JDBC. The CSV foreign data wrapper requires you to specify a path to a folder in a local file system. The JDBC foreign data wrapper requires you to name a JDBC connection to connect with an external database.

There is no limit on the amount of foreign tables you can define on a foreign server.

A user that creates a foreign server must have the %MANAGE_FOREIGN_SERVER administrative privilege.

The following example demonstrates how to create a foreign server that uses the CSV foreign data wrapper.

CREATE FOREIGN SERVER Sample.TestFile 
   FOREIGN DATA WRAPPER CSV HOST '\path\to\file'

The following example demonstrates how to create a foreign server that uses the JDBC foreign data wrapper.

CREATE FOREIGN SERVER Sample.PostgresDB 
   FOREIGN DATA WRAPPER JDBC CONNECTION 'postgresConnection'

Step 2: Define a Foreign Table

After defining a foreign server, you can use the CREATE FOREIGN TABLE command to define a foreign table. This table may have the same column names as the data from the external source, or you may choose to refer to the columns by new names within InterSystems IRIS. The syntax for creating a foreign table is similar to the LOAD DATA command.

CREATE FOREIGN TABLE Sample.AccountTeam (
   TeamID BIGINT,
   Name VARCHAR(50),
   CountryCode VARCHAR(10)
) SERVER Sample.PostgresDB TABLE 'Sample.Teams'

Creating a foreign table through a data definition language statement will create a corresponding class, which has a ClassType of “view”. You should not manually edit this class; furthermore, you must define a foreign table with the CREATE FOREIGN TABLE command and cannot create one by creating a class definition.

The user that creates a foreign table must have the %CREATE_TABLE administrative privilege and the USE object privilege on the foreign server that the table is created on.

Querying a Foreign Table

Querying a foreign table is exactly like querying a native table:

SELECT Name, CountryCode FROM Sample.AccountTeam ORDER BY Name

Your queries can also take advantage of more advanced syntaxes:

SELECT t.Name, COUNT(m.*)
FROM Sample.AccountManager m JOIN Sample.AccountTeam t
     ON m.TeamID = t.TeamID
WHERE t.CountryCode = 'UK' AND m.Salary > 100000
GROUP BY t.Name

Where possible, InterSystems SQL will send, or push down, simple predicates in a WHERE clause, limiting the amount of data transferred over the network and taking advantage of optimizations on the remote database. However, some more complicated clauses, such as a GROUP BY or a JOIN between two foreign tables, occur within InterSystems IRIS after the external data has been retrieved. View the query plan to see the statement that is sent to the remote server and see which clauses from the original query are pushed down to the remote database.

The user that issues a query on the external data source must have the %Gateway_Object:USE privilege.

Note:

Issuing a query against a foreign table makes use of an underlying Java-based engire that requires a Java Virtual Machine (JVM) installation on your server. If you have a JVM set up and accessible in your PATH environment variable, then the first time you issue a query, InterSystems IRIS automatically uses that JVM to start an External Language Server. To customize your External Language Server to use a specific JVM, or to use a remote server, see Managing External Server Connections.

Issuing a Passthrough Query

Using the THROUGH command, you can send a SELECT query straight to the external data source for execution through a foreign server. Such a query is called a passthrough query. The external data source is exclusively responsible for the preparing and executing a passthrough query, which can be used to explore the data stored in an external data source on the fly, prior to creating a foreign table for more convenient access.

To issue a passthrough query, you must create a foreign server that uses a foreign data wrapper that supports passthrough queries, such as JDBC. There is no need to create a foreign table before issuing a passthrough query.

Deleting a Foreign Table

To delete a foreign table, you should use the DROP FOREIGN TABLE command:

DROP FOREIGN TABLE Example.MyForeignTable

Alternatively, you may use the DROP FOREIGN SERVER command with the CASCADE option to drop a foreign server and all the foreign tables defined on that foreign server:

DROP FOREIGN SERVER Example.PostgresDB CASCADE

The user that issues the DROP FOREIGN SERVER command must have the %MANAGE_FOREIGN_SERVER administrative privilege.

Modifying the Database

Federated Tables