Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Vertica Partition table creation - Error

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

Comments

  • Hi arathnas,

     

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

     

    Thanks

    Arun

     

    I work for HPE Vertica

  • 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. 

     

     

     

     

  • 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

  •  

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

  • Hi arathnas,

     

    I have sent my email address as private message

     

    Thanks

    Arun

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.