projections in vertica

Moderator: NorbertKrupa

Post Reply
Posts: 11
Joined: Wed Apr 03, 2013 9:30 am

projections in vertica

Post by bhupal » Thu Apr 04, 2013 7:49 am

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

User avatar
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am

Re: projections in vertica

Post by nnani » Thu Apr 04, 2013 11:13 am

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
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.
Long way to go

You can check out my blogs at vertica-howto

User avatar
Posts: 54
Joined: Sun Mar 18, 2012 1:34 am

Re: projections in vertica

Post by fsalvelt » Thu Apr 04, 2013 9:32 pm

#nnani: Great post! Thanks!
Thank, Fred

Post Reply

Return to “New to Vertica Database Development”