Partitioning on Vertica tables!... ?

Moderator: NorbertKrupa

Post Reply
jagadeesh
Newbie
Newbie
Posts: 21
Joined: Tue Feb 05, 2013 9:36 am

Partitioning on Vertica tables!... ?

Post by jagadeesh » Mon Feb 25, 2013 2:37 pm

Can we partition a table in vertica?
Ex: I need to partition employee table based on dept number.

1. i think, since vertica stores all the data of the table as columns under super projection and all the columns will be sorted. In this case do we need a partition?

2. Whenever a table is created and the data is inserted, by default a super projection is created. is this SP will have all the column sorted and compressed or Encoded by default? or it only happens when we create a projection and force it for compression/Encoding?

3. If Partition can be created, can you give me a example SQL to create on emp table for deptno?


Thanks,
Jagadeesh.

adrian.oprea
Intermediate
Intermediate
Posts: 163
Joined: Tue Jun 19, 2012 2:44 pm
Location: Rio de Janeiro
Contact:

Re: Partitioning on Vertica tables!... ?

Post by adrian.oprea » Tue Feb 26, 2013 5:32 pm

Yes we can create partitions in Vertica and the max number of partitions on a vertica table is 1024.

Sinatx :
CREATE TABLE emp (
emp_id integer not null,
emp_name varchar(30),
dept_id integer not null)
PARTITION BY dept_id;
- make sure the partitioned column is not null
- when creating the projection place the partion column first in the order by.
Example :

CREATE PROJECTION emp_p (dept_id,emp_id,emp_name) as
select * from emp
order by dept_is,emp_id,emp_name unsegmented all nodes;

select refresh('emp'); -- this is to populate your new projections

- for better performance the order by statement is crucial when working with partiotions , start with a low-cardinality value/partition column to a high card value at the end.

The most important part in using partitioning is my opinion is the delete part , When deleting data normaly vertica does not free the space in the same time(it even writes more data)/remember that Vertica never updates datafiles it always add ne data(files)
but when using drop partion Vertica will free the space imedialetly and will skip delete background steps.

Partitioning and external table is not possible.
Last edited by adrian.oprea on Tue Feb 26, 2013 5:41 pm, edited 1 time in total.
trying so hard !!!

adrian.oprea
Intermediate
Intermediate
Posts: 163
Joined: Tue Jun 19, 2012 2:44 pm
Location: Rio de Janeiro
Contact:

Re: Partitioning on Vertica tables!... ?

Post by adrian.oprea » Tue Feb 26, 2013 5:37 pm

To get the benefits of encoding and compressing you must create tuned projections other by hand or using the DBdesigner(recommended).

Remember that vertica works(query's) on encoded data and not on compressed data(compressed data must first be uncompressed and then used).

Is good ot know the encoding type just for you to know how to use them.
trying so hard !!!

jagadeesh
Newbie
Newbie
Posts: 21
Joined: Tue Feb 05, 2013 9:36 am

Re: Partitioning on Vertica tables!... ?

Post by jagadeesh » Mon May 06, 2013 5:27 pm

Thanks for all the replies, helpfull :)

Post Reply

Return to “New to Vertica”