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
[ PUSHDOWN pushdown-type ]
[ id-options ]
[ passthrough-option ]
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.
Arguments
server-name
The name for the foreign server. The specified name must be a valid identifier, subject to the same additional naming restrictions as a table name.
A foreign server name is a qualified name.
server-type
The type of the foreign server. Foreign servers can be of two types: 'DB' or 'FILE'. These types must be delimited by single quotation marks.
FOREIGN DATA WRAPPER
The foreign data wrapper describes the protocol that will be used to access external data. There are currently two options: CSV or JDBC. The CSV foreign data wrapper indicates that the foreign server processes .csv files to access data stored external to InterSystems IRIS. The JDBC foreign data wrapper indicates that the foreign server uses a predefined JDBC connection to access data in an external data source.
host-name
Only used with the CSV foreign data wrapper, the host-name specifies name of a folder in a file system that serves as the external data source.
The host-name must be delimited by single quotation marks.
connection-name
The name of the JDBC connection that connects InterSystems IRIS to the external system, delimited with single quotation marks. For details on establishing a JDBC connection, see Connecting the SQL Gateway via JDBC.
pushdown-type
The name of the pushdown type your foreign server should support. Valid options are: OFF, BASIC (the default), ANSI92, EXTENDED, and IRIS.
id-options
An optional argument that specifies whether the external data source accepts delimited identifiers or not, with a value of either DELIMITEDIDS or NODELIMITEDIDS. By default, the external data source is specified to accept delimited identifiers.
passthrough-option
An optional argument that specifies whether the foreign server allows passthrough queries or not, with a value of either ENABLE PASSTHROUGH or DISABLE PASSTHROUGH. By default, the server allows passthrough queries.
Pushdown Reference
You can configure the extent to which InterSystems attempts to push processing of predicates down to the external data source using the pushdown-type argument. By pushing predicates down to the external data source, you can reduce the processing load on your instance.
The pushdown types, in order of how many commands they allow to be pushed down, are OFF, BASIC, ANSI92, EXTENDED, and IRIS.
Does not push any predicates down to the external data source.
Pushes down many predicates commonly supported by other databases.
Pushes down predicates explicitly mentioned by the 1992 ANSI SQL Standard. Most major databases do not support all of the operations mentioned in this category. For tighter control, you should use BASIC.
Pushes down predicates not guaranteed to be supported by all ANSI-92 compliant database, but still enjoy wide support (such as CASE), in addition to all ANSI92 predicates.
Pushes down most InterSystems IRIS-specific predicates, such as DATEADD. It should only be used when a foreign server accesses another instance of InterSystems IRIS as an external data source.
Supported Predicates
| Function Name | Pushdown Level | Notes |
|---|---|---|
| ABS | BASIC | |
| ACOS | BASIC | |
| ASCII | EXTENDED | |
| ASIN | BASIC | |
| ATAN | BASIC | |
| ATAN2 | IRIS | |
| CASE | EXTENDED | |
| CAST |
BASIC: BIGINT, CHAR, CHARACTER, INT, INTEGER, NCHAR, SMALLINT, TINYINT ANSI92: DATE, TIME, TIMESTAMP EXTENDED: DOUBLE IRIS: BIT, CHAR VARYING, CHARACTER VARYING, DATETIME, NVARCHAR, POSIXTIME, SMALLDATETIME, VARCHAR |
As the pushdown type your foreign server uses becomes more permissive, the CAST command supports casting to more types of data. |
| CEILING | ANSI92 | Not supported by Oracle Database. |
| CHAR | IRIS | |
| CHARACTER_LENGTH | IRIS | |
| CHARINDEX | IRIS | |
| CONCAT | ANSI92 | Not supported by Microsoft SQL Server. |
| CONVERT |
OFF: MONEY EXTENDED: DOUBLE IRIS: CHAR, CHARACTER, DATE, INTEGER, POSIXTIME, TIME, TIMESTAMP, VARCHAR |
As the pushdown type your foreign server uses becomes more permissive, the CONVERT command supports casting to more types of data. |
| COS | BASIC | |
| COT | EXTENDED | |
| CURDATE | IRIS | |
| CURRENT_DATE | IRIS | |
| CURRENT_TIME | IRIS | |
| CIRRENT_TIMESTAMP | EXTENDED | |
| CURTIME | IRIS | |
| DATALENGTH | IRIS | |
| DATE | ANSI92 | |
| DATEADD | IRIS | |
| DATEDIFF | IRIS | |
| DATEPART | IRIS | |
| DAY | IRIS | |
| DAYNAME | IRIS | |
| DAYOFMONTH | IRIS | |
| DAYOFYEAR | IRIS | |
| DECODE | IRIS | |
| DEGREES | ANSI92 | Not supported by Oracle Database. |
| %EXACT | IRIS | |
| EXP | BASIC | |
| %EXTERNAL | IRIS | |
| $EXTRACT | IRIS | |
| $FIND | IRIS | |
| FLOOR | BASIC | |
| GETDATE | IRIS | |
| GETUTCDATE | IRIS | |
| GREATEST | ANSI92 | |
| HOUR | IRIS | |
| INSTR | IRIS | |
| %INTERNAL | IRIS | |
| ISNUMERIC | IRIS | |
| $JUSTIFY | IRIS | |
| LAST_DAY | IRIS | |
| LCASE | BASIC | |
| LEAST | ANSI92 | Not supported by Microsoft SQL Server. |
| LEFT | ANSI92 | Not supported by Oracle Database. |
| LEN | IRIS | |
| LENGTH | IRIS | |
| $LENGTH | IRIS | |
| LOG | ANSI92 | The one argument variant of this function is not supported by Oracle Database. |
| LOG10 | ANSI92 | Not supported by Oracle Database. |
| LOWER | EXTENDED | |
| LPAD | ANSI92 | Not supported by Microsoft SQL Server. |
| LTRIM | BASIC | |
| %MINUS | IRIS | |
| MINUTE | IRIS | |
| MOD | ANSI92 | |
| MONTH | IRIS | |
| MONTHNAME | IRIS | |
| NOW | EXTENDED | Not supported by Microsoft SQL Server. |
| PI | EXTENDED | |
| $PIECE | IRIS | |
| %PLUS | IRIS | |
| POSITION | ANSI92 | Not supported by Oracle Database or Microsoft SQL Server. |
| POWER | BASIC | |
| QUARTER | IRIS | |
| RADIANS | EXTENDED | |
| REPEAT | EXTENDED | |
| REPLACE | EXTENDED | |
| REPLICATE | EXTENDED | |
| REVERSE | EXTENDED | |
| RIGHT | ANSI92 | Not supported by Oracle Database. |
| ROUND | BASIC | |
| RPAD | ANSI92 | Not supported by Microsoft SQL Server. |
| RTRIM | BASIC | |
| SECOND | IRIS | |
| SIGN | EXTENDED | |
| SIN | BASIC | |
| SPACE | IRIS | |
| %SQLSTRING | IRIS | |
| %SQLUPPER | IRIS | |
| SQRT | BASIC | |
| SQUARE | BASIC | |
| STR | IRIS | |
| STRING | IRIS | |
| STUFF | IRIS | |
| SUBSTR | IRIS | |
| SUBSTRING | ANSI92 | Not supported by Oracle Database. |
| SYSDATE | EXTENDED | |
| TAN | BASIC | |
| TIMESTAMPADD | IRIS | |
| TIMESTAMPDIFF | IRIS | |
| TO_CHAR | EXTENDED | |
| TO_DATE | ANSI92 | Not supported by Microsoft SQL Server. |
| TO_NUMBER | ANSI92 | |
| TO_POSIXTIME | IRIS | |
| TO_TIMESTAMP | IRIS | |
| $TRANSLATE | IRIS | |
| TRIM | ANSI92 | Microsoft SQL Server only supports the TRIM(BOTH <string-expression>) variant. |
| TRUNCATE | IRIS | |
| %TRUNCATE | IRIS | |
| UCASE | BASIC | |
| UPPER | BASIC | |
| WEEK | IRIS | |
| YEAR | IRIS |
