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))));
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
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