Tables in Vertica are logical entities and they don't store actual data. This tutorials provides most of the information related to tables in Vertica.

Vertica Tables

Tables in Vertica are logical entities and they don't store actual data. Data for tables are stored in projections (a kind of object in Vertica). 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.

→ Table definition & its Projections: Getting the table definition & associated projections.

SELECT EXPORT_OBJECTS('','schema_name.table_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.

→ List of all tables: Checking all the tables created in a schema on which user has access.

select * from ALL_TABLES WHERE LOWER(schema_name) = 'schema_name';
 
More Detail on tables
select * from TABLES where LOWER(schema_name) = 'schema_name' AND LOWER(table_name) = 'table_name' ;

→ List of columns: Checking all the columns present in vertica table.

SELECT table_schema, table_name, column_name, data_type, is_nullable FROM columns WHERE LOWER(table_name) = 'table_name';

→ Alter a column in table: Vertica has a little different syntax to update existing column

• Adding column in Vertica table

  
  ALTER TABLE schema_name.table_name ADD COLUMN column_name INT;
  

• Modifying a column in Vertica table

  
  ALTER TABLE schema_name.table_name ALTER COLUMN column_name SET DEFAULT 'Y';
  

• Dropping a column in Vertica table

  
  ALTER TABLE schema_name.table_name DROP COLUMN column_name;
  

• Altering a column datatype in Vertica table

  
  ALTER TABLE schema_name.table_name ALTER COLUMN column_name SET DATA TYPE numeric(20,0);
  

→ Drop a Table: Dropping a table in vertica.

DROP TABLE schema_name.table_name;

→ Drop Table Failing: If drop table in vertica is failing with below error then DROP table command should be used with CASCADE.

[Vertica][VJDBC](3128) ROLLBACK: DROP failed due to dependencies
[Vertica][VJDBC]Detail: Cannot drop Table [tablename] because other objects depend on it [SQL State=2BV01, DB Errorcode=3128]

Syntax for dropping table in such scenario
DROP TABLE schema_name.tablename CASCADE;

→ Creating table using other table (no data): Vertica provides a way to copy a table structure from another table including projections(optional).

CREATE TABLE schema_name.tablename_new LIKE schema_name.tablename INCLUDING PROJECTIONS;

→ Statistics on table: There are 2 functions available to take statistics on Vertica table. Vertica query optimizer uses this collected statistics to recommend the best possible plan to execute a query. Optimizer will assume uniform data distribution & same storage usage for all the projections if statistics are not available.

Function 1: SELECT ANALYZE_STATISTICS ('schema_name.table_name');

Function 2: select ANALYZE_HISTOGRAM('schema_name.table_name',100);

Note: 100 in the ANALYZE_HISTOGRAM function specifys that 100 percentage of data to be sampled. This can specified as required.

ANALYZE_STATISTICS functions allows to take statistics on a single column as well but it do a fixed-size(10 percent) statistical data sampling.

select ANALYZE_STATISTICS('schema_name.table_name.column_name');

Note: ANALYZE_HISTOGRAM function is more accurate if more than 10 percent data is sampled.


→ Size of table: Finding the size of table in Vertica.

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

→ Locks on table: Checking if the table is locked in Vertica.

SELECT * FROM LOCKS WHERE LOWER(object_name) like '%schema_name.table_name%';

→ Select failure: Vertica table cannot be directly accessed using other user except the one it is created even select 'GRANT' is provided to the user. It will fail with below error and in order to access the table in that schema 'GRANT' usage on schema should be provided.

Vertica][VJDBC](3580) ERROR: Insufficient privilege: USAGE on SCHEMA 'schema_name' not granted for current user [SQL State=42501, DB Errorcode=3580]

Solution: GRANT USAGE ON SCHEMA schema_name to other_user;

→ All System tables: Query to get all the Vertica system tables.

SELECT * FROM ALL_TABLES WHERE schema_name IN ('v_catalog','v_monitor') ORDER BY 1,3;