请问下建立物化视图时报错 Do not allow create materialized view的原因。
物化视图语句及报错如下:
MySQL [intlsdk_log_prod]> create materialized view obs_1000_monitor_core_version as
-> select core_version from obs_1000_monitor group by core_version;
ERROR 1064 (HY000): Table[obs_1000_monitor]'s state is ROLLUP not NORMAL.Do not allow create materialized view
建表语句如下:
CREATE TABLE obs_1000_monitor
(
wux_s_date
date NULL COMMENT “”,
core_version_sub
varchar(65533) NULL COMMENT “”,
wux_c_gameid
varchar(65533) NULL COMMENT “”,
wux_c_os
int(11) NULL COMMENT “”,
stage
varchar(65533) NULL COMMENT “”,
method_id
varchar(65533) NULL COMMENT “”,
method_name
varchar(65533) NULL COMMENT “”,
wux_s_country
varchar(65533) NULL COMMENT “”,
wux_c_channelid
int(11) NULL COMMENT “”,
ret_code
varchar(65533) NULL COMMENT “”,
seq_id
varchar(65533) NULL COMMENT “”,
wux_c_openid
varchar(65533) NULL COMMENT “”,
wux_c_sdk_version
varchar(65533) NULL COMMENT “”,
wux_c_app_version
varchar(65533) NULL COMMENT “”,
wux_c_xwid
varchar(65533) NULL COMMENT “”,
wux_c_client_source
bigint(20) NULL COMMENT “”,
wux_c_session_id
varchar(65533) NULL COMMENT “”,
wux_s_ts
bigint(20) NULL COMMENT “”,
wux_s_hour
int(11) NULL COMMENT “”,
wux_s_dt_event_time
varchar(65533) NULL COMMENT “”,
wux_s_client_ip
varchar(65533) NULL COMMENT “”,
wux_s_province
varchar(65533) NULL COMMENT “”,
wux_s_city
varchar(65533) NULL COMMENT “”,
wux_s_carrier_id
varchar(65533) NULL COMMENT “”,
wux_c_create_ts
bigint(20) NULL COMMENT “”,
wux_c_send_ts
bigint(20) NULL COMMENT “”,
wux_c_event_name
varchar(65533) NULL COMMENT “”,
wux_c_dis_channel_id
varchar(65533) NULL COMMENT “”,
wux_c_os_version
varchar(65533) NULL COMMENT “”,
wux_c_network_type
bigint(20) NULL COMMENT “”,
wux_c_carrier_id
varchar(65533) NULL COMMENT “”,
wux_c_bundle_id
varchar(65533) NULL COMMENT “”,
wux_c_device_model
varchar(65533) NULL COMMENT “”,
wux_c_device_brand
varchar(65533) NULL COMMENT “”,
wux_c_screen_width
int(11) NULL COMMENT “”,
wux_c_screen_height
int(11) NULL COMMENT “”,
wux_c_cpu_name
varchar(65533) NULL COMMENT “”,
wux_c_cpu_core
int(11) NULL COMMENT “”,
wux_c_cpu_freq
bigint(20) NULL COMMENT “”,
wux_c_gpu_name
varchar(65533) NULL COMMENT “”,
wux_c_ram_total
bigint(20) NULL COMMENT “”,
wux_c_rom_total
bigint(20) NULL COMMENT “”,
wux_c_android_id
varchar(65533) NULL COMMENT “”,
wux_c_android_adid
varchar(65533) NULL COMMENT “”,
wux_c_xwid_flag
varchar(65533) NULL COMMENT “”,
wux_c_ios_idfv
varchar(65533) NULL COMMENT “”,
wux_c_ios_idfa
varchar(65533) NULL COMMENT “”,
channel
varchar(65533) NULL COMMENT “”,
extra_json
varchar(65533) NULL COMMENT “”,
third_code
varchar(65533) NULL COMMENT “”,
ret_msg
varchar(65533) NULL COMMENT “”,
third_msg
varchar(65533) NULL COMMENT “”,
core_version
varchar(65533) NULL COMMENT “”,
INDEX idx_seq_id (seq_id
) USING BITMAP COMMENT ‘’,
INDEX idx_openid (wux_c_openid
) USING BITMAP COMMENT ‘’,
INDEX idx_stage (stage
) USING BITMAP COMMENT ‘’
) ENGINE=OLAP
DUPLICATE KEY(wux_s_date
, core_version_sub
, wux_c_gameid
, wux_c_os
, stage
, method_id
, method_name
, wux_s_country
, wux_c_channelid
, ret_code
, seq_id
, wux_c_openid
)
COMMENT “OLAP”
PARTITION BY RANGE(wux_s_date
)
(PARTITION p_20220416 VALUES [(‘2022-04-16’), (‘2022-04-17’)),
)
DISTRIBUTED BY HASH(wux_s_date
, core_version_sub
, wux_c_gameid
, wux_c_os
, stage
, method_id
, method_name
, wux_s_country
, wux_c_channelid
, ret_code
) BUCKETS 32
PROPERTIES (
“replication_num” = “3”,
“bloom_filter_columns” = “stage, core_version_sub, ret_code”,
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.time_zone” = “Asia/Shanghai”,
“dynamic_partition.start” = “-30”,
“dynamic_partition.end” = “3”,
“dynamic_partition.prefix” = “p_”,
“dynamic_partition.buckets” = “32”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”
);