$30 off During Our Annual Pro Sale. View Details »

地理情報データをデータベースに格納しよう~ GPUを活用した爆速データベース PG-Strom...

sakaik
November 10, 2024

地理情報データをデータベースに格納しよう~ GPUを活用した爆速データベース PG-Stromの紹介 ~

2024年11月10日に開催された FOSS4G 2024 Japan での、
『地理情報データをデータベースに格納しよう~ GPUを活用した爆速データベース PG-Stromの紹介 ~ 』の発表資料です。

https://www.osgeo.jp/events/2024-2/foss4g-2024-japan

sakaik

November 10, 2024
Tweet

More Decks by sakaik

Other Decks in Technology

Transcript

  1. 地理情報をデータベースに格納しよう ~GPUを活用した爆速データベースPG-Stromの紹介 • 坂井 恵 @sakaik • 有限会社アートライ 代表取締役 •

    日本仮想化技術株式会社「爆速DB Powered by PG-Strom」チームに参画中 • MySQL 8.0(2018) でGIS機能が拡充されたのをきっかけに地理情報に関心 • 測量士補 発表者紹介 日本仮想化技術株式会社 • サーバ仮想化 や DevOps の技術を提供 • PG-Stromを使用したデータ分析基盤「爆速DB」を提供 • OSGeo.JP 団体会員 講演 タイトル
  2. データベースで地理データを管理しよう 私の印象ですが地理情報の人って結構、 • データを、あんまりデータベースに入れない • ファイルで十分だと思っているフシがある データベース(DBMS)を使わなくても良い? • データが少なかったらDBMSなくてもいいかも? •

    変化がない固定のデータなら一度ファイルを作って終わりで良い? • ファイルベースでの情報配信の仕組みが確立している分野 データベースの使いどころ • データ量が多く構造的であれば使うとメリットがあるかも • 配信用地図データのマスタデータの保存場所として • 条件を指定しての抽出操作、集計・集約操作が得意 • 様々な切り口でのデータ提供が可能
  3. データベースで扱える主なデータ型 • 文字列 • 数値 • 整数 • 小数 •

    日付・時刻 • 空間情報 • 点 • 線(ライン) • 面(ポリゴン) • その他 • JSONとか 「表(テーブル)」の形式で格納 データの保全 データの高速な取扱い (集計、検索(抽出))
  4. PG-Stromとは 今日、これだけは知って帰ってください! • GPUパワーを活用した高速データベース • 大抵のデータベースはCPUのみを使用している • 大量データの処理が得意 • 例:

    テラバイトクラス~、数十億件~ • PostgreSQL の extensionとして動作 • 通常の PostgreSQLを使う感覚で使用できる • Apache Arrow形式のファイルをテーブルとして使用可能
  5. 爆速DB powered by PG-Strom • PG-Stromをベースにデータ分析基盤としてアプライアンス提供 • 位置情報、IoT、ログ分析などに(GISにも対応) • 大量データの処理や演算に向いている

    • 爆速DB powered by PG-Strom の技術 • GPU : 数千コアによる超並列処理 • NVMe SSD : 高速バス経由でGPUにダイレクト転送 • Apache Arrow : 列指向フォーマットをテーブルとして使用可能
  6. PG-Stromと位置情報1 • 複雑なポリゴンとの交叉または包含関係 • 1000万ポイント • 新宿区のポリゴン(ポイント数の多い複雑なポリゴン) SELECT p.gid, p.x,

    p.y FROM tdfk j, geopoint p WHERE ST_Contains(j.geom,ST_SetSRID(ST_MakePoint(x,y), 4326)) AND j.n03_004 like '新宿区' CPU(PostgreSQL) : 5500 msec (5.5秒) GPU(PG-Strom) : 630 msec (0.6秒) in 新宿区 Result: Query: 公共施設/コンビニ/チェーン店/ 公衆トイレ/看板/人流 etc
  7. PG-Stromと位置情報2 • 複雑なポリゴンとの交叉または包含関係 • 1000万ポイント • 静岡県内の市区町村ごとのポリゴン(複雑かつ多量) • 市区町村ごとに集計 SELECT

    n03_001 || n03_004, COUNT(*) cnt FROM tdfk j, geopoint p WHERE ST_Contains(j.geom, ST_SetSRID(ST_MakePoint(x,y), 4326)) AND n03_001 LIKE '静岡県' GROUP BY n03_001, n03_004 ORDER BY COUNT(*) DESC CPU(PostgreSQL) : 26800 msec (26秒) GPU(PG-Strom) : 900 msec ( 0.9秒) Result: Query: in 静岡県 市区町村毎に集計
  8. PG-Stromと位置情報 まとめ • 交叉/包含判定関数をはじめとした様々な関数がGPU対応 • 注)現時点で PostGISの全関数に対応しているわけではない • 案件や検証で必要であれば追加を提案できるかも 対応関数の例

    st_makepoint (float8,float8) st_point (float8,float8) st_makepoint (float8,float8,float8) st_makepoint (float8,float8,float8,float8) st_setsrid (geometry,int4) st_distance (geometry,geometry) st_dwithin (geometry,geometry,float8) st_contains (geometry,geometry) st_crosses (geometry,geometry) st_linecrossingdirection (geometry,geometry) https://heterodb.github.io/pg-strom/ja/ref_devfuncs/#postgis
  9. PG-Stromと列指向 • lineorder_flat テーブル • 列数: 54列 • 件数:約24億件 •

    データサイズ • PostgreSQL(heap)テーブル • 1526GB • Arrowファイル • heapとほぼ同サイズ(1598GB) lo_orderkey lo_linenumber lo_custkey lo_partkey lo_suppkey lo_orderdate lo_orderpriority lo_shippriority lo_quantity lo_extendedprice lo_ordertotalprice lo_discount lo_revenue lo_supplycost lo_tax lo_commit_date lo_shipmode c_name c_address c_city c_nation c_region c_phone c_mktsegment s_name s_address s_city s_nation s_region s_phone p_name p_mfgr p_category p_brand1 p_color p_type p_size p_container d_date d_dayofweek d_month d_year d_yearmonthnum d_yearmonth d_daynuminweek d_daynuminmonth d_daynuminyear d_monthnuminyear d_weeknuminyear d_sellingseason d_lastdayinweekfl d_lastdayinmonthfl d_holidayfl d_weekdayfl 列名一覧:
  10. PG-Stromと列指向 1 CPU(PostgreSQL) : 5分14秒 GPU(PG-Strom)+Arrow : 5秒165 Result: Query:

    SELECT lo_orderpriority, COUNT(*) FROM %TABLE% GROUP BY lo_orderpriority ORDER BY lo_orderpriority; lo_orderpriority | count ------------------+----------- 1-URGENT | 479992014 2-HIGH | 480037805 3-MEDIUM | 479957940 4-NOT SPECI | 479967889 5-LOW | 480056415 (5 rows) 単純なグループカウント(全件対象)
  11. PG-Stromと列指向 2 CPU(PostgreSQL) : 6分07秒 GPU(PG-Strom)+Arrow : 3秒958 Result: Query:

    SELECT lo_orderpriority, COUNT(*) FROM %TABLE% WHERE d_year=1995 GROUP BY lo_orderpriority ORDER BY lo_orderpriority; lo_orderpriority | count ------------------+---------- 1-URGENT | 72803951 2-HIGH | 72816667 3-MEDIUM | 72799284 4-NOT SPECI | 72802326 5-LOW | 72831556 (5 rows) 抽出付グループカウント(d_yearで絞り込み)
  12. PG-Stromと列指向 3 CPU(PostgreSQL) : 5分58秒 GPU(PG-Strom)+Arrow : 11秒253 Result: Query:

    SELECT d_year, lo_orderpriority, sum(lo_extendedprice) FROM %TABLE% WHERE s_region='ASIA' GROUP BY d_year,lo_orderpriority ORDER BY d_year, lo_orderpriority; d_year | lo_orderpriority | sum --------+------------------+---------------- 1992 | 1-URGENT | 55754054433397 1992 | 2-HIGH | 55720071150222 1992 | 3-MEDIUM | 55737456621929 1992 | 4-NOT SPECI | 55745409996470 1992 | 5-LOW | 55747692515383 1993 | 1-URGENT | 55582507698342 1993 | 2-HIGH | 55617539949435 : 抽出付き 2列指定でのグループ合計
  13. PG-Stromと超大量データ • 登記所備付地図データを例に • 点、線、ポリゴンへと積み上げのデータ構造 • surface - curves -

    points と積み上げるデータ構造 • 次ページに詳細情報 • basics に名称等の情報 num | tbl | cnt -----+-----------------+--------------- 1 | moj_basics | 316,300 2 | moj_curves_data | 6,455,800,708 3 | moj_fude_info | 243,443,243 4 | moj_line_info | 3,206,759,982 5 | moj_points | 1,556,071,844 6 | moj_surface | 3,194,536,648 8 | moj_zukaku_info | 7,541,896 9 | moj_zukaku_ref | 286,190,006 参考:全データ件数:
  14. PG-Stromと超大量データ points curves_data surface ver | filename | point_no |

    xml_pid | x | y | refpoint_type | buri_kbn | point_tag --------+---------------------+----------+------------+------------+-----------+---------------+----------+----------- 202404 | 12222-0424-1265.zip | 2529047 | P000000541 | -15402.457 | 24941.103 | | | 筆界点 202404 | 12222-0424-1265.zip | 2529048 | P000000542 | -15407.726 | 24937.207 | | | 筆界点 202404 | 12222-0424-1265.zip | 2529049 | P000000543 | -15412.905 | 24933.192 | | | 筆界点 202404 | 12222-0424-1265.zip | 2529050 | P000000544 | -15417.984 | 24929.054 | | | 筆界点 202404 | 12222-0424-1265.zip | 2529051 | P000000545 | -15422.967 | 24924.789 | | | 筆界点 ver | filename | curve_id | x | y | xml_pid | num --------+---------------------+------------+---+---+------------+----- 202404 | 12222-0424-1265.zip | C000004229 | | | P000000110 | 1 202404 | 12222-0424-1265.zip | C000004229 | | | P000002610 | 2 202404 | 12222-0424-1265.zip | C000004230 | | | P000001373 | 1 202404 | 12222-0424-1265.zip | C000004230 | | | P000001408 | 2 ver | filename | surface_id | curve_id | num --------+---------------------+------------+------------+----- 202404 | 12222-0424-1265.zip | F000000062 | C000000469 | 1 202404 | 12222-0424-1265.zip | F000000062 | C000000470 | 2 202404 | 12222-0424-1265.zip | F000000062 | C000000471 | 3 202404 | 12222-0424-1265.zip | F000000062 | C000000472 | 4 202404 | 12222-0424-1265.zip | F000000063 | C000000473 | 1 登記所備付地図データ 点、線、面データの例
  15. PG-Stromと超大量データ 登記所備付地図データ 点-線-面データの例(注目列を抜粋) points curves_data surface xml_pid | x |

    y | ------------+------------+-----------+ P000000541 | -15402.457 | 24941.103 | P000000542 | -15407.726 | 24937.207 | P000000543 | -15412.905 | 24933.192 | P000000544 | -15417.984 | 24929.054 | P000000545 | -15422.967 | 24924.789 | curve_id | xml_pid | num ------------+------------+----- C000004229 | P000000110 | 1 C000004229 | P000002610 | 2 C000004230 | P000001373 | 1 C000004230 | P000001408 | 2 surface_id | curve_id | num ------------+------------+----- F000000062 | C000000469 | 1 F000000062 | C000000470 | 2 F000000062 | C000000471 | 3 F000000062 | C000000472 | 4 F000000063 | C000000473 | 1 F000000062 | POLYGON((140.10768708299807 35.8623759553336,140.10771663745726 35.86237950222092,140.10778125873233 (略),140.10768708299807 35.8623759553336)) 得たい結果: 点ー線-面の積み上げにより面のポリゴン座標を得たい データ構造
  16. PG-Stromと超大量データ WITH t1 AS ( SELECT b.ver, b.filename, MAX(city_name) city_name

    , MAX(coord) coord, MAX(s.num) surface_num, MAX(s.surface_id) surface_id, c.curve_id, MAX(CASE WHEN c.num=1 THEN c.xml_pid ELSE '' END) pid1, MAX(CASE WHEN c.num=2 THEN c.xml_pid ELSE '' END) pid2, MAX(CASE WHEN c.num=1 THEN p.x ELSE null END)::float p1x, MAX(CASE WHEN c.num=1 THEN p.y ELSE null END)::float p1y, MAX(CASE WHEN c.num=2 THEN p.x ELSE null END)::float p2x, MAX(CASE WHEN c.num=2 THEN p.y ELSE null END)::float p2y FROM moj_surface s LEFT OUTER JOIN moj_basics b ON (b.ver=s.ver AND b.filename=s.filename) LEFT OUTER JOIN moj_curves_data c ON (s.ver=c.ver AND s.filename=c.filename AND s.curve_id=c.curve_id ) LEFT OUTER JOIN moj_points p ON (b.ver=c.ver AND b.filename=p.filename AND p.xml_pid=c.xml_pid) WHERE b.filename LIKE '12222-%.zip' AND coord LIKE '公共座標%' GROUP BY b.ver, b.filename, c.curve_id ORDER BY ver, filename, surface_id, surface_num ), t2 AS( SELECT filename, surface_id, ST_MakeLine(ST_MakePoint(p1y,p1x)) g FROM t1 GROUP BY filename, surface_id ) SELECT filename, surface_id, ST_Transform(ST_SetSRID(ST_MakePolygon(ST_AddPoint(g,ST_StartPoint(g))), 6668+9), 6668) FROM t2; Query:
  17. PG-Stromと超大量データ • 得られた結果件数 約4400 Polygons • PG-Stromを使わない素のPostgreSQLでは、3時間40分で結果を得られなかったので中断 した filename |

    surface_id | st_astext ---------------------+------------+------------------------------------------------------------------------------------------------ 12222-0424-1265.zip | F000000001 | POLYGON((140.10768708299807 35.8623759553336,140.10771663745726 35.86237950222092,140.10778125873233 35.86202309043169,140.1074876615119 35.86200930321591,140.107469067712 12222-0424-1265.zip | F000000002 | POLYGON((140.10881934551975 35.862082186144555,140.10875883806983 35.862086885655714,140.10869821611357 35.86209049477578,140.10863747831317 35.86209262593087,140.10857670 12222-0424-1265.zip | F000000003 | POLYGON((140.10730388459191 35.86209545801973,140.10746906771254 35.862114649358936,140.1074876615119 35.86200930321591,140.10741172337947 35.86200573590365,140.1073605579 12222-0424-1265.zip | F000000004 | POLYGON((140.10782966239697 35.86183496904813,140.1078239837028 35.86181937980182,140.10781692226777 35.86180418129098,140.1078088432409 35.86178930057391,140.107799658476 12222-0424-1265.zip | F000000005 | POLYGON((140.10786214492612 35.86133905787694,140.10782198356955 35.86131633668292,140.10778081864078 35.861294780498305,140.10777241140715 35.8612906715582,140.1076900701 12222-0424-1265.zip | F000000006 | POLYGON((140.1077476547699 35.861609226133645,140.10776428149174 35.86162077940842,140.10779716037655 35.86164443662493,140.10780678528442 35.86165165240926,140.1078091949 : CPU(PostgreSQL) : ∞ GPU(PG-Strom) : 58秒 Result:
  18. まとめ: PG-Strom • GPUを活用した爆速DB • 大量のデータを分析したい場合 • 位置情報の抽出を行いたい場合 • Arrow:

    カラム数が多いデータに悩んでいる場合 • 複雑な処理を速くしたい場合 マッチする場合としない場合がありますので 一緒に検証していければと思います。 お声がけください。