Page 1 of 1

Quarterly Partition non-deterministic error

Posted: Thu Apr 10, 2014 2:26 pm
by nnani
Hello All,

I am trying to change table's partition expression from monthly to quarterly.

The column on which the partition is made is an integer column having value like
20120230

I tried this query to extract the quaerter for each year and see my partition_key

Code: Select all

select distinct(partition_key) from (select cast((EXTRACT(year FROM cast(cast(xxxx as varchar)as date))*100 + extract (quarter from cast(cast(xxxx as varchar)as date))) as varchar) as partition_key from xx.xxxx) sub
Got result as was expecting

Code: Select all

201104
201203

But now I try to implement the same while changing partition for a table this way

Code: Select all

ALTER TABLE xx.xxxx PARTITION BY (EXTRACT(year FROM cast(cast(xxxx as varchar)as date))*100 + extract (quarter from cast(cast(xxxx as varchar)as date))) ;

I get this error

NOTICE 4954:  The new partitioning scheme will produce 3 partitionsROLLBACK 2552:  Cannot use meta function or non-deterministic function in PARTITION BY expression
What is a non-deterministic function and any other way I can achieve quarterly partition on my data.

Thanks

Re: Quarterly Partition non-deterministic error

Posted: Fri Apr 11, 2014 2:25 pm
by nnani
Found the solution to this.

The ALTER partition statement should be like this.

Code: Select all

ALTER TABLE xx.xxxx PARTITION BY cast(substring(cast(xx as varchar) , 1,4) as int)*100 + ceil(cast(substring(cast(xx as varchar) , 5,2)as int)/3)
I observed that PARTITION by clause does not work or gives an error while performing casting to date column on the column the EXTRACT is running. Which says it is a non-deterministic function.

There was a solution to this issue , when we cast the column to timestamp, but in my case it was date column , so the solution didn't work.

https://community.vertica.com/vertica/t ... _field_how

So I had to find a different way of coming up with the partition keys.

Any more workaround on this are welcomed.

Thanks

Re: Quarterly Partition non-deterministic error

Posted: Sat Apr 12, 2014 6:54 pm
by cbennett
Nice solution. For future reference, a non-deterministic function is a function that returns different things depending on where you happen to be standing on the face of the planet. Date is often one of those. Though, usually we see problems with date+TZ giving problems. It's possible that your LINUX settings were causing your date value to be non-deterministic. Perhaps it was running with DST on, which could cause the date to shift at some point. Just a theory.

Glad you found a workaround.

For what it's worth, some clients find value in partitioning by something like month/quarter for older data, and retaining a less granular partition for newer data. You could have opted to make two tables, partitoned in different ways, with a VIEW on top of both representing the customer view.

Re: Quarterly Partition non-deterministic error

Posted: Sat Apr 12, 2014 9:35 pm
by scutter
You could have opted to make two tables, partitoned in different ways, with a VIEW on top of both representing the customer view.
Insted of using a view, you can create a PARTITION BY expression that uses different granularities for different time periods - using an IF or CASE to determine whether to generate a daily or monthly key for example. ALTER TABLE REORGANIZE when you want to change the partitioning every month or quarter or whatever and to adjust the partitions. I haven’t hit a use case where I’ve needed this, but it has seemed like a good option.

—Sharon