Vertica Partition table creation - Error

arathnas

Hi All - 

Im trying to create vertica year and month partition table.

Year and Month for the partition column has been drived from integer column - "PAYMENT_ID" using the user defined function - "PROD4.PAYID_PREDICT_TIMEZ" 

 

For example -  when i call this function .. this convert payment id into time stamp. as like below example..

 

dbadmin=> select PROD4.PAYID_PREDICT_TIMEZ(PAYMENT_ID), payment_id from PROD4.VAP_PAYMENTS_ADDITIONAL_DATA LIMIT 10
dbadmin-> ;
PAYID_PREDICT_TIMEZ | payment_id
---------------------+----------------
2013-01-08 01:47:48 | 26628156895938
2013-01-08 01:47:48 | 26628156896332

 

 

Using the function constructed the DDL to create partion table .. year and month drive from payment_id using the UDF. however table creation failed with error "ERROR 5371:  User defined function not allowed: payid_predict_timez" 

 

following is the DDL for your reference. If any one know fix to this issue.. please let me know. Thanks

 

 

CREATE TABLE PROD4.TEST_TABLE
(
PAYMENT_ID int NOT NULL,
PROCESSING_TYPE int,
ORIGINAL_TRANSACTION_AMOUNT int
)
PARTITION BY (((date_part('year', PROD4.PAYID_PREDICT_TIMEZ(TEST_TABLE.PAYMENT_ID)) * 100) + date_part('month', PROD4.PAYID_PREDICT_TIMEZ(TEST_TABLE.PAYMENT_ID))));

Arun_Prasad

Hi arathnas,

 

That's an interesting question. Could you describe your UDF?

 

Thanks

Arun

 

I work for HPE Vertica

arathnas

Thanks Arun - is there is the highlevel detail of the process. This integer id - using the different paramater by  Luhn algorithm - payment_id is generated. 

using payment_id we can extract.. payment_id creation time stamp.. by the math formula. 

 

 

 

 

Arun_Prasad

Hi arathnas,

 

I mean, would you be able to share the function with us? Probably we can try to reproduce internally.

 

Thanks

Arun

 

I work for HPE Vertica

arathnas

 

Thanks Arun - Will provide the details.. Could you please drop the mail to my id <personal info removed>

Arun_Prasad

Hi arathnas,

 

I have sent my email address as private message

 

Thanks

Arun