Vertica 的 PARTITION BY 中的 Unix 时间 [英] Unix time in PARTITION BY for Vertica

问题描述

我在 vertica 中有一个大表，它有 time_stamp (int) 作为 unix 时间戳.我想按周对这个表进行分区(周一开始的一周).

I have Big table in vertica which has time_stamp (int) as unix timestamp. I want to partition this table on week basis (week start day Monday).

有没有更好的方法可以一步完成此操作，而不是将 time_stamp 从 unix 转换为 TIMESTAMP (Vertica) 然后进行分区?

Is there a better way to do this in one step rather than converting time_stamp from unix to TIMESTAMP (Vertica) then doing partitions ?

推荐答案

最好使用日期/时间类型.您将无法在 PARTITION BY 表达式中使用非确定性函数，例如 TO_TIMESTAMP.另一种方法是使用数学逻辑来创建分区:

Optimally, you should be using the date/time type. You won't be able to use non-deterministic functions such as TO_TIMESTAMP in the PARTITION BY expression. The alternative is to use math to logically create the partitions:

使用 Unix 时间戳进行分区:

Using a Unix timestamp to partition by:

              Divide By
Minutes       60
Hours         60 * 60 (3600)
Days          60 * 60 * 24 (86400)
Weeks         60 * 60 * 24 * 7 (604800)

如果我们使用 604800，这将为您提供从 1970 年 1 月 1 日 00:00:00 UTC 开始的周数.

If we use 604800, this will give you the week number from January 1, 1970 00:00:00 UTC.

让我们建立一个测试表:

Let's set up a test table:

CREATE TABLE public.test (
  time_stamp int NOT NULL
);

INSERT INTO public.test (time_stamp) VALUES (1404305559);
INSERT INTO public.test (time_stamp) VALUES (1404305633);
INSERT INTO public.test (time_stamp) VALUES (1404305705);
INSERT INTO public.test (time_stamp) VALUES (1404305740);
INSERT INTO public.test (time_stamp) VALUES (1404305778);

COMMIT;

让我们创建分区:

ALTER TABLE public.test PARTITION BY FLOOR(time_stamp/604800) REORGANIZE;

然后我们得到:

NOTICE 4954:  The new partitioning scheme will produce 1 partitions
WARNING 6100:  Using PARTITION expression that returns a Numeric value
HINT:  This PARTITION expression may cause too many data partitions.  Use of an expression that returns a more accurate value, such as a regular VARCHAR or INT, is encouraged
NOTICE 4785:  Started background repartition table task
ALTER TABLE

您还需要注意这会创建多少个分区.Vertica 建议分区数在10-20之间.

You'll also want to be mindful of how many partitions this creates. Vertica recommends keeping the number of partitions between 10-20.

这篇关于Vertica 的 PARTITION BY 中的 Unix 时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！