Rename Table, Table column or view in vertica

        Below syntax's to rename a Table, column and view.

Rename a Table: 
               ALTER TABLE <Schema Name>.<Table Name> RENAME TO <New Name for that table>

     Example:
               ALTER TABLE Test.Sample1 RENAME TO SAMPLE2


Rename a Column:
                 ALTER TABLE <Schema Name>.<Table Name> RENAME COLUMN <Column Name>  TO  <New Name For that column>

       Example:
                ALTER TABLE Test.Sample RENAME COLUMN Sample_Id TO Sample_2;


Rename a View:
                ALTER VIEW <Schema Name>.<Table Name> RENAME TO <New Name>

        Example:
                 ALTER VIEW TEST.SAMPLE4 RENAME TO SAMPLE6

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'

Projection Creation

   We can create projection on a table to improve the query performance.

Syntax:
   CREATE PROJECTION <Schema Name>.<Projection Name>
   ( <Column Name1>, <Column Name2>,....)   --table related all columns
   AS
   SELECT <Column Name1>,<Column Name2>,...  --table related all columns
   ORDER BY <Column Name1>,<Column Name2>,...  --required key columns
   SEGMENTED BY HASH(<Column Name1>,<Column Name2>,.. )  --required columns
   ALL NODES;

Example:
 Table Creation: 
   CREATE TABLE SAMPLE.SAMPLE1 (ID INT,NAME VARCHAR(50))

 Projection Creation:
   CREATE PROJECTION Sample.Proj_sample
   (id, name)
   AS
   SELECT id, name
   FROM Sample.sample1
   ORDER BY id
   SEGMENTED BY hash(id,name) ALL NODES;

Currently Running Sessions in Vertica

Vertica is having a table 'Sessions' to maintain currently running sessions information. Using this table we can find the current sessions information.

Syntax: Connect to DB as Admin and run the below statement
SELECT * FROM SESSIONS

See also:
Close Session 

Close Session in Vertic

       If one of the session is running long time. If you want to close the session, then we can use below syntax.

Syntax:

• Find the Session Id for long running session. Connect to Vertica DB Using super user and run the below query.

          SELECT * FROM  SESSIONS

•  Then we will get list of current running sessions information, find the required session and copy the 'Session_Id'
• In below two statements Run any one of the statement to kill the long running sessions.

          SELECT CLOSE_SESSION('<Session ID>')
          OR
          SELECT INTERUPT_STATEMENT('<Session ID>')

Grant Execute Permissions on ODBC

If the Vertica database User is not having Execute permissions on ODBC, follow the below syntax to grant execute permissions. dbadmin (Super user) can give the permissions.

Syntax:
GRANT EXECUTE ON SOURCE public.ODBCSource() to <User Name>;

GRANT EXECUTE ON SOURCE public.ODBCLoader() to <User Name>;

Execute Permissions on User Defined Functions For New Users

          If we create a new user, that user may not have execute permissions on existing 'User define functions' (Created by some other user). Using below syntax we can grant execute permissions on 'User define Function' to the new users.

Connect to dbadmin (Super user) and execute statement.
Syntax:



GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA PUBLIC TO <New User Name> 

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA <Schema Name> TO <User Name>;