マテビュー - HTN20190109の日記

MySQL

※MySQLにネイティブなマテビューはない

(5.6)
--Flexviews
https://archive.fosdem.org/2015/schedule/event/flexviews/attachments/slides/600/export/events/attachments/flexviews/slides/600/Flexviews_slides.pdf
http://greenlion.github.io/swanhart-tools/flexviews/manual.html
https://github.com/greenlion/swanhart-tools
https://www.percona.com/blog/2011/03/25/using-flexviews-part-two-change-data-capture/

--1.インストール

vim /etc/my.cnf

binlog_format = ROW
server_id = 101
log_slave_updates = 1

systemctl restart mysqld

unzip swanhart-tools-master.zip

cd /root/swanhart-tools-master/flexviews/consumer

cp consumer.ini.example consumer.ini

vim consumer.ini

mysqlbinlog=/usr/bin/mysqlbinlog

[source]
user=root
host=127.0.0.1
port=3306
password=password

[dest]
user=root
host=127.0.0.1
port=3306
password=password

yum install -y epel-release
rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-7.rpm

yum install -y --enablerepo=remi,remi-php56 php php-devel php-mbstring php-pdo php-gd php-xml php-mcrypt

rpm -qa | grep php
php --version

yum install -y --enablerepo=remi,remi-php56 php-pear php-mysqli

--2.CDCセットアップ

cd /root/swanhart-tools-master/flexviews/consumer
php ./setup_flexcdc.php --ini consumer.ini

mysql -e 'select * from flexviews.binlog_consumer_status\G' -u root -p

--3.CDCサービス起動
cd /root/swanhart-tools-master/flexviews/consumer
sh consumer_safe.sh --ini=consumer.ini &

ps -ef | grep consumer
cat flexcdc.pid

--停止する場合
kill -9 4043 4053

--4.flexviewsインストール

cd /root/swanhart-tools-master/flexviews

mysql -u root -p
source install.sql

--5.マテビューログ作成

drop table test.tab2;
drop table test.tab3;

create table test.tab2(col1 int);
create table test.tab3(col1 int);

CALL flexviews.create_mvlog('test','tab2');
CALL flexviews.create_mvlog('test','tab3');

select * from flexviews.mvlogs where table_name='tab2';
select * from flexviews.mvlogs where table_name='tab3';

select * from flexviews.mvlog_b0f62d8bbcd749aa9374b1dcd38e4fa8;
select * from flexviews.mvlog_16fd7aa706ad3a4c47bce851e35b8e45;

insert into test.tab2 values(1);
insert into test.tab3 values(1),(2),(3);

--6.マテビュー作成

call flexviews.create('test','test_mv1','INCREMENTAL');

set @mv_id = flexviews.get_id('test', 'test_mv1');
select @mv_id;

select * from flexviews.mview;

call flexviews.add_table(@mv_id, 'test', 'tab2', 'T2', NULL);
call flexviews.add_table(@mv_id, 'test', 'tab3', 'T3', 'ON T2.col1 = T3.col1');

select * from flexviews.mview_table;

call flexviews.add_expr(@mv_id, 'SUM', 'T2.col1', 'goukei');
select * from flexviews.mview_expression;

call flexviews.enable(@mv_id);

select * from test.tab2;
select * from test.tab3;
select * from test.test_mv1;

insert into test.tab2 values(2);

--リフレッシュ
set @mv_id = flexviews.get_id('test', 'test_mv1');
select @mv_id;
call flexviews.refresh(@mv_id,'COMPUTE',NULL);
call flexviews.refresh(@mv_id,'APPLY',NULL);

(8.0.27)

--Flexviews
https://archive.fosdem.org/2015/schedule/event/flexviews/attachments/slides/600/export/events/attachments/flexviews/slides/600/Flexviews_slides.pdf
http://greenlion.github.io/swanhart-tools/flexviews/manual.html
https://github.com/greenlion/swanhart-tools
https://www.percona.com/blog/2011/03/25/using-flexviews-part-two-change-data-capture/

https://tech-it.r-net.info/program/php/243/
https://qiita.com/mach3/items/7d8282c83cd57606d158
https://mita2db.hateblo.jp/entry/2020/01/13/163218
https://qiita.com/seltzer/items/3096f0805440bfa19bff

--1.インストール

vim /etc/my.cnf

[mysqld]
binlog_format = ROW
server_id = 101
log_slave_updates = 1

collation-server = utf8_unicode_ci
character-set-server = utf8
default_authentication_plugin = mysql_native_password

secure_file_priv=""

validate_password.length = 4
validate_password.policy = LOW
validate_password.check_user_name = OFF

[mysql]
default-character-set=utf8

systemctl restart mysqld

unzip swanhart-tools-master.zip

cd /root/swanhart-tools-master/flexviews/consumer

cp consumer.ini.example consumer.ini

vim consumer.ini

mysqlbinlog=/usr/bin/mysqlbinlog

[source]
user=root
host=127.0.0.1
port=3306
password=password

[dest]
user=root
host=127.0.0.1
port=3306
password=password

yum install -y epel-release
rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-7.rpm

yum install -y --enablerepo=remi,remi-php56 php php-devel php-mbstring php-pdo php-gd php-xml php-mcrypt

rpm -qa | grep php
php --version

yum install -y --enablerepo=remi,remi-php56 php-pear php-mysqli

--2.CDCセットアップ

cd /root/swanhart-tools-master/flexviews/consumer
php ./setup_flexcdc.php --ini consumer.ini

mysql -e 'select * from flexviews.binlog_consumer_status\G' -u root -p

--3.CDCサービス起動
cd /root/swanhart-tools-master/flexviews/consumer
sh consumer_safe.sh --ini=consumer.ini &

ps -ef | grep consumer
cat flexcdc.pid

--停止する場合
kill -9 2396 2386

--4.flexviewsインストール

cd /root/swanhart-tools-master/flexviews

★エラー発生のためsqlファイルを修正
------------------------------------
cp install_schema.inc install_schema.inc.bk
vim install_schema.inc

GRANT ALL
ON flexviews.*
TO flexviews@localhost
IDENTIFIED BY 'flexdemo';

↓

CREATE USER flexviews@localhost IDENTIFIED BY 'flexdemo';
GRANT ALL ON flexviews.* TO flexviews@localhost;

cp schema/schema.sql schema/schema.sq.bk
vim schema/schema.sql

storage_engine 記載の行削除

LOAD DATA LOCAL INFILE 'schema/errors.csv'
↓
LOAD DATA INFILE '/tmp/errors.csv'

cp ./schema/errors.csv /tmp/errors.csv

chmod 777 /tmp/errors.csv

------------------------------------

mysql -u root -p
source install.sql

--5.マテビューログ作成

drop table test.tab2;
drop table test.tab3;

create table test.tab2(col1 int);
create table test.tab3(col1 int);

CALL flexviews.create_mvlog('test','tab2');
CALL flexviews.create_mvlog('test','tab3');

select * from flexviews.mvlogs where table_name='tab2';
select * from flexviews.mvlogs where table_name='tab3';

select * from flexviews.mvlog_b0f62d8bbcd749aa9374b1dcd38e4fa8;
select * from flexviews.mvlog_16fd7aa706ad3a4c47bce851e35b8e45;

insert into test.tab2 values(1);
insert into test.tab3 values(1),(2),(3);

--6.マテビュー作成

call flexviews.create('test','test_mv1','INCREMENTAL');

set @mv_id = flexviews.get_id('test', 'test_mv1');
select @mv_id;

select * from flexviews.mview;

call flexviews.add_table(@mv_id, 'test', 'tab2', 'T2', NULL);
call flexviews.add_table(@mv_id, 'test', 'tab3', 'T3', 'ON T2.col1 = T3.col1');

select * from flexviews.mview_table;

call flexviews.add_expr(@mv_id, 'SUM', 'T2.col1', 'goukei');
select * from flexviews.mview_expression;

★flexviews.enableプロシージャ再作成
------------------------------------
MySQL は、ルーチンが作成または変更されたときの有効な sql_mode システム変数の設定を格納し、
ルーチンが実行を開始したときの現在のサーバー SQL モードには関係なく、常にそのルーチンを強制的にこの設定で実行します。

SELECT
*
FROM
information_schema.ROUTINES
WHERE
ROUTINE_TYPE = 'PROCEDURE'
and routine_name = 'update_refresh_step_info'\G

use flexviews
SET sql_mode="";
select @@sql_mode;

drop PROCEDURE update_refresh_step_info;

DELIMITER ;;
CREATE DEFINER=`flexviews`@`localhost` PROCEDURE `update_refresh_step_info`(
IN v_mview_id INT,
IN v_last_step VARCHAR(255)
)
BEGIN
INSERT INTO flexviews.refresh_step_info (mview_id, last_step)
VALUES (v_mview_id, v_last_step)
ON DUPLICATE KEY UPDATE last_step = v_last_step, last_step_at=NOW();
END ;;
DELIMITER ;

------------------------------------

call flexviews.enable(@mv_id);

select * from test.tab2;
select * from test.tab3;
select * from test.test_mv1;

insert into test.tab2 values(2);

--リフレッシュ
set @mv_id = flexviews.get_id('test', 'test_mv1');
select @mv_id;
call flexviews.refresh(@mv_id,'COMPUTE',NULL);
call flexviews.refresh(@mv_id,'APPLY',NULL);

Oracle

CREATE MATERIALIZED VIEW LOG ON tab1 WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON tab2 WITH ROWID;

CREATE MATERIALIZED VIEW mview1
PARALLEL BUILD IMMEDIATE
REFRESH FAST AS
SELECT T1.rowid "tab1_rid", T2.rowid "tab2_rid", T1.col1 "tab1_col1", T2.col1 "tab2_col1"
FROM tab1 T1, tab2 T2
WHERE T1.col1 = T2.col1
;

select * from tab1;
select * from tab2;
select * from mview1;

execute DBMS_MVIEW.REFRESH('mview1','f');

PostgreSQL

\dm
\dm+

drop table tab1 CASCADE;

create table tab1(col1 int);

insert into tab1 values(1);

create materialized view mview1 as select * from tab1;
create unique index ind12 on mview1(col1);

insert into tab1 values(2);

select * from tab1;
select * from mview1;

refresh materialized view CONCURRENTLY mview1;

※CONCURRENTLY
このオプションは、マテリアライズドビューに、列名だけを使い、
すべての行を含むUNIQUEインデックスが少なくとも1つある場合にのみ使えます。

SQL Server

近いものとしてインデックス付きビューがある

use test
go

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--ビューを作成する
CREATE VIEW [dbo].[mview1] WITH SCHEMABINDING
AS
SELECT col1 as col1
,col1 as col2
,col1 as col3
,col1 as col4
FROM dbo.tab1
go

--クラスタ化インデックスを作成する
CREATE UNIQUE CLUSTERED INDEX mview_ind1
ON dbo.mview1 ( col1,col2 )
go

--非クラスタ化インデックスを作成する
CREATE NONCLUSTERED INDEX IDX_mview_ind2
ON dbo.mview1 ( col3,col4 )
go