1.CREATE TABLE test (
a TINYINT NULL DEFAULT "0",
b INT NULL DEFAULT "0",
c INT NULL DEFAULT "0",
t DATETIME NOT NULL COMMENT '',
d BIGINT SUM NULL DEFAULT "0",
e BIGINT SUM NULL DEFAULT "0"
)ENGINE = OLAP AGGREGATE KEY
(a,b,c,t)
PARTITION BY RANGE(t) ()
DISTRIBUTED BY random buckets 1
PROPERTIES
(
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "HOUR",
"dynamic_partition.start" = "-3",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "1",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "3",
"light_schema_change" = "true"
);
2. CREATE MATERIALIZED VIEW mv_test
BUILD IMMEDIATE
REFRESH AUTO
ON SCHEDULE EVERY 1 MINUTE
PARTITION BY (t)
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
"partition_sync_limit" = "1",
"grace_period"="300",
"partition_date_format" = "%Y%m%d%H",
"replication_num" = "2"
)
AS
SELECT a,b,t,SUM(d) AS d,SUM(e) AS e FROM test GROUP BY 1,2,3;
3. show create materialized view mv_test on test;
版本 2.1.5
