Page 1 of 1

projections in vertica

Posted: Thu Apr 04, 2013 7:49 am
by bhupal
hi i am new to vertica. please provide answers for the below questions.

what is need of creating projections? how to create projections in vertica? how to pull data from vertica?

thanks and regards
Bhupal

Re: projections in vertica

Posted: Thu Apr 04, 2013 11:13 am
by nnani
Hi Bhupal,

Welcome to Vertica forums !!!!!

Always remember In Vertica
Tables are logical and Projections are physical
What is the need of creating projections?

This means When you create a table, the definition of table is stored logically and it not present in the Database, rather a projection is automatically created for each table your create. So whenever your fire your select queries the queries go to projections and fetch data for you. So it is the projections where all your data is getting stored and not tables.

So in order to store and fetch data you need projections

How to create projections?

Projections are of different types depending on the method used to create them and your requirement.
In any case you always have a super projection for each of your table in Vertica.
A super projection is automatically created for you when you insert your first record into the table.
If you want to create extra projections for a single table in Vertica, then you have to use
CREATE PROJECTION STATEMENT in Vertica
Other then using CREATE PROJECTION command, you can also use the DBD for designing the new projections for you.

how to pull data from Vertica ?

If you are thinking of Select queries on the Vertica database, then it is no different then other databases. Just fire your Select queries referencing the table name

Code: Select all

dbadmin=> select * from nums;
 id | v1 | v2 
----+----+----
  1 | 49 | a
  2 | 24 | D
  3 | 67 | >
(3 rows)
The table name in the above example is nums, you don't have to use the projection name in the query, Vertica does it for you internally. It will fetch the data from super projection for table nums or any other projection existing for the table nums.
You don't have to change your way of writing SQL at all.

A Note for you :- If you have used other databases and come across Materialized View, then you can understand projections easily. Projections are nothing but Materialized views with added functionality.

Re: projections in vertica

Posted: Thu Apr 04, 2013 9:32 pm
by fsalvelt
#nnani: Great post! Thanks!