Skip to main content

CREATE FOREIGN TABLE (SQL)

Creates a foreign table.

Synopsis

Foreign Table from File

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name 
  ( column type, column2 type2, ... ) 
  SERVER server-name FILE file-name 
  [ USING json-options ] 

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name 
  ( column type, column2 type2, ... ) 
  SERVER server-name FILE file-name 
  COLUMNS ( col-name, col-name2, ... ) 
  [ USING json-options ] 

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name 
  ( column type, column2 type2, ... )
  SERVER server-name FILE file-name 
  COLUMNS ( col-name, col-name2, ... ) 
  VALUES ( header, header2, ... ) 
  [ USING json-options ] 

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name 
  ( column type, column2 type2, ... )
  SERVER server-name FILE file-name 
  VALUES ( header, header2, ... ) 
  [ USING json-options ]
Foreign Table from Database

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name 
  [ ( column type, column2 type2, ... ) ] 
  SERVER server-name 

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name 
  [ ( column type, column2 type2, ... ) ]  
  SERVER server-name TABLE external-table 
  [ VALUES ( header, header2, ... ) ] 

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name 
  [ ( column type, column2 type2, ... ) ]
  SERVER server-name QUERY query 

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name 
  [ ( column type, column2 type2, ... ) ]
  SERVER server-name VALUES ( header, header2, ... )

Description

The CREATE FOREIGN TABLE command creates a foreign table definition in the specified structure. CREATE FOREIGN TABLE creates a projection of data from an external data source than can be queried alongside data native to InterSystems IRIS.

If you do not specify the IF NOT EXISTS option and attempt to create a foreign table with the same name as a pre-existing foreign table, the system returns an SQLCODE -201 error. The IF NOT EXISTS option suppresses the error, but InterSystems IRIS does not recreate the foreign table.

Suppresses the error that arises if a schema with name already exists. The schema is not re-created.

If you create a foreign table from a .csv file, you may specify projection options by using a JSON object or a string containing a JSON object in a USING clause, just as you might with a LOAD DATA command.

Foreign Table from File

You can create a foreign table that projects data from a file external to your instance of InterSystems IRIS. In these cases, the foreign server on which you create the table must use CSV as its foreign data wrapper. Note that there is a slight difference in behavior between usage when the file does and does not.

  • CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name ( column type, column2 type2, ...) SERVER server-name FILE file-name [ USING json-options ] creates a foreign table that projects data stored in the specified file name.

    • If the file does not have a header, the columns in the new foreign table contain data from the first n columns in the file, where n is the length of the primary column list. Within InterSystems SQL, you can query this foreign table by referring to the column names in the primary column list.

      CREATE FOREIGN TABLE (
         firstName VARCHAR(15), 
         lastName VARCHAR(15), 
         DOB DATE
      ) Sample.Person SERVER Sample.HospitalDir FILE 'person.csv'
      
    • If the file does have a header, The column names in the primary column list must correspond with header names of columns in the file. Only the column names in the file that correspond to column names in the primary column list appear in the projected table.

      CREATE FOREIGN TABLE (
         firstName VARCHAR(15), 
         lastName VARCHAR(15), 
         DOB DATE
      ) Sample.Person SERVER Sample.HospitalDir FILE 'person.csv' USING { "from": { "file": { "header": true } } }
      
  • CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name ( column type, column2 type2, ...) SERVER server-name FILE file-name COLUMNS ( col-name type, col-name2 type2, ... ) [ USING json-options ] creates a foreign table that projects data stored from the specified file with a column order specified by the COLUMNS clause. Names in the primary column list specify the names of the columns in the table and positionally correlate with the columns of the file. The names in the COLUMNS clause must be identical to the names in the primary column list and each name must appear in both lists. The COLUMNS clause can be used to reorder the columns from the file; the order of columns in the COLUMNS clause does not need to match the order of the columns in the primary column list. The order of the columns in the foreign table is determined by the position of the column names in the COLUMNS clause.

    If the file has a header, this command behaves identically, as the file’s header is disregarded. In this case, you should specify the from.file.header JSON option as true in the USING clause.

    CREATE FOREIGN TABLE Sample.Person (
       FileColumnOne VARCHAR(10),
       FileColumnTwo VARCHAR(20)
    ) SERVER Sample.HospitalDir FILE person.csv COLUMNS (FileColumnTwo VARCHAR(20), FileColumnOne VARCHAR(10))
    
  • CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name ( column type, column2 type2, ...) SERVER server-name FILE file-name COLUMNS ( col-name type, col-name2 type2, ... ) VALUES ( header, header2, ...) [ USING json-options ] creates a foreign table that projects data stored from the specified file with a column order specified by the VALUES clause, possibly omitting certain columns from the .csv file. The primary column list defines the column names and types that appear in the foreign table. The COLUMNS clause lists the columns in the file and their type; the length of this list can be longer than the length of the primary column list and the names need be similar. The VALUES clause reorders the column names in the COLUMNS clause, but is the length of the primary columns list.

    You may use the VALUES clause to omit certain columns from the file (specified in the COLUMNS clause) from the foreign table. The order of names in the VALUES clause is mapped onto the order of column names in the primary column list. Within InterSystems SQL, you can query this foreign table by referring to the column names in the primary column list.

    If the file has a header, this command behaves identically, as the file’s header is disregarded. In this case, you should specify the from.file.header JSON option as true in the USING clause.

    In the following example, the FieldOne column projects data from the second element of the COLUMNS clause, the FieldTwo column projects data from the first element of the COLUMNS clause, and the FieldThree column projects data from the fourth element of the COLUMNS clause.

    CREATE FOREIGN TABLE Sample.Person (
       FieldOne VARCHAR(10),
       FieldTwo VARCHAR(20),
       FieldThree INTEGER
    ) SERVER Sample.HospitalDB FILE person.csv COLUMNS (FirstName VARCHAR(10), LastName(20), DOB DATE, Age INTEGER) VALUES (LastName, FirstName, Age)
    
  • CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name ( column type, column2 type2, ...) SERVER server-name FILE file-name VALUES ( header, header2, ...) [ USING json-options ] creates a foreign table that projects a subset of data stored from the specified file into the table. The column names in the VALUES clause must correspond to column names in the .csv file, which may be different from the names in the primary column list. The order of columns in the foreign table is determined by the order of the columns in the primary column list, with the data in those columns coming from the positionally related element of the VALUES clause.

    If the file does not have a header, the VALUES clause is ignored and meaningless.

    CREATE FOREIGN TABLE Sample.Person (
       FirstName VARCHAR(10),
       LastName VARCHAR(20)
    ) SERVER Sample.HospitalDB FILE person.csv VALUES (FirstNameInFile, LastNameInFile) USING { "from": { "file": { "header": 1 } } }
    

Foreign Table from Database

You can create a foreign table that projects data from a database external to your instance of InterSystems IRIS. In these cases, the foreign server on which you create the table must use JDBC as its foreign data wrapper.

  • CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name ( column type, column2 type2, ... ) SERVER server-name [ TABLE external-table ] creates a foreign table that projects data from a table that exists in specified table. The created table has the same columns as the table in the external database. If you omit the TABLE clause, InterSystems IRIS attempts to access a table on the foreign server using table-name, rather than external-table.

    CREATE FOREIGN TABLE Sample.Person (
       FirstName VARCHAR(10),
       LastName VARCHAR(20)
    ) SERVER Sample.ExternalDB TABLE 'hospital.people'
    
  • CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name ( column type, column2 type2, ... ) SERVER server-name QUERY query creates a foreign table that projects data returned from executing a query, specified by query, against a table that exists in an external database. InterSystems SQL does not validate the query before attempting to execute it against the external database.

    CREATE FOREIGN TABLE Sample.Team (
       FirstName VARCHAR(10),
       LastName VARCHAR(20)
    ) SERVER Sample.ExternalDB QUERY 'SELECT FirstName,LastName FROM Hospital.Patients'
    
  • CREATE FOREIGN TABLE [ IF NOT EXISTS ] table-name ( column type, column2 type2, ... ) SERVER server-name [ TABLE external-table ] VALUES ( header, header2, ... ) creates a foreign table that projects data stored in the specified table with column names that differ from those in the external data source. If you omit the TABLE clause, InterSystems IRIS attempts to access a table on the foreign server using table-name, rather than external-table. The headers named in the VALUES clause identify the column names from the external data source, but may differ from the names that you have specified in the column list. Consequently, the VALUES clause must have the same number of columns as the column list.

    CREATE FOREIGN TABLE Sample.Team (
       TeamID BIGINT,
       Name VARCHAR(100)
    ) SERVER Sample.ExternalDB TABLE 'hospital.teams' VALUES (team_id, name)
    

Arguments

table-name

In a CREATE FOREIGN TABLE command, this argument specifies the name of the foreign table that you want to create as a valid identifier. A table name can be qualified or unqualified.

  • An unqualified foreign table name has the following syntax: tablename; it omits schema (and the period (.) character). An unqualified table name takes the default schema name. The initial system-wide default schema name is SQLUser, which corresponds to the default class package name User. Schema search path values are ignored.

    If you have created a foreign table using a JDBC connection and omitted the TABLE clause, then the unqualified table name is leveraged against the external data source to create the project, but the table is accessible through InterSystems SQL under the default schema qualified name.

    If you have specified an unqualified foreign table name with a JDBC connection and do not specify a TABLE clause, then the

    The system-wide default schema name can be configured.

    To determine the current system-wide default schema name, use the $SYSTEM.SQL.Schema.Default()Opens in a new tab method.

  • A qualified foreign table name has the following syntax: schema.tablename. It can specify either an existing schema name or a new schema name. Specifying an existing schema name places the foreign table within that schema. Specifying a new schema name creates a new schema and associated class package, and places the table within that schema.

column

In a CREATE FOREIGN TABLE command, specify the column name or a comma-separated list of column names, used to define the columns of the table you are creating, in the primary column list. You can specify the column names in any order, with a space separating the column name from its associate data type. By convention, each column definition is usually presented on a separate line and indentation is used. This convention is recommended for readability, but is not required.

Enclose primary column lists in parentheses.

type

The InterSystems SQL data type class of the column name specified by column. A specified data type limits a column’s allowed data values to the values appropriate for that data type. InterSystems SQL supports most standard SQL data types.

Data from the external data source is coerced into the specified type as part of the project. If the field cannot be coerced, such as an invalid date format, a runtime error is raised.

server-name

In a CREATE FOREIGN TABLE command, this argument specifies the foreign server configuration that accesses the external data source.

You may specify a qualified or unqualified foreign server name. If you specify an unqualified foreign server name, the system attempts to locate the foreign server within the default schema, which is SQLUser by default. If you specify a qualified foreign server name, the system attempts to locate the foreign server within the provided schema.

If the foreign server cannot be located within the determined schema, the system raises an SQLCODE -360 error.

file-name

In a CREATE FOREIGN TABLE command, this argument specifies the location of a .csv file containing the data to project into InterSystems IRIS, defined as a complete file path enclosed in quotes. This argument should only be used when the foreign server specified in the command uses the CSV option for its foreign data wrapper.

  • Each line in a file specifies a separate row to be projected into the foreign table. Newline (“\n”) is the default line separator. Blank lines are ignored.

  • Data values in a row are separated by a column separator character. A comma is the default column separator character. All data fields must be indicated by column separators, including unspecified data indicated by placeholder column separators. You can define a different column separator character by specifying the columnseparator option in the USING json-options clause.

  • By default, no escape character is defined. To include the column separator character as a literal in a data value, enclose the data value in quotation marks. To include a quotation mark in a quoted data value, double the quote character. You can define an escape character specifying the escapechar option in the USING json-options clause.

  • By default, data values are specified in the order of the fields in the foreign table. You can use the COLUMNS and VALUES clauses to specify the data in a different order.

  • All data in a .csv file is validated against the table’s data criteria, including the number of data fields in the record, and the data type and data length for each field. If a certain record in the file cannot be validated, an error message is issued. Note that date and time constructs in .csv files must be in ODBC format, as other formats may produce errors or incorrect query results.

col-name

In a CREATE FOREIGN TABLE command, this argument appears in a COLUMNS clause. When the file has no header, the COLUMNS clause provides a name for the columns in the file. When the file has a header, the COLUMNS clause can often be omitted.

header

In a CREATE FOREIGN TABLE command, this argument appears in a VALUES clause. A VALUES clause may be used in a variety of scenarios

external-table

In a CREATE FOREIGN TABLE command that connects to an eternal data source through a JDBC connections, this argument supplies the name of the external table to project into InterSystems IRIS. If you omitted a column list, a foreign table created in this manner copies the column definitions, including column names and data types (where supported), from the data source.

query

In a CREATE FOREIGN TABLE command that connects to an external data source through a JDBC connection, this argument supplies the column definitions and column data for a foreign table by querying a table in the external data source. It is a SELECT query that is executed against the external data source.

Foreign tables created in this way copy column definitions from the external data source, including column names and data types (when supported). A foreign table can copy column definitions from multiple tables if the query specifies joined tables from the external data source.

json-options

This argument specifies loading options as a JSON object or a string containing a JSON object in the USING clause. Its usage is nearly identical to the corresponding argument in the LOAD DATA command. For a complete overview on the syntax and options, refer to the LOAD DATA documentation. Note that the CREATE FOREIGN TABLE command supports only the options in the from.file tree.

See Also

FeedbackOpens in a new tab