CREATE FOREIGN TABLE¶

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

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 [ OR REPLACE ] FOREIGN TABLE [ "<schema_name>" ]."<table_name>" (
  [ column_def [, ...] ] -- When creating foreign tables using CSV source files, it is mandatory to provide the complete table DDL
)
  [ FOREIGN DATA ] WRAPPER fdw_name
  [ OPTIONS ( option_def [, ...  ] ) ]

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 }'
  | QUOTE = {'C' | E'\ooo') -- for CSV only
 ]

path_spec ::= { GS URI | 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 and datatype. Other column constraints and default values may optionally be added. When creating foreign tables using CSV source files, it is mandatory to provide the complete table DDL
`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
`QUOTE`	Specifies an alternative quote character. The quote character must be a single, 1-byte printable ASCII character, and the equivalent octal syntax of the copy command can be used. The quote character cannot be contained in the field delimiter, the record delimiter, or the null marker. `QUOTE` can be used with `csv_fdw` in `COPY FROM` and foreign tables. The following characters cannot be an alternative quote character: `"-.:\\0123456789abcdefghijklmnopqrstuvwxyzN"`

Examples¶

Creating a Tab-Delimited Table¶

CREATE
OR REPLACE FOREIGN TABLE nba_new(
  "player_name" TEXT null,
  "team_name" TEXT null,
  "jersey_number" INT null,
  "position" TEXT null,
  "age" INT null,
  "height" TEXT null,
  "weight" INT null,
  "college" TEXT null,
  "salary" INT null
)
WRAPPER
  csv_fdw
OPTIONS
   (LOCATION = 'gs://blue_docs/nba.csv',
   DELIMITER = '\t'
  );

Creating a Table Located In a HDFS Directory¶

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

Creating a Table Located Within a S3 Bucket of ORC Files¶

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

Converting a Foreign Table to an Internal Table¶

Using a foreign table allows you to perform ETL-like operations by applying SQL functions and operations to raw files.

CREATE TABLE
  real_table AS
SELECT
  *
FROM
  some_foreign_table;

Customizing Quotations Using Alternative Characters¶

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