how to define a table with more than 1 partition column.

priya1cvr

It is said that Vertica supports up to 1024 partitions per table; however, they recommend between 10 and 20 partitions for optimal performance .

 

but when i define a table with partition by clause having 2 columns i am getting the error .

 

CREATE TABLE vmart.mytest_table
(
    Rslr_Store_Id int,
    Store_Name varchar(1000),
    Rslr_Country_Cd char(2),
    Reseller_Name varchar(1000),
    Apple_Id int,
    fiscal_year int not null  ,
    fiscal_month int not null
    
)SEGMENTED BY hash(Rslr_Store_Id) ALL NODES KSAFE 1
Partition by HASH  (fiscal_year  )  
Partition by HASH(fiscal_month)
 ;

 

ERROR 4856:  Syntax error at or near "Partition" at character 336
LINE 13: Partition by HASH(fiscal_month)

 

 

Could you please say how to define partitons in this case

SruthiA

Hi,

 

   If you have more than one column in the partition by clause, you can define it as follows

 

CREATE TABLE mytest_table1
(
    Rslr_Store_Id int,
    Store_Name varchar(1000),
    Rslr_Country_Cd char(2),
    Reseller_Name varchar(1000),
    Apple_Id int,
    fiscal_year int not null  ,
    fiscal_month int not null)
SEGMENTED BY hash(Rslr_Store_Id) ALL NODES KSAFE 1
Partition by HASH  (fiscal_year,fiscal_month); 

 

 

-Regards,

 Sruthi

Yogi_Baba

Hi, 

 

Vertica currently does not offer sub-partitions.    But there is an alternative design option you may choose that will give you something very close to sub-partitions.

 

 

CREATE TABLE mytest_table1
(
    Rslr_Store_Id int,
    Store_Name varchar(1000),
    Rslr_Country_Cd char(2),
    Reseller_Name varchar(1000),
    Apple_Id int,
    fiscal_year int not null  ,
    fiscal_month int not null)

SEGMENTED BY hash(Rslr_Store_Id) ALL NODES KSAFE 1
Partition by ( fiscal_year * 100)+ (fiscal_month) ; 

 

 

priya1cvr

Hi Shruthi,

 

I get the following error when in tried to create the table as suggested by you .

 

CREATE TABLE mytable_test
(
   Rslr_Store_Id int,
Store_Name varchar(1000),
Rslr_Country_Cd char(2),
Reseller_Name varchar(1000),
Apple_Id int,
fiscal_year int not null  ,
fiscal_month int not null
 )SEGMENTED BY hash(Rslr_Store_Id) ALL NODES KSAFE 1
sfo_owner-> Partition by (fiscal_year ,  fiscal_month );
ERROR 4331:  PARTITION BY expression cannot return a tuple

SruthiA

Hi Priya,

 

     Looks like the word HASH is missing in Parition by clause

 

CREATE TABLE mytest_table1
(
Rslr_Store_Id int,
Store_Name varchar(1000),
Rslr_Country_Cd char(2),
Reseller_Name varchar(1000),
Apple_Id int,
fiscal_year int not null ,
fiscal_month int not null)
SEGMENTED BY hash(Rslr_Store_Id) ALL NODES KSAFE 1
Partition by HASH (fiscal_year,fiscal_month);

 

-Regards,

 Sruthi

 

priya1cvr

Thanks shruti