Partition by TIMESTAMPTZ field. How?

spektom

Hello, I'm trying to re-partition some table using week number counting from some day: my_fact table contains a field called time_stamp of type TIMESTAMPTZ Unfortunately, re-partition doesn't work, and I'm getting the error: MyDB=> ALTER TABLE my_fact PARTITION BY MOD(TIMESTAMPDIFF('day', time_stamp::TIMESTAMP, TIMESTAMP '2013-09-23'), 156) REORGANIZE; NOTICE 4954: The new partitioning scheme will produce 12 partitions ROLLBACK 2552: Cannot use meta function or non-deterministic function in PARTITION BY expression What cast of time_stamp field can be done to make it deterministic? Thanks! Michael

Sharon_Cutter

Hi Michael, When partitioning based on timestamps, I've forced the timestamps to be UTC using AT TIME ZONE 'UTC'. --Sharon

spektom

Hi Sharon, This doesn't work either. This is my syntax: MyDB=> ALTER TABLE controller_fact PARTITION BY MOD(TIMESTAMPDIFF('week', time_stamp AT TIME ZONE 'UTC', TIMESTAMP '2013-09-23' AT TIME ZONE 'UTC'), 156) REORGANIZE; NOTICE 4954: The new partitioning scheme will produce 3 partitions ROLLBACK 2552: Cannot use meta function or non-deterministic function in PARTITION BY expression It looks like nothing is gonna make time_stamp field immutable. Thanks, Michael

Daniel_Leybovic

Hi Michael! Sharon means that table has UTC zone definition, in such way TimestampTz value will be deterministic, otherwise value isn't deterministic(because TimestampTz depends on TIME ZONE and if zone is changed so value of TimestampTz is changed as well). But if TIME ZONE for TimestampTz values will be a constant, value will not change.

spektom

Hi Daniel, I'm sorry, I still don't understand how can I make my existing TIMESTAMPTZ field deterministic. Can you please elaborate? Thanks, Michael

Daniel_Leybovic

Something like this: daniel=> create table fact (id int not null primary key, tmz timestamptz not null); CREATE TABLE daniel=> alter table fact partition by (date(tmz at time zone 'UTC') - date('2013-09-23'::TimestampTZ at time zone 'UTC')) % 156 reorganize; NOTICE 4785: Started background repartition table task ALTER TABLE

spektom

Thanks a lot! This worked

Daniel_Leybovic

Hi! Do you know what? Im confused... 1. What about number of nodes? Does "scaling factor" enabled? Let : n - number of nodes f - scaling factor (default 4) p - partitions (156, because MOD 156 and zero included) ----------------- segments = f * p = 620 (rough calculation) (max 1024) It's too much. I think you in trouble with ROS in the feature: https://my.vertica.com/docs/6.1.x/HTML/index.htm#15985.htm

Local data segmentation increases the number of storage containers stored on each node. This is not an issue unless a table contains many partitions. For example, if the table is partitioned by day and contains one or more years. If local data segmentation is enabled, then each of these table partitions is broken into multiple local storage segments, which potentially results in a huge number of files which can lead to ROS "pushback" (Too Many ROS containers). Consider your table partitions and the effect enabling local data segmentation may have before enabling the feature.

2. Also Im confused about result of expression. TMZ converted to UTC - it's ok, but I suggest you to check - what is impact on result of constant DATE conversion? Does it's fit your requirements?

spektom

Hi Daniel, 1. Thank you for pointing out to this issue. I'm beginner in Vertica (and in big data area at all), so this is really useful comment! What I wanted is to partition the data in such a way, so only 3-year data history is hold. For instance, when reaching the 156th week of the 3-year period, I drop the first partition, and thus let the cycle restart. I didn't touch scaling factor, so it's set to default. Number of nodes is currently 1, but it may (and will) grow in the future I will consider using monthly partitions instead. 2. I ended up with this partition key (without DATE conversion): ALTER TABLE fact PARTITION BY MOD(TIMESTAMPDIFF('week', '2011-01-01'::timestamptz AT TIME ZONE 'UTC', time_stamp AT TIME ZONE 'UTC'), 156) REORGANIZE; Translating to creating monthly partitions (for 3 years) should give: ALTER TABLE fact PARTITION BY MOD(TIMESTAMPDIFF('month', '2011-01-01'::timestamptz AT TIME ZONE 'UTC', time_stamp AT TIME ZONE 'UTC'), 36) REORGANIZE; Thanks again for your help! Michael

Sharon_Cutter

A more conventional way to do this in Vertica would be to use date_part() to get the year and concatenate it to the week number. You could create partition expressions such as 2012W01, 2012W02, etc. Then regularly drop the partitions regularly that are older than the 3 years that you want to retain. --Sharon

spektom

Thanks, Sharon!