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).
Note: 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 |
Note: 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
CREATE FLEX EXTERNAL TABLE AS COPY
- Creating Flex Tables in the Administrator's Guide