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

  • 9
    いいね
  • 0
    コメント
この記事は最終更新日から1年以上が経過しています。

TreasureDataの2015年を新機能で振り返る」でも触れられておりますが、2015年にリリースされたオプションサービス「DataTanks」についてご紹介致します!

DataTanks

Screen Shot 2015-12-20 at 14.00.02.png

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

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

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

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

とりあえず今回は

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

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

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

Screen Shot 2015-12-19 at 23.05.16.png

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

  • Web console
    1. Result Export先の選択 advent_01.png  
    2. 「Favorites」よりDataTanksへの書き出し先の定義であるdatatank(行指向テーブル)or datatank_cstore(列指向テーブル)を選択
      ※注意:登録済みの情報は書き換えず、そのままご利用下さい。 advent_02.png  
    3. 情報を確認して実行! advent_03.png  
       

FDW

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

Screen Shot 2015-12-19 at 23.10.08.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から集計結果が出力されたタイミングで外部テーブルと結合したテーブル(列指向型)を生成する」ということをやってみます。
Screen Shot 2015-12-19 at 23.42.18.png

準備するもの

対象 準備するもの
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モードで出力します。

advent07.png

上記を実行すると、DataTanksに該当のテーブル(rain_10sum_from_td)が作成されます。
Screen Shot 2015-12-20 at 01.37.47.png

  • マスタ
    外部のMySQLサーバにてテーブルの作成/データの投入を行います。
    同じく対象サイトから「雨量観測局情報」データをダウンロード(CSV)し、インポートします。

テーブル名 : rain_observation_point_master

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

MySQL側で以下のような情報が見える状態となります。
Screen Shot 2015-12-20 at 13.21.46.png

外部テーブルの作成

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上のマスタ情報の参照が可能となります。
Screen Shot 2015-12-20 at 01.41.12.png

トリガの作成

DataTanksでトリガを作成します。
PlazmaDB側からrain_10sum_from_tdにデータが投入されたタイミングで外部テーブルrain_master_ft_mysqlpoint_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();

Screen Shot 2015-12-20 at 12.37.01.png

実行

トリガ作成後、集計クエリを実行することで以下のような結合テーブルの作成を確認できました!
advent06.png

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

まとめ

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

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

参考: