Database JUNKY

MySQL,MariaDBを中心としたブログです

トップ > column based > InfiniDB でリアルタイムDWHを作る PART2（環境構成詳細）

InfiniDB でリアルタイムDWHを作る PART2（環境構成詳細）

column based Engines Infinidb infinidb MySQL データウェアハウス データ連携 リアルタイムDWH レプリケーション 列指向

前回のMySQLとInfiniDB でリアルタイムDWHを作る PART1から、だいぶ間が空いてしまいましたが、そろそろリアルタイムDWHの話を復活しようと思います。PART2では、リアルタイムDWHを構築する際の環境構成について説明し、結果何をどうしたいのか？の結果を記載します。

・MySQL データベースサーバが３台、 ・MySQLデータ連携サーバが３台 ・InfiniDB データベースサーバが１台 と書きましたが、すみません、そんなに環境ないので ・MySQL データベースサーバが２台、 ・MySQLデータ連携サーバが１台でポート毎にインスタンスを作成（ポートは二種類） ・InfiniDB データベースサーバが１台 という構成に変更します（汗）もうすこし詳細を書きます。

▼ 環境情報 二台で構成されており、受注DBとマスターDBがあり、マスターDB側には、商品スキーマとユーザースキーマがあるものと思ってください。

★ 受注データベース 以下の環境構成です

1-1) 受注スキーマ [SQL] mysql> show create database sqorder; +----------+------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------+ | sqorder | CREATE DATABASE sqorder /!40100 DEFAULT CHARACTER SET utf8 / | +----------+------------------------------------------------------------------+ 1 row in set (0.00 sec) [/SQL]

1-2) 受注テーブル [SQL] mysql> show tables; +-------------------+ | Tables_in_sqorder | +-------------------+ | trn_jyucyu | +-------------------+ 1 row in set (0.00 sec)

CREATE TABLE trn_jyucyu ( id int(11) unsigned NOT NULL AUTO_INCREMENT, j_cd int(11) NOT NULL COMMENT '受注コード', j_date date NOT NULL COMMENT '受注日', s_cd int(11) NOT NULL COMMENT '商品コード', j_price int(11) NOT NULL COMMENT '受注金額', user_id int(11) NOT NULL COMMENT 'ユーザーコード', last_update datetime NOT NULL COMMENT '最終更新日', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=599168 DEFAULT CHARSET=utf8 [/SQL]

1-3) データ内容/データ件数 [SQL] mysql> select count() from trn_jyucyu; +----------+ | count() | +----------+ | 599167 | +----------+ 1 row in set (0.00 sec)

mysql> select * from trn_jyucyu order by id desc limit 10; +--------+------------+------------+------+---------+---------+---------------------+ | id | j_cd | j_date | s_cd | j_price | user_id | last_update | +--------+------------+------------+------+---------+---------+---------------------+ | 599167 | 201227638 | 2012-05-25 | 6 | 1027 | 1 | 2012-04-06 22:35:30 | | 599166 | 201293988 | 2012-05-29 | 10 | 7939 | 1 | 2012-04-06 22:35:30 | | 599165 | 2147483647 | 2012-04-30 | 3 | 6629 | 2 | 2012-04-06 22:35:30 | | 599164 | 2012125737 | 2012-01-07 | 6 | 6626 | 4 | 2012-04-06 22:35:30 | | 599163 | 2147483647 | 2012-02-07 | 9 | 145 | 4 | 2012-04-06 22:35:30 | | 599162 | 2012521227 | 2012-01-19 | 3 | 2585 | 5 | 2012-04-06 22:35:30 | | 599161 | 2012526949 | 2012-04-19 | 7 | 6561 | 4 | 2012-04-06 22:35:29 | | 599160 | 2147483647 | 2012-09-20 | 10 | 1059 | 5 | 2012-04-06 22:35:29 | | 599159 | 201252333 | 2012-12-14 | 8 | 9579 | 5 | 2012-04-06 22:35:29 | | 599158 | 2012225273 | 2012-06-20 | 3 | 1436 | 3 | 2012-04-06 22:35:29 | +--------+------------+------------+------+---------+---------+---------------------+ 10 rows in set (0.00 sec) [/SQL]

★マスタデータベース（ユーザー情報）

1-1) ユーザースキーマ [SQL] mysql> show create database squser; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | squser | CREATE DATABASE squser /!40100 DEFAULT CHARACTER SET utf8 / | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) [/SQL]

1-2) ユーザーテーブル [SQL] mysql> show tables; +--------------------+ | Tables_in_squser | +--------------------+ | user_address | | user_bookmarks | | user_delete | | user_fav | | user_login_history | | users | +--------------------+ 6 rows in set (0.00 sec)

CREATE TABLE users ( id int(11) unsigned NOT NULL AUTO_INCREMENT, user_name varchar(100) NOT NULL COMMENT 'ユーザー名', user_mail varchar(100) NOT NULL COMMENT 'メールアドレス', last_update datetime NOT NULL COMMENT '最終更新日', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 [/SQL]

1-3) データ内容/データ件数 [SQL] mysql> select * from users; +----+------------------+----------------------------------+---------------------+ | id | user_name | user_mail | last_update | +----+------------------+----------------------------------+---------------------+ | 1 | テスト太郎1 | test_taro1@testdomain.test.local | 2012-04-07 01:28:26 | | 2 | テスト太郎2 | test_taro2@testdomain.test.local | 2012-04-07 01:28:26 | | 3 | テスト太郎3 | test_taro3@testdomain.test.local | 2012-04-07 01:28:26 | | 4 | テスト太郎4 | test_taro4@testdomain.test.local | 2012-04-07 01:28:26 | | 5 | テスト太郎5 | test_taro5@testdomain.test.local | 2012-04-07 01:28:26 | +----+------------------+----------------------------------+---------------------+ 5 rows in set (0.00 sec)

mysql> select count() from users; +----------+ | count() | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) [/SQL]

★マスタデータベース（商品情報）

1-1) 商品スキーマ [SQL] mysql> show create database sqitem; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | sqitem | CREATE DATABASE sqitem /!40100 DEFAULT CHARACTER SET utf8 / | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) [/SQL]

1-2) ユーザーテーブル [SQL] mysql> show tables; +------------------+ | Tables_in_sqitem | +------------------+ | item_zaiko | | mst_item | | mst_item_spec | +------------------+ 3 rows in set (0.00 sec)

CREATE TABLE mst_item ( id int(11) unsigned NOT NULL AUTO_INCREMENT, item_name varchar(100) NOT NULL COMMENT '商品名', last_update datetime NOT NULL COMMENT '最終更新日', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; [/SQL]

1-3) データ内容/データ件数 [SQL] mysql> select * from mst_item; +----+-------------+---------------------+ | id | item_name | last_update | +----+-------------+---------------------+ | 1 | 商品名1 | 2012-04-07 01:45:19 | | 2 | 商品名2 | 2012-04-07 01:45:19 | | 3 | 商品名3 | 2012-04-07 01:45:19 | | 4 | 商品名4 | 2012-04-07 01:45:19 | | 5 | 商品名5 | 2012-04-07 01:45:19 | | 6 | 商品名6 | 2012-04-07 01:45:19 | | 7 | 商品名7 | 2012-04-07 01:45:19 | | 8 | 商品名8 | 2012-04-07 01:45:19 | | 9 | 商品名9 | 2012-04-07 01:45:19 | | 10 | 商品名10 | 2012-04-07 01:45:19 | +----+-------------+---------------------+ 10 rows in set (0.00 sec)

mysql> select count() from mst_item; +----------+ | count() | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) [/SQL]

▼上記をまとめますと、以下のような構成になります。

▼ポイント ・商品スキーマ、受注スキーマ、ユーザースキーマから必要なテーブルのみ抽出してDWHに格納する ・テーブルの連携には、EXPORT/LOAD等の処理は一切使わない ・MySQLのレプリケーションで行う ・DWHは、InfiniDBを利用する。つまりストレージエンジンは、InfiniDBです

上記表中のBLACKBOXの部分は今回は触れません、しかしながら、このBLACKBOXの設定が肝になります。こちらの設定については、次回PART3で触れたいと思います。

結局のところ、複数のデータベースサーバに散在している主要テーブルを一つのDWHサーバに収めJOINクエリーするようにするのが最終形です、しかも、InfiniDBだから、その速度は驚速です。参考までに結果のサンプルクエリーを載せておきます。

▼サンプルクエリーとサンプルの結果 サンプルでは、もっとも注文金額多いユーザー、商品別の一覧を出力しております。以下でJOINして結果を取得しているので、つまりは、ここがテーブルの集約地点（DWH）になっているわけです。 [SQL] select usr.id as user_id, usr.user_name , itm.id as s_cd, itm.item_name, sum(j_price) as sum_price from users usr inner join trn_jyucyu trn on usr.id = trn.user_id inner join mst_item itm on itm.id = trn.s_cd group by usr.id, itm.id order by sum_price desc limit 10;

+---------+------------------+------+------------+-----------+ | user_id | user_name | s_cd | item_name | sum_price | +---------+------------------+------+------------+-----------+ | 5 | テスト太郎5 | 9 | 商品名9 | 104926456 | | 5 | テスト太郎5 | 7 | 商品名7 | 102576399 | | 3 | テスト太郎3 | 9 | 商品名9 | 102194492 | | 3 | テスト太郎3 | 7 | 商品名7 | 99681643 | | 4 | テスト太郎4 | 9 | 商品名9 | 98271491 | | 4 | テスト太郎4 | 7 | 商品名7 | 97877942 | | 2 | テスト太郎2 | 7 | 商品名7 | 85680747 | | 1 | テスト太郎1 | 7 | 商品名7 | 84988623 | | 1 | テスト太郎1 | 9 | 商品名9 | 84576007 | | 2 | テスト太郎2 | 9 | 商品名9 | 84506047 | +---------+------------------+------+------------+-----------+ 10 rows in set (2.07 sec) [/SQL]