今からお前んちこいよ

品川にて細々とお勉強。

PostgreSQLのRDSでfunctionにjavascriptを使う

概要

PostgreSQLのRDSでファンクションにjavascriptを使う。
自分でインストールすれば拡張言語でplpythonを入れてpythonでストアドプロシージャやストアドファンクションを書くことができる。*1 だが、AWSのRDSの場合、plpythonモジュールは信頼性の側面から採用されていない。*2

AWS公式ページ(Amazon RDS for PostgreSQL | AWS)より、

PostgreSQL では、拡張によって、プロシージャ言語をデータベースに読み込むことができます。PostgreSQL には 4 つの言語拡張が含まれており、Perl、pgSQL、Tcl、および JavaScript(V8 JavaScript エンジンにより)がサポートされます。

どうやらRDSでは、
 ・ Perl
 ・ pgSQL
 ・ Tcl
 ・ javascript(V8)
といった拡張言語を提供している。
ということでpythonの代わりに、javascriptを使う。*3

RDSの準備

plv8 は version 9.3.5 以降に対応。
まず、extensionが対応しているかを確認。

show rds.extensions; 

対応していれば、DBに反映させる。

create extension plv8;

試しにファンクションと戯れる

・ファンクションを作る

CREATE FUNCTION plv8_test(keys text[], vals text[]) RETURNS text AS $$
    var o = {};
    for(var i=0; i<keys.length; i++){
        o[keys[i]] = vals[i];
    }
    return JSON.stringify(o);
$$ LANGUAGE plv8 IMMUTABLE STRICT;

・存在確認

SELECT prosrc FROM pg_proc WHERE proname = 'plv8_test';
                prosrc
---------------------------------------
                                      +
     var o = {};                      +
     for(var i=0; i<keys.length; i++){+
         o[keys[i]] = vals[i];        +
     }                                +
     return JSON.stringify(o);        +

(1 row)

・使ってみる

SELECT plv8_test(ARRAY['name', 'age'], ARRAY['Tom', '29']);
         plv8_test        
---------------------------
 {"name":"Tom","age":"29"}
(1 row)

 

おおー

さて、今回やりたかったこと

ここからはかなり個人的メモ。\ 突然の地図業界 /
日本の全道路データ(1千万件ほど)を線単位からノード単位に変換する。

・線単位のデータとは
( roadid, len, points, direction, roadname, etc ...)
  例: 1234, 54, "12345,6561435,1234325,56134514,... ,234515,6143514", 1, "新宿西口交差点入口", etc .....
  ※ points : 形状を正方向の緯度経度列で表したもの。 "緯度0,経度0,緯度1,経度1, ...,緯度x,経度x"
  ※ direction : 道路の進行方向。 両方向通行可/正方向一方通行/逆方向一方通行/通行止め とか

・ノード単位データとは
( nodeid, connection, roadid, len) 
  例: "123456561435", "2345156143514", 1234, 54
  ※ nodeid : 線の始点の緯度経度  "緯度0経度0"
  ※ connection : ノードが繋がるノード。つまり線の末端  "緯度x,経度x"

 

DBの外でコードを書いてこの量のデータをこの程度の処理のためにread/writeするのは不毛なので、 タイトルの通り、PostgreSQLのRDSでfunctionにjavascriptを使う

こんな感じのファンクションを書いた。

CREATE TYPE nodedata AS (roadid integer, direction integer, len integer, nodeid text, connection text);
CREATE FUNCTION gen_node_data(org_tblname text) RETURNS SETOF nodedata AS
$$
    var dataNum = plv8.execute( 'SELECT count(*) FROM $1', [org_tblname]);
    for (var i=1; i <= dataNum[0]; i++){
        var tmp = {};
        var orgData = plv8.execute( 'SELECT * FROM $1 WHERE roadid = $2', [org_tblname, i]);
        var points = orgData[0].points.split(",");
        var pcnt = points.length;
        var direction = 0;
        var isOneway = (parseInt(orgData[0].direction) != 0)  // 両方向なのかどうか
        tmp.roadid = parseInt(orgData[0].roadid);
        tmp.len = parseInt(orgData[0].len);

        // 正方向
        if (parseInt(orgData[0].direction) == 1 || isOneway == false) {  
            tmp.direction = 1;
            tmp.nodeid = points[0].toString() + points[1].toString();
            tmp.connection = points[pcnt-2].toString() + points[pcnt-1].toString();
            plv8.return_next(tmp);  // ここで出力データ追加
        }

        // 逆方向
        if (parseInt(orgData[0].direction) == 2 || isOneway == false) {  
            tmp.direction = 2;
            tmp.nodeid = points[pcnt-2].toString() + points[pcnt-1].toString();
            tmp.connection = points[0].toString() + points[1].toString();
            plv8.return_next(tmp);  // ここで出力データ追加
        }
    }
    return ;
$$
LANGUAGE plv8;

 

実行すると

select * from gen_node_data('tbl_road');

 roadid | direction |  len |       nodeid       |     connection
--------+-----------+------+--------------------+--------------------
      1 |         1 |  707 | 151227008506239453 | 151246893506224401
      2 |         1 |  642 | 151246893506224401 | 151266741506215969
      3 |         1 |  261 | 151266741506215969 | 151275000506213333
      4 |         1 | 1856 | 151275000506213333 | 151328906506179110
      5 |         1 |  845 | 151328906506179110 | 151350000506155709
      6 |         1 | 1812 | 151350000506155709 | 151406781506137500

終わりに

きっとこういう目的でファンクションを作っていくのは恒久的ではなく、今回はあくまでも試しにデータが欲しいという方針からパパッと対応するためにファンクションを作った。 普段コード書いててだるいなって思うことがすんなりできて新鮮だけれど、管理面がネックなので使う気にはならないかなという気持ち。