CREATE EXTERNAL TABLE
Warning
The CREATE EXTERNAL TABLE
syntax is deprecated, and will be removed in future versions.
Starting with SQream DB v2020.2, external tables have been renamed to foreign tables, and use a more flexible foreign data wrapper concept. See CREATE FOREIGN TABLE instead.
Upgrading to a new version of SQream DB converts existing tables automatically. When creating a new external tables, use the new foreign table syntax.
CREATE TABLE
creates a new external table in an existing database.
See more in the External tables guide.
Tip
Data in an external 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 ] EXTERNAL TABLE [schema_name].table_name (
{ column_def [, ...] }
)
USING FORMAT format_def
WITH { external_table_option [ ...] }
;
schema_name ::= identifier
table_name ::= identifier
format_def ::= { PARQUET | ORC | CSV }
external_table_option ::= {
PATH '{ path_spec }'
| FIELD DELIMITER '{ field_delimiter }'
| RECORD DELIMITER '{ record_delimiter }'
| 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 |
---|---|
|
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
A simple table from 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';
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';
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 to a regular table
Materializes an external table into a regular table.
CREATE TABLE real_table
AS SELECT * FROM external_table;