Friday, August 28, 2015

Creating User Schema Table and Projections in Vertica

Vertica is a an exciting database with some real nifty features. Projections is a ground breaking unique feature of Vertica which dramatically increases performance benefits in terms of querying and space benefits in terms of compression.



Following test commands are impromptu sesssion in which a user is being created, then a schema is created, and that user is authorized on that schema. Then a table is created with a default superprojection and then a projection is created and then we see its usage.

Create new vertica database user, create schema and authorize that user to that schema. Create 4 column table and insert data.

select user_name from v_catalog.users;

vtest=> create user mytest identified by 'user123';
CREATE USER
vtest=>

vtest=> \du
      List of users
 User name | Is Superuser
-----------+--------------
 dbadmin   | t
 mytest    | f
(2 rows)

vtest=> \dn
         List of schemas
     Name     |  Owner  | Comment
--------------+---------+---------
 v_internal   | dbadmin |
 v_catalog    | dbadmin |
 v_monitor    | dbadmin |
 public       | dbadmin |
 TxtIndex     | dbadmin |
 store        | dbadmin |
 online_sales | dbadmin |
(7 rows)


vtest=> \q
[dbadmin@vtest1 root]$ /opt/vertica/bin/vsql -U mytest -w user123 -h 0.0.0.0 -p 5433 -d vtest
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit


vtest=> create table testtab (col1 integer,col2 integer, col3 varchar2(78), col4 varchar2(90));
ROLLBACK 4367:  Permission denied for schema public

[dbadmin@vtest1 root]$ /opt/vertica/bin/vsql -U dbadmin -w vtest -h 0.0.0.0 -p 5433 -d vtest
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

vtest=> \du
      List of users
 User name | Is Superuser
-----------+--------------
 dbadmin   | t
 mytest    | f
(2 rows)

vtest=> create schema mytest authorization mytest;
CREATE SCHEMA
vtest=> select current_user();
 current_user
--------------
 dbadmin
(1 row)

vtest=>

vtest=> \q
[dbadmin@vtest1 root]$ /opt/vertica/bin/vsql -U mytest -w user123 -h 0.0.0.0 -p 5433 -d vtest
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

vtest=> create table testtab (col1 integer,col2 integer, col3 varchar2(78), col4 varchar2(90));
CREATE TABLE
vtest=> select current_user();
 current_user
--------------
 mytest
(1 row)

vtest=>

vtest=> \dt
               List of tables
 Schema |  Name   | Kind  | Owner  | Comment
--------+---------+-------+--------+---------
 mytest | testtab | table | mytest |
(1 row)

vtest=> insert into testtab values (1,2,'test1','test2');
 OUTPUT
--------
      1
(1 row)

vtest=> insert into testtab values (2,2,'test2','test3');
 OUTPUT
--------
      1
(1 row)

vtest=> insert into testtab values (3,2,'test2','test3');
 OUTPUT
--------
      1
(1 row)

vtest=> insert into testtab values (4,2,'test4','tesrt3');
 OUTPUT
--------
      1
(1 row)

vtest=> insert into testtab values (4,2,'test4','tesrt3');
 OUTPUT
--------
      1
(1 row)

vtest=> insert into testtab values (4,2,'test4','tesrt3');
 OUTPUT
--------
      1
(1 row)

vtest=> insert into testtab values (4,2,'test4','tesrt3');
 OUTPUT
--------
      1
(1 row)

vtest=> commit;
COMMIT
vtest=>


Create a projection on 2 columns.

Superprojection exists already:

vtest=> select anchor_table_name,projection_name,is_super_projection from projections;
 anchor_table_name | projection_name | is_super_projection
-------------------+-----------------+---------------------
 testtab           | testtab_super   | t
(1 row)

vtest=>


vtest=> \d testtab
                                    List of Fields by Tables
 Schema |  Table  | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+---------+--------+-------------+------+---------+----------+-------------+-------------
 mytest | testtab | col1   | int         |    8 |         | f        | f           |
 mytest | testtab | col2   | int         |    8 |         | f        | f           |
 mytest | testtab | col3   | varchar(78) |   78 |         | f        | f           |
 mytest | testtab | col4   | varchar(90) |   90 |         | f        | f           |
(4 rows)

vtest=>
vtest=> create projection ptest (col1,col2) as select col1,col2 from testtab;
WARNING 4468:  Projection is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION
vtest=>


vtest=> select anchor_table_name,projection_name,is_super_projection from projections;
 anchor_table_name | projection_name | is_super_projection
-------------------+-----------------+---------------------
 testtab           | testtab_super   | t
 testtab           | ptest           | f
(2 rows)


vtest=> select * from ptest;
ERROR 3586:  Insufficient projections to answer query
DETAIL:  No projections eligible to answer query
HINT:  Projection ptest not used in the plan because the projection is not up to date.
vtest=>

vtest=> select start_refresh();
             start_refresh
----------------------------------------
 Starting refresh background process.

(1 row)

vtest=> select * from ptest;
 col1 | col2
------+------
    1 |    2
    2 |    2
    3 |    2
    4 |    2
    4 |    2
    4 |    2
    4 |    2
(7 rows)

vtest=>


 projection_basename | USED/UNUSED |           last_used
---------------------+-------------+-------------------------------
 testtab             | UNUSED      | 1970-01-01 00:00:00-05
 ptest               | USED        | 2015-08-28 07:14:49.877814-04
(2 rows)

vtest=> select * from testtab;
 col1 | col2 | col3  |  col4
------+------+-------+--------
    1 |    2 | test1 | test2
    3 |    2 | test2 | test3
    2 |    2 | test2 | test3
    4 |    2 | test4 | tesrt3
    4 |    2 | test4 | tesrt3
    4 |    2 | test4 | tesrt3
    4 |    2 | test4 | tesrt3
(7 rows)

projection_basename | USED/UNUSED |           last_used
---------------------+-------------+-------------------------------
 ptest               | USED        | 2015-08-28 07:14:49.877814-04
 testtab             | USED        | 2015-08-28 07:16:10.155434-04
(2 rows)

11 comments:

Scart said...

Packers and Movers

Packers and Movers in Pune
Packers and Movers in Mumbai
Packers and Movers in Gurgaon
Packers and Movers in Noida
Packers and Movers in Delhi
Packers and Movers in Chennai
Packers and Movers in Hyderabad
Packers and Movers in Bangalore
Packers and Movers in Ghaziabad
Packers and Movers in Faridabad

Movers and Packers

Movers and Packers in Pune
Movers and Packers in Mumbai
Movers and Packers in Gurgaon
Movers and Packers in Noida
Movers and Packers in Delhi
Movers and Packers in Chennai
Movers and Packers in Hyderabad
Movers and Packers in Bangalore
Movers and Packers in Ghaziabad
Movers and Packers in Faridabad

rose said...


Packers and Movers

Packers and Movers Delhi
Packers and Movers Mumbai
Packers and Movers Bangalore
Packers and Movers hyderabad
Packers and Movers Chennai
Packers and Movers Gurgaon
Packers and Movers Pune
Packers and Movers Faridabad
Packers and Movers Ghaziabad
Packers and Movers Noida



Movers and Packers

Movers and Packers Delhi
Movers and Packers Mumbai
Movers and Packers Bangalore
Movers and Packers hyderabad
Movers and Packers Chennai
Movers and Packers Gurgaon
Movers and Packers Pune
Movers and Packers Faridabad
Movers and Packers Ghaziabad
Movers and Packers Noida

Anjilo Jilo said...

Quikcbooks Support Phone Number

Quikcbooks Support Number
Quikcbooks Support Phone Number
Quikcbooks Tech Support Number
Quikcbooks Technical Support Number
Quikcbooks Tech Support Phone Number
Quikcbooks Technical Support Phone Number
Quikcbooks Helpdesk Phone Number
Quikcbooks Customer Service Phone Number
Quikcbooks Customer Service Number
Quikcbooks Customer Support Number

Anjilo Jilo said...

Quicken Support Phone Number

Quicken Support Number
Quicken Support Phone Number
Quicken Tech Support Number
Quicken Technical Support Number
Quicken Tech Support Phone Number
Quicken Technical Support Phone Number
Quicken Helpdesk Phone Number
Quicken Customer Service Phone Number
Quicken Customer Service Number
Quicken Customer Support Number

Anjilo Jilo said...


Sage and Peachtree Support Phone Number

Sage Support Number
Sage Support Phone Number
SageTech Support Number
Sage Technical Support Number
Sage Tech Support Phone Number
Sage Technical Support Phone Number
Sage Helpdesk Phone Number
Sage Customer Service Phone Number
Sage Customer Service Number
Sage Customer Support Number

Anjilo Jilo said...

Packers and Movers New York

Packers and Movers New York
Packers and Movers In New York
Movers and packers New York
Movers and packers In New York

Anjilo Jilo said...

AVG Antivirus Support Phone Number
AVG Antivirus Tech Support Number
AVG Antivirus Technical Support Number
AVG Antivirus Tech Support Phone Number
AVG Antivirus Technical Support Phone Number
Avast Antivirus Support Phone Number
Avast Antivirus Tech Support Number
Avast Antivirus Technical Support Number
Avast Antivirus Tech Support Phone Number
Avast Antivirus Technical Support Phone Number
Norton Antivirus Support Phone Number
Norton Antivirus Tech Support Number
Norton Antivirus Technical Support Number
Norton Antivirus Tech Support Phone Number
Norton Antivirus Technical Support Phone Number
Panda Antivirus Support Phone Number
Panda Antivirus Tech Support Number
Panda Antivirus Technical Support Number
Panda Antivirus Tech Support Phone Number
Panda Antivirus Technical Support Phone Number

Anjilo Jilo said...

Mcafee Antivirus Support Phone Number
Mcafee Antivirus Tech Support Number
Mcafee Antivirus Technical Support Number
Mcafee Antivirus Tech Support Phone Number
Mcafee Antivirus Technical Support Phone Number
Webroot Antivirus Support Phone Number
Webroot Antivirus Tech Support Number
Webroot Antivirus Technical Support Number
Webroot Antivirus Tech Support Phone Number
Webroot Antivirus Technical Support Phone Number
Kaspersky Antivirus Support Phone Number
Kaspersky Antivirus Tech Support Number
Kaspersky Antivirus Technical Support Number
Kaspersky Antivirus Tech Support Phone Number
Kaspersky Antivirus Technical Support Phone Number
Bitdefender Antivirus Support Phone Number
Bitdefender Antivirus Tech Support Number
Bitdefender Antivirus Technical Support Number
Bitdefender Antivirus Tech Support Phone Number
Bitdefender Antivirus Technical Support Phone Number

Anjilo Jilo said...

Windows Support Number
Windows Support Phone Number
Windows Tech Support Number
Windows Technical Support Number
Windows Tech Support Phone Number
Windows Technical Support Phone Number
Office 365 Support Number
Office 365 Support Phone Number
Office 365 Tech Support Number
Office 365 Tech Support Phone Number
Office 365 Technical Support Phone Number
Office 365 Technical Support Number
Ms Office Tech Support Number
Ms Office Tech Support Phone Number
Ms Office Technical Support Number
Ms Office Technical Support Phone Number
Ms Office Support Number
Ms Office Support Phone Number
Skype Tech Support Number
Skype Tech Support Phone Number
Skype Technical Support Number
Skype Technical Support Phone Number
Skype Support Phone Number
Skype Support Number

Anjilo Jilo said...


Kindle Technical Support Phone Number
Kindle Support Phone Number
Kindle Tech Support Number
Kindle Technical Support Number
Kindle Support Number
Kindle Customer Support Phone Number
Kindle Customer Support Number
Kindle Tech Support Phone Number

Anjilo Jilo said...

Pogo Technical Support Phone Number
Pogo Support Number
Pogo Tech Support Number
Pogo Technical Support Number
Pogo Support Phone Number
Pogo Tech Support Phone Number
Pogo Customer Support Phone Number
Pogo Customer Support Number