doris-2.1.5
SELECT * FROM IoTrove.iot_device_status_fact WHERE utc_time BETWEen '2024-09-12' AND '2024-09-13' AND device_mac = 'eb5ae1c09eac88e480mika' ORDER BY utc_time DESC
查询发现有device_mac != 'eb5ae1c09eac88e480mika' 的设备，device_ma有使用倒排索引
建表语句
CREATE TABLE IF NOT EXISTS IoTrove.iot_device_status_fact
(
utc_time DATETIME(3),
device_mac varchar(100),
sn varchar(100),
user_id varchar(100),
device_type varchar(100),
seq int,
tags json,
property json,
process_time varchar(40),
index idx_utc_time(utc_time) using inverted,
index idx_device_mac(device_mac) using inverted,
index idx_sn(sn) using inverted
)
ENGINE = OLAP
DUPLICATE KEY(utc_time)
PARTITION BY RANGE(utc_time)()
DISTRIBUTED BY HASH(device_mac) BUCKETS 10
PROPERTIES(
"replication_allocation" = "tag.location.default: 2",
"storage_medium" = "SSD",
"compression" = "zstd",
"compaction_policy" = "time_series",
"storage_policy" = "IoTrove_policy",
"dynamic_partition.enable" = "true",
"dynamic_partition.storage_medium" = "SSD",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.end" = "1",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_num" = "2",
"dynamic_partition.buckets" = "10"
);
