CREATE FOREIGN SERVER (SQL)
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 ]
[ passthrough-options ]
Arguments
Arguments | Description |
---|---|
server-name | The name for the foreign server. 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. By default, the external data source is specified to accept delimited identifiers. |
passthrough-options | Optional — Either ENABLE PASSTHROUGH or DISABLE PASSTHROUGH. Specifies whether the foreign server allows passthrough queries or not. By default, the server allows passthrough queries. |
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.
A user must have the %MANAGE_FOREIGN_SERVER administrative privilege in order to execute this command. This privilege can be grated through the GRANT command.
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.