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.

The CREATE FOREIGN TABLE command 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

For more information, see the Foreign tables guide.

The CREATE FOREIGN TABLE page describes the following:

Syntax¶

The following is the correct syntax for CREATE_FOREIGN_TABLE:

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¶

The following table shows the CREATE_FOREIGN_TABLE 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¶

This section includes the following examples:

Creating a Simple Table from a Tab-Delimited File
Creating a Table from a Directory of Parquet Files on HDFS
Creating a Table from a Bucket of Files on S3
Changing an External Table into a Regular Table

Creating a Simple Table from a Tab-Delimited File ¶

The following example shows how to create a simple table from a tab-delimited file (TSV):

CREATE OR REPLACE EXTERNAL TABLE cool_animals
  (id INT NOT NULL, name VARCHAR(30) NOT NULL, weight FLOAT NOT NULL)
USING FORMAT csv
WITH  PATH  '/home/rhendricks/cool_animals.csv'
      FIELD DELIMITER '\t';

Creating a Table from a Directory of Parquet Files on HDFS ¶

The following example shows how to create a table from a directory of Parquet files on HDFS:

CREATE EXTERNAL TABLE users
  (id INT NOT NULL, name VARCHAR(30) NOT NULL, email VARCHAR(50) NOT NULL)
USING FORMAT Parquet
WITH  PATH  'hdfs://hadoop-nn.piedpiper.com/rhendricks/users/*.parquet';

Creating a Table from a Bucket of Files on S3 ¶

The following example shows how to create a table from a bucket of files on S3:

CREATE EXTERNAL TABLE users
  (id INT NOT NULL, name VARCHAR(30) NOT NULL, email VARCHAR(50) NOT NULL)
USING FORMAT Parquet
WITH  PATH  's3://pp-secret-bucket/users/*.parquet'
      AWS_ID 'our_aws_id'
      AWS_SECRET 'our_aws_secret';

Changing an External Table into a Regular Table ¶

The following example shows how to change an external table into a regular table:

CREATE TABLE real_table
 AS SELECT * FROM external_table;

Permissions¶

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