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.