In Vertica, actual physical data is stored in projections and not in tables. This tutorials provides most of the information related to projections in Vertica.

Vertica Projections

Projections are one kind of objects in Vertica where actual data for tables is stored. There can be multiple projections on a table but there will/should be atleast 1 projection which must have all the columns included in projection definition and such projections with all columns included are called 'Super Projection'. Other projections may be created considering usage of columns in the query and it may have few specific columns required for query. Each projection will have its own set of data.

→ Projections Definition: Getting the projections definition.

SELECT EXPORT_OBJECTS('','schema_name.projection_name');

→ List of projections: Checking all the projections created on vertica table.

SELECT projection_schema, projection_name, projection_basename,is_super_projection FROM PROJECTIONS WHERE LOWER(anchor_table_name) = 'table_name' ;
 
 Note: Value 'true' in is_super_projection column specify that it is a Super Projection.

→ Projection Refresh Status: To check the projection refresh status.

select * from PROJECTION_REFRESHES where projection_name='projection_name' ;

Note:

• Value 't' in 'is_executing' column specify that Vertica is refreshing the projection.
• Value in refresh_status column indicates whether projection is queued, refreshing, already refreshed or failed

→ Size of Projection: Finding the size of projection in Vertica.

SELECT projection_schema, projection_name, SUM (used_bytes) / (1024^3) AS size_in_gb FROM v_monitor.projection_storage WHERE LOWER(projection_schema) = 'schema_name' AND LOWER(projection_name) = 'projection_name' GROUP BY projection_schema, projection_name;

→ Restriction in Projections: There are some restrictions while creating projections. Few of them are listed below:

• Views are not allowed : If user will trying to create projections using views then it will fail with below error

  
  [Vertica][VJDBC](4268) ERROR: Only tables are allowed in FROM clause of projection [SQL State=42601, DB Errorcode=4268].
  

  
  
• 'Order By' are not allowed in aggregate Projections : If user will trying to create projections with 'Order By' clause then it will fail with below error

  
  [Vertica][VJDBC](6372) ERROR: ORDER BY is not allowed in aggregate projection. The aggregate projection is automatically ordered on group by columns.
  

  
  
• Filter conditions are not allowed : If user will trying to create projections by adding 'WHERE' clause on some column then it will fail with below error

  
  [Vertica][VJDBC](5600) ERROR: Invalid predicate in projection-select. Only PK=FK equijoins are allowed [SQL State=42601, DB Errorcode=5600].
  

  
  
• Only PK=FK equijoins are allowed: If user will trying to create projections on two tables with joins on non Primary Key(PK) & non Foreign Key(FK) then it will fail with below error

  
  [Vertica][VJDBC](5600) ERROR: Invalid predicate in projection-select. Only PK=FK equijoins are allowed [SQL State=42601, DB Errorcode=5600].