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, as noted below. 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.

Privileges

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

Permission requirements for flex external tables differ from other flex tables. 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).

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

Syntax

CREATE {FLEX | FLEXIBLE} EXTERNAL TABLE [ IF NOT EXISTS ] [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

The following parameters from the parent statements are not supported in the CREATE FLEXIBLE EXTERNAL TABLE AS COPY statement:

CREATE TABLE
AS AT EPOCH LAST
AT TIME 'timestamp'
ORDER BY table-column [,...]
ENCODED BY
hash-segmentation-clause
UNSEGMENTED {node | node all}
KSAFE [k_num]
PARTITION BY partition-clause
COPY
FROM STDIN
FROM LOCAL
DIRECT
TRICKLE
NO COMMIT

For all supported parameters, see the CREATE TABLE and COPY statements.

Notes

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 once the table exists.

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)