トップ > treasuredata

PostgreSQL内の外部テーブルをBIツールからアクセスできるように試みた話

postgresql treasuredata

この記事は Treasure Data Advent Calendar 2015 - Qiita の14日目の記事で、現在12/21です。

なぜこの記事がTreasure DataのAdvent Calendarに入っているかというと、Treasure DataのDataTanksというオプションサービスでPostgreSQLが利用されているためちょっと関係していると個人的に考えています。

DataTanksそのものについては、こちらの記事に良く纏められているのでご参照ください！

qiita.com

このDataTanks、ベースであるPostgreSQLのFDWを用いた外部テーブルを利用することが多く、かつBIツールと接続して利用することが多い、のですが幾つかのBIツールからPostgreSQL上の外部テーブルにアクセスできない（BIツールが外部テーブルの存在を検知できない）問題に気がつきました。

まぁ、workaroundとしては、この外部テーブルをVIEWで包めばBIツールからアクセスできるようになるのですが、あまりスマートではありません。そこで、対応案を考えてみた、というのが今回のお話です。決して、途中まで書いていた記事の内容が思いっきり被っていたので強引に方向展開した訳ではありません。

何が問題なのか？

例えば、TableauをPostgreSQLのデータベースに接続させると以下のようなクエリーが飛んできます。このクエリーによって、テーブルの種別が 'r':通常のテーブル または 'v':ビュー であるものの一覧を取得しています。

BEGIN;
DECLARE "SQL_CUR0x0123456789ab" CURSOR FOR
SELECT relname, nspname, relkind
FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
WHERE relkind IN ('r', 'v')
AND nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast', 'pg_temp_1')
AND n.oid = relnamespace
ORDER BY nspname, relname;
FETCH 100 IN "SQL_CUR0x0123456789ab";
CLOSE "SQL_CUR0x0123456789ab";

しかし、外部テーブル（foreign table）の種別は 'f' であるため、このクエリ条件の対象とはならず、Tableauからは認識されません。

どうすれば良いのか？

色々調べてみると同様の問題が某所のサポートページに載っているのを発見しましたが、BIツール側で対応しそうな雰囲気は無さそうでした。仕方が無いので、上記のようなクエリーが発行された場合はQuery treeを書き換えて、検索対象のテーブル種別に 'f':外部テーブル も追加できないか検討してみました。

幸いなことにPostgreSQLは拡張ライブラリを書くことで簡単にクエリーの書き換えができるようなので、僕のようなPostgreSQL拡張素人でも何とかなるのではないかと楽観的に考えていました（その時は...）

どうやったか？

post_parse_analyze_hookの利用

他の拡張ライブラリを見るとanalyze済みのQuery構造体にアクセスしているものが幾つかありました。どうやら、backend/parser/analyze.c の post_parse_analyze_hook を利用するとanalyze後に任意の関数を呼んでもらえそうです。

backend/parser/analyze.c

/* Hook for plugins to get control at end of parse analysis */
post_parse_analyze_hook_type post_parse_analyze_hook = NULL;

そこで最初の一歩として、

  • 拡張ライブラリ内に適当にログを吐く関数を作って
  • 拡張ライブラリ内の _PG_init() で上記のフックにその関数を指定し
  • Makefile書いて.soを生成しインストール
  • postgresql.confのshared_preload_librariesにそのライブラリ名を指定し
  • PostgreSQLを起動

とやってみたところ、期待通りにクエリの発行時にログが出力されたので、これは余裕で行けるのでは？感が高まりました。

Query構造体をチェック

拡張ライブラリ内でフックに指定した関数にはanalyze済みのQuery構造体が渡ってきます。なのでこれを見れば、BIツールから飛んできた前述のクエリのように "テーブルを列挙しようとしているが外部テーブルが含まれていない" クエリなのかどうか？が判断できそうです。

Query構造体にはrtableというスキャン対象のテーブル情報が詰まったRangeTblEntry構造体とjointreeというpredicatesが詰まっているFromExpr構造体が含まれています。

include/nodes/parsenodes.h

typedef struct Query
{
    :
    List       *rtable;         /* list of range table entries */
    FromExpr   *jointree;       /* table join tree (FROM and WHERE clauses) */
    :

RangeTblEntry構造体にはOid relidが含まれており、これを辿ってRelationData構造体経由でpg_class用の構造体にアクセスできそうです。ちなみにPostgreSQLの実装内ではList構造体が使われることが多いのですが、便利な一方、実際の型が読み取れないのでgrepやctagsでコードを追いにくいなぁと思いました。

include/nodes/parsenodes.h

typedef struct RangeTblEntry
{   
    NodeTag     type;
    RTEKind     rtekind;        /* see above */
    Oid         relid;          /* OID of the relation */
    char        relkind;        /* relation kind (see pg_class.relkind) */
    Alias      *eref;           /* expanded reference names */
        :

include/utils/rel.h

typedef struct RelationData
{
        :
    Form_pg_class rd_rel;       /* RELATION tuple */
        :

include/catalog/pg_class.h

CATALOG(pg_class,1259) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83) BKI_SCHEMA_MACRO
{
    NameData    relname;        /* class name */
    Oid         relnamespace;   /* OID of namespace containing this class */
    Oid         reltype;        /* OID of entry in pg_type for table's
                                 * implicit row type */
        :

pg_class用の構造体にはrelnameやrelnamespaceが含まれているので、これらを用いるとクエリ対象のテーブルが "pg_catalog.pg_class" であるかチェックできます。

次に当該クエリがテーブル種別を表すrelkindを参照しているかどうかチェックし、参照している場合はクエリ上におけるrelkindの登場位置を覚えておきます。この情報は後ほど利用します。チェックの仕方はRangeTblEntry構造体に含まれるerefのcolnamesをなめていけば可能そうです。もう勝ったも同然な気持ちでいました。

include/nodes/primnodes.h

typedef struct Alias
{   
    NodeTag     type;
    char       *aliasname;      /* aliased rel name (never qualified) */
    List       *colnames;       /* optional list of column aliases */
} Alias;

クエリの条件書き換え

PostgreSQLにはexpression_tree_walker()というNodeをtraverseするための関数が用意されています。それ用の関数を自分で書くのは面倒だなぁと思っていたのでとても助かりました。これが無かったらこの拡張ライブラリは半日で書けなかったと思います。

で、expression_tree_walkerにはvisitorとしての処理を行う関数を渡してあげるのですが、この関数内では以下のことを行うようにしました。

  • 対象のNodeがScalarArrayOpExprである場合（例："n in (1, 2, 3)"）
  • かつ、pg_catalog.pg_class.relkindを参照している場合（前述のRangeTblEntry->eref->colnamesチェック時に保存した位置を利用）
  • かつ、IN句の条件に 'r':標準のテーブル が含まれており、'f':外部テーブル が含まれていない場合
  • ScalarArrayOpExprの条件配列に 'f':外部テーブル を追加する

本来は、IN句を用いずにrelkindがチェックされるケース（例："relkind = 'r' and relkind = 'v'"）を考慮しOpExprも見るべきなのですが、BIツールから発行されるクエリーで今のところそのようなものを観測していないこと、及び、このケースのクエリ書き換えの場合、traverse対象Nodeの上位Nodeを書き換える必要があり素人にはちょっと面倒くさそう、といった理由で未実装となっています。

以上のような感じで実装してみたところ、無事想定するクエリを書き換えることができました。

BEGIN;
DECLARE "SQL_CUR0x0123456789ab" CURSOR FOR
SELECT relname, nspname, relkind
FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
WHERE relkind IN ('r', 'v')
AND nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast', 'pg_temp_1')
AND n.oid = relnamespace
ORDER BY nspname, relname;
FETCH 100 IN "SQL_CUR0x0123456789ab"; 
         relname         | nspname | relkind 
-------------------------+---------+---------
 ftex_test_foreign_table | public  | f
(1 row)

現在の状況と今後の予定

現在は komamitsu/foreign_table_exposer · GitHub 上で開発を進めているので、何かフィードバックがあれば是非issue登録やpull requestを頂けると有難いです。あとPGNXにも登録してみましたので、こちらからも利用できると思います（foreign_table_exposer: Expose foreign tables as a regular table / PostgreSQL Extension Network）。

今後は、以下のような場合に問題が出そうなので、もう少し情報を集めつつ、必要であれば発動条件の制限をしていこうかと思っています。

  • pg_catalog.pg_class.relkind経由でテーブルを列挙し、標準テーブルのみ扱うことを想定し外部テーブルを扱うと問題があるケース

今回初めてPostgreSQLの拡張ライブラリを書いてみたのですが、簡単に色々なことができるので非常に面白かったです。以前、komamitsu/td-fdw · GitHub というMulticornを用いたFDWを書いたのですが、機会があればCで書き直せそうだなぁと思いました。

Raspberry PiでFluentdを立ち上げてTDにデータを流してみた話

raspberrypi fluentd treasuredata

一ヶ月程前に同僚である @doryokujin 先生からRaspberry Pi（面倒なので以下Pi）を頂いたのだけど、Piに必要なSDカードが手元に無くてすっかり放置していたのですが、満を持してSDカードを買ってきたのでちょっとPiを立ち上げてみた。という話を備忘録的に。
 
必要なもの（というかこれで間に合った、的なもの）

  • USBケーブル
  • RCAケーブル
  • USBキーボード
  • LANケーブル
  • SDカード（SDHD 8GB）

 
startxとか叩かなければUSBマウスは要らない。
 

Piの立ち上げ

  • 準備に使った環境はMac OX 10.8.5
  • SDカードは挿してある状態
  • SDカードの/dev/disk番号を確認してunmount
$ diskutil list
/dev/disk0
     :
/dev/disk4
   #:                       TYPE NAME                    SIZE       IDENTIFIER
   0:     FDisk_partition_scheme                        *7.8 GB     disk4
   1:                 DOS_FAT_32 NO NAME                 7.8 GB     disk4s1
$ diskutil unmountDisk /dev/disk4
Unmount of all volumes on disk4 was successful

 

  • OSにはRaspbianを選択。2014-01-07-wheezy-raspbian.zip を落としてきて展開したimgファイルをddで書き込み。かなり時間がかかる
$ sudo dd bs=1m if=~/Downloads/2014-01-07-wheezy-raspbian.img of=/dev/disk4
Password:
2825+0 records in
2825+0 records out
2962227200 bytes transferred in 1071.833726 secs (2763700 bytes/sec)
  • ddでイメージが書き込まれたSDカードをPiに挿してMBPとか適当なやつからUSBケーブルをつなげて電源を供給して上げれば起動
  • 自宅にディスプレイが無いので、あまり使われていないアナログテレビを用いて動作確認。RCAケーブルの黄色いやつ（映像用）をつなげれば滲んだ雰囲気の画面で確認出来る
  • Setting画面が表示されてユーザーのパスワードの変更とか色々できるので、やりたい場合はやる感じで。SSHの設定に関しては失敗してたけどひとまず先に進む
  • Settingを終えるとlogin promptが表示されるのでlogin。sshdも起動していたのでログインしてみたら普通に入れた。
  • Rubyは1.9.3, Javaは1.7が入っていて良い感じ

 

Fluentdとかを入れてTDにデータを流し込んでみる

  • Fluentdの安定版であるtd-agent（http://docs.fluentd.org/articles/install-by-deb）入れようかと思ったけど、何となくgemから入れてみた。FluentdはC拡張のビルドが発生するので、ruby-dev的なものを入れておく
$ sudo aptitude install ruby-dev
$ sudo gem install fluentd
  • TreasureDataにデータを入れるのでfluent-flugin-tdも。というか事前にTDのアカウントが必要なので適当に作っておくのが前提
$ sudo fluent-gem install fluent-plugin-td
  • fluentdのconfファイルを作成(/home/pi/fluentd/fluent.confとか)。入力はin_forwardだけの超シンプルな設定。出力はout_td。
<match td.*.*>
  type tdlog
  apikey <replace this with your TD API key!>
 
  auto_create_table
  buffer_type file
  buffer_path /home/pi/fluentd/td
</match>
 
<source>
  type forward
</source>
  • fluentd起動
$ fluentd -c /home/pi/fluentd/fluent.conf

fluentdデーモンとして起動したい場合は以下のようにしても良いかも。

$ export FLUENTD_ROOT=/home/pi/fluentd
$ fluentd -c $FLUENTD_ROOT/fluent.conf --log $FLUENTD_ROOT/fluentd.log --daemon $FLUENTD_ROOT/fluentd.pid
  • fluent-catとかfluent-postコマンドなどでレコードを放り込んでみる
$ echo '{"name":"pi", "age":49}' | fluent-cat td.pidb.test
  • td tablesコマンドで件数を確認
$ td tables pidb
+----------+-------+------+-------+--------+---------------------------+---------------------------+-----------------------+
| Database | Table | Type | Count | Size   | Last import               | Last log timestamp        | Schema                |
+----------+-------+------+-------+--------+---------------------------+---------------------------+-----------------------+
| pidb     | test  | log  | 1     | 0.0 GB | 2014-02-15 23:17:20 +0900 | 2014-02-15 23:14:15 +0900 | age:long, name:string |
+----------+-------+------+-------+--------+---------------------------+---------------------------+-----------------------+
1 row in set
  • td query コマンドでクエリーを投げてみる
$ td query -w -d pidb 'select name, age from test'
   :
Status     : success
Result     :
+------+-----+
| name | age |
+------+-----+
| pi   | 49  |
+------+-----+
1 row in set

 
見返してみると、記事にするほどの内容でも無い気がしてきたけど、勢いで書いたのでまぁ良いのではと。
 
PiにはGPIOとか付いているのでセンサー的なものをくっつけて、そこからの情報をTDに送り続けるというのは割と簡単に出来そう。データ量をあまり気にせずどんどん放り込んで後で集計の切り口を試行錯誤しやすいのは、1TDユーザーとしても使っていてかなり楽なので相性が良さげ。