CREATE EXTERNAL TABLE AS COPY

The CREATE EXTERNAL TABLE AS COPY statement creates a query definition for a table external to your Vertica database. This statement is a combination of the CREATE TABLE and COPY statements, supporting a subset of each statement's parameters, noted below. You can also use user-defined load extension functions (UDLs) to create external tables. For more information about UDL syntax, see User Defined Load (UDL).

You might discover that you need to change a column data type. For example, a VARCHAR might have longer data values than you anticipated at table-creation time. You can use ALTER TABLE to change the data types of columns instead of dropping and recreating the table.

You can use CREATE EXTERNAL TABLE AS COPY with any types except types from the Place package.

Syntax

CREATE EXTERNAL TABLE [ IF NOT EXISTS ] [[database.]schema.]table-name 
... ( Column-Definition  [ , ... ] ) 
[{INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES] 
AS COPY 
... [ ( { column-as-expression | column }
...... [ DELIMITER [ AS ] 'char' ]
...... [ ENCLOSED [ BY ] 'char' ]
...... [ ENFORCELENGTH ]
...... [ ESCAPE [ AS ] 'char' | NO ESCAPE ]
...... [ FILLER datatype ]
...... [ FORMAT 'format' ] 
...... [ NULL [ AS ] 'string' ]
...... [ TRIM 'byte' ]
... [, ... ] ) ]
... [ COLUMN OPTION ( column 
...... [ DELIMITER [ AS ] 'char' ]
...... [ ENCLOSED [ BY ] 'char' ]
...... [ ENFORCELENGTH ]
...... [ ESCAPE [ AS ] 'char' | NO ESCAPE ]
...... [ FORMAT 'format' ]
...... [ NULL [ AS ] 'string' ]
...... [ TRIM 'byte' ]
... [, ... ] ) ]
{ FROM  
... 'pathToData' [ , ... ] [ ON nodename | ON ANY NODE | ON (nodeset) ] 
... | [ WITH ]  SOURCE source([arg=value [,...] ]) }
...... [ BZIP | GZIP | LZO | UNCOMPRESSED ] [, ...]  
.......[ NATIVE 
......| FIXEDWIDTH COLSIZES {( integer ) [,....]}
......| NATIVE VARCHAR  
......| ORC 
......| PARQUET 
...]
...[ ABORT ON ERROR ]
...[ DELIMITER [ AS ] 'char' ]
...[ ENCLOSED BY 'char' [ AND 'char' ] ]
...[ ENFORCELENGTH ]
...[ ERROR TOLERANCE ]
...[ ESCAPE AS 'char' | NO ESCAPE ]
...[ EXCEPTIONS 'path' [ ON nodename ] [, ...] ]
...[ WITH ] ...[ FILTER filter([arg=value [,...] ]) ] [...] 
...[ NULL [ AS ] 'string' ]
...[ WITH ] [ PARSER parser([arg=value [,...] ]) ]
...[ RECORD TERMINATOR 'string' ]
...[ REJECTED DATA 'path' [ ON nodename ] [, ...] ]
...[ REJECTMAX integer ]
...[ SKIP integer ]
...[ SKIP BYTES integer ]
...[ TRAILING NULLCOLS ]
...[ TRIM 'byte' ]

Parameters

For all supported parameters, see the CREATE TABLE and COPY statements. For information on using this statement with UDLs see Load (UDLs).

Privileges

You must be a database superuser to create external tables, unless you have created a user-accessible storage location to which the COPY refers, see CREATE LOCATION. If external tables exist, you must also be a database superuser to access them through a select statement.

You must have full access (including SELECT) to an external table that a user has privileges to create. The database superuser must also grant READ access to the USER-accessible storage location, see GRANT (Storage Location).

Examples

Examples of external table definitions:

=> CREATE EXTERNAL TABLE ext1 (x integer) AS COPY FROM '/tmp/ext1.dat' DELIMITER ',';
=> CREATE EXTERNAL TABLE ext1 (x integer) AS COPY FROM 'hdfs:///dat/ext1.dat';
=> CREATE EXTERNAL TABLE ext1 (x integer) AS COPY FROM '/tmp/ext1.dat.bz2' BZIP DELIMITER ',';
=> CREATE EXTERNAL TABLE ext2 (x integer, y integer) AS COPY (x as '5', y) FROM '/tmp/ext1.dat.bz2' BZIP DELIMITER ',';				
=> CREATE EXTERNAL TABLE sales (itemID INT, date DATE, price FLOAT) 
	AS COPY FROM 's3://datalake/sales/*.parquet' PARQUET;

To allow users without superuser access to use these tables, create a location for 'user' usage and grant access to it. This example shows granting access to a user named Bob to any external table whose data is located under /tmp (including in subdirectories to any depth):

=> CREATE LOCATION '/tmp' ALL NODES USAGE 'user';  
=> GRANT ALL ON LOCATION '/tmp' to Bob;

The following example shows CREATE EXTERNAL TABLE using a source:

=> CREATE SOURCE curl AS LANGUAGE 'C++' NAME 'CurlSourceFactory' LIBRARY curllib;
=> CREATE EXTERNAL TABLE curl_table1 as COPY SOURCE CurlSourceFactory;