はじめまして。
キャスレーコンサルティングLS(リーディング・サービス)部の田村です。
データベースにストアドプロシージャを作成する際、皆さんは何を使っていますか?
私は今まで、PL/pgSQL(PostgresSQL)等のDB固有の言語を利用していましたが、
カーソル等DB固有言語独特の書き方に違和感がありました。
何かないか探していたところ、その他のスクリプト言語でも
直接ストアドプロシージャの作成ができることを知りました。
(詳しくは、こちらの公式マニュアルを参照してください。)
今回は、業務で使用しているスクリプト言語のひとつ、PL/Pythonの使い方をご紹介いたします。
目次
・動作環境
・環境構築
・統計情報の取得
・おまけ
・終わりに
動作環境
・PostgreSQL-10.4-1
・Python-3.4.4
・Windows10
・QlikView12(おまけ)
環境構築
まずは、PL/Pythonが利用できる環境を構築していきます。
1.Postgresのインストール
配布先:https://www.enterprisedb.com/software-downloads-postgres/
※設定は、デフォルトのままでインストールしてください。
2.Pythonのインストール
配布先:https://www.python.org/downloads/
※設定は、デフォルトのままでインストールしてください。
インストールバージョンは3.4.4を選択してください。
PATHが通っているか確認
3.PythonのdllをPostgresへコピー
Postgresには、Pythonをストアドプロシージャとして利用できる、インターフェースが用意されていますが
実際の処理を行うには、Pythonのdllが必要になります。
コピー元:(Postgresインストール先)\Python34\Python34.dll
コピー先:(Postgresインストール先)\PostgreSQL\10\lib
4.pgAdminを立ち上げ、ExtensionsにPL/Pythonを追加
pgAdminはPostgresSQLをインストールするとついてくる、データベース管理ツールです。
5.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/)
■事前準備
1.pipを使って、PythonのAPI通信用モジュールをインストール
コマンドプロンプトからCommandを実行して、インストールします。
--Command-- pip install requests
2.e-Statにログインして、アプリケーションIDを取得
※WebAPIへの連携には、アプリケーションIDが必要になります。
e-Statのアカウントを作成して、アプリケーションIDを発行してください。
3.統計情報格納用のテーブル作成
QueryToolにてSouceCodeを実行して、格納用テーブルを作成してください。
--SourceCode-- CREATE TABLE population_ratio ( year char(4), ratio numeric )
■PL/Pythonの実装
1.ストアドプロシージャを作成
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;
2.動作確認
検証用のSourceCodeを実行して、下記の結果が戻ってくれば成功です!
--SourceCode-- select py_create_population_ratio(); select * from population_ratio;
おまけ(統計情報の利用)
せっかく取得してきた統計情報なので、BIツールであるQlikViewで視覚化してみました。
(QlikViewの使用方法については、こちらのキャスレー技術ブログをご覧ください。)
終わりに
今回ストアドプロシージャを、PL/Pythonで作成してみましたが、下記の効果を感じました。
・直感的に処理が作成できる。(作業短縮になる)
・SourceCodeが読みやすい!
・今回のAPIやファイル操作など、スクリプトの便利な機能がそのまま使える。
以上、最後までご覧頂き、ありがとうございました。