【详述】一个表有 77亿的数据，查询已经很慢了，想要构建物化视图，但是无法构建
创建物化视图的指令：
create materialized view device_day
as
select date,
tenementId,
deviceId,
sum(runningSec),
sum(durationSec),
sum(workerOnJobDurationSec)
from device_machine_sec_detail_v2
group by date, tenementId, deviceId;
-- 基表
CREATE TABLE `device_machine_sec_detail_v2` (
`natureDateTime` datetime NULL COMMENT "",
`tenementId` bigint(20) NULL COMMENT "",
`deviceId` bigint(20) NULL COMMENT "",
`lineId` bigint(20) NULL COMMENT "",
`lineSnapId` bigint(20) NULL COMMENT "",
`procedureId` bigint(20) NULL COMMENT "",
`workshiftDate` date NULL COMMENT "",
`date` date NULL COMMENT "",
`hour` datetime NULL COMMENT "",
`workshiftName` varchar(50) NULL COMMENT "",
`eventCode` tinyint(4) NULL COMMENT "",
`onJobWorkerNum` bigint(20) NULL COMMENT "",
`runningSec` bigint(20) NULL COMMENT "",
`durationSec` bigint(20) NULL COMMENT "",
`workerOnJobDurationSec` bigint(20) NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`natureDateTime`, `tenementId`, `deviceId`)
PARTITION BY RANGE(`natureDateTime`)
DISTRIBUTED BY HASH(`tenementId`, `deviceId`) BUCKETS 8
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "WEEK",
"dynamic_partition.time_zone" = "Etc/UTC",
"dynamic_partition.start" = "-200",
"dynamic_partition.end" = "60",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "8",
"dynamic_partition.start_day_of_week" = "1",
"in_memory" = "false",
"storage_format" = "DEFAULT"
);
已经做过的操作
- 重试
- 调大 timeout 参数：
ADMIN SET FRONTEND CONFIG ("tablet_create_timeout_second" = "100") ;
【业务影响】查询很慢
【StarRocks版本】例如：2.2.10
【报错信息如下】
