PL/Pythonを利用して統計処理用のデータを取得する

はじめまして。
キャスレーコンサルティングLS(リーディング・サービス)部の田村です。

データベースにストアドプロシージャを作成する際、皆さんは何を使っていますか？

私は今まで、PL/pgSQL(PostgresSQL)等のDB固有の言語を利用していましたが、
カーソル等DB固有言語独特の書き方に違和感がありました。

何かないか探していたところ、その他のスクリプト言語でも
直接ストアドプロシージャの作成ができることを知りました。
（詳しくは、こちらの公式マニュアルを参照してください。）

今回は、業務で使用しているスクリプト言語のひとつ、PL/Pythonの使い方をご紹介いたします。

動作環境

・PostgreSQL-10.4-1
・Python-3.4.4
・Windows10
・QlikView12（おまけ）

環境構築

まずは、PL/Pythonが利用できる環境を構築していきます。

１．Postgresのインストール

配布先：https://www.enterprisedb.com/software-downloads-postgres/
※設定は、デフォルトのままでインストールしてください。

２．Pythonのインストール

配布先：https://www.python.org/downloads/
※設定は、デフォルトのままでインストールしてください。
インストールバージョンは3.4.4を選択してください。

PATHが通っているか確認

３．PythonのdllをPostgresへコピー

Postgresには、Pythonをストアドプロシージャとして利用できる、インターフェースが用意されていますが
実際の処理を行うには、Pythonのdllが必要になります。
コピー元：(Postgresインストール先)\Python34\Python34.dll
コピー先：(Postgresインストール先)\PostgreSQL\10\lib

４．pgAdminを立ち上げ、ExtensionsにPL/Pythonを追加

pgAdminはPostgresSQLをインストールするとついてくる、データベース管理ツールです。

５．PL/Pythonの動作確認

試験用のストアドプロシージャを作成して、PL/Pythonが利用可能かチェックします。
今回は、pgAdminのQueryToolから実施しました。

①メニューのToolsからQueryToolを起動、SourceCodeを記載して実行します
（F5キーで実行できます）

--SourceCode--
CREATE FUNCTION py_test(x text)
returns text
AS $$
return x
$$ LANGUAGE plPython3u;

②検証用のSourceCodeを実行して、下記の結果が戻ってくれば成功です。

--SourceCode--
select py_test('成功!');

これで準備完了です。

統計情報の取得

今回はe-StatのAPI機能を利用して、政府統計情報から東京の老年人口割合を取得します。

（e-Stat:https://www.e-stat.go.jp/)

■事前準備

１．pipを使って、PythonのAPI通信用モジュールをインストール

コマンドプロンプトからCommandを実行して、インストールします。

--Command--
pip install requests

２．e-Statにログインして、アプリケーションIDを取得

※WebAPIへの連携には、アプリケーションIDが必要になります。
e-Statのアカウントを作成して、アプリケーションIDを発行してください。

３．統計情報格納用のテーブル作成

QueryToolにてSouceCodeを実行して、格納用テーブルを作成してください。

--SourceCode--
CREATE TABLE population_ratio
(
year char(4),
ratio numeric
)

■PL/Pythonの実装

１．ストアドプロシージャを作成

QueryToolにて、統計情報取得用のストアドプロシージャを作成します。

--SourceCode--
CREATE FUNCTION py_create_population_ratio ()
  RETURNS integer
AS $$

    import requests
    import urllib.parse
    import json

    # リクエストurl
    api = "http://api.e-stat.go.jp/rest/2.0/app/json/getStatsData?appId={appid}&statsDataId={statsDataId}&cdCat01={cdCat01}"
    # アプリケーションID
    api_key = "6a886e502671da6536be5abb254d244c56c07185"
    # 統計表ID
    statsdata_id = "C0020050213000"
    # コード
    cd_cat ="#A03503"

    # urlencode
    url = api.format(appid=urllib.parse.quote_plus(api_key, encoding='utf-8'), statsDataId=urllib.parse.quote_plus(statsdata_id, encoding='utf-8'), cdCat01=urllib.parse.quote_plus(cd_cat, encoding='utf-8'))

    # リクエスト
    r = requests.get(url)

    # decode
    data = json.loads(r.text)

    # データ登録
    plpy.execute("truncate table population_ratio", 0)
    for rowdata in data["GET_STATS_DATA"]["STATISTICAL_DATA"]["DATA_INF"]["VALUE"]:
        plpy.execute("insert into population_ratio values('" + rowdata["@time"] + "', " + rowdata["$"] + ")", 0)

    return 0

$$ LANGUAGE plpython3u;

２．動作確認

検証用のSourceCodeを実行して、下記の結果が戻ってくれば成功です！

--SourceCode--
select py_create_population_ratio();
select * from population_ratio;

おまけ（統計情報の利用）

せっかく取得してきた統計情報なので、BIツールであるQlikViewで視覚化してみました。
（QlikViewの使用方法については、こちらのキャスレー技術ブログをご覧ください。）

終わりに

今回ストアドプロシージャを、PL/Pythonで作成してみましたが、下記の効果を感じました。

・直感的に処理が作成できる。（作業短縮になる）
・SourceCodeが読みやすい！
・今回のAPIやファイル操作など、スクリプトの便利な機能がそのまま使える。

以上、最後までご覧頂き、ありがとうございました。

目次

動作環境

環境構築

統計情報の取得

おまけ（統計情報の利用）

終わりに

人気の記事

カテゴリー