それPostgreSQLでできるよ @ Rails Developer Meetup 2018 Day 1

それPostgreSQLでできるよ @ Rails Developer Meetup 2018 Day 1

Ceda558ac47bd7af518c12a51614664a?s=128

Fujimura Daisuke

March 24, 2018
Tweet

Transcript

  1. それPostgreSQL でできるよ @Rails Developer Meetup 2018 Day 1 Daisuke Fujimura

    2018-03-24
  2. この発表について PostgreSQLには便利な機能がたくさんあります。業務の中で「これできないかな?」 と思って調べると関数や拡張が用意されていた、ということも多々ありました。 ActiveRecordおよび標準SQLを使って仕事をしていると見えてこない「レールの外」 のPostgreSQLの世界をご紹介しようと思います。 PostgreSQLの便利な機能をそれぞれユースケースを交えて解説します。 原則、AWS RDS、Google Cloud SQL

    for PosterSQLで使える機能のみ紹介します。 利用したPostgreSQLのバージョンは10.1、PostGISのバージョンは2.4.3です。 発表で登場するコード例は https://github.com/fujimura/railsdm_2018_postgresql にありま す。
  3. 自己紹介 藤村大介 ご近所SNSマチマチを運営する株式会社マチマチのCTO。Railsは2.2位から仕事で使って いる。フロントエンド開発も得意。 twitter.com/ffu_ fujimuradaisuke.com SQL歴は足掛け10年程度。PostgreSQL歴は4年程度。内部構造の詳しい知識などはあり ません。

  4. マチマチについて(1 )

  5. マチマチについて(2 ) チーム @fujimura @knu @imaz(フリーランス) テクノロジー バックエンド:Rails, PostgreSQL フロントエンド:React,

    Flowtype ネイティブアプリ:これからReact Nativeで作る エンジニア募集中です!藤村まで気軽にお声かけ下さい。
  6. Window 関数(1 ) 最近MySQLにも入ったWindow関数ですが皆さん使っていますか?データ分析ではよく 使いますが、アプリケーション開発ではあまり使わないかもしれません。

  7. Window 関数(1 )やりたいこと このテーブルの各行のposition を重複なしタイムスタンプ順で振りなおしたい name | position | updated_at

    ­­­­­­+­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­ A | 1 | 2018­03­21 22:17:49.215978 B | 2 | 2018­03­21 22:17:49.215978 F | 3 | 2018­03­23 10:17:49.220138 C | 3 | 2018­03­21 22:17:49.215978 D | 4 | 2018­03­21 22:17:49.215978 E | 5 | 2018­03­21 22:17:49.215978
  8. Window 関数(1 )そもそもWindow 関数とは Window関数は「現在の行」の情報を使った値を計算する機能です。例えば ROW_NUMBER を使うと、特定のカラムでソートした際の現在の行番号がわかります。 例えばさきほどのテーブルにこのクエリを実行すると position 、updated_at

    の順にソートした場合の行番号が取得できます。 name | position | i | updated_at ­­­­­­+­­­­­­­­­­+­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­ A | 1 | 1 | 2018­03­21 22:17:49.215978 B | 2 | 2 | 2018­03­21 22:17:49.215978 F | 3 | 3 | 2018­03­21 22:17:49.215978 C | 3 | 4 | 2018­03­21 22:17:49.215978 D | 4 | 5 | 2018­03­21 22:17:49.215978 E | 5 | 6 | 2018­03­21 22:17:49.215978 SELECT name, position, ROW_NUMBER() OVER ( ORDER BY position, updated_at DESC) AS i FROM categories;
  9. Window 関数(1 )position を振り直す 実際にposition を振り直すには、下記のSQLを実行します。新たにposition を振っ た表をWindow関数を使って用意して、それを元のテーブルと結合してUPDATEしてい ます。 name

    | position | updated_at ­­­­­­+­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­ A | 1 | 2018­03­21 22:17:49.215978 B | 2 | 2018­03­21 22:17:49.215978 F | 3 | 2018­03­23 10:17:49.220138 C | 4 | 2018­03­21 22:17:49.215978 D | 5 | 2018­03­21 22:17:49.215978 E | 6 | 2018­03­21 22:17:49.215978 UPDATE categories SET POSITION = c.row_number FROM (SELECT name, ROW_NUMBER() OVER (ORDER BY POSITION, updated_at DESC) AS row_number FROM categories) AS c WHERE c.name = categories.name;
  10. Window 関数(2) やりたいこと アクセスログから30分以内の連続したアクセスを一つのセッションとして、セッション の一覧を出したい。

  11. Window 関数(2) アクセスログのデータ ユーザーIDとしてid 、タイムスタンプとしてtime を持った簡単なログテーブルがある とします。 id | time

    ­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­ 1 | 2018­03­10 06:14:06.265533 1 | 2018­03­10 07:04:06.265533 2 | 2018­03­10 07:31:06.265533 1 | 2018­03­10 07:35:06.265533 2 | 2018­03­10 08:02:06.265533 2 | 2018­03­10 08:03:06.265533 2 | 2018­03­10 08:04:06.265533 1 | 2018­03­10 08:05:06.265533 2 | 2018­03­10 08:14:06.265533 1 | 2018­03­10 08:14:06.265533
  12. Window 関数(2) 現在の行と前の行の差を取得 LAG で前の行が取得できます。この例ではid ごとに前の行のtime を取得、現在の行の time との差を求めています。 id

    | current_row | previous_row | difference ­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­ 1 | 2018­03­10 06:14:06.265533 | ¤ | ¤ 1 | 2018­03­10 07:04:06.265533 | 2018­03­10 06:14:06.265533 | 00:00:50 1 | 2018­03­10 07:35:06.265533 | 2018­03­10 07:04:06.265533 | 00:00:31 1 | 2018­03­10 08:05:06.265533 | 2018­03­10 07:35:06.265533 | 00:00:30 1 | 2018­03­10 08:14:06.265533 | 2018­03­10 08:05:06.265533 | 00:00:09 2 | 2018­03­10 07:31:06.265533 | ¤ | ¤ 2 | 2018­03­10 08:02:06.265533 | 2018­03­10 07:31:06.265533 | 00:00:31 2 | 2018­03­10 08:03:06.265533 | 2018­03­10 08:02:06.265533 | 00:00:01 2 | 2018­03­10 08:04:06.265533 | 2018­03­10 08:03:06.265533 | 00:00:01 2 | 2018­03­10 08:14:06.265533 | 2018­03­10 08:04:06.265533 | 00:00:10 SELECT id, time as current_row, LAG(time) OVER (PARTITION BY id ORDER BY time) as previous_row, (time ­ (LAG(time) OVER (PARTITION BY id ORDER BY time))) / 60 as difference FROM access_logs ORDER BY id, current_row ASC
  13. Window 関数(2) セッション一覧 さきほどのSQLより、前回との差がNULL(初回アクセス)、前回との差が30分以上 (新しいセッション)の行を取得すると、セッション一覧になります。 SELECT id, s.time as timestamp

    FROM ( SELECT CASE WHEN LAG(time) OVER (PARTITION BY id ORDER BY time) IS NULL THEN time WHEN EXTRACT(epoch FROM time ­ (LAG(time) OVER (PARTITION BY id ORDER BY time))) > 60 * 30 THEN time ELSE NULL END AS time, id FROM access_logs ORDER BY time ) as s WHERE time IS NOT NULL order by timestamp, id
  14. Window 関数(2) セッション一覧 id | current_row | previous_row | difference

    ­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­ 1 | 2018­03­10 06:14:06.265533 | ¤ | ¤ 1 | 2018­03­10 07:04:06.265533 | 2018­03­10 06:14:06.265533 | 00:00:50 1 | 2018­03­10 07:35:06.265533 | 2018­03­10 07:04:06.265533 | 00:00:31 1 | 2018­03­10 08:05:06.265533 | 2018­03­10 07:35:06.265533 | 00:00:30 1 | 2018­03­10 08:14:06.265533 | 2018­03­10 08:05:06.265533 | 00:00:09 2 | 2018­03­10 07:31:06.265533 | ¤ | ¤ 2 | 2018­03­10 08:02:06.265533 | 2018­03­10 07:31:06.265533 | 00:00:31 2 | 2018­03­10 08:03:06.265533 | 2018­03­10 08:02:06.265533 | 00:00:01 2 | 2018­03­10 08:04:06.265533 | 2018­03­10 08:03:06.265533 | 00:00:01 2 | 2018­03­10 08:14:06.265533 | 2018­03­10 08:04:06.265533 | 00:00:10 id | timestamp ­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­ 1 | 2018­03­10 06:14:06.265533 1 | 2018­03­10 07:04:06.265533 2 | 2018­03­10 07:31:06.265533 1 | 2018­03­10 07:35:06.265533 2 | 2018­03­10 08:02:06.265533
  15. Window 関数 Rails では? ActiveRecord::Connection.execute ActiveRecord::Querying#find_by_sql ActiveRecord::QueryMethods#select

  16. Trigger PostgreSQLではTriggerという仕組みを使って、行への操作(挿入・更新・削除)があっ た時に特定の関数を実行することができます。

  17. Trigger - やりたいこと テーブルの変更履歴をアプリケーション側ではなくデータベース側のみで自動で記録し たい。

  18. Trigger - とは? 公式ドキュメントによると、 CREATE TRIGGERは新しいトリガを作成します。 作成したトリガは指定したテーブ ルまたはビューと関連付けられ、特定のイベントが発生した時に指定した関数 function_nameを実行します https://www.postgresql.jp/document/10/html/sql-createtrigger.html

    とのこと。 関数はPL/pgSQLという手続き型プログラミング言語で記述し、コードの中にSQLを書い て行の操作をすることができます。
  19. Trigger - レコードの変更をJSON で保存するトリ ガーの関数を定義する 下記のトリガーでレコードに更新があった際にchanges というテーブルに変更を保存 します。変更前、変更後の行をOLD とNEW で参照することができるので、それを

    row_to_json でJSONに変換して保存しています。 CREATE OR REPLACE FUNCTION audit_changes() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF (TG_OP = 'UPDATE') THEN INSERT INTO changes (table_name, operation, old_content, new_content, created_at) VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), now()); RETURN NEW; END IF; END; $$;
  20. Trigger - トリガーをテーブルに適用する CREATE TRIGGER でトリガーをテーブルに適用します。 CREATE TRIGGER audit_items_changes BEFORE

    UPDATE ON items FOR EACH ROW EXECUTE PROCEDURE audit_changes();
  21. Trigger - 実行結果 このテーブルを name | type ­­­­­­­­­­­+­­­­­­­ Apple |

    Fruit Wine | Drink Beer | Drink Chocolate | Food 変更すると UPDATE items SET name = 'Orange' WHERE name = 'Apple'; 履歴が保存されます。 table_name | operation | old_content | new_content | created_at ­­­­­­­­­­­­+­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­ items | UPDATE | {"name": "Apple", | {"name": "Orange", | 2018­03­10 09:27:05.213614 | | "type": "Fruit"} | "type": "Fruit"} |
  22. Trigger - Rails では? マイグレーション内でActiveRecord::Connection.execute

  23. Materialized view 要はキャッシュされたビューです。

  24. Materialized view - やりたいこと 下記のように、「ワイン」テーブルと「ビール」テーブルがあるとします。これを「飲 み物」テーブルとして横断して検索したくなりました。 CREATE EXTENSION pgcrypto; ­­

    gen_random_uuid﴾﴿ のために必要 CREATE TABLE wines ( id uuid DEFAULT gen_random_uuid() NOT NULL, name varchar, color varchar, price integer ); CREATE TABLE beers ( id uuid DEFAULT gen_random_uuid() NOT NULL, name varchar, type varchar, price integer );
  25. Materialized view - そもそもビューとは? ビューはSELECT文を保存してあたかもテーブルかのように扱える機能です。

  26. Materialized view - とは? 通常のビューは毎回その定義のSELECT文を実行します。マテリアライズド・ビューは 通常のビューとは違い、SELECT文の結果が保存されます。 結果が保存されているので通常のビューよりも多くの場合高速ですが、データの更新は 手動で行う必要があります。また、インデックスを貼ることも可能です。

  27. Materialized view - テーブル定義 「ワイン」テーブルと「ビール」テーブルをまとめた、「飲み物」マテリアライズド・ ビューを定義してみましょう。

  28. Materialized view - テーブル定義(おさらい) 改めて先程の「ワイン」テーブルと「ビール」テーブルの定義です。 CREATE TABLE wines ( id

    uuid DEFAULT gen_random_uuid() NOT NULL, name varchar, color varchar, price integer ); CREATE TABLE beers ( id uuid DEFAULT gen_random_uuid() NOT NULL, name varchar, type varchar, price integer );
  29. Materialized view - マテリアライズド・ビュー の定義 「ワイン」テーブルと「ビール」テーブルの型を揃えてUNION したものを「飲み物」ビ ューとして定義します。 beverages.id の型はUUIDなので重複がありません。なのでユニークインデックスを

    貼ることができます。 CREATE MATERIALIZED VIEW beverages AS SELECT w.id AS id, w.name AS name, w.price AS price, 'wines' AS type FROM wines AS w UNION ALL SELECT b.id AS id, b.name AS name, b.price AS price, 'beers' AS type FROM beers AS b ; CREATE UNIQUE INDEX index_beverages_id ON beverages USING btree (id);
  30. Materialized view - データ投入、更新 データを投入した後、REFRESH MATERIALIZED VIEW でデータを更新します。ユニー クインデックスがあるとCONCURRENTLY オプションを使うことができます。これを指

    定すると更新中のビューへの読み込みロックが回避できます。 INSERT INTO wines (name, color, price) VALUES ('Rotten Highway', 'White', 10000), ('yellow tail Chardonnay', 'White', 1000) ; INSERT INTO beers (name, type, price) VALUES ('Old Rasputin', 'Imperial Stout', 1300), ('Ichiban Shibori', 'Lager', 300) ; REFRESH MATERIALIZED VIEW CONCURRENTLY beverages;
  31. Materialized view - 飲み物ビューを検索 マテリアライズド・ビューを使って1000円以下の「ビール」と「ワイン」を一度に検索 することができました id | name |

    price | type ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­+­­­­­­­ 1f17edd2­f3df­4cd7­b72e­faff06df6130 | Ichiban Shibori | 300 | beers 73afb8eb­9f6f­4e1c­a3ef­5f4ee36ff3af | yellow tail Chardonnay | 1000 | wines SELECT * FROM beverages WHERE price <= 1000;
  32. Materialized view - Rails では? https://github.com/thoughtbot/scenic でマイグレーションとして管理できます。とても 便利!

  33. Materialized view - 補足 ディスクスペースが必要になる、リフレッシュが必要、リフレッシュのコストがかか るというデメリットがあります。詳しくは https://www.slideshare.net/SoudaiSone/postgre-sql-54919575 を参照ください。 個人的には 1)

    リフレッシュが遅れても問題なく 2) 更新頻度が低い 場合のみマテリアライズ ド・ビュー使用可としています。 ソースとなるテーブルの主キー(id )をRailsでよくあるincremental idにするとビュー で主キー(的なもの)が作れません。 キーが衝突するのでユニークインデックスが貼れないのでリフレッシュ時に CONCURRENTLY オプションが使えず、更新中にソースとなっているテーブルにロックがかか ってしまいます。 id をUUIDにするとこれを回避できます。 ちなみにRails 5からActiveRecordでprimary keyをUUIDにできるようになりました。詳しく http://blog.bigbinary.com/2016/04/04/rails-5-provides-application-config-to-use-UUID-as-primary-key.html を 参照ください。
  34. PostGIS

  35. PostGIS - やりたいこと 東京都の近くに公園が多い駅ランキングを出したい。

  36. PostGIS - って何? 地理空間情報を扱うための拡張です。そもそも地理空間情報って何?という問いについ ては、これからお話しする実例を通してお答えできればと思います。 PostGISが具体的に提供する機能としては、1) ジオメトリカラムの定義 2) ジオメトリ関 数

    の2つです。 ジオメトリは点、線、面など、空間上を占める何らかの情報のことです。
  37. PostGIS - 例 駅のテーブル(stations 、定義はのちほど)を使って、代々木公園駅から距離が近い 駅ランキングを出してみましょう。 下記のようにST_Distance_Sphere で二点間の 距離を出すことができます。 +­­­­­­­­­­­­+­­­­­­­­­­­­+­­­­­­­­­­­­+

    | name | name | distance | |­­­­­­­­­­­­+­­­­­­­­­­­­+­­­­­­­­­­­­| | 代々木公園 | 代々木上原 | 0.00894604 | | 代々木公園 | 参宮橋 | 0.0104074 | | 代々木公園 | 駒場東大前 | 0.0115478 | | 代々木公園 | 初台 | 0.0123519 | | 代々木公園 | 神泉 | 0.0127556 | +­­­­­­­­­­­­+­­­­­­­­­­­­+­­­­­­­­­­­­+ SELECT s1.name, s2.name, ST_Distance_Sphere(s1.geom, s2.geom) AS distance FROM stations s1 CROSS JOIN stations s2 WHERE s1.name = ' 代々木公園' AND s1.name <> s2.name ORDER BY distance LIMIT 5;
  38. PostGIS - 公園が多い駅ランキングを出すにあた っての戦略 「駅の最寄りエリア」の面を作って、その中にある公園を数えるという方向で挑みま す。

  39. PostGIS - テーブル定義 geometry(Point, 4326) ※ という設定で空間上の「点」を表すジオメトリ型のカラ ムを定義できます。要は緯度経度です。geometry(Polygon, 4326) でポリゴン、つ

    まり「面」を定義できます。 公園(parks )にはgeom として公園を代表する地点を、駅(stations )にはgeom とし て駅の地点、nearby として最寄りエリアを定義しました。 ※: 4326 って何?と思う方へ: 緯度経度の測り方は何種類もあって、それぞれ測地系と呼ばれています。4326 は世界測地系WGS84というよく使われている測地系を表すコード です。 CREATE EXTENSION postgis; CREATE TABLE parks ( prefecture varchar, city varchar, name varchar, type varchar, geom geometry(Point, 4326) ); CREATE TABLE stations ( code integer, name varchar, prefecture_code integer, address varchar, geom geometry(Point, 4326), nearby geometry(Polygon, 4326) );
  40. PostGIS - 「駅の最寄りエリア」テーブルを作成 平面上にいくつか点があって、その平面を「どの点に一番近いか」で分割した図がボロ ノイ図です。これを使うと「最寄りエリア」のポリゴンを作れます。 下記のように駅 の地点を使ってボロノイ図を描画し、それを最寄りエリアテーブル(polygons )とし て保存します。 CREATE

    TABLE polygons (geom geometry(Polygon, 4326)); CREATE INDEX index_polygons_geom ON polygons USING gist (geom); INSERT INTO polygons (geom) SELECT g.geom FROM ( SELECT ( ­­ ST_Dump: 複数のジオメトリを行に展開する。ボロノイ図は面の集合として返されるので、展開が必要 ST_Dump( ­­ ST_VoronoiPolygons: 複数のジオメトリ(駅の地点の集合)からボロノイ図を描画する ST_VoronoiPolygons( ­­ ST_Union: 複数のジオメトリ(ここでは駅の地点)を一つのジオメトリ(駅の地点の集合)にまとめる ST_Union(geom) ) ) ).geom AS geom FROM stations ) as g;
  41. PostGIS - 「駅の最寄りエリア」テーブルのイメ ージ 地図上に表示すると、このように区分けができました。がしかし、これだとどこがどの 駅がわかりません。

  42. PostGIS - 「駅の最寄りエリア」データを駅テー ブルに挿入 最寄りエリアにどの駅(の地点)が含まれているかがわかれば、エリアのポリゴンと駅 の対応がわかるはず。 PostGISの関数ST_Contains で包含関係を判定できるので、これ を使って駅テーブルと駅の最寄りエリアテーブルを結合し、更新します。 UPDATE

    stations SET nearby = polygons.geom FROM polygons WHERE ST_Contains(polygons.geom, stations.geom);
  43. PostGIS - 「駅の最寄りエリア」と駅の対応がで きた!

  44. PostGIS - 近くに公園が多い駅ランキングを計算 さきほど登場したST_Contains を使って、最寄りエリアに入っている公園を数えま す。 ジオメトリの面積をST_Area で求められるので※、それを使って平方キロメート ルあたりの公園数をp_sqkm として計算しています。

    ※ メートル単位での計算はジオグラフィ型で行う必要があるので、ST_Transform を変換しています。 SELECT stations.name, stations.address, COUNT(parks.name) p, FLOOR(ST_Area(ST_Transform(stations.nearby, 4326)::geography)) area, FLOOR(COUNT(parks.name) / ST_Area(ST_Transform(stations.nearby, 4326)::geography) / (1000 * 1000)) p_sqkm FROM stations LEFT OUTER JOIN parks ON st_contains(stations.nearby, parks.geom) WHERE prefecture_code = 13 GROUP BY stations.address, stations.name, stations.nearby ORDER BY p_sqkm DESC
  45. PostGIS - 近くに公園が多い駅ランキング まさかの「新大久保」駅がナンバーワンでした! name | address | p |

    area | p_sqkm ­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+­­­­­+­­­­­­­­­­­+­­­­­­­­ 新大久保 | 東京都新宿区百人町一丁目10­15 | 12 | 513173 | 23 京成曳舟 | 墨田区京島1­ 39­ 1 | 13 | 712581 | 18 大鳥居 | 大田区西糀谷3­ 37­ 18 | 31 | 1638396 | 18 大森町 | 大田区大森西3­ 24­ 7 | 25 | 1464670 | 17 千鳥町 | 大田区千鳥1­ 20­ 1 | 14 | 844929 | 16 松陰神社前 | 世田谷区若林4­ 21­ 16 | 10 | 604776 | 16 志村三丁目 | 板橋区志村3­ 23­ 1 | 27 | 1615272 | 16
  46. PostGIS - 新大久保駅の様子 結果をビジュアライズしました。ポケットパークという公園が沢山あります。

  47. PostGIS - 京成曳舟駅の様子 多いような気もします。

  48. PostGIS - 玉川学園前駅の様子 面積を考慮しない公園の数ではここがトップです。広い。

  49. PostGIS - Rails では? https://github.com/rgeo/activerecord-postgis-adapter ジオメトリ型がマイグレーションで定義できます。 class CreateStations < ActiveRecord::Migration[5.1]

    def change create_table :stations do |t| t.string :name t.string :line_name t.st_point :geom t.timestamps end add_index :welfare_facilities, :geom, using: :gist end end
  50. PostGIS - データの出典とクレジット 公園: 国土数値情報 都市公園データ http://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-P13.html 駅: 駅データ.jp http://www.ekidata.jp/

    地図: OSM Standard on QGIS, ©OpenStreetMap contributors
  51. まとめ PostgreSQLには便利な機能が沢山 少しRailsのレールを外れると面白い世界が広がっている ご利用は計画的に