Vertica Partition Function Usage Issue

Adrian_Oprea_1

 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. 

 

 

Curtis_1

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!

 

 

Adrian_Oprea_1

 

 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.