set hive.vectorized.execution.enabled=false;
set hive.support.concurrency=true;
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.strict.checks.cartesian.product=false;
set hive.materializedview.rewriting=false;
set hive.stats.column.autogather=true;

create database db1;
use db1;

create table cmv_basetable_n7 (a int, b varchar(256), c decimal(10,2), d int) stored as orc TBLPROPERTIES ('transactional'='true');

insert into cmv_basetable_n7 values
 (1, 'alfred', 10.30, 2),
 (2, 'bob', 3.14, 3),
 (2, 'bonnie', 172342.2, 3),
 (3, 'calvin', 978.76, 3),
 (3, 'charlie', 9.8, 1);

create database db2;
use db2;

create materialized view cmv_mat_view_n7
as select a, b, c from db1.cmv_basetable_n7 where a = 2;

select * from cmv_mat_view_n7;

show tblproperties cmv_mat_view_n7;

create materialized view if not exists cmv_mat_view2_n2
as select a, c from db1.cmv_basetable_n7 where a = 3;

select * from cmv_mat_view2_n2;

show tblproperties cmv_mat_view2_n2;

create database db3;
use db3;

explain
select a, c from db1.cmv_basetable_n7 where a = 3;

select a, c from db1.cmv_basetable_n7 where a = 3;