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)
4 comments:
I would appreciate it if you could write a more detailed post on the video schema.
Our website provides Pinoy Teleserye Shows to users with the most recent online series and episodes within the simplest HD quality. Almost all of these dramas and shows are launched by the GMA and ABS CBN networks 0n Teleserye Lovers Platform Pinoy Teleserye is a great platform that refers to Filipinos and their culture within the Philippines and overseas Filipinos among Pinoy Lambingan.
Wow PinoyFlix, with wonderful blog layout! How long have you ever been running this blog Lambingan? you made blogging glance easy. The overall look of your web site is great which include Pinoy Tv Teleserye, and
Pinoy Lambingan let alone the content Lambingan TV
Pinoy Lambingan. is specially designed to get happiness and enjoyment in Lambingan households.
Post a Comment