PROJECTION in vertica database

databaseprojectionvertica

Could someone explain me the concept of PROJECTION in vertica database with an example query?

Best Answer

Vertica doesn't use indexes to find the data.

Conceptually, you still access tables using SQL. But underneath the hood, the data in the table are stored in projections, which you can optimize for different queries.

I like to think of it as a table representing a deck of cards. If playing poker you may still say something like 

Select * from CardDeck limit 5;

Let's say you have a table defined with the following columns:

FaceValue int (let's just assume face values are ints),
Suit varchar(10)

I can then create my projections (I'm omitting details about partitioning, super-projections, buddy-projections, etc.).

create projection CardDeck_p1
(
FaceValue ENCODING RLE,
Suit
)
as 
select FaceValue, Suit from CardDeck order by FaceValue;

create projection CardDeck_p2
(
FaceValue,
Suit
)
as
select FaceValue, Suit from CardDeck order by Suit;

Now, each column can get a different type of encoding which is defined in the projection. And the database designer, which I haven't used much since I've been on an older version, can help design the projections for you.

So getting back to the deck of card analogies, imagine you want to access a deck of cards but you want to have different shuffles of the cards. Projections in Vertica gives you the different shuffles. Tables are really a construct that allows you to access the data which is stored in projections. But if you are writing SQL, you access tables.

Related Topic