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)
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
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)
1 comment:
I would appreciate it if you could write a more detailed post on the video schema.
Post a Comment