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 int, long varchar, and long varbinary data types.

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).

Syntax

CREATE EXTERNAL TABLE [ IF NOT EXISTS ] [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) ] 
...... [ BZIP | GZIP | LZO | UNCOMPRESSED ] [, ...]  
...[ NATIVE 
......| FIXEDWIDTH COLSIZES {( integer ) [,....]}
......| NATIVE VARCHAR  
......| ORC 
......| PARQUET 
...[ WITH ] [ SOURCE source([arg=value [,...] ]) ]
...]
...[ 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).

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 ext1 (x integer, y integer) AS COPY (x as '5', y) FROM '/tmp/ext1.dat.bz2' BZIP DELIMITER ',';

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;