Automatic Replication of Unsegmented Projections
Refer to the CREATE PROJECTION command in the SQL Reference Manual for detailed information about using unsegmented projections.
Vertica requires an exact, non-segmented copy of each 
dimension table superprojection on each node. You can accomplish this using a single CREATE PROJECTION command for each dimension table. The UNSEGMENTED ALL NODES syntax automatically creates a unsegmented projection on each node in the database.
A superprojection is a projection that contains every column of a table in the Logical Schema. A table can have multiple superprojections with different sort orders.
A dimension table (sometimes called a lookup or reference table) is one of a set of companion tables to a fact table in a star schema. It contains the PRIMARY KEY column corresponding to the join columns in fact tables. For example, a business might use a dimension table to contain item codes and descriptions.
Dimension tables can be connected to other dimension tables to form a hierarchy of dimensions in a snowflake schema.
Automatically replicated projections are named:
projection-name_node-name
where projection-name is the name specified in the CREATE PROJECTION statement and node-name is the name of a node in the database. The list of nodes is based on a snapshot of the nodes defined at command execution time. To view a list of the nodes in a database, use the View Database command in the Administration Tools.
The tools needed for administering a Vertica database are provided in the form of a graphical user interface that allows you to perform various tasks quickly and easily. The tools also provide a convenient way to connect to a database using vsql. Always run the Administration Tools on the Administration Host if possible.
vsql is the Vertica implementation of psql, a character-based, interactive, front-end that is part of PostgreSQL and used by other database management systems. It allows you to type in SQL statements and see the results. It also provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.
$ /opt/vertica/bin/adminTools
See the Administration Tools Reference for a complete description.
For example, if the:
- name of the projection is ABC
- names of the nodes are NODE01, NODE02, and NODE03
- command is
CREATE PROJECTION ABC ... UNSEGMENTED ALL NODES
The automatically replicated projections have the names ABC_NODE01, ABC_NODE02, and ABC_NODE03.
