「TreasureDataの2015年を新機能で振り返る」でも触れられておりますが、2015年にリリースされたオプションサービス「DataTanks」についてご紹介致します！
#DataTanks
トレジャーデータがホスティングするデータマートのオプションサービスです。
主に以下のような特徴がございます。
- TDが保守・運用を行うPostgreSQL(9.4)に準拠したRDB
- 外部データラッパ（FDW）の利用が可能
- トリガ、ストアドプロシージャ等の作成が可能
- テーブル単位で行指向／列指向ストレージの選択が可能
- VPN接続が可能
（プランにより異なる部分もございますので、詳しい内容についてはお問い合わせ下さい。）
DataTanksを利用頂くことで、今までトレジャーデータ（PlazmaDB）が苦手としていた「多数のセッションからの頻繁なアクセス」や「レコード単位での更新」というポイントを補完できるようになります。
また、RDBということで、低レイテンシが求められるBI／クライアンツールからの接続に適しています。
もちろん、接続に関してはPostgreSQL用の接続方式／ドライバの利用が可能です！
とりあえず今回は
- PlazmaDBからDataTanksへのデータ投入方法
- FDW
- 外部テーブル&トリガを使った連携例
についてご紹介させて頂きます！
##PlazmaDB から DataTanksへのデータ投入方法
トレジャーデータ（PlazmaDB）からDataTanksへのデータ投入はResult Exportを利用します。
DataTanksを利用頂くお客様にはExport先として専用の定義をご用意しておりますのでそちらを選択頂きます。
- Web console
- Result Export先の選択
- 「Favorites」よりDataTanksへの書き出し先の定義であるdatatank（行指向テーブル）or datatank_cstore（列指向テーブル）を選択
※注意：登録済みの情報は書き換えず、そのままご利用下さい。
- 情報を確認して実行！
##FDW
PostgreSQLの機能ではございますが、DataTanks内で利用可能な機能の１つなので合わせてご紹介します。
FDW（外部データラッパ）とは外部データを通常のテーブルと同じように検索できる機能です。
PostgreSQL9.1から導入され、現在も様々な拡張が行われています。
DataTanksでも以下のようなFDWの利用が可能です。
postgres_fdw # PostgreSQL
mysql_fdw # MySQL
mongo_fdw # MongoDB
tds_fdw # TreasureDataService(PlazmaDB)
and others.
DataTanks にはtds_fdwというTD用のFDWが事前にインストールされているため、DataTanks からPlazmaDB上のテーブルを外部テーブルとして定義することも可能です。
参考:
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
##️外部テーブル&トリガを使った連携例
それでは PlazmaDB/DataTanks/外部データソース（マスタ） を利用した例をご紹介したいと思います。
###やること
DataTanks内で外部テーブルとトリガを作成し、「PlazmaDBから集計結果が出力されたタイミングで外部テーブルと結合したテーブル（列指向型）を生成する」ということをやってみます。
###準備するもの
| 対象 | 準備するもの |
|---|---|
| PlazmaDB | 集計元テーブル（rain_info_table） 集計クエリ |
| 外部DB-MySQL | マスタ（rain_observation_point_master） |
| DataTanks | 集計結果テーブル（rain_10sum_from_td） 外部テーブル（rain_master_ft_mysql） トリガ関数（rain_info_cstore_replace_function） トリガ（rain_info_cstore_replace_trigger） 結果結合テーブル（rain_info_cstore） |
集計用データとマスタデータは以下のオープンデータを利用させて頂きました。
（静岡県）ふじのくにオープンデータカタログ／雨量情報
###事前作業
- 集計元テーブル
PlazmaDBにてテーブルの作成／データの投入を行います。
対象サイトから「雨量情報」データをダウンロード（CSV）し、インポートします。
テーブル名 : rain_info_table
| 項目名 | 項目内容 | 解説 |
|---|---|---|
| time | 観測時刻 | |
| point_id | 雨量観測局番号 | 雨量観測局情報を参照 |
| rain_10min | 10分雨量 | 単位：mm 欠測：9999 観測データ無し：-1111111111 値は10倍値で表示 |
- 集計クエリ
point_id毎の10分雨量の合計値を算出するクエリとします。
下記クエリの結果をDataTanksへrain_10sum_from_tdという名前のテーブル（行指向型）としてtruncate/insertモードで出力します。
上記を実行すると、DataTanksに該当のテーブル（rain_10sum_from_td）が作成されます。
- マスタ
外部のMySQLサーバにてテーブルの作成／データの投入を行います。
同じく対象サイトから「雨量観測局情報」データをダウンロード（CSV）し、インポートします。
テーブル名 : rain_observation_point_master
| 項目名 | 項目内容 | 解説 |
|---|---|---|
| point_id | 雨量観測局番号 | |
| pointname | 雨量観測局名称 | |
| office_id | 所属事務所 | |
| latitude | 緯度 単位：度 値は10000倍値で表示 |
|
| longitude | 経度 単位：度 値は10000倍値で表示 |
|
| address | 住所 |
MySQL側で以下のような情報が見える状態となります。
###外部テーブルの作成
MySQL上のマスタ情報を参照するために
DataTanksで外部テーブルを作成します。
-- リモートサーバ（マスタ情報を持っているDBサーバ）の登録
CREATE SERVER mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '[MySQL-URL]', port '3306' );
-- ユーザマッピング（接続情報）の登録
CREATE UESR MAPPING for public
SERVER mysql_server
OPTIONS ( username '[MySQL接続用ユーザ]', password '[MySQL接続用パスワード]' );
-- 外部テーブルの作成
CREATE FOREIGN TABLE rain_master_ft_mysql
(
point_id integer not null,
pointname character varying(20),
office_id integer,
latitude integer,
longitude integer,
address character varying(100)
)
SERVER mysql_server
OPTIONS (dbname '[DB名]', table_name 'rain_observation_point_master' );
上記を実行すると、DataTanksからMySQL上のマスタ情報の参照が可能となります。
###トリガの作成
DataTanksでトリガを作成します。
PlazmaDB側からrain_10sum_from_tdにデータが投入されたタイミングで外部テーブルrain_master_ft_mysqlをpoint_idで結合して新しくrain_info_cstoreという結合テーブルを作成する（既にあれば作り直す）というトリガです。
-- ファンクションの作成
CREATE OR REPLACE FUNCTION rain_info_cstore_replace_function()
RETURNS TRIGGER AS $$
BEGIN
DROP FOREIGN TABLE IF EXISTS rain_info_cstore;
CREATE FOREIGN TABLE rain_info_cstore
(
point_id integer,
pointname character varying(20),
office_id integer,
address character varying(100),
rain_sum integer
)
SERVER cstore_server OPTIONS (compression 'pglz');
INSERT INTO rain_info_cstore
SELECT
i.point_id
,m.pointname
,m.office_id
,m.address
,i.rain_sum
FROM
rain_10sum_from_td i
LEFT JOIN
rain_master_ft_mysql m
ON i.point_id = m.point_id
;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
-- トリガの作成
CREATE TRIGGER rain_info_cstore_replace_trigger AFTER INSERT OR UPDATE ON rain_10sum_from_td
FOR EACH STATEMENT EXECUTE PROCEDURE rain_info_cstore_replace_function();
###実行
トリガ作成後、集計クエリを実行することで以下のような結合テーブルの作成を確認できました！
あとは定期的に集計クエリを実行することで、結果に合わせて自動的に結合テーブルも洗い替えされます。
まとめ
「集計結果と外部マスタをDataTanksで紐付ける」ということをトリガを使った例で紹介させて頂きました。（プロシージャ／トリガ／カラムナテーブルが使えますよーということをアピールしたかったのでちょっと強引だったかもしれませんが、、）
トレジャーデータ（PlazmaDB）+ DataTanksを組み合わせることで業務の幅が広がるのでは！と感じているので、新しい使い道や機能のアップデートがありましたら、また紹介させて頂きたいと思いますmm
参考: