----- 创建表 -------
drop table IF EXISTS iot_kafka;
CREATE TABLE iot_kafka (
node_id bigint NOT NULL COMMENT '点位 ID',
device_time DATETIME NOT NULL COMMENT '时间',
device_timestamp bigint NULL COMMENT '时间',
uuid varchar(30) NULL COMMENT 'uuid',
iot_json STRING NULL COMMENT 'iot_json',
create_time DATETIMEV2 NOT null default CURRENT_TIMESTAMP COMMENT '时间'
)
ENGINE=OLAP
DUPLICATE KEY(node_id,device_time,device_timestamp)
AUTO PARTITION BY RANGE (DATE_TRUNC(device_time, 'day'))()
DISTRIBUTED BY HASH(node_id) BUCKETS 10
PROPERTIES (
"dynamic_partition.time_unit"="day",
"dynamic_partition.start" = "-1",
"dynamic_partition.end" = "1",
"dynamic_partition.prefix" = "p",
"dynamic_partition.enable" = "true",
"dynamic_partition.replication_num" = "1"
);
----- 创建LOAD -------
STOP ROUTINE LOAD FOR iot_kafka_load;
CREATE ROUTINE LOAD iot_kafka_load ON iot_kafka
COLUMNS(node_id,device_time,device_timestamp,uuid,iot_json)
PROPERTIES(
"format"="json",
"jsonpaths"="["$.node_id","$.create_time","$.device_timestamp","$.uuid","$.iot_json"]"
)
FROM KAFKA(
"kafka_broker_list" = "192.168.80.7:30092",
"kafka_topic" = "ddps.ts_kv_iot_main_kafka",
"property.kafka_default_offsets" = "OFFSET_END"
);
SHOW ROUTINE LOAD;
----- 创建物化视图 -------
----- 创建明细表物化视图 -------
drop MATERIALIZED VIEW IF EXISTS iot_main;
CREATE MATERIALIZED VIEW iot_main
BUILD IMMEDIATE REFRESH AUTO ON commit
DUPLICATE KEY(node_id,device_time,device_timestamp)
DISTRIBUTED BY HASH(node_id) BUCKETS 10
PROPERTIES (
"replication_num" = "1"
)
AS
select node_id,device_time,device_timestamp,uuid,key_id,key_v,CAST(key_v AS double) as d_key_v
from (select node_id,device_time,device_timestamp,uuid, CAST( iot_json AS MAP<text, string>) AS map_data from iot_kafka) t
lateral view explode_map_outer(map_data) tmp as key_id,key_v;
----- 创建1分钟聚合物化视图 -------
drop MATERIALIZED VIEW IF EXISTS iot_main_minute_avg;
CREATE MATERIALIZED VIEW iot_main_minute_avg
BUILD IMMEDIATE REFRESH AUTO ON commit
DUPLICATE KEY(node_id,device_time,device_timestamp)
DISTRIBUTED BY HASH(node_id) BUCKETS 10
PROPERTIES (
"replication_num" = "1"
)
AS
SELECT
node_id,DATE_TRUNC(device_time, 'minute') AS device_time, key_id, avg(d_key_v) AS avg_v
FROM iot_main
GROUP BY
node_id,key_id, DATE_TRUNC(device_time, 'minute');
在创建iot_main_minute_avg的时候报错：
SQL 错误 [1105] [HY000]: errCode = 2, detailMessage = Cannot invoke "org.apache.doris.nereids.StatementContext.generateColumnName()" because the return value of "org.apache.doris.qe.ConnectContext.getStatementContext()" is null
另外iot_main_minute_avg可以创建自己的partion by规则吗，还是跟iot_main一样，必须是基表的partion by规则。想按月做分区，如何实现
