We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Vertica Partition table creation - Error — Vertica Forum

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