主键表
CREATE TABLE sales_records_pri (
record_id int(11) NOT NULL COMMENT “”,
sale_date date NOT NULL COMMENT “”,
seller_id int(11) NULL COMMENT “”,
store_id int(11) NULL COMMENT “”,
sale_amt bigint(20) NULL COMMENT “”
) ENGINE=OLAP
PRIMARY KEY(record_id, sale_date)
COMMENT “OLAP”
PARTITION BY date_trunc(‘month’, sale_date)
DISTRIBUTED BY HASH(sale_date, record_id) BUCKETS 8
ORDER BY(record_id, seller_id)
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “true”,
“replicated_storage” = “true”,
“compression” = “LZ4”
);
分区数据
INSERT INTO sales_records_pri
VALUES
(001,“2023-03-01”,01,1,8571),
(002,“2023-03-02”,02,2,8572),
(003,“2023-03-03”,01,3,8573),
(004,“2023-03-04”,03,1,8574),
(005,“2023-03-05”,02,2,6948),
(006,“2023-03-06”,01,1,4319),
(007,“2023-03-07”,03,3,8734),
(008,“2023-03-08”,03,3,4212),
(009,“2023-03-09”,02,2,9515),
(001,“2023-04-01”,01,1,8571),
(002,“2023-04-02”,02,2,8572),
(003,“2023-04-03”,01,3,8573),
(004,“2023-04-04”,03,1,8574),
(005,“2023-04-05”,02,2,6948),
(006,“2023-04-06”,01,1,4319),
(007,“2023-04-07”,03,3,8734),
(008,“2023-04-08”,03,3,4212),
(009,“2023-04-09”,02,2,9515),
(001,“2023-05-01”,01,1,8571),
(002,“2023-05-02”,02,2,8572),
(003,“2023-05-03”,01,3,8573),
(004,“2023-05-04”,03,1,8574),
(005,“2023-05-05”,02,2,6948),
(006,“2023-05-06”,01,1,4319),
(007,“2023-05-07”,03,3,8734),
(008,“2023-05-08”,03,3,4212),
(009,“2023-05-09”,02,2,9515);
查询写入
SELECT * FROM sales_records_pri;
创建单表异步物化视图
CREATE MATERIALIZED VIEW store_amt_pri_async
DISTRIBUTED BY HASH(store_id) BUCKETS 8
REFRESH ASYNC START(‘2023-05-09 14:00:00’) EVERY (INTERVAL 1 HOUR)
AS SELECT store_id, SUM(sale_amt)
FROM sales_records_pri
GROUP BY store_id;
查询异步物化视图
SELECT * FROM store_amt_pri_async;
结果数据
|1|64392|
|2|75105|
|3|64557|
新增分区数据
INSERT INTO sales_records_pri VALUES (001,“2023-03-10”,01,1,8572);
手动刷新单分区物化视图
REFRESH MATERIALIZED VIEW store_amt_pri_async
PARTITION START (“2023-03-01”) END (“2023-04-01”);
报错
SQL 错误 [1064] [42000]: Not support refresh by partition for single partition mv.
查看分区
SHOW PARTITIONS FROM sales_records_pri;
结果
| 26352 | p202303 | 3 | 2023-05-09 11:45:58 | 0 | NORMAL | sale_date | [types: [DATE]; keys: [2023-03-01]; …types: [DATE]; keys: [2023-04-01]; ) | sale_date, record_id | 8 | 1 | HDD | 9999-12-31 23:59:59 | 6KB | false | 10 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 26369 | p202304 | 2 | 2023-05-09 11:43:10 | 0 | NORMAL | sale_date | [types: [DATE]; keys: [2023-04-01]; …types: [DATE]; keys: [2023-05-01]; ) | sale_date, record_id | 8 | 1 | HDD | 9999-12-31 23:59:59 | 5.3KB | false | 9 | |
| 26386 | p202305 | 2 | 2023-05-09 11:43:10 | 0 | NORMAL | sale_date | [types: [DATE]; keys: [2023-05-01]; …types: [DATE]; keys: [2023-06-01]; ) | sale_date, record_id | 8 | 1 | HDD | 9999-12-31 23:59:59 | 5.3KB | false | 9 |
问题
如何做到单独更新3月分区数据