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 |
---|---|
|
Create a new table, and overwrite any existing table by the same name. Does not return an error if the table already exists. |
|
The name of the schema in which to create the table. |
|
The name of the table to create, which must be unique inside the schema. |
|
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. |
|
Specifies the format of the source files, such as |
|
Specifies a path or URI of the source files, such as |
|
Specifies the field delimiter for CSV files. Defaults to |
|
Specifies the record delimiter for CSV files. Defaults to a newline, |
|
Credentials for authenticated S3 access |
Examples¶
This section includes the following examples:
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.