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

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

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) ; 

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

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

Thanks shruti