CREATE FLEX TABLE

Creates a flex table in the logical schema. Declaring columns (or other supported parameters) is optional. If you do not declare any column definitions, the statement creates two columns automatically: 

__raw__ : A LONG VARBINARY type column to store any unstructured data you load. This column has a NOT NULL constraint by default. .

__identity__ : An IDENTITY column. Flex tables use this value for segmentation and sorting, when no other column definition exists.

Additionally, creating any flex table results in three associated objects:

  • A flex table (flex_table) named in this statement
  • A related keys table, called flex_table_keys
  • A related view, called flex_table_view

Both the flex table and its associated _keys table are required to use flex tables successfully. The _keys table and _view are subservient objects of the flex table. Neither can exist without the flex table.

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

CREATE FLEX TABLE supports many of the parameters available when creating columnar tables, but not all. This section presents the optional use of column definitions, and the subset of supported parameters.

You can also create flex external tables, with some syntactical variations, as described in CREATE FLEX EXTERNAL TABLE AS COPY .

You cannot partition a flex table on any virtual column (key).

Vertica does not support flexible global temporary tables.

Syntax

CREATE {FLEX | FLEXIBLE} TABLE [ IF NOT EXISTS ] [[database.]schema.]table-name {
... ( [ column‑definition[,…]] ) 
... | [ table-constraint ( column_name,… )]
... | [ column-name-list  (create table)  ] 
}
... [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
... [ ORDER BY table-column[,…] ] 
... [ ENCODED BY column-definition[,…]
... [ hash-segmentation-clause
..... | UNSEGMENTED { NODE node | ALL NODES } ] 
... [ KSAFE [k_num] ]
... [ PARTITION BY partition-expression]
... [ AS SELECT (column-name-list) FROM (table-name) ]

Parameters

See the CREATE TABLE statement for all parameter descriptions.

Unsupported CREATE Flex Table Option

You cannot use the following options when creating a flex table:

... AS [COPY] [ [ AT EPOCH LATEST ] ... | [ AT TIME 'timestamp' ] ] 
.....[ /*+ direct */ ] query 
... | [ LIKE [schema.]existing-table [ INCLUDING PROJECTIONS | EXCLUDING PROJECTIONS ] ]
 

Default Flex Table and Keys Table Projections

Vertica automatically creates superprojections for both the flex table and keys tables when you create them.

If you create a flex table with one or more of the ORDER BY, ENCODED BY, SEGMENTED BY, or KSAFE clauses, the clause information is used to create projections. If no clauses are in use, Vertica uses the following defaults for unspecified aspects:

Table order_by encoded_by Segmentation Ksafe
flexible table
__identity__
none
by hash __identity__
1
keys_table
frequency
none
replicated/unsegmented all nodes
1

When you build a view for a flex table (see BUILD_FLEXTABLE_VIEW), the view is ordered by frequency, desc, and key_name.

Privileges

CREATE privileges on the table schema.

Examples

The following example creates a flex table named darkdata without specifying any column information. Vertica creates a default superprojection and buddy projection as part of creating the table:

=> CREATE FLEXIBLE TABLE darkdata();
CREATE TABLE

The following example creates a table called darkdata1 with one column definition (date_col). The statement specifies the partition by clause to partition the data by year. Vertica creates a default superprojection and buddy projections as part of creating the table:

=> CREATE FLEX TABLE darkdata1 (date_col date NOT NULL) partition by 
  extract('year' from date_col);
CREATE TABLE

See Also