Not able to create partitions

Ravi_Vijay

Hi All, Have an integer field in my table which contains the epoch. I want to partition the table based on this time. I am trying to include both year and month in the partition identifier using : PARTITION BY EXTRACT(year FROM TO_TIMESTAMP(event_time))*100 + EXTRACT(month FROM TO_TIMESTAMP(event_time)); Vertica gives me an error saying ERROR 2552: Cannot use meta function or non-deterministic function in PARTITION BY expression Aren't TO_TIMESTAMP and EXTRACT both immutable functions ? Cheers, RAvi

[Deleted User]

Hi Ravi, Thanks for noticing this -- actually it appears that there's a typo in our documentation. TO_TIMESTAMP() is not immutable; it is always stable. The reason for this is that the UNIX epoch is defined as the number of seconds since midnight on January 1, 1970 UTC. And timestamps are intended to always be in the current timezone. So if you change the current timezone, you change the value of TO_TIMESTAMP()'s output. I unfortunately don't know offhand of a correct way to do this, that's currently provided by Vertica... You could look through our date/time functions: https://my.vertica.com/docs/6.1.x/HTML/index.htm#8657.htm Or define what you want as a macro; math on UNIX epochs isn't super-hard: https://my.vertica.com/docs/6.1.x/HTML/index.htm#15022.htm Adam

Ravi_Vijay

On trying to create my own function it again throws an error :: ERROR 5371: User defined function not allowed: extractyear Cheers, RAvi

Ravi_Vijay

Still Stuck on this one. Created an extra column in the table of type timestamp. However populating that column from the original time (as int) column is taking a lot of time since the table has around a billion entries and there is constant content update in the table. Tried updating after removing projections and using the keyword /* + direct */ , still the updates are taking a lot of time. Any help would be appreciated. Thanks. RAvi

[Deleted User]

Hi RAvi, Apologies, it looks like I forgot to actually hit "Reply" to respond to your previous question. (Or something; my response isn't here, anyway.) For the macro, sorry about that -- I was incorrect; that's not supported in the current version of Vertica. You could always just copy&paste the function definition that you want, directly as the partitioning expression. Partition expressions can be nontrivial. Kinda ugly, but it ought to work... For the updates, how long is "a long time"? It's true that the process could take some number of hours, depending on your cluster size and configuration, your projection design, the load on the cluster, etc. Re-partitioning will likely take a long time as well, though, for similar reasons. Adam

Gil_Peretz

Hello, My name is Gil, and I am from HP Israel. sorry for interrupt, but I have the same problem, having epoch as integer, and trying to partition by hour. How did you solve it?

Ravi_Vijay

Hello Gil, I used a sort of a hack which does not give me exact partitions but is still workable .. Used the following formula to calculate partition key : floor(event_time/31557600 + 1970)*100 + ceiling ((event_time - floor(event_time/31557600)*31557600)/2629800) The first term is year , and second month , you'll have to add day and hour to it. Thanks, Ravi.

Sajan_Sangraula

Hi Gil, You can create a partition by hour using floor(epoch_column/3600), where 3600 is the number of seconds per hour. We have a similar requirement to create a partition per day and this approach is working well

Gil_Peretz

Many Thanks , It help!!!