CREATE FLEX EXTERNAL TABLE AS COPY

The CREATE FLEX EXTERNAL TABLE AS COPY statement creates a flexible external table. This statement is a combination of the CREATE FLEX TABLE and COPY statements, supporting a subset of each statement's parameters. You can also use user-defined load extension functions (UDLs) to create external flex tables. For more information about UDL syntax, see User Defined Load (UDL) and COPY. For more details about creating and using flex tables, see Using Flex Tables.

Note: Vertica does not create a superprojection for an external table when you create it.

For details about creating and using flex tables, see Creating Flex Tables in Using Flex Tables.

Caution: Canceling a CREATE FLEX EXTERNAL TABLE AS COPY statement can cause unpredictable results. Vertica recommends that you allow the statement to finish, then use DROP TABLE after the table exists.

Syntax

CREATE {FLEX | FLEXIBLE} EXTERNAL TABLE [ IF NOT EXISTS ] [[database.]schema.]table-name {
... ( [ column‑definition[,…] ] ) 
} [INCLUDE | EXCLUDE [SCHEMA] PRIVILEGES]
... AS COPY .... [ ( { column-as-expression | column } [ FILLER datatype ] ]
[ FROM 
... 'pathToData' [ ON nodename | ON ANY NODE | ON (nodeset) ] 
...... [ BZIP | GZIP | LZO | UNCOMPRESSED ] [,…] 
...[ WITH ]  
...[ SOURCE source(arg='value')] 
...[ FILTER filter(arg='value') ]
...[ PARSER flexparser(arg='value') ] 
...[ DELIMITER [ AS ] 'char' ]
...[ TRAILING NULLCOLS ]
...[ NULL [ AS ] 'string' ]
...[ ESCAPE [ AS ] 'char' | NO ESCAPE ]
...[ ENCLOSED [ BY ] 'char' ] 
...[ RECORD TERMINATOR 'string' ]
...[ SKIP integer ]
...[ SKIP BYTES integer ]
...[ TRIM 'byte' ]
...[ REJECTMAX integer ]
...[ EXCEPTIONS 'path' [ ON nodename ] [,…] ]
...[ REJECTED DATA 'path' [ ON nodename ]  [,…] ]
...[ ENFORCELENGTH ]
...[ ABORT ON ERROR ]

Parameters

See CREATE TABLE and COPY parameters for all parameter descriptions.

Excluded Parameters

CREATE FLEXIBLE EXTERNAL TABLE AS COPY does not support the following parameters:

CREATE TABLE 
AS AT EPOCH
 
AT TIME 'timestamp'
 
ORDER BY
 
ENCODED BY
 
hash‑segmentation‑clause
 
unsegmented‑clause
 
KSAFE k‑num
 
PARTITION BY
COPY 
FROM STDIN
 
FROM LOCAL
 
DIRECT
 
TRICKLE
 
NO COMMIT

Privileges

Superuser, or non-superuser with the following privileges:

Examples

To create an external flex table: 

=> CREATE flex external table mountains() AS COPY FROM 'home/release/KData/kmm_ountains.json' PARSER fjsonparser();
CREATE TABLE

As with other flex tables, creating an external flex table produces two regular tables: the named table and its associated _keys table.  The keys table is not an external table: 

=> \dt mountains
                 List of tables
 Schema |   Name    | Kind  |  Owner  | Comment
--------+-----------+-------+---------+---------
 public | mountains | table | release |
(1 row)

You can use the helper function, COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW, to compute keys and create a view for the external table: 

=> SELECT compute_flextable_keys_and_build_view ('appLog');

                     compute_flextable_keys_and_build_view
--------------------------------------------------------------------------------------------------
Please see public.appLog_keys for updated keys
The view public.appLog_view is ready for querying
(1 row)

  1. Check the keys from the _keys table for the results of running the helper application:

    => SELECT * FROM appLog_keys;
                          key_name                       | frequency |   data_type_guess
----------------------------------------------------------+-----------+------------------
contributors                                             |         8 | varchar(20)
coordinates                                              |         8 | varchar(20)
created_at                                               |         8 | varchar(60)
entities.hashtags                                        |         8 | long varbinary(186)
.
    .
    .
retweeted_status.user.time_zone                          |         1 | varchar(20)
retweeted_status.user.url                                |         1 | varchar(68)
retweeted_status.user.utc_offset                         |         1 | varchar(20)
retweeted_status.user.verified                           |         1 | varchar(20)
(125 rows)

  2. Query from the external flex table view:

    => SELECT "user.lang" FROM appLog_view;
     user.lang
-----------
it
en
es
en
en
es
tr
en
(12 rows)

See Also