tencent cloud

CREATE DATABASE IF NOT EXISTS mv_test3;
create table student(id int, name string, score int);
insert into student values (1,'zhangsan', 90);
insert into student values (2,'lisi', 100);
insert into student values (3,'wangwu', 80);
insert into student values (4,'zhaoliu', 30);
select * from student order by id;

CREATE MATERIALIZED VIEW mv_student_sum AS (
  select sum(score) from student
);

DESCRIBE MATERIALIZED VIEW mv_student_sum;

REFRESH MATERIALIZED VIEW mv_student_sum;

SHOW MATERIALIZED VIEW JOBS IN mv_student_sum;

select sum(score) from student;

DROP MATERIALIZED VIEW mv_student_sum;

CREATE TABLE link_mv_student AS (
  select sum(score) from student
);
--Create Mapped Materialized View: Use the CREATE MATERIALIZED VIEW statement to create a mapped materialized view.
--When creating the materialized view, use the WITH META LINK clause and specify the name of the mapping table as the association.
CREATE MATERIALIZED VIEW link_mv_student WITH META LINK AS (
  select sum(score) from student
);

DESCRIBE MATERIALIZED VIEW link_mv_student;
SHOW MATERIALIZED VIEW JOBS IN link_mv_student;

--Manually Specify the SQL to be rewritten
select /*+ OPTIONS('eos.sql.materializedView.enableRewrite'='true') */
sum(score) from student; 

DROP MATERIALIZED VIEW link_mv_student;
DESCRIBE link_mv_student; --The source table still exists

CREATE EXTERNAL TABLE student_2(id int, name string, score int)
LOCATION 'cosn://guangzhou-test-1305424723/mv_test4/student_2';
insert into student_2 values (1,'zhangsan', 90);
insert into student_2 values (2,'lisi', 100);
insert into student_2 values (3,'wangwu', 80);
insert into student_2 values (4,'zhaoliu', 30);
select * from student_2;

CREATE EXTERNAL TABLE link_mv_student_hive (
sum_score BIGINT
) LOCATION 'cosn://guangzhou-test-1305424723/mv_test4/link_mv_student_hive';


--Insert data into the mapping table
INSERT OVERWRITE link_mv_student_hive 
select sum(score) from student;

CREATE MATERIALIZED VIEW link_mv_student_hive WITH META LINK AS (
    select sum(score) from student_2
);