関数の改良（ⅱ）複数行に対するウェブアクセスを、ウィンドウ関数で1回にまとめる

ウィンドウ関数の一般的な使い方からは外れますが、今回やりたい「ウェブアクセスは1回に集約し、結果はテーブルの各行に対して付ける」方法を考えた結果、PL/v8でウィンドウ関数のストアドを作るのが最も簡単な気がしました。最初に検索語のサンプルテーブル（昨日と同じ）を再掲します。↓

select * from cities;
+--------+
|  city  |
+--------+
| 東京   |
| 大阪   |
| 札幌幌 | -- 検索結果なしのテスト用
| 仙台   |
| 広島   |
| 北九州 |
| 福岡   |
+--------+
(7 rows)

最終形のイメージは ↓ こんな感じ。結果は昨日の複雑なクエリと同様ですが、検索語の列（city）をストアドに渡すだけで、ストアド内で1回だけウェブアクセスを行い（全ての検索語を一括してMediaWiki APIに渡す）、返ってくるJSONを上手く処理して元テーブルの各行にくっつけて返してほしいなぁと。

select city, plv8_winf_expected(city) over() from cities;
+--------+----------+
|  city  |  pageid  |
+--------+----------+
| 東京   |  2126632 |
| 大阪   |  3211539 |
| 札幌幌 |       -1 |
| 仙台   |  3211582 |
| 広島   |  3211568 |
| 北九州 | 16326328 |
| 福岡   |  9336564 |
+--------+----------+
(7 rows)

まず前段、検索語を配列にまとめて1回のウェブアクセスで済ます部分だけストアド化すると ↓ こんな感じ。ウィンドウ関数だから指定列の各行について処理しますが、ウェブアクセスは1行目で行い、2行目以降は何もしません。

1行目のところで全行のデータがウィンドウ関数オブジェクトに格納されており、get_partition_row_countとget_func_arg_in_partitionメソッドで検索語の配列を作成。後は昨日の関数と同様です。ただし結果をJSON配列にする手間が不要に。（ウィンドウ関数内で検索語をキーにオブジェクトから取り出せば済む）

create or replace function plv8_MediaWiki_ex3(wiki_title text)
returns json language plv8 window immutable as $$

    // prepare window function object
    var w = plv8.get_window_object();

    // do nothing at the second row later
    if (w.get_current_position() > 0) return null;

    // construct an array of keywords
    var wiki_titles = [];
    for(var i = 0; i < w.get_partition_row_count(); i++) {
        wiki_titles.push(w.get_func_arg_in_partition(0, i, w.SEEK_HEAD, false));
    }
    
    // followed by getting data from MediaWiki API and converting it to JSON
    // as well as the function in http://kenpg.bitbucket.org/blog/201601/08.html

    var url = 'https://en.wikipedia.org/w/api.php?action=query&format=json&titles='
            + encodeURIComponent(wiki_titles.join('|')),
        sql = "select convert_from(plpy_urlopen('" + url + "'), 'utf8')";
        res = plv8.execute(sql),
        objTmp1 = JSON.parse(res[0]['convert_from']);

    if (!objTmp1) return null;

    // search a pageid matching each title
    var objTmp2 = objTmp1['query']['pages'],
        res = {};
    for (var i = 0; i < wiki_titles.length; i++) {
        var titleTarget = wiki_titles[i];
        for (var pid in objTmp2) {
            var objCand = objTmp2[ pid ];
            if (objCand['title'] === titleTarget) {
                res[ titleTarget ] = pid;
                break;
            }
        }
    }
    return res;
$$;

↓ 実行結果。1行目で行ったウェブアクセスとJSON生成の結果に、各行の検索語が入ってます。得られた結果を2行目以降も保持して検索語のキーで取り出せばOK。次にその処理を追加します。

select *, plv8_MediaWiki_ex3(city) over() from cities;
+--------+--------------------------------------------------------------------------------------------------------------------------+
|  city  |                                                    plv8_mediawiki_ex3                                                    |
+--------+--------------------------------------------------------------------------------------------------------------------------+
| 東京   | {"東京":"2126632","大阪":"3211539","札幌幌":"-1","仙台":"3211582","広島":"3211568","北九州":"16326328","福岡":"9336564"} |
| 大阪   |                                                                                                                          |
| 札幌幌 |                                                                                                                          |
| 仙台   |                                                                                                                          |
| 広島   |                                                                                                                          |
| 北九州 |                                                                                                                          |
| 福岡   |                                                                                                                          |
+--------+--------------------------------------------------------------------------------------------------------------------------+
(7 rows)

↓ 処理追加後。これが今回の一応の完成形です。先ほど1行目の処理でウェブから得たJSONをウィンドウ関数オブジェクトにセットし、各行で検索語をキーにpageidを取り出します。

create or replace function plv8_MediaWiki_ex4(wiki_title text)
returns json language plv8 window immutable as $$

    // prepare window function object
    var w = plv8.get_window_object();

    // at the first row
    if (w.get_current_position() === 0) {

        // construct an array of keywords
        var wiki_titles = [];
        for(var i = 0; i < w.get_partition_row_count(); i++) {
            wiki_titles.push(w.get_func_arg_in_partition(0, i, w.SEEK_HEAD, false));
        }
        
        // followed by getting data from MediaWiki API and converting it to JSON
        // as well as the function in http://kenpg.bitbucket.org/blog/201601/08.html

        var url = 'https://en.wikipedia.org/w/api.php?action=query&format=json&titles='
                + encodeURIComponent(wiki_titles.join('|')),
            sql = "select convert_from(plpy_urlopen('" + url + "'), 'utf8')";
            res = plv8.execute(sql),
            objTmp1 = JSON.parse(res[0]['convert_from']);

        if (!objTmp1) {
            w.set_partition_local(null);
        } else {
            // search a pageid matching each title
            var objTmp2 = objTmp1['query']['pages'];
                objRes = {};
            for (var i = 0; i < wiki_titles.length; i++) {
                var titleTarget = wiki_titles[i];
                for (var pageid in objTmp2) {
                    var objCand = objTmp2[ pageid ];
                    if (objCand['title'] === titleTarget) {
                        objRes[ titleTarget ] = pageid;
                        break;
                    }
                }
            }
            w.set_partition_local(objRes);
        }
    }
    
    // output result (pageid)
    var obj = w.get_partition_local();
    if (!obj) {
        return null;
    } else {
        var title_curr = w.get_func_arg_in_partition(0, 0, w.SEEK_CURRENT, false);
        return obj[ title_curr ];
    }
$$;

↓ 実行結果。コンソール画像では確認のため元テーブル表示も入ってます。ストアドの出力列名を分かりやすくpageidとかにしたかったですが、今回は方法が見つからず関数名のまま。また結果もJSON型のままです。

select *, plv8_MediaWiki_ex4(city) over() from cities;
+--------+--------------------+
|  city  | plv8_mediawiki_ex4 |
+--------+--------------------+
| 東京   | 2126632            |
| 大阪   | 3211539            |
| 札幌幌 | -1                 |
| 仙台   | 3211582            |
| 広島   | 3211568            |
| 北九州 | 16326328           |
| 福岡   | 9336564            |
+--------+--------------------+
(7 rows)

↓ 出力列名と型を分かりやすくする例。ついでにpageidで並べ替えてみました。結果が普通の表形式なのでサブクエリやWITH句に組み込むのも簡単です。

select *, plv8_MediaWiki_ex4(city) over() :: text :: int as pageid
from cities
order by pageid;
+--------+----------+
|  city  |  pageid  |
+--------+----------+
| 札幌幌 |       -1 |
| 東京   |  2126632 |
| 大阪   |  3211539 |
| 広島   |  3211568 |
| 仙台   |  3211582 |
| 福岡   |  9336564 |
| 北九州 | 16326328 |
+--------+----------+
(7 rows)

関数のテスト:取得したpageidからWikipediaの記事本文を確認

まず、得られたWikipediaのpageidが正しいか確かめます。今回の関数が返すpageidを ↓ のURLに入れてブラウザで開けば、英語版Wikipediaの記事ページそのもの。例として東京（pageid : 2126632）の画面を示します。

https://en.wikipedia.org/w/index.php?curid=（関数が返すpageid）

次にエラー処理の確認。ネットワーク非接続だと ↓ 全てNULLになり、ウェブアクセスに使うPL/PythonストアドによるNOTICEが出ます。APIサーバ停止時とか何かの理由でウェブから結果取得できなかった場合も同様。実際叩いたURLが表示されるので確認に便利。

select *, plv8_MediaWiki_ex4(city) over() from cities;

NOTICE:  URLError or HTTPError. failed to reach a server
CONTEXT:  PL/Python function "plpy_urlopen"
SQL statement "select convert_from(plpy_urlopen('https://en.wikipedia.org/w/api.php?a
ction=query&format=json&titles=%E6%9D%B1%E4%BA%AC%7C%E5%A4%A7%E9%98%AA%7C%E6%9C%AD%E5
%B9%8C%E5%B9%8C%7C%E4%BB%99%E5%8F%B0%7C%E5%BA%83%E5%B3%B6%7C%E5%8C%97%E4%B9%9D%E5%B7%
9E%7C%E7%A6%8F%E5%B2%A1'), 'utf8')"
+--------+--------------------+
|  city  | plv8_mediawiki_ex4 |
+--------+--------------------+
| 東京   |                    |
| 大阪   |                    |
| 札幌幌 |                    |
| 仙台   |                    |
| 広島   |                    |
| 北九州 |                    |
| 福岡   |                    |
+--------+--------------------+
(7 rows)

関数のテスト:一括取得数を多めにしてみる

今までは一括検索といっても数件程度でしたが、MediaWiki APIの下記ドキュメントによれば1回のクエリで最大50件まで記事タイトル検索ができるので、多めにテストしてみます。

MediaWiki - API:Query（一部は日本語）

Specifying titles through the query string (either through titles or pageids) is limited to 50 titles per query (or 500 for those with the apihighlimits right, usually bots and sysops).

検索語は何でもいいですが、どっかからデータを持ってくるのが面倒なのでクエリ上でランダムな英字3文字のテーブルを ↓ のように作りました。行数は162。この中にSQLとかXMLとか記事があるものはpageidが返り、それ以外は記事なしの結果になると予想。

create table random3chars as
with a (str) as (
    select string_agg(chr(cast(ceil(random() * 26) + 64 as int)), '')
    from generate_series(1, 500) as gs
)
select distinct (regexp_matches(str, '.{3}', 'g'))[1]
from a;

select * from random3chars;
+----------------+
| regexp_matches |
+----------------+
| ETB            |
| GNJ            |
| EPW            |
| GDL            |
| DGL            |
| OSP            |
| PDM            |
| BJF            |
| PVH            |
| HCL            |
| BIF            |
| JTN            |
| EVO            |
| CZV            |
(...)

↓ 試しに20件の一括検索結果。聞いたことがない英字3文字にも結構pageidがあり、何らか固有名詞や略語になってるようです。記事なしの語には-1からの負数が付くので、最小値を取れば記事なしの件数が分かって便利。

with a as (
    select regexp_matches as word
    from random3chars
    order by random()
    limit 20
)
select *, plv8_MediaWiki_ex4(word) over()
from a;
+------+--------------------+
| word | plv8_mediawiki_ex4 |
+------+--------------------+
| UVA  | 38443306           |
| IZJ  | -1                 |
| XYT  | -2                 |
| DDP  | 1709559            |
| CMU  | 61137              |
| BTC  | 1010613            |
| ERF  | 658194             |
| XKN  | -3                 |
| KKO  | -4                 |
| ETB  | 612729             |
| AWI  | 3613552            |
| IIV  | -5                 |
| HMO  | 4278947            |
| EDM  | 9967               |
| YJR  | -6                 |
| NQF  | 3754357            |
| VPB  | 1828379            |
| KST  | 6180744            |
| JKU  | 1267051            |
| KRF  | 1115400            |
+------+--------------------+
(20 rows)

次に、最大50件という仕様を知りつつ ↓ あえて100件を一括クエリしてみました。件数を確認しやすくするため、普通のウィンドウ関数のrow_number()を付加してます。ありがたいことに50件までは正常に結果が返り、それ以上は無視されるようです。レスポンスのJSONを見ればエラーIDとか情報が入ってそうですが、今回は未確認。

with a as (
    select regexp_matches as word
    from random3chars
    order by random()
    limit 100
)
select row_number() over(),
    word,
    plv8_MediaWiki_ex4(word) over()
from a;

関数のテスト:検索語（英語）の大文字・小文字について

最後に、これは今回の関数というよりMediaWiki APIの仕様に関する確認ですが、同じ検索語でも大文字・小文字の付け方によってpageidが変わります。例えばPostgreSQLとMySQLで各3通りのつづりを渡すと ↓ こんな結果。正式のつづり方でなくとも先頭が大文字ならpageid（本来のページへリダイレクトするもの）が返り、それ以外はNULL。

select *, plv8_MediaWiki_ex4(word) over()
from unnest('{
    PostgreSQL,
    MySQL,
    Postgresql,
    Mysql,
    postgresql,
    mysql
}' :: text[]) as word;
+------------+--------------------+
|    word    | plv8_mediawiki_ex4 |
+------------+--------------------+
| PostgreSQL | 23824              |
| MySQL      | 19545              |
| Postgresql | 253356             |
| Mysql      | 378657             |
| postgresql |                    |
| mysql      |                    |
+------------+--------------------+
(6 rows)

これにはAPI側による検索語の「正規化」という処理が関係しており、postgresqlのように先頭が大文字でない語は、先頭大文字に「正規化」して検索する模様。その結果pageidが見つかっても、返されるJSONの形式が少し変わるので今回のストアドでは捕捉できずNULLになりました。レスポンス全体の中には、どの語をどう「正規化」して検索したかの情報もあるので、それをストアド内で処理すればNULLでなくpageidを返せます。

今後の課題

MediaWiki APIに限らず、複数検索を一括して行えるAPIでは件数上限が当然あると思うので、検索数が多い場合はストアドで上限内に絞るとか、リクエストを分割する等の工夫が必要。後者の場合、件数以外に単位時間あたりリクエスト数の制限とかも考えないといけません。

MediaWiki APIに関しては、今回テストしたpageidだけでなく記事本文や直近の更新日時など様々なデータを得られるので、それらにストアドを対応させればより実用的。例えば取得したい内容を引数で指定するとか。あと、正直なところMediaWiki以外にまだ複数検索を一括で行えるAPIを発見しておらず、別のが見つかればまた課題やストアドの修正点が浮かんでくると思います。

最後に、今回は「PL/v8で出来ないウェブアクセスをPL/Pythonで補う」方法でしたが、ウェブアクセスをwww_fdwでできるかも。Windows用のバイナリもどこかで配布されていた気がするので、時間があればいずれ。