2016年12月24日

オープンデータ＋PostGIS＋Google Maps で観光マップを作ってみた

本エントリは PostgreSQL Advent Calendar 2016 の Day24 のエントリです。昨日は @mazudakz さんの「pg_stats_reporter をしくじった話」でした。読み応えあって面白かった。

さて、先日（と言っても結構前）、地理情報をPostgreSQLで扱う例として、巡回セールスマン問題をPostgreSQLで解きつつGoogle Mapsで可視化するエントリを書きました。
今回は、もう少し進んでPostgreSQLにおける地理情報の検索とGoogle Mapsの動的な可視化を連動させてみましたので、その内容を紹介します。

実現したいことは、
  • 観光に関連する情報をPostgreSQLに取り込んで、
  • Google Mapsで地図上にマッピングして可視化しつつ、
  • 地図上をブラウジングしながら、
  • 興味のある場所があったらそのままGoogle検索に飛ぶ
という仕組みです。

年末年始のお出かけの検討に、または雑談のお供にご活用いただければと思います。

■オープンデータ「国土数値情報　観光資源データ」とは


まず、今回使うデータですが、国土交通省が公開している「国土数値情報」の中から「観光資源」のデータを使います。
このデータは各都道府県が「観光資源」として登録しているデータで、以下のような項目が含まれています。
  • 観光資源_ID
  • 観光資源名
  • 都道府県コード
  • 行政コード
  • 種別名称
  • 所在地住所
  • 観光資源分類コード
  • 観光資源（地理情報）
そのため、これらをうまくPostgreSQLに取り込んでやる必要があります。

このデータは地理情報のデータフォーマットとして広く使われている「シェープファイル（Shape File）」と呼ばれる形式で配布されています。

続きを読む »
Posted by at 0 件のコメント:
Labels: ,

2016年12月1日

Logical Decodingを使ったCDC（Change Data Capture）の実現方法を考えてみる

今年も風物詩である PostgreSQL Advent Calendar の時期がやって参りました。Day1担当のデータマエショリスト @snaga です。
去年もDay1を担当した気がしますが、それはさておき。

余談ですが、今年のAdvent Calendarは
にも参加しております。また、
というのにも（個人的に）チャレンジしていますので、この辺に興味のある方はよろしければどうぞ。

■Logical Decoding?


閑話休題。

皆さんご存知の通り、「Logical Decoding」と呼ばれる機能がPostgreSQL 9.4で導入されました。

PostgreSQLでは「新しい機能入ったらしいが一体何にどう使えばいいんだ？」というような機能が稀によくあります。そのため、2年前にリリースされた機能にも関わらず誰かが使っているという話を聞いたことがない、といったことが起こります。

Logical Decodingにもその空気を感じます。

続きを読む »
Posted by at 0 件のコメント:
Labels: ,

2016年11月6日

Jupyter NotebookからPostgreSQLに接続してデータを可視化する

最近、なんだかんだとデータに触る機会が増えてきております。

Unix系エンジニア兼DBAとしては、CLI（コマンドラインインターフェース）が生産性が高くて好きだけど、一方で可視化もお手軽にやりたい、というケースが多々あります。

Jupyter Notebookでデータベースに接続して可視化できる、という話は以前から聞いたことがあったのですが、実際に試してみたことがありませんでした。

今回、軽くPostgreSQLで試してみたのでその手順を簡単にご紹介します。

■セットアップ


以下の3つのモジュールをpipでインストールします。
  • jupyter
  • psycopg2
  • ipython-sql
続きを読む »
Posted by at 0 件のコメント:
Labels:

2016年10月1日

PostgreSQL 9.5日本語マニュアルの検索システムをリリースしました（追記あり）

PostgreSQL 9.5の日本語マニュアルの検索システムをリリースしたので、ご紹介します。
少し前からPostgreSQLのマニュアルを細かく調べる必要性が出てきたのですが、ご存じの通り、PostgreSQLのオンラインのマニュアルはGoogleと相性が良くありません。

本当はgrep -cでもいいくらいの機能なのですが、公開されているフォーマットがHTML、マニュアルのソースファイルはSGMLファイルなので、実際にそのままgrepしても、見栄え的にあまり嬉しくありません。

そのため、自分の開発の練習もかねてWebアプリとして作ってみました。

■マニュアル検索システムの機能


検索システムのURLは以下です。
検索対象となるのは、日本PostgreSQLユーザ会が翻訳して以下で公開しているPostgreSQL 9.5の日本語マニュアルで、リリースノートと索引を除いたページです。
続きを読む »
Posted by at 0 件のコメント:
Labels: ,

2016年9月22日

MADlib 1.9.1 Release (GA)がリリースされました

このブログでも何度か紹介しているPostgreSQLのデータベース内で機械学習の処理を行えるApache MADlibですが、1.9.1 GAがリリースされました。
前のリリース1.9からの変更点は、以下のようになっています。
  • New function: One class SVM
  • SVM: Added functionality to assign weights to each class, simplying classification of unbalanced data.
  • New function: Prediction metrics
  • New function: Sessionization
  • New function: Pivot
  • Path: Major performance improvement
  • Path: Add support for overlapping patterns
  • Build: Add support for PG 9.5 and 9.6
  • PGXN: Update PostgreSQL Extension Network to latest release
私の送ったパッチも取り込まれて、無事に最新のPostgreSQL 9.5と、パラレルクエリを実装した次期バージョンである9.6でも動作するようになりました。

興味のある方は、ぜひ試してみていただければと思います。

続きを読む »
Posted by at 0 件のコメント:
Labels: ,

2016年9月17日

巡回セールスマン問題における最短経路をpgRoutingで探索する

先日、PostgreSQLアンカンファレンスを開催した際、「pgRoutingを使って巡回セールスマン問題を解く」という発表を国府田さんがされていました。
非常に面白そうな機能で、私も少し使ってみましたので、今回はその使い方や使用例などを含めてご紹介します。

■「巡回セールスマン問題」とは何か


「巡回セールスマン問題」というのは、以下のようなものです。
巡回セールスマン問題（じゅんかいセールスマンもんだい、英: traveling salesman problem、TSP）は、都市の集合と各2都市間の移動コスト（たとえば距離）が与えられたとき、全ての都市をちょうど一度ずつ巡り出発地に戻る巡回路の総移動コストが最小のものを求める（セールスマンが所定の複数の都市を1回だけ巡回する場合の最短経路を求める）組合せ最適化問題である。
巡回セールスマン問題 - Wikipedia
簡単に言うと、「セールスマンが何か所か回る時、回る場所が増えれば増えるほど、可能性のある経路の候補が爆発的に増えていくので、最短経路を導き出すのが困難になる」ということです。回る場所の数「n」に対して、計算量はその階乗「n!」のオーダーとなります。

続きを読む »
Posted by at 0 件のコメント:
Labels: ,

2016年9月4日

9月10日（土）に第8回PostgreSQLアンカンファレンスを開催します

開催まであと1週間を切りましたが、9/10にPostgreSQLアンカンファレンスを開催します。多分、8回目くらいだと思います。
いつもの通り、プログラムやタイムテーブルは当日集まってから募集して調整します。

PostgreSQLに興味があって、いろんな技術レベルの人が集まっていますので、初めての方もお気軽にご参加ください。

いつもオープニングの時に聞いているのですが、参加者のうち、だいたい2/3くらいは初めて参加の方っぽいですので。

では、来週末お会いしましょう。
Posted by at 0 件のコメント:
Labels: ,

2016年8月4日

【翻訳】 On Uber’s Choice of Databases （データベースにおけるUberの選択について）

数日前、Uberのブログで「Why Uber Engineering Switched from Postgres to MySQL」というエントリが公開されました。
それに対して、PostgreSQLコミュニティ界隈でもいろいろなブログエントリが公開されました。
今回は、そのエントリの中でも、「Use The Index, Luke!」でおなじみのMarkus Winand氏のエントリ「On Uber’s Choice of Databases」が個人的に興味深かったので、同氏の翻訳許可をいただきまして、ここに対訳形式で公開します。

なお、当然ですが翻訳に際しての文責は翻訳者である永安にありますので、問題を見つけた場合にはコメント欄またはTwitter (@snaga)などで連絡いただけますと幸いです。

では、どうぞ。

■On Uber’s Choice of Databases （データベースにおけるUberの選択について）


On 7-29-2016
By Markus Winand

続きを読む »
Posted by at 2 件のコメント:
Labels: , ,

2016年7月19日

TF-IDFでデータベース内の類似テキストを検索する Part 4 （MADlib svec編）

TF-IDF 感動巨編3部作は前回のエントリで完結したわけですが、今回はその番外編、スピンオフとして「MADlib svec編」をお送りします。

MADlib には、sparse（疎）な配列、つまり多くの要素がゼロであるような配列を扱うデータ型として svec というデータ型があります。
本エントリでは、TF-IDF のベクトルに MADlib の svec を使って、通常の float8[] などとどのように違うのかを見てみます。

■「MADlib」とは何か


MADlib については、ガッツリと割愛します。以前のエントリで詳しくご紹介しましたので、そちらを参照してください。

■「svec」 とは何か


svec は、ゼロの多い sparse な配列を圧縮して保持するデータ型です。データ分析をしていると、頻繁に遭遇するデータの構造になります。

例えば、float8 の配列で以下のようにゼロが並ぶデータがあったとします。 
'{0, 33,...40,000個のゼロ..., 12, 22 }'::float8[]
続きを読む »
Posted by at 0 件のコメント:
Labels: , ,

2016年7月13日

TF-IDFでデータベース内の類似テキストを検索する Part 3 （性能改善編）

PostgreSQL 感動巨編 TF-IDF 3部作の最終回、「性能改善編」です。 前回の最後で、今回作成した UDF である euclidean_distance の処理に時間がかかってそうだ、ということが分かってきました。

そのため、本エントリではこの UDF をもう少し詳細に見ながら、パフォーマンスを改善する方法を探ります。

■euclidean_distanceの性能分析


処理時間がかかっていることが分かった euclidean_distance 関数ですが、改めて処理を詳細に見ていきます。 
CREATE OR REPLACE FUNCTION euclidean_distance(tfidf_a jsonb, tfidf_b jsonb)
  RETURNS float8
AS $$
    from sklearn.metrics.pairwise import euclidean_distances
    import json

    aa = json.loads(tfidf_a)
    bb = json.loads(tfidf_b)
    w = list(set(aa.keys()).union(set(bb.keys())))
    vec_a = []
    vec_b = []
    for t in w:
        if t in aa:
            vec_a.append(aa[t])
        else:
            vec_a.append(0)
        if t in bb:
            vec_b.append(bb[t])
        else:
            vec_b.append(0)
    distance = euclidean_distances([vec_a], [vec_b])
    return distance[0][0]
$$
LANGUAGE 'plpython2u';
まず、ボトルネックの確認のため、少しずつこの関数のコードをコメントアウトして実行時間を比較してみると、

続きを読む »
Posted by at 0 件のコメント:
Labels: , , ,

2016年7月12日

TF-IDFでデータベース内の類似テキストを検索する Part 2 （実践編）

前回の TF-IDF Part 1 の続きです。
今回は、現実的なドキュメントをPostgreSQLに格納して TF-IDF の類似度に基づく検索をしてみます。

■検索対象とするドキュメント


今回題材として使うドキュメント（コーパス）はPostgreSQLの日本語マニュアルです。
PostgreSQLのマニュアルは、かなりしっかりした内容が書かれている反面、ボリュームが多い、どこに書いてあるのか分からない、そもそも分かっていない人には分かりづらい、などの指摘がされることがあります。

そういう文書を読むときに、「関連するページ」を自動的にピックアップすることができれば、より深い理解や周辺の理解につながるのではないか、というのがもともとのモチベーションです。

■PostgreSQL内にコーパスを作成する


まず、PostgreSQLのマニュアルを保存するテーブルを作成します。スキーマは以下の通りです。 
CREATE TABLE pgsql_doc (
  docid SERIAL PRIMARY KEY,
  filename TEXT NOT NULL,
  html TEXT NOT NULL,
  plain TEXT,
  tf JSONB,
  tfidf JSONB
);
続きを読む »
Posted by at 0 件のコメント:
Labels: , ,

2016年7月11日

TF-IDFでデータベース内の類似テキストを検索する Part 1 （基本機能編）

最近、「TF-IDF」と呼ばれる手法を使ってPostgreSQL内に保存されたテキストの類似度を計算して、似ているテキストを検索する方法を試していました。

一通り目途が立った気がしてきましたので、今回から3回に渡ってその方法をご紹介します。
  • Part 1: 基本機能編
  • Part 2: 実践編
  • Part 3: 性能改善編
Part 1 は基本機能編ということで、TF-IDF に基づく類似性検索を PostgreSQL 内部で実装する方法をご紹介します。

Part 2 は実践編として、大量のドキュメントをPostgreSQLに格納して、TF-IDF を計算して検索するまでを解説します。

Part 3 は性能改善編ということで、検索性能を改善する方法を検討します。

■「TF-IDF」とは


TF-IDFは、自然言語処理で使われるアルゴリズムで、文書やコーパス（文書群）の中における単語の出現頻度を用いて、文書の単語に重み付けをする手法です。
TF-IDFでは、「Term Frequency」と呼ばれる「単一の文書中における単語の出現頻度」と、「Inverse Document Frequency」と呼ばれる「コーパス全体における単語の出現頻度（の逆数）」を組み合わせて、単語に重み付けをします。
  • TF: 特定の単語の出現回数 / 文書全体の単語数
  • IDF: log(全文書数 / 単語を含んでいる文書数) + 1
として定義され、これらを組み合わせて「TF-IDF」が「TF * IDF」として計算されます。

このTF-IDFを用いて、文書中に出現する個々の単語に対して重み付けがされることになります。

続きを読む »
Posted by at 0 件のコメント:
Labels: , , ,

2016年7月5日

パラレル処理可能な集約関数をPL/Pythonで作成する

先日、次期メジャーバージョンの9.6のbeta2がリリースされました。
9.6では、集約関数やJOINなどもパラレルクエリに対応しており、パラレル処理されるようになっていますので、みなさんはもちろんパラレルクエリをゴリゴリと検証されている最中かと思います。

また、言うまでもないことですが、パラレルクエリはデータ分析のためにあり、データ分析といえばPythonなわけです。

本エントリでは、そんなパラレルクエリとデータ分析大好きな方たちに向けて、パラレル処理が可能な集約関数をPL/Pythonで作成する方法を紹介します。

前提としているバージョンは、PostgreSQL 9.6 beta2 です。

■PL/Pythonでの集約関数の作成


パラレルクエリに関係のない通常の集約関数をPL/Pythonで作成する方法については以前書きましたので、以下のエントリを参照してください。
本エントリは、この内容を理解していることが前提となります。

■パラレル対応の集約関数とは？


では、パラレルクエリでパラレル実行可能な集約関数の作り方を見てみましょう。

まずは、パラレルクエリに対応した集約関数がどのように作成されているのかを見てみます。

続きを読む »
Posted by at 0 件のコメント:
Labels: , ,

2016年6月28日

「10 Reasons to Start Your Analytics Project with PostgreSQL（アナリティクスをPostgreSQLで始めるべき10の理由）」のスライド公開しました

先週末、香港で開催された HKOSCon 2016 でのセッション「10 Reasons to Start Your Analytics Project with PostgreSQL（アナリティクスをPostgreSQLで始めるべき10の理由）」のスライドを公開しました。


目次は以下のような感じです。

続きを読む »
Posted by at 0 件のコメント:
Labels: ,

2016年4月23日

形態素解析を使ってPostgreSQLに保存された文章データから話題を抽出する

PythonやPL/Python、PostgreSQLを使ってデータ分析をIn-Database処理させるのがマイブームです。

今回は、データベース内に保存された文章のテキストデータから単語の出現頻度を使って話題になっているトピックを抽出する、という処理を行ってみます。
  • テキストを形態素解析する
  • 形態素解析した結果をJSONBで取得する
  • JSONBデータを対象に集計処理を行う
  • 上記すべてをサーバサイドで実行する
といったことをPostgreSQLを使って処理してみます。

■データの準備


今回も東京カレンダーの「東京女子図鑑」からの文章をサンプルとして使ってみます。
今回は、docidという主キーとテキストを値としてdoctextカラムに持つテーブルを作成し、そこにテキストを保存しておくようにします。今回のテキストは約4,000文字あります。
snaga=# \d docs
      Table "public.docs"
 Column  |  Type   | Modifiers
---------+---------+-----------
 docid   | integer | not null
 doctext | text    |
Indexes:
    "docs_pkey" PRIMARY KEY, btree (docid)

snaga=# SELECT docid, length(doctext) FROM docs;
 docid | length
-------+--------
     1 |   4423
(1 row)

snaga=# SELECT docid, substring(doctext,0,60) AS doctext FROM docs;
 docid |                                                       doctext
-------+---------------------------------------------------------------------------------------------------------------------
     1 | 20代後半頃から、同期が1人また１人と、会社を辞めていきました。辞める理由はいろいろありますが、病んでしまった子もいれ
(1 row)

snaga=#

■形態素解析を行うユーザ定義関数を作成する


まず、テキストを入力として受け取り、形態素解析した結果をJSONB型として返却するユーザ定義関数を作成します。

続きを読む »
Posted by at 0 件のコメント:
Labels: , , ,

2016年4月21日

5月28日（土）にPostgreSQLアンカンファレンスを開催します

5月28日（土）にPostgreSQLアンカンファレンスを開催いたします。
PostgreSQL関連何でもアリのごった煮感溢れるカジュアルな感じのイベントですので、PostgreSQLに興味のある方はぜひご参加いただければと思います。

技術的な知見だけではなく、コミュニティの人々と知り合いになるチャンスでもあります。お申込みは上記ATNDからどうぞ。

では。
Posted by at 0 件のコメント:
Labels: ,

2016年4月10日

データ分析用ライブラリ MADlib を使って PostgreSQL で機械学習する

MADlibは、現代的なデータ分析には欠かせない回帰分析やデータマイニングのアルゴリズムが実装されているオープンソースのライブラリです。

MADlibを導入することによって、これらのアルゴリズムをPostgreSQLのユーザ定義関数の形で使うことのでき、データベースサーバの内部でデータ分析の処理できるようになります。

今回は、このMADlibの導入方法から動作確認、ロジスティック回帰分析における簡単な使い方までをご紹介します。

■MADlibとは何か


MADlibは、もともとはGreenplumというPostgreSQLをベースにしたMPP製品（DWH用RDBMS）を開発していた企業が開発していたライブラリで、Greenplumで利用できるように開発されていたものでした。

2015年9月に、Greenplum（を買収したEMC）がMADlib（や他のソフトウェア類）をApache Foundationに寄贈し、MADlib は Apache Incubator のプロジェクトとなりました。

そして、4月6日に Apache Incubator のプロジェクトになって最初の GA(Generally Available) リリースである1.9がリリースされました。
続きを読む »
Posted by at 0 件のコメント:
Labels: ,

2016年3月6日

パラレルスキャンのスケーラビリティ調査とFlame Graphsによるプロファイリング可視化

先月、弊社にデータベース系の研究をしていた中国人留学生がインターンに来ており、その彼にお願いしてPostgreSQLのパラレルクエリのスケーラビリティの調査と、プロファイリング＋可視化のツールとしてFlameGraphを使ってもらいました。

大学のスケジュールの関係上、インターンの期間が急遽、3週間から2週間に短縮されてしまったため、結果をきちんとまとめたり追試をしたりといったところまでは到達できなかったのですが、個人的にもそれなりに面白いアウトプットになったと思いますので、簡単にご紹介したいと思います。

なお、細かい手順の詳細などは、インターンに参加していた学生さんのGithubにまとまっています。参考文献に載せておきますので、興味のある方はそちらも参照してください。（本テストと直接関係のない内容も含まれています）

■テストの背景


PostgreSQLの9.6develにパラレルシーケンシャルスキャンが実装されたのは、みなさんご承知のことと思います。
続きを読む »
Posted by at 0 件のコメント:
Labels: , , ,

2016年1月21日

[翻訳] PostgreSQLの過去、現在、未来: ゴールポストを動かし続けて（Robert Haas）

PostgreSQL 9.5が正式リリースされて少し経ちましたが、みなさん既に試されたでしょうか？

PostgreSQLの主要な開発者のひとりに Robert Haas 氏がいます。Robert Haas 氏は EnterpriseDB社で働いていますが、今、PostgreSQL開発コミュニティの中で、もっともアグレッシブに開発している一人であり、開発コミュニティの中でも非常に大きな影響力と信頼を勝ち得ている一人であると言えるでしょう。

また、Robert Haas 氏は、2012年には日本のPostgreSQLカンファレンスで基調講演を行ったこともあります。
そのRobert Haas氏が9.5のリリース直後に、「これまでのPostgreSQLの5年間を振り返り、これからの5年間を考える」というブログエントリを書いていました。
そのエントリが非常に興味深く、日本語でも紹介したいと本人にお願いしたところ翻訳掲載の許可をもらえましたので、本エントリではその全文をご紹介します。

PostgreSQLの主要な開発者が今何を考えているのだろうか。PostgreSQLの今後をどう考えているのだろうか。開発者の頭の中をちょっと覗いてみよう、といった趣で、PostgreSQLの今後を考える参考にしていただければと思います。

文中のリンクは原文のままになっています。

それではどうぞ。

続きを読む »
Posted by at 0 件のコメント:
Labels: , ,

2016年1月10日

PL/Pythonで独自の集約関数を作成する

先月、PostgreSQLアンカンファレンスで「PL/Pythonで独自の集約関数を作ってみる」という発表をしました。

本エントリでは、その詳細について紹介させていただきます。

■なぜPL/Pythonで集約関数なのか？


既に広く知られている通り、PostgreSQLではさまざまなプログラミング言語でプロシージャ／ファンクションをユーザが定義することができます。

では、なぜPL/Pythonで集約関数なのでしょうか？

まず1つ目の理由としては、Pythonとデータ処理が非常に相性が良い、ということが挙げられます。古くから統計解析でRが使われてエコシステムが発展してきたのと同じように、最近ではPythonを取り巻くデータ分析に関するエコシステムが大きくなり、データ分析に関するさまざまな処理をPythonで行えるようになってきています。

2つ目の理由としては、データ処理と集約関数というのは切っても切れない関係にあるということです。シンプルな COUNT() や AVG() から、最近ではウィンドウ関数まで、集約関数にはさまざまな処理があり、データベースにおけるデータ分析に集約関数は欠かせません。

そして、最後の理由としては、集約関数は「インデータベース（In-Database）処理」である、ということです。「インデータベース処理」というのは、データベースからデータを取り出してクライアントサイドで処理させるのではなく、データベースの内部でデータ処理をさせることを言います。こうすることによって、データの転送にかかる時間を削減したり、よりスペックの高いサーバ側で大量の処理をさせることが可能になります。

これらの理由から、これからPL/Pythonを使って独自の集約関数を作成できる意義はより高まっていくでしょう。

■PostgreSQLにおける集約関数の構造


次に、PostgreSQLの集約関数の構造を見てみましょう。

以下は、基本的なPostgreSQLの集約関数の構造および動作です。

続きを読む »
Posted by at 0 件のコメント:
Labels: ,
登録: 投稿 (Atom)