CREATE FOREIGN TABLE

Note

Starting with SQream DB v2020.2, external tables have been renamed to foreign tables, and use a more flexible foreign data wrapper concept.

Upgrading to a new version of SQream DB converts existing external tables automatically.

CREATE FOREIGN TABLE creates a new foreign table in an existing database.

See more in the Foreign tables guide.

Tip

Data in a foreign table can change if the sources change, and frequent access to remote files may harm performance.
To create a regular table, see CREATE TABLE

Permissions

The role must have the CREATE permission at the database level.

Syntax

create_table_statement ::=
    CREATE [ OR REPLACE ] FOREIGN TABLE [schema_name].table_name (
        { column_def [, ...] }
    )
    [ FOREIGN DATA ] WRAPPER fdw_name
    [ OPTIONS ( option_def [, ...  ] ) ]
    ;

schema_name ::= identifier

table_name ::= identifier

fdw_name ::=
    { csv_fdw | orc_fdw | parquet_fdw }

option_def ::=
{
   LOCATION = '{ path_spec }'
   | DELIMITER = '{ field_delimiter }' -- for CSV only
   | RECORD_DELIMITER = '{ record_delimiter }' -- for CSV only
   | AWS_ID '{ AWS ID }'
   | AWS_SECRET '{ AWS SECRET }'
}

path_spec ::= { local filepath | S3 URI | HDFS URI }

field_delimiter ::= delimiter_character

record_delimiter ::= delimiter_character

column_def ::=
    { column_name type_name [ default ] [ column_constraint ] }

column_name ::= identifier

column_constraint ::=
    { NOT NULL | NULL }

default ::=
    DEFAULT default_value
    | IDENTITY [ ( start_with [ , increment_by ] ) ]

Parameters

Parameter	Description
`OR REPLACE`	Create a new table, and overwrite any existing table by the same name. Does not return an error if the table already exists. `CREATE OR REPLACE` does not check the table contents or structure, only the table name.
`schema_name`	The name of the schema in which to create the table.
`table_name`	The name of the table to create, which must be unique inside the schema.
`column_def`	A comma separated list of column definitions. A minimal column definition includes a name identifier and a datatype. Other column constraints and default values can be added optionally.
`WRAPPER ...`	Specifies the format of the source files, such as `parquet_fdw`, `orc_fdw`, or `csv_fdw`.
`LOCATION = ...`	Specifies a path or URI of the source files, such as `/path/to/*.parquet`.
`DELIMITER = ...`	Specifies the field delimiter for CSV files. Defaults to `,`.
`RECORD_DELIMITER = ...`	Specifies the record delimiter for CSV files. Defaults to a newline, `\n`
`AWS_ID`, `AWS_SECRET`	Credentials for authenticated S3 access

Examples

A simple table from Tab-delimited file (TSV)

CREATE OR REPLACE FOREIGN TABLE cool_animals
  (id INT NOT NULL, name text(30) NOT NULL, weight FLOAT NOT NULL)
WRAPPER csv_fdw
OPTIONS
  ( LOCATION = '/home/rhendricks/cool_animals.csv',
    DELIMITER = '\t'
  )
 ;

A table from a directory of Parquet files on HDFS

CREATE FOREIGN TABLE users
  (id INT NOT NULL, name text(30) NOT NULL, email text(50) NOT NULL)
WRAPPER parquet_fdw
OPTIONS
  (
    LOCATION =  'hdfs://hadoop-nn.piedpiper.com/rhendricks/users/*.parquet'
  );

A table from a bucket of ORC files on S3

CREATE FOREIGN TABLE users
  (id INT NOT NULL, name text(30) NOT NULL, email text(50) NOT NULL)
WRAPPER orc_fdw
OPTIONS
  (
      LOCATION = 's3://pp-secret-bucket/users/*.orc',
      AWS_ID = 'our_aws_id',
      AWS_SECRET = 'our_aws_secret'
   );

Changing a foreign table to a regular table

Materializes a foreign table into a regular table.

CREATE TABLE real_table
 AS SELECT * FROM some_foreign_table;