Vertica Partition Function Usage Issue

 I have a doubt regarding some internal Vertica functions.

 

 - in the latest Vertica version the copy_partition_to_table has been implement and i find this very usefull.

 

 

COPY_PARTITIONS_TO_TABLE (
'[[db-name.]schema.]source-table',
'min-range-value',
'max-range-value',
'[[db-name.]schema.]target-table'
)

Also we have  swap_partition_... which is an older function .

 

SWAP_PARTITIONS_BETWEEN_TABLES (
'[[db-name.]schema.]staging-table',
'min_range_value',
'max_range_value',
'[[db-name.]schema.]target-table'
)

 

Now here is my doubt !:

 

 Why would in one we should use integer values as an input and in other it has to be a string .

 

To give you an example as my use case:

-- i am moving data from staging to prod once this is clean.

 

SELECT SWAP_PARTITIONS_BETWEEN_TABLES ( 
'$$Vertica_Stg_Schema.Fact', CAST(TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM') AS INTEGER),
CAST(TO_CHAR(SYSDATE,'YYYYMM') AS INTEGER),
'$$Vertica_Prd_Schema.Fact'
) ;

-- it works great , no isues so far .

 

Notice the cast i issue to make it a INTEGER.

 

 

Now i wanna use the copy_partition_to_table for my work using the same dynamic partition key generation.

 

SELECT /*+ LABEL(copy_part) */ COPY_PARTITIONS_TO_TABLE('
prod.Fact,
CAST(TO_CHAR(ADD_MONTHS(SYSDATE,-26),'YYYYMM')AS INTEGER),
CAST(TO_CHAR(SYSDATE,'YYYYMM')AS INTEGER) ,
'staging.Fact_to_bkp');

I get this error :

 

 

 ERROR: Function COPY_PARTITIONS_TO_TABLE(unknown, int, int, 
unknown) does not exist, or permission is denied for
COPY_PARTITIONS_TO_TABLE(unknown, int, int, unknown)

This is strange !? Right 

 

 

So the working SQL is :

 

SELECT /*+ LABEL(copy_part) */ COPY_PARTITIONS_TO_TABLE(
'prod.Fact',
TO_CHAR(ADD_MONTHS(SYSDATE,-26),'YYYYMM'),
TO_CHAR(SYSDATE,'YYYYMM') ,
'staging.FactScript_to_bkp'
);

Solution :

- pass the partition keys as String values. 

 

 

 

 What is the reason befind makeing them different one takes int and another takes string ?

Just out of curiosity :) 

 

Thanks all HP Vertica Eng Team. 

 

 

Comments

  • I don't see any differences in your solution SQL.  Perhaps you pasted the wrong thing?  I suspect what you did was to cast the two partition key parameters as ::char. 

     

    I don't know what the actual answer is, though I've noticed similar behavior with drop_partition - the parameter takes a string, but also accepts integers as a valid argument.  My guess is that the actual answer is nothing more complex than - whoever wrote drop_partition is probably not the same person who wrote copy_partitions_to_table.  

     

    But if you look at the to_char function, at face value, it looks like an integer:

    select TO_CHAR(ADD_MONTHS(SYSDATE,-26),'YYYYMM') ;
    TO_CHAR
    ---------
    201401
    (1 row)

     

    It seems Vertica treats it as whatever is in its best interest. I'm not really sure *what* to_char is returning, because this also works:

     

    dbadmin=> select TO_CHAR(ADD_MONTHS(SYSDATE,-26),'YYYYMM')+ 10 ;
    ?column?
    ----------
    201411
    (1 row)

     

    But then so does this:

    dbadmin=> select TO_CHAR(ADD_MONTHS(SYSDATE,-26),'YYYYMM') || 'abc' ;
    ?column?
    -----------
    201401abc
    (1 row)

     

    nice!

     

    Final answer: When in doubt, cast!

     

     

  •  

     Hi Curtis, 

    Yes i have forgot to put in the CAST to INT, now is correct.

     

    And yes i belive the same thing, thow i think it should be consistent across all similar functions.

     

    Thx for your answer.

     

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.