isnumeric function

isnumeric function is not a built in function in Vertica, but we can create it.

CREATE FUNCTION ISNUMERIC(X VARCHAR) RETURN VARCHAR
AS
BEGIN
RETURN REGEXP_COUNT(X, '^[0-9.-]+$');

HP Vertica

DB User:

• User Creation
• GRANT USAGE ON SCHEMA PUBLIC
• Create Schema
• Grant usage privileges on a schema to the user
• Grant all privileges on a schema to the user
• Set default search path
• Change Vertica User Privileges from Existing User to New user
• Execute Permissions on User Defined Functions For New Users
• Grant Execute Permissions on ODBC

Vertica Table

• Creation
• Rename Table, Table Column Or View in Vertica
• Projection Creation
• Create A New Table Using Existing Table
• Create Table Partitions
• Change the Owner of Table
• Drop Partition

Sequence:

• Create Sequence Number
• Alter Sequence
• Drop Sequence
• Add Sequence Or Set Default value to a table
• Drop Default Or Sequence on a Table
• Change the Owner of Sequence

Vertica Sessions

• Close Session in Vertica
• Currently Running Sessions in Vertica

Drop Partition

     Using drop partition command we can drop the table partitions.

Syntax: 
SELECT DROP_PARTITION ('<Table Name>',<Partition Name>,true)

Example:
SELECT DROP_PARTITION ('Sample',1,true)

 
See also: Create Table Partitions

Sample table with table partitions on ID Column:

Applying Drop partition command:

Data after applying drop partition command:

Creating Table Partitions

   This post explains how to do the table partition with examples.

    Vertica supports data partitioning at the table level, which divides one large table into smaller pieces. Partitions are a property of the table.

Example:

Table Creation script:

create table sample (id int,name varchar(10))

insert into sample values (1,'a');

insert into sample values (1,'b');

insert into sample values (1,'c');

insert into sample values (2,'a');

insert into sample values (2,'b');

insert into sample values (2,'c');

• I am going to create partitions based on ID Column. So first we need to set ID column as NOT NULL.

ALTER TABLE sample  alter column id set not null;

•  Then run the table partition script

ALTER TABLE sample  PARTITION BY id REORGANIZE;

• Use below commands to check the table partition status. If partition reorganize status is 100% then partitioning is completed. If any error is happened then error table will populate.
  select * from V_MONITOR.PARTITION_STATUS where table_name='sample'

  

select * from V_MONITOR.PARTITION_REORGANIZE_ERRORS where table_name='sample' 

See Also: Drop Partition

Set Default Search Path

      Using alter command we can set the user default search path.

Syntax:
ALTER USER <User Name> SEARCH_PATH <Schema Name>, "$user", public;

Example:
ALTER USER sample_user SEARCH_PATH test_schema, "$user", public;


See also:

•  User creation
• Grant usage on schema public
• Create Schema
• Grant usage privileges on a schema to the user
• Grant all privileges on a schema to the user

Grant all Privileges on a Schema to the user

   Using below syntax we can grant all privileges on a schema to required user.

Syntax:
GRANT ALL ON SCHEMA <Schema Name> TO <User Name>;

Example:
GRANT ALL ON SCHEMA test_schema TO sample_user;


See also:

• User creation
• Grant usage on schema public
• Create Schema
• Grant usage privileges on a schema to the user
• Set default search path

Grant Usage Privileges on a Schema to the user

    Using below syntax we can grant usage privileges on a schema to required user.

Syntax:
GRANT USAGE ON SCHEMA <Schema Name> TO <User Name>;

Example:
GRANT USAGE ON SCHEMA test_schema TO sample_user;


See also:

• User creation
• Grant usage on schema public
• Create Schema
• Grant all privileges on a schema to the user
• Set default search path

Grant Usage On Public Schema

      Using below syntax we can grant usage privileges on Public schema to the user.

Syntax:
GRANT USAGE ON SCHEMA PUBLIC to <User Name>

Example:
GRANT USAGE ON SCHEMA PUBLIC to Test_User


See also:

•  User creation
• Grant usage on schema public
• Create Schema
• Grant all privileges on a schema to the user
• Set default search path

Create Schema

    Connect to vertica database super user. Using create statement we can create a new schema.

Syntax:
CREATE SCHEMA <Schema Name>;

Example:
CREATE SCHEMA test_schema;


See also:

•  User creation
• Grant usage on schema public
• Grant usage privileges on a schema to the user
• Grant all privileges on a schema to the user
• Set default search path

How to create a copy of existing table

      Using below command we can create a new table using existing table.
Syntax:
CREATE TABLE <New Table Name> AS SELECT * FROM <Existing table name>

Example:
CREATE TABLE SAMPLE_TBL_BKP AS SELECT * FROM SAMPLE_TBL

If you need only table structure then use below command:
CREATE TABLE SAMPLE_TBL_BKP AS SELECT * FROM SAMPLE_TBL WHERE 1 = 2



Note: If we use this command to create a new table, that new table will have only super projection (It will come from source). Source table user define projections will not come.

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>;

Change the Owner of Sequence

If you want to change the Owner of Vertica sequence number, connect data base with super user(dbadmin) and follow the below syntax.

One table: 
ALTER SEQUENCE <Schema Name>.<Table Name> OWNER TO <User Name>

Multiple tables: Capture the below query result and execute through dbadmin
SELECT 'ALTER SEQUENCE <Schema name>.'||SEQUENCE_NAME||' OWNER TO <New User>;'
FROM V_CATALOG.SEQUENCES WHERE SEQUENCE_SCHEMA='<Schema Name>' AND OWNER_NAME = '<Existing User>'

Drop Default OR Drop sequence on a table

If we need to drop a table sequence number, we can follow below syntax.

ALTER TABLE <Table Name> ALTER COLUMN <Column Name> DROP DEFAULT

Add Sequence OR Set Default value to a Table

First we need to create a sequence number. Then we can add this sequence to a table.

Syntax:
ALTER <Table Name> ALTER COLUMN <Column Name> SET DEFAULT NEXTVAL('<Schema Name>.<Sequence Name>');

Example:
ALTER SAMPLE_TBL ALTER COLUMN COL_ID SET DEFAULT NEXTVAL('DEV.MY_SEQ')


See Also:
Drop Sequence on a Table 

Drop Sequence

Using Drop Command we can drop the sequence number.

Syntax: DROP SEQUENCE <Schema Name>.<Sequence Name>

Alter Sequence

In Vertica, using 'Alter' Command we can reset the sequence number, we can rename sequence Name and we can change Schema.

Reset Sequence number:
         ALTER SEQUENCE <Sequence Name> RESTART WITH 200

Rename Sequence:
         ALTER SEQUENCE <Schema Name>.<Sequence Name> TO <New Sequence name>

Change Schema:
         ALTER SEQUENCE <Schema Name>.<Sequence Name> SET SCHEMA <New Schema Name>

Create Sequence Number

Using sequence number we can generate sequence numbers.

Syntax:
CREATE SEQUENCE <Sequence Name> INCREMENT BY <Increment Number> START <Starting Number> NO CACHE

Example:

CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START 100 NO CACHE


See Also:

• Alter Sequence 
• Drop Sequence 

Changing the Owner of Tables

In Vertica one table is having only one Owner. Owner can truncate/Drop the table. If we need to give truncate and Drop permissions to other user, we need to change the Owner of the table to required User.

Connect to the dbadmin user and alter the table owner.
Syntax: ALTER TABLE <SCHEMA NAME>.<TABLE NAME> OWNER TO  <REQUIRED USER>

If you want to change all tables owner:
     Capture the below query result and execute through dbadmin

SELECT 'ALTER TABLE FIN_PROD.'||TABLE_NAME||' OWNER TO <NEW USER>;'

from v_catalog.tables

where table_schema='<SCHEMA NAME>'

and owner_name = '<EXISTING USER>'

Change Vertica User Privileges from Existing User to New user

In Vertica Copying User Privileges From ‘Existing User’ To ‘New User’

Here we need to follow four steps to copy the user privileges.

• For schema and resource pools
• For schema objects (tables, libraries, views, etc)
• Set search_path
• Set resource pool

 Note: Only a superuser can give privileges. Schema privileges must be granted first before schema object privileges.

For schema and resource pools: 

I am going to use "Grants" table to create, code to give Schema and Resource pool privileges to the new user from existing user. We can find user privileges in "Grants" table. This table provides information about privileges granted on various objects, the granting user and grantee user.

SELECT 'GRANT ' || REPLACE(privileges_description,'*','')  ||
       '   ON  SCHEMA ' || COALESCE(object_name,'') ||
       '   TO  <New User Name> ;'
FROM GRANTS
WHERE COALESCE(privileges_description,'') > ''

and object_type in ('SCHEMA', 'RESOURCE_POOL')

and grantee = '<Existing User Name>'
ORDER BY 1;

            Take the query result set and execute same.

 For schema objects (tables, libraries, views, etc)

SELECT 'GRANT ' || REPLACE(privileges_description,'*','')  ||
       '   ON ' || COALESCE(object_schema,'') || '.' || object_name ||
       '   TO  <New User Name> ;'
FROM GRANTS
WHERE COALESCE(privileges_description,'') > ''

and object_type not in ('SCHEMA', 'RESOURCE_POOL')

and grantee = '<Existing User Name>'
ORDER BY 1;

            Take the query result set and execute same.

Set search_path:

 I am going to use "Users" Table to create, code to set search path. 

SELECT 'ALTER USER <New User Name> SEARCH_PATH ' || SEARCH_PATH  || ';'
FROM USERS
WHERE USER_NAME = '<Existing User Name>';

Take the query result set and execute same.

 Set resource pool:

I am going to use "Users" Table to create, code to set search path.

SELECT 'ALTER USER <New User Name> resource_pool ' || resource_pool  || ';'
FROM USERS
WHERE USER_NAME = '<Existing User Name>';

Take the query result set and execute same.

DB User Creation

CREATE USER

Simple way to create user:-
Syntax:
CREATE USER <User Name> IDENTIFIED BY <'Password'>

If you don't want to create give password then follow below syntax.
CREATE USER <User Name>

 Example:
CREATE USER sample_user IDENTIFIED BY 'Password';

See also:

• Newly-created users do not have access to schema PUBLIC by default. Make sure to GRANT USAGE ON SCHEMA PUBLIC to all users you create
• Create Schema
• Grant usage privileges on a schema to the user
• Grant all privileges on a schema to the user
• Set default search path

Actual way to create user:-
Adds a name to the list of authorized database users.

Syntax

CREATE USER name

... [ ACCOUNT {LOCK | UNLOCK} ]

... [ IDENTIFIED BY 'password' ]

... [ PASSWORD EXPIRE ]

... [ MEMORYCAP {'memory-limit' | NONE} ]

... [ PROFILE {profile | DEFAULT} ]

... [ RESOURCE POOL pool-name ]

... [ RUNTIMECAP {'time-limit' | NONE} ]

... [ TEMPSPACECAP {'space-limit' | NONE} ] 

Parameters:

•  name: Specifies the name of the user to create; names that contain special characters must be double-quoted.
• LOCK | UNLOCK:  Specifying LOCK prevents the user from logging in. Specifying UNLOCK unlocks the account, allowing the user to log in
•  password:  If this parameter is omitted, then it will not ask for the password, while the time connecting to the data base using this user. 
•  PASSWORD EXPIRE: The user will be forced to change the password when he or she next logs in.
• memory-limit: Limits the amount of memory that the user's requests can use. This value is a number representing the amount of space, followed by a unit (for example, '10G'). The unit can be one of the following:
• % percentage of total memory available to the Resource Manager. (In this case value for the size must be 0-100)
• K Kilobytes
• M Megabytes
• G Gigabytes
• T Terabytes

Note: Setting this value to NONE means the user's sessions have no limits on memory use. This is the default value.

• profile | DEFAULT :  Profiles set the user's password policy.Using the value DEFAULT here assigns the user to the default profile. If this parameter is omitted, the user is assigned to the default profile.
•  pool-name: Sets the name of the resource pool from which to request the user's resources. This command creates a usage grant for the user on the resource pool unless the resource pool is publicly usable. 
• time-limit: Sets the maximum amount of time any of the user's queries can execute. time-limit is an interval, such as '1 minute' or '100 seconds'. The maximum duration allowed is one year. Setting this value to NONE means there is no time limit on the user's queries.
• space-limit: Limits the amount of temporary file storage the user's requests can use. This parameter's value has the same format as the MEMORYCAP value.

Notes:-

• Only a superuser can create a user.
• User names created with double-quotes are case sensitive. For example:
  => CREATE USER "FrEd1";
  In the above example, the login name must be an exact match. If the user name was created without double-quotes (for example, FRED1), then the user can log in as FRED1, FrEd1, fred1, and so on.
  • ALTER USER and DROP USER are case-insensitive.
• Newly-created users do not have access to schema PUBLIC by default. Make sure to GRANT USAGE ON SCHEMA PUBLIC to all users you create.
• You can change a user password by using the ALTER USER statement. If you want to configure a user to not have any password authentication, you can set the empty password ‘’ in CREATE or ALTER USER statements, or omit the IDENTIFIED BY parameter in CREATE USER.
• By default, users have the right to create temporary tables in the database.