Vertica Table Creation

In two ways we can create a table in vertica,

• Without projections
• With Projections

Without Projection: 
      If we don't mention projections while the time of table creation, default super projection will create (During data insertion) based on inserted data.

Syntax:

CREATE TABLE <Schema Name>.<Table Name>

( <Column name0> <data type0>,

<Column name1> <data type1>,

<Column name2> <data type2>,

.

.

.

)

Example:

CREATE TABLE SAMPLE.SAMPLE1 (ID INT,NAME VARCHAR(50))

With Projection: 
      If we mention projection while the time of table creation, based on that projection will create along with table.

Syntax:

CREATE TABLE <Schema Name>.<Table Name>

( <Column name0> <data type0>,

<Column name1> <data type1>,

<Column name2> <data type2>,

.

.

.

) 

ORDER BY <Column Name1>,<Column Name2>,....

SEGMENTED BY HASH(<Column Name1>,<Column Name2>,...

ALL NODES;                   

Example:

CREATE TABLE SAMPLE.SAMPLE1 (ID INT,NAME VARCHAR(50)) ORDER BY ID SEGMENTED BY HASH(ID,NAME)ALL NODES;

Note: In order by section we need to mention key columns (frequently used columns),
SEGMENTED BY is used for big tables. UNSEGMENTED BY is used for small tables.



See Also:
Create a projection on 'Existing table'