Defining Foreign Tables

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.

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 '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)
) SERVERT Sample.PostgresDB TABLE 'Sample.Teams'

Creating a foreign table through a data definition language statement will create a corresponding class. This class has a ClassType of “view” and should not be edited.

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.

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