— 建表语句
CREATE TABLE IF NOT EXISTS tbale_111(
binlog_table string not NULL COMMENT “binlog_table”,
city_name string not NULL COMMENT “city_name”,
deposit string not NULL COMMENT “我也不晓得这是啥”,
dt date not NULL COMMENT “分区时间”,
gmt_modify datetime COMMENT “修改时间”,
INDEX idx_city_name (city_name) USING BITMAP COMMENT “city_name index”,
INDEX idx_deposit (deposit) USING BITMAP COMMENT “deposit index”,
INDEX idx_gmt_modify(gmt_modify) USING BITMAP COMMENT “gmt_modify index”
)ENGINE=OLAP
DUPLICATE KEY(binlog_table,city_name,deposit,dt) COMMENT " 测试表"
PARTITION BY RANGE(dt)
(
START (“2022-05-01”) END (“2022-05-20”) EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH(binlog_table,city_name,deposit) BUCKETS 6
PROPERTIES (
“replication_num” = “3”,
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.time_zone” = “Asia/Shanghai”,
“dynamic_partition.start” = “-356”,
“dynamic_partition.end” = “3”,
“dynamic_partition.prefix” = “p”,
“dynamic_partition.buckets” = “6”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“bloom_filter_columns”=“binlog_table”
);
– 物化视图
create materialized view table_11_dt_deposit as
select gmt_modify, HLL_UNION(hll_hash(deposit))
from tbale_111
group by gmt_modify;
– 查询语句
explain
select
count(distinct deposit),
date_trunc(“hour”, gmt_modify) as __time
from tbale_111
where
(binlog_table = ‘charging_battery_record_info’)
and dt = ‘’
and gmt_modify >= ‘2022-05-13 13:00:00’
and gmt_modify < ‘2022-05-13 14:00:00’
group by date_trunc(“hour”, gmt_modify);
无法命中 物化视图