CREATE FOREIGN SERVER (SQL)

Creates a foreign server.

Synopsis

CREATE [ FOREIGN ] SERVER server-name [ TYPE server-type ] FOREIGN DATA WRAPPER CSV
HOST host-name CREATE [ FOREIGN ] SERVER server-name [ TYPE server-type ] FOREIGN
DATA WRAPPER JDBC CONNECTION connection-name id-options

Arguments

Arguments	Description
server-name	The name for the foreign server definition being created. A valid identifier, subject to the same additional naming restrictions as a table name. A foreign server name is a qualified name.
TYPE server-type	The type of the foreign server. Foreign servers can be of two types: 'DB' or 'FILE'. Note that the delimiters are required.
FOREIGN DATA WRAPPER [ CSV \| JDBC ]	Describes the protocol that will be used to access external data. The foreign data wrapper can be one of two options: CSV or JDBC.
HOST host-name	The source that stores the data. This name is a folder in a file system. The host-name must be delimited by single quotation marks.
CONNECTION connection-name	The name of the JDBC connection that connects InterSystems IRIS to the external system. Must be delimited. For details on establishing a JDBC connection, see Connecting the SQL Gateway via JDBC.
id-options	Optional — Either DELIMITEDIDS or NODELIMITEDIDS. Specifies whether the external data source accepts delimited identifiers or not.

Description

The CREATE FOREIGN SERVER command defines a remote location that InterSystems SQL can use to access an external data source, called a foreign server. This command stores metadata that the system can use to project data from an external data source into foreign tables that can be queried alongside native tables. In addition, it defines the foreign data wrapper, which determines the protocol that the foreign server uses to access data from an external source.

InterSystems SQL currently supports two types of foreign servers (optionally specified with the TYPE keyword), 'FILE' and 'DB', that retrieve external data from either a .csv file or database, respectively. Foreign servers of type 'FILE' access files in file systems, while foreign servers of type 'DB' use pre-defined JDBC connections to access external databases. The type of a foreign server is implicitly set by the foreign data wrapper.

Create a Foreign Server for .csv Files

When defining a foreign server that will create foreign tables by reading data stored in .csv files, you will use the CSV foreign data wrapper. Foreign servers defined in this manner must define, at minimum, a local file path that stores any .csv files that you may project into InterSystems IRIS. This file path is specified by using the HOST keyword.

The following example creates a foreign server that accesses .csv files:

CREATE FOREIGN SERVER Sample.DumpDir FOREIGN DATA WRAPPER CSV HOST '/data/dumps'

Create a Foreign Server with a JDBC Connection

When defining a foreign server that will create foreign tables by reading data stored in an external database, you will use the JDBC foreign data wrapper. Foreign servers defined in this manner must specify a JDBC connection that will connect the instance of InterSystems IRIS with the external data source. This connection’s name is specified by using the CONNECTION keyword.

The following example creates a foreign server for JDBC connections:

CREATE FOREIGN SERVER Sample.Postgres FOREIGN DATA WRAPPER JDBC CONNECTION 'PostgresSQLConnection'

Using Delimited Identifiers

When connecting to an external data source, you may need to specify whether or not the foreign server should accept delimited identifiers. By default, InterSystems IRIS may send delimited identifiers to an external database management system when creating a projection, but not all database management systems allow delimited identifiers. If you are using an external database management system that does not accept delimited identifiers, you should specify the NODELIMITEDIDS at the end of your CREATE FOREIGN SERVER command. The default setting allows delimited identifiers.