DataTanks のご紹介と外部テーブル&トリガを使った連携例

「[TreasureDataの2015年を新機能で振り返る](http://qiita.com/toru-takahashi/items/2336782c69a7a5533dc6)」でも触れられておりますが、2015年にリリースされたオプションサービス「DataTanks」についてご紹介致します！

#DataTanks

<img width="315" alt="Screen Shot 2015-12-20 at 14.00.02.png" src="https://qiita-image-store.s3.amazonaws.com/0/100186/3563a503-7e78-a827-b528-cead62915a7d.png">


トレジャーデータがホスティングするデータマートのオプションサービスです。
主に以下のような特徴がございます。

- TDが保守・運用を行うPostgreSQL(9.4)に準拠したRDB
- 外部データラッパ（FDW）の利用が可能
- トリガ、ストアドプロシージャ等の作成が可能
- テーブル単位で行指向／列指向ストレージの選択が可能
- VPN接続が可能

（プランにより異なる部分もございますので、詳しい内容についてはお問い合わせ下さい。）

DataTanksを利用頂くことで、今までトレジャーデータ（PlazmaDB）が苦手としていた「多数のセッションからの頻繁なアクセス」や「レコード単位での更新」というポイントを補完できるようになります。
また、RDBということで、低レイテンシが求められるBI／クライアンツールからの接続に適しています。
もちろん、接続に関してはPostgreSQL用の接続方式／ドライバの利用が可能です！


とりあえず今回は

- PlazmaDBからDataTanksへのデータ投入方法
- FDW
- 外部テーブル&トリガを使った連携例

についてご紹介させて頂きます！


##PlazmaDB から DataTanksへのデータ投入方法

<img width="415" alt="Screen Shot 2015-12-19 at 23.05.16.png" src="https://qiita-image-store.s3.amazonaws.com/0/100186/911df7d4-9ec6-714c-c6fc-5a532566aabb.png">

トレジャーデータ（PlazmaDB）からDataTanksへのデータ投入はResult Exportを利用します。
DataTanksを利用頂くお客様にはExport先として専用の定義をご用意しておりますのでそちらを選択頂きます。

- **Web console**
 1. Result Export先の選択
![advent_01.png](https://qiita-image-store.s3.amazonaws.com/0/100186/5ea6bbb7-6d06-4606-867c-7cf25b2c3f5b.png)
　<br>
 2. 「Favorites」よりDataTanksへの書き出し先の定義であるdatatank（行指向テーブル）or datatank_cstore（列指向テーブル）を選択 <br>**※注意：登録済みの情報は書き換えず、そのままご利用下さい。**
![advent_02.png](https://qiita-image-store.s3.amazonaws.com/0/100186/2934dd14-5540-0487-9e3b-48e0e1f4842a.png)
　<br>
 3. 情報を確認して実行！
![advent_03.png](https://qiita-image-store.s3.amazonaws.com/0/100186/eac665f9-863d-08c4-bdd3-769c3651839f.png)
　<br>
　<br>


##FDW
PostgreSQLの機能ではございますが、DataTanks内で利用可能な機能の１つなので合わせてご紹介します。

<img width="480" alt="Screen Shot 2015-12-19 at 23.10.08.png" src="https://qiita-image-store.s3.amazonaws.com/0/100186/cc6d5ced-4e25-6508-cfaf-e1b2e00502b6.png">

FDW（外部データラッパ）とは外部データを通常のテーブルと同じように検索できる機能です。
PostgreSQL9.1から導入され、現在も様々な拡張が行われています。
DataTanksでも以下のようなFDWの利用が可能です。

```例.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から集計結果が出力されたタイミングで外部テーブルと結合したテーブル（列指向型）を生成する」ということをやってみます。
<img width="481" alt="Screen Shot 2015-12-19 at 23.42.18.png" src="https://qiita-image-store.s3.amazonaws.com/0/100186/c296f6a8-92b8-f595-8966-0f0782d64b77.png">


###準備するもの

| 対象 | 準備するもの |
|:-----------|:------------|
| PlazmaDB | 集計元テーブル（rain_info_table） <br> 集計クエリ |
| 外部DB-MySQL | マスタ（rain_observation_point_master） |
| DataTanks | 集計結果テーブル（rain_10sum_from_td） <br> 外部テーブル（rain_master_ft_mysql） <br> トリガ関数（rain_info_cstore_replace_function） <br> トリガ（rain_info_cstore_replace_trigger） <br> 結果結合テーブル（rain_info_cstore） |

集計用データとマスタデータは以下のオープンデータを利用させて頂きました。
[（静岡県）ふじのくにオープンデータカタログ／雨量情報](http://open-data.pref.shizuoka.jp/mu0b05bub-139/)


###事前作業
- 集計元テーブル<br>
PlazmaDBにてテーブルの作成／データの投入を行います。<br>
[対象サイト](http://open-data.pref.shizuoka.jp/mu0b05bub-139/)から「雨量情報」データをダウンロード（CSV）し、インポートします。<br>

テーブル名 : rain_info_table <br>

| 項目名 | 項目内容 | 解説 |
|:-----------:|:------------:|:------------|
| time | 観測時刻 | |
| point_id | 雨量観測局番号 | 雨量観測局情報を参照 |
| rain_10min | 10分雨量 | 単位：mm<br>欠測：9999<br>観測データ無し：-1111111111<br>値は10倍値で表示<br>|

- 集計クエリ <br>
```point_id```毎の10分雨量の合計値を算出するクエリとします。<br>
下記クエリの結果をDataTanksへ```rain_10sum_from_td ```という名前のテーブル（行指向型）として```truncate/insert```モードで出力します。

<img width="766" alt="advent07.png" src="https://qiita-image-store.s3.amazonaws.com/0/100186/516ce7b9-fd60-964c-b138-82ed6c46ed03.png">


上記を実行すると、DataTanksに該当のテーブル（```rain_10sum_from_td```）が作成されます。
<img width="514" alt="Screen Shot 2015-12-20 at 01.37.47.png" src="https://qiita-image-store.s3.amazonaws.com/0/100186/bff19544-76c1-4c47-74fb-f9dc03eddc40.png">

- マスタ<br>
外部のMySQLサーバにてテーブルの作成／データの投入を行います。<br>
同じく[対象サイト](http://open-data.pref.shizuoka.jp/mu0b05bub-139/)から「雨量観測局情報」データをダウンロード（CSV）し、インポートします。<br>

テーブル名 : rain_observation_point_master

| 項目名 | 項目内容 | 解説 |
|:-----------:|:------------:|:------------|
| point_id | 雨量観測局番号 | |
| pointname | 雨量観測局名称 | |
| office_id | 所属事務所 | |
| latitude | 緯度<br>単位：度<br>値は10000倍値で表示 |
| longitude | 経度<br>単位：度<br>値は10000倍値で表示 |
| address | 住所 |

MySQL側で以下のような情報が見える状態となります。
<img width="683" alt="Screen Shot 2015-12-20 at 13.21.46.png" src="https://qiita-image-store.s3.amazonaws.com/0/100186/10200a49-db9b-6938-d1b2-7a51adb8da5f.png">




###外部テーブルの作成
MySQL上のマスタ情報を参照するために
DataTanksで外部テーブルを作成します。

```sql
-- リモートサーバ（マスタ情報を持っている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上のマスタ情報の参照が可能となります。
<img width="661" alt="Screen Shot 2015-12-20 at 01.41.12.png" src="https://qiita-image-store.s3.amazonaws.com/0/100186/5f2c6cad-ce06-9839-da5e-29b340d91b1c.png">


###トリガの作成
DataTanksでトリガを作成します。
PlazmaDB側から```rain_10sum_from_td```にデータが投入されたタイミングで外部テーブル```rain_master_ft_mysql```を```point_id```で結合して新しく```rain_info_cstore```という結合テーブルを作成する（既にあれば作り直す）というトリガです。

```sql
-- ファンクションの作成
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();
```
<img width="556" alt="Screen Shot 2015-12-20 at 12.37.01.png" src="https://qiita-image-store.s3.amazonaws.com/0/100186/189b48a9-0c67-5be8-1aeb-6967f39ce8d4.png">


###実行
トリガ作成後、集計クエリを実行することで以下のような結合テーブルの作成を確認できました！
<img width="582" alt="advent06.png" src="https://qiita-image-store.s3.amazonaws.com/0/100186/6f6d9eca-7480-86b9-538f-bcb807cb9041.png">

あとは定期的に集計クエリを実行することで、結果に合わせて自動的に結合テーブルも洗い替えされます。


# まとめ
「集計結果と外部マスタをDataTanksで紐付ける」ということをトリガを使った例で紹介させて頂きました。（プロシージャ／トリガ／カラムナテーブルが使えますよーということをアピールしたかったのでちょっと強引だったかもしれませんが、、）

トレジャーデータ（PlazmaDB）+ DataTanksを組み合わせることで業務の幅が広がるのでは！と感じているので、新しい使い道や機能のアップデートがありましたら、また紹介させて頂きたいと思いますmm


参考:

- [Data Tanks Overview](https://docs.treasuredata.com/articles/data-tank)
- [Treasure Dataの新機能(Data Tank)をAudienceOneのレポート機能で利用した話](http://yebisupress.dac.co.jp/2015/11/06/datatank_aone/)