我建了一个Duplicate 模型的表,然后创建物化视图后,很多查询查不了

Viewed 55

创建物化视图语句:
create
materialized view ods_min_max_date as
select datasource_code, data_type, min(date), max(date)
from ods_bill_of_landing
group by datasource_code, data_type;
查询语句:
select count(distinct datasource_code) from ods_bill_of_landing;
报错语句:
errcode = 2, detailmessage = select fail reason: errcode = 2, detailmessage = updateslotuniqueid: do not found mvcolumn=datasource_code from index=ods_min_max_date
basc表结构:
CREATE TABLE IF NOT EXISTS ods_bill_of_landing
(
date DATE NULL COMMENT "日期",
datasource_code VARCHAR(4) NOT NULL COMMENT "数据源编码",
data_type varchar(2) NULL comment "数据类型,I:进口 E:出口 S:船运 M:镜像 A:联盟 R:航线",
key VARCHAR(50) NOT NULL COMMENT "唯一标识",
product_desc STRING NULL COMMENT "产品描述",
product_desc_en STRING NULL COMMENT "产品描述英文",
hscode VARCHAR(500) NULL COMMENT "海关编码",
importer_std VARCHAR(500) NULL COMMENT "进口商标准",
exporter_std VARCHAR(500) NULL COMMENT "出口商标准",
estimated_arrival_date DATE NULL COMMENT "预计到港日期",
actual_arrival_date DATE NULL COMMENT "实际到港日期",
datasource VARCHAR(50) NOT NULL COMMENT "数据源国家",
origin_country VARCHAR(200) NULL COMMENT "原产国",
origin_country_code VARCHAR(50) NULL COMMENT "原产国编码",
destination_country VARCHAR(200) NULL COMMENT "目的国名称",
destination_country_code VARCHAR(200) NULL COMMENT "目的国编码",
importer VARCHAR(500) NULL COMMENT "进口商",
importer_id VARCHAR(255) NULL COMMENT "进口商ID",
importer_address STRING NULL COMMENT "进口商地址",
importer_state VARCHAR(1000) NULL COMMENT "进口商城市/州/省",
importer_post_code VARCHAR(50) NULL COMMENT "进口商邮编",
importer_email VARCHAR(500) NULL COMMENT "进口商邮箱",
importer_iphone VARCHAR(500) NULL COMMENT "进口商电话",
importer_fax VARCHAR(500) NULL COMMENT "进口商传真",
importer_tax_id VARCHAR(100) NULL COMMENT "进口商税号",
importer_website VARCHAR(200) NULL COMMENT "进口商网址",
importer_notify VARCHAR(200) NULL COMMENT "进口通知人",
importer_notify_address VARCHAR(500) NULL COMMENT "进口通知人地址",
import_type VARCHAR(200) NULL COMMENT "进口类型",
import_type_code VARCHAR(100) NULL COMMENT "进口类型代码",
exporter VARCHAR(1000) NULL COMMENT "出口商",
exporter_id VARCHAR(100) NULL COMMENT "出口商ID",
exporter_address STRING NULL COMMENT "出口商地址",
exporter_state VARCHAR(500) NULL COMMENT "出口商城市/州/省",
exporter_post_code VARCHAR(200) NULL COMMENT "出口商邮编",
exporter_email VARCHAR(65533) NULL COMMENT "出口商邮箱",
exporter_iphone VARCHAR(500) NULL COMMENT "出口商电话",
exporter_fax VARCHAR(500) NULL COMMENT "出口商传真",
exporter_website VARCHAR(500) NULL COMMENT "出口商网址",
hscode_2 VARCHAR(100) NULL COMMENT "2位海关编码",
hscode_4 VARCHAR(100) NULL COMMENT "4位海关编码",
hscode_6 VARCHAR(100) NULL COMMENT "6位海关编码",
hscode_8 VARCHAR(100) NULL COMMENT "8位海关编码",
hscode_desc STRING NULL COMMENT "海关编码描述",
item_number VARCHAR(100) NULL COMMENT "商品项号",
brand VARCHAR(500) NULL COMMENT "品牌",
marks STRING NULL COMMENT "唛头",
value DECIMAL(24, 5) NULL COMMENT "美元价值",
value_unit VARCHAR(50) NULL COMMENT "美元",
quantity DECIMAL(24, 5) NULL COMMENT "数量",
quantity_unit VARCHAR(200) NULL COMMENT "数量单位",
net_weight DECIMAL(24, 5) NULL COMMENT "净重",
net_weight_unit VARCHAR(50) NULL COMMENT "净重单位",
weight DECIMAL(24, 5) NULL COMMENT "重量",
weight_unit VARCHAR(50) NULL COMMENT "重量单位",
quantity_price DECIMAL(24, 5) NULL COMMENT "数量单价",
quantity_price_unit VARCHAR(50) NULL COMMENT "数量单价单位",
net_weight_price DECIMAL(24, 5) NULL COMMENT "净重",
net_weight_price_unit VARCHAR(50) NULL COMMENT "净重单价单位",
weight_price DECIMAL(24, 5) NULL COMMENT "重量单价",
weight_price_unit VARCHAR(50) NULL COMMENT "重量单价单位",
fob_price DECIMAL(24, 5) NULL COMMENT "fob单价",
fob_total DECIMAL(24, 5) NULL COMMENT "fob总价",
fob_unit VARCHAR(50) NULL COMMENT "fob单位",
cif_value DECIMAL(24, 5) NULL COMMENT "cif总价",
cif_price DECIMAL(24, 5) NULL COMMENT "cif单价",
cif_unit VARCHAR(50) NULL COMMENT "cif单位",
freight DECIMAL(24, 5) NULL COMMENT "运费",
foreign_value DECIMAL(24, 5) NULL COMMENT "外币价值",
foreign_quantity_price DECIMAL(24, 5) NULL COMMENT "外币数量单价",
foreign_weight_price DECIMAL(24, 5) NULL COMMENT "外币重量单价",
foreign_net_weight_price DECIMAL(24, 5) NULL COMMENT "外币净重单价",
foreign_unit VARCHAR(50) NULL COMMENT "外币单位",
exchange_rate DECIMAL(24, 5) NULL COMMENT "汇率",
tax DECIMAL(24, 5) NULL COMMENT "税费",
tax_rate DECIMAL(24, 5) NULL COMMENT "税率",
insurance DECIMAL(24, 5) NULL COMMENT "保险费",
total_invoice_value DECIMAL(24, 5) NULL COMMENT "发票总价",
total_invoice_value_unit VARCHAR(50) NULL COMMENT "发票总价单位",
container_number VARCHAR(500) NULL COMMENT "集装箱号",
container_quantity INT NULL COMMENT "集装箱数量",
vessel_name VARCHAR(500) NULL COMMENT "船名",
receipt_place VARCHAR(500) NULL COMMENT "装货地",
loading_port VARCHAR(200) NULL COMMENT "装货港",
loading_port_code VARCHAR(100) NULL COMMENT "起运港代码",
destination_port VARCHAR(500) NULL COMMENT "目的港",
destination_place VARCHAR(500) NULL COMMENT "目的地",
destination_port_code VARCHAR(500) NULL COMMENT "目的港代码",
transport_type VARCHAR(200) NULL COMMENT "运输方式",
customs_name VARCHAR(200) NULL COMMENT "海关名称 ",
customs_code VARCHAR(200) NULL COMMENT "海关代码",
carrier VARCHAR(500) NULL COMMENT "承运人",
carrier_code VARCHAR(50) NULL COMMENT "承运人代码",
agent VARCHAR(200) NULL COMMENT "代理商",
package_quantity VARCHAR(50) NULL COMMENT "包装数量",
packaging_type VARCHAR(100) NULL COMMENT "包装类型",
bill_of_lading_no VARCHAR(100) NULL COMMENT "提单号",
declaration_number VARCHAR(100) NULL COMMENT "申报号",
in_date DATE NULL COMMENT "录入日期",
id BIGINT NOT NULL COMMENT "id",
INDEX inverted_index_ods_bill_of_landing_product (product_desc) USING INVERTED PROPERTIES("parser" = "unicode") COMMENT "",
INDEX inverted_index_ods_bill_of_landing_hscode (hscode) USING INVERTED PROPERTIES("parser" = "unicode") COMMENT "",
INDEX inverted_index_ods_bill_of_landing_importer_std (importer_std) USING INVERTED PROPERTIES("parser" = "unicode") COMMENT "",
INDEX inverted_index_ods_bill_of_landing_exporter_std (exporter_std) USING INVERTED PROPERTIES("parser" = "unicode") COMMENT "",
)DUPLICATE KEY(date,datasource_code,data_type,key)
PARTITION BY RANGE(date) ()
DISTRIBUTED BY HASH(date) BUCKETS auto
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "day",
"dynamic_partition.end" = "7",
"dynamic_partition.prefix" = "bof",
"dynamic_partition.hot_partition_num" = "365",
"dynamic_partition.start" = "-1095",
"dynamic_partition.create_history_partition" = "true"
);

1 Answers

204 版本未复现,先close了,后面有问题可以加一下我的微信Faith_xzc
image.png