読者です 読者をやめる 読者になる 読者になる

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で書き直せそうだなぁと思いました。