タグ: PostgreSQL

個人データ基盤を敢えてオンプレで構築する理由

時代はクラウド。しかしお財布が……

AWSやAzure、GCP、OCIなど大手ITはクラウドサービスを揃え、熱心に推進しています。個人でもクラウドを学習しやすいように、無料枠やクーポンを拡充するベンダーも増えてきました。

クラウドはPCやLANなどの初期投資が不要でコスト的にも敷居は低く、高性能PCを上回る処理能力の仮想マシン(VM)インスタンスも提供されています。

しかし、常時稼働するとランニングコストが1台あたり月1万円を超えてきます。私は90年代からレンタルサーバーで当サイトを運営してきましたが、普通のサラリーマンとして自腹でサーバー費用を負担するのは月 1万円あたりが限界でした。

クラウドがコスト的に有利なのは「必要なものを必要なときだけ」調達するからです。具体的には当Webサイトのように最小限のリソースだけ常時稼働する場合、あるいは機械学習などのために高性能GPUなど贅沢なリソースを一定時間だけ用いる場合などに限られます。技術力が高い方なら、データ処理タスクにしか課金されないサーバーレスアーキテクチャでシステム全体を構築することも選択肢となり得ます。

いずれにせよ、「使い放題はクラウドでは実現出来ない(現実的な費用では)」ということです。例外として、常時稼働こそ出来ませんが月1,000円でGPUインスタンスが使い放題なColab Proあたりがデータ基盤として個人が契約出来るクラウドの限界、との認識です。

Excelで出来るデータ分析にデータ基盤は要らない

ここでいったん原点に戻り、「個人が何のためにデータ基盤を構築するのか？」というテーマを考察します。

学校の課題や仕事で、初めてデータ分析に取り組んだときのことを思い出してみましょう。ほとんどの人はExcelだったと思います。機械学習界隈では「なぜかいきなりPython」というストーリーが多いです。しかし、PythonやRはExcelより圧倒的に敷居が高いですから、本来は「Excelでは無理だから」PythonやRに取り組むのが筋、というものでしょう。具体的には

  • Excelにない機能を使いたいのでPythonやRなどの言語を使う
  • Excelでは処理出来ないほど巨大なデータを操作したいのでSQLデータベースを使う
  • ギガバイト、テラバイト単位のデータベースを管理したいのでデータ基盤を構築する

といった動機であるはずです。これってかなり高度で贅沢ですよね。まずExcelやGoogleスプレッドシートを試してみて、データ分析の目的が本当にExcelやGoogleスプレッドシートで足りないのか確認することをお勧めします。特にGoogleスプレッドシートはPC版のExcelではやりにくいウェブからのデータ取得も無料で出来ますので、集めたいデータがビッグデータでなければ最初に検討するべき選択肢だと思います。

個人データ基盤を作るのは「ビッグデータを贅沢に使いたいから」

ここまで来ると、個人でデータ基盤を作るのは「ビッグデータを贅沢に使いたいから」だということが見えてきます。本来なら企業や大学でしか扱えないような巨大なデータを敢えて個人で蓄積・活用したいのが動機ですから、石油王でもなければクラウドでは無理ということになります。

オンプレと言っても、PC1台で出来るタスクなら『データ基盤』などという仰々しいものを個人で作る必要はありません。バックアップの観点でもGoogleドライブでは100GBが年額2,500円ですから、将来的に1TBを超えるようなデータを管理したいような人が『データ基盤』を検討する価値がある人、ということになります。

個人データ基盤で想定するアーキテクチャ＝『PCクラスタ』

個人でテラバイト単位のデータを大量に蓄積して利活用するには、さすがにクラウドやPC1台では無理があります。そこで複数台のPCを連携する『PCクラスタ』を考えてみます。

個人用PCは最近ではノートPCが主流ですし、タワー型のような大型PCを複数台自宅に置くのは現実的ではありません。しかしコールセンターや受付用に販売されている超小型PCなら、5台くらい自宅に置いても場所はさほど取りません。私の自宅では、実際に超小型PCが10台ほど稼働しています。

超小型PCで組むPCクラスタ

1台あたりの消費電力は30W前後で、電気料金は1台あたり月額数百円程度です。電気料金だけで見れば「オンプレデータ基盤のランニングコストはクラウドの100分の1」ということになります。

データ基盤としてのPCクラスタ利用を想定する場合、LANの速度が深刻なボトルネックとなります。現状ではコストとの兼ね合いから2.5GbEで構築するのが現実的です。少しお値段が張りますが2.5GbE対応のハブとUSB接続のLANアダプターを用意することをお勧めします。

OSは、アップデート時の通信量の少なさやサーバー管理の容易さなどからUbuntu Linuxを使用しています。現在はUbuntu 20.04 LTSを使用していますが、2022年4月末にリリース予定のUbuntu 22.04 LTSに入れ替える予定です。Ubuntu 22.04 LTSは、最近のAMD Ryzen APUでも素のバニラ状態で動く初のLTSリリースとなるため大いに期待しています。なお、後にご紹介する『分散データベース』は大半がJava VM上で動作するため、中古PCバンドルで安価に入手出来るWindows Professionalなど一般の方が使い慣れたOSでも構築運用自体は可能と思われます。

念のため補足しておきますが、超小型PCをクラスタ運用する際には、モニターやキーボードは邪魔なので外しています。必要に応じてHDMI/DisplayPortで接続出来るモバイルモニター、キーボードとタッチパッドが一体化した入力装置を接続してメンテナンスを行います。部屋が暑くなる方は先の写真のように超小型PCと同サイズのUSBファンを重ねて設置しておくと夏場も少し安心です。

SQL or NoSQL?

PCクラスタ向けのデータ管理には専用のソフトウェアが必要です。スタンドアロンのデータベースは定期的なバックアップでデータを保全しますが、PCクラスタ向けの分散データベースはデータ本体を分割して複数の『レプリカ』としてサーバーに分散格納する『シャーディング』という仕組みで動きます。

これまで分散データベースにはNoSQLの『Elasticsearch』を使ってきました。しかしElasticsearchはKibanaなど同社のBIツールと連携して用いることを前提としており、無償の範囲では汎用的なJDBCコネクタを利用出来ないことなどから、次期のデータ基盤は分散RDBに替えようと思っています。

NoSQLはJSON&KeyValueでのデータ格納を前提としています。ログデータの蓄積に向いており柔軟なデータ構造に対応出来るという意味では優れています。しかし、データ分析の際はほぼほぼ表形式に加工して用いることやデータ格納時にデータ型をチェックしておかないと分析時に前処理で困ることが多いことなどから、私にとってはSQL対応の分散RDBでデータを管理するのが一番、という結論に至りました。

クラウドでは『ボタンひとつ』で使える分散RDB。しかしオンプレでは……

分散RDBは分散データベースの一種で、複数のサーバーにデータを複製して保存するシャーディングという技術を用います。サーバーの数を増やしてレプリカを再構築すれば自動的に新しいサーバーにデータが複製されますし、サーバーのうち1台が壊れても他のサーバーにレプリカが保存されているのでデータを失わずに済みます。

個人でもNASなどでRAIDを運用している方も多いと思いますが、NASそのものが壊れてしまうとデータはHDDからサルベージしなければならず、無事サルベージ出来るかも運次第、というのが現実です。ハードウェア障害時にもデータを実運用出来る形で保持するためには、PCクラスタでデータをレプリケーションして分散運用するのがオンプレで唯一の有効な手段と考えています。

このような技術は、大手クラウドでは既に当たり前のものとなっています。GoogleのCloud Spannerやデータ分析に特化したBigQuery、AWSのAmazon Redshiftなどはユーザーが意識しない形で巨大な分散データベースを構築しており、これによりペタバイト級のデータを扱えるとされています。データのレプリケーションもユーザーに意識させることなく複数のデータセンターに分散して格納されているため、データの堅牢性という意味ではクラウドが最強であることに疑いはありません。ただし、当然ながら堅牢な分だけお高いので、データ保全命でクラウドにビッグデータを置くかどうかは「お財布と相談」になります。

しかし、オンプレ用の分散RDBには従来、めぼしいものがありませんでした。オープンソースの製品もいくつか存在してはいるのですが、SQLの対応が弱かったりJDBCドライバで文字化けが出るなど汎用的なデータ基盤としては厳しいものがありました。開発元もアメリカではなく会社としての事業継続性も未知数、という印象。

分散RDBの決定打となるか?PostgreSQL拡張『Citus』

ところが最近になって、マイクロソフトからAzure Database for PostgreSQL – Hyperscale (Citus) というクラウドサービスが出てきました。これ自体は他社クラウドの分散RDBと大差無いのですが、

  • 定番のOSS RDBであるPostgreSQLを拡張する形で分散RDBを実現している
  • PostgreSQLの拡張であるため、BIなど各種ツールが用意しているPostgreSQL用のJDBC/ODBCドライバがそのまま利用できる(かも知れない)
  • 【これが重要】CitusはOSSであり、オンプレで利用できる
  • 開発元のCitus Dataは2019年にマイクロソフトが買収しており、経営が安定している

など利点が多く「まさに決定的」という印象です。

Citusはマイクロソフト傘下でありながらOSSであり、オンプレで使える

マイクロソフトの藤田氏によれば「Citus Dataの創立者から聞いた話ですが、買収提案を受け入れた理由は、MicrosoftがOSSコミュニティーに最も貢献しているパブリッククラウドプロバイダーだったからとのことです」とのこと。ベンダーがここまで明言している以上は、CitusがOSSサポートを中止することは当分ないはず、です。

Citusを使うのはAzureが一番楽だと思いますが、Citusがオンプレで提供されている限り、その気になればAWSやGCPのVM上でもCitusによる分散RDBを構築運用できるはず、です。これはクラウドベンダーにロックインされた挙句、突然の大幅値上げで泣く羽目になっても逃げる余地があることを意味します。これがどれだけ重要なことかは、IT業界に長い方ならよくご存知でしょう。

うちでは「全部これから」です💦

我が家の自宅クラスタは、2022年4月末のUbuntu 22.04 LTSのリリースを待って全てのサーバーOSを入れ替える予定です。これに合わせて分散データベースもCitusに入れ替えたいと考えており、「全部これから」です。近い将来PostgreSQL自体も分散RDB機能を持つようになるかも知れませんが、それを待っていられるほど私の寿命は長くなさそうです。

データ活用の観点からも、従来kibanaやPythonなどに限られていた分析ツールがJDBC/ODBC対応の各種BIツールに拡がることが期待されます。無償で多機能なPower BI Desktopなどが使えるはずですし、PostgreSQL自体が持っているマテリアライズドビューなどの機能を用いてRDB側でクエリを高速化することも(スキル次第では)出来るでしょう。

これからマイペースで記事も投稿していこうと考えていますので、引き続きよろしくお願いします。

Jupyter NotebookからPythonでPostgreSQL 13を操作する

まずは堅実に定番RDBから

　NoSQLブームで勉強会界隈では影が薄れた感があるRDBMSですが、当たり前の存在になっただけで健在です。当サイトが使っているCMS『WordPress』にはMySQLが組み込まれていますし、当サイトを置いているAWSでもPostgreSQLがRedShiftやAuroraなど基幹サービスで使われています。

　統計モデリングを知らないのにディープラーニングでイキる人が信用出来ないのと同様、RDBMSを知らないのにNoSQLを語るのもイタい人です。私も昨年はElasticsearchなどのNoSQLやApache Igniteなどの分散データベースにハマっていましたが、自分のイタさに気づいたためPostgreSQLを学び直しています。

　NoSQLや分散データベースを業務で使いこなしている方々の講演は勉強会で聞くことが出来ますが、彼らは「RDBMSを経験した上でNoSQLに進んでいる」のです。未経験者が講演を聞いていきなりNoSQLに行くのは『悪手』です。

初心者がNoSQLや分散RDBでやりたいことはPostgreSQLでも出来ている

　NoSQLが主に扱うJSONはPosgreSQLでも『JSON型』として扱えます。IMDBで話題となったインメモリ処理はPostgreSQLでも駆使されています。分散データベースの特徴であるクエリの並列処理はPostgreSQLでもパラレルクエリとして実装されている上、特別の設定無しで自動的に使ってくれます。

　昨年、実際にApage IgniteでPCサーバ5台のクラスタを構築しましたが、数10GB程度のデータではクエリに要する時間がシングルノードのPostgreSQLの方が速かったです。2.5GbEでもLANの遅延で並列処理のメリットが相殺されてしまいます。この規模のデータでクエリを高速化するなら、32コア64スレッドのパソコンでも買えば良いと思います。

　こういう事実は、基本的すぎて勉強会ではなかなか教えてもらえないですね。エンジニアの方にとっては常識なのでしょうが……💦

PythonでPostgreSQLを扱うパッケージ『psycopg2』

　PythonでPostgreSQLを扱う際は『psycopg2』というパッケージを使うのが一般的なようです。視覚化のためのパッケージ『Plotly』と併せてインストールします。

(base) masaru@ASUS-TUF-Gaming:~$ conda install psycopg2
(base) masaru@ASUS-TUF-Gaming:~$ conda install plotly

Jupyter Notebookでの実装

　必要なパッケージの読み込みなどの初期設定を行います。

# -*- coding: utf-8 -*-
import psycopg2
import pandas as pd
import plotly as py
import plotly.graph_objs as go
from plotly.offline import iplot, init_notebook_mode
init_notebook_mode()

　psychopg2でデータベース接続を定義します。

def connect():
    con = psycopg2.connect("host=" + "localhost" +
                           " port=" + "5432" +
                           " dbname=" + "google_mobility" +
                           " user=" + "masaru" +
                           " password=" + "xxxxxxxxxxxx")
    return con

　続いて、クエリを定義します。

def select_execute(con, sql):
    with con.cursor() as cur:
        cur.execute(sql)
        rows = cur.fetchall()

    return rows

　定義したクエリを実行します。

con = connect()
sql =  "select * from google_mobility where SUB_REGION_1 = 'Tokyo'"
result = select_execute(con, sql)

　クエリの結果をPandasデータフレームに代入します。

df = pd.DataFrame(result)
df.head()

　とりあえず全部カラム名をつけてあげます。

columns = ["id","country_region_code","country_region","sub_region_1","sub_region_2","metro_area","iso_3166_2_code","census_fips_code","date","retail","grocery","parks","transit","workplaces","residental","place_id"]
df.columns = columns
df.head()

　Plotlyで表示するデータを設定します。

trace1 = go.Scatter(
        x = list(df.date),
        y = list(df.retail),
        mode = 'lines+markers',
        name = 'retail',
        marker = dict(
                color = 'blue'
                )
        )

trace2 = go.Scatter(
        x = list(df.date),
        y = list(df.grocery),
        mode = 'lines+markers',
        name = 'grocery',
        marker = dict(
                color = 'orange'
                )
        )

data = [trace1, trace2]

　続いて、Plotlyの特長であるスライダーを設定します。

layout = dict(
    title='Time series with range slider and selectors',
    xaxis=dict(
        rangeselector=dict(
            buttons=list([
                dict(count=1,
                     label='1m',
                     step='month',
                     stepmode='backward'),
                dict(count=6,
                     label='6m',
                     step='month',
                     stepmode='backward'),
                dict(count=1,
                    label='YTD',
                    step='year',
                    stepmode='todate'),
                dict(count=1,
                    label='1y',
                    step='year',
                    stepmode='backward'),
                dict(step='all')
            ])
        ),
        rangeslider=dict(
            visible = True
        ),
        type='date'
    )
)

　グラフを描画します。

fig = dict(data=data, layout=layout)
iplot(fig)

　スライダーを動かせるグラフの出来上がりです。

Ubuntu 21.04にPostgreSQL 13をインストールする

準備

sudo apt install curl ca-certificates gnupg
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update

　PosgreSQLをaptのレポジトリに登録します。

インストール

(base) masaru@ASUS-TUF-Gaming:~$ sudo apt install postgresql
パッケージリストを読み込んでいます... 完了
依存関係ツリーを作成しています... 完了        
状態情報を読み取っています... 完了        
以下の追加パッケージがインストールされます:
  postgresql-13 postgresql-client-13 postgresql-client-common
  postgresql-common sysstat
提案パッケージ:
  postgresql-doc postgresql-doc-13 libjson-perl isag
以下のパッケージが新たにインストールされます:
  postgresql postgresql-13 postgresql-client-13 postgresql-client-common
  postgresql-common sysstat
アップグレード: 0 個、新規インストール: 6 個、削除: 0 個、保留: 6 個。
15.6 MB のアーカイブを取得する必要があります。
この操作後に追加で 48.1 MB のディスク容量が消費されます。
続行しますか? [Y/n] Y

　デフォルトではバージョン13が入るようです。

(base) masaru@ASUS-TUF-Gaming:~$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor pr>
     Active: active (exited) since Mon 2021-04-05 17:36:05 JST; 6min ago
   Main PID: 116889 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 37748)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

 4月 05 17:36:05 ASUS-TUF-Gaming systemd[1]: Starting PostgreSQL RDBMS...
 4月 05 17:36:05 ASUS-TUF-Gaming systemd[1]: Finished PostgreSQL RDBMS.

無事動いているようです。

PgAdmin 4は未対応の模様

PgAdminはまだUbuntu 21.04に未対応のようです。

(base) masaru@ASUS-TUF-Gaming:~$ sudo apt install pgadmin4
[sudo] masaru のパスワード: 
パッケージリストを読み込んでいます... 完了
依存関係ツリーを作成しています... 完了        
状態情報を読み取っています... 完了        
パッケージ pgadmin4 は使用できませんが、別のパッケージから参照されます。
これは、パッケージが欠落しているか、廃止されたか、または別のソース
からのみ利用可能であることを意味します。

E: パッケージ 'pgadmin4' にはインストール候補がありません

LANからPostgreSQLに接続可能にする設定

(base) masaru@ASUS-TUF-Gaming:~$ sudo gedit /etc/postgresql/13/main/pg_hba.conf 
(base) masaru@ASUS-TUF-Gaming:~$ sudo systemctl restart postgresql
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all         192.168.100.1/24            md5

ユーザーの作成

postgres=# create role masaru with superuser login;
select * from pg_shadow;

 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+--------+----------+-----------
 postgres |       10 | t           | t        | t       | t            |        |          | 
 masaru   |    16384 | f           | t        | f       | f            |        |          | 
(2 rows)

postgres=# alter role masaru with password 'xxxxxxxxxxx';
ALTER ROLE

データベースの作成

　『Google コミュニティ モビリティ レポート』のデータベースを作ってみます。

(base) masaru@ASUS-TUF-Gaming:~$ createdb google_mobility

　DBeaverで接続します。

　DBeaverでSQL文を実行してテーブルを作成します。

DBeaverでテーブルを作成する
create table google_mobility( 
	id SERIAL primary key, 
	country_region_code varchar(2) , 
	country_region varchar(255) , 
	sub_region_1 varchar(255) , 
	sub_region_2 varchar(255) , 
	metro_area varchar(255) , 
	iso_3166_2_code varchar(32) , 
	census_fips_code varchar(32) , 
	date date , 
	retail_and_recreation_percent_change_from_baseline integer , 
	grocery_and_pharmacy_percent_change_from_baseline integer , 
	parks_percent_change_from_baseline integer , 
	transit_stations_percent_change_from_baseline integer , 
	workplaces_percent_change_from_baseline integer , 
	residential_percent_change_from_baseline integer);

CSVデータのインポート

　いよいよCSVのインポートです。DBeaverはCSVも格納先フォルダをデータベースとして登録してからPostgreSQLにインポートするのがお作法のようです。

　データベース上のテーブルとCSVでカラム名が一致していれば、マッピングで困ることはないようです。

　とは言え、Google Mobilityデータも短期間のうちにplace_idというカラムが増えており、オープンデータの扱いのやっかいさを実感します。さらにCSVの中身が壊れているとインポートの段階で苦しみます……💦

　増えていたカラムはvarchar(32767)で格納されてしまっているので、varchar(32)に縮小します。

select place_id from google_mobility limit 10;
alter table google_mobility alter column place_id type varchar(32);

SQL select文でデータ抽出

select count(*) from google_mobility;
select * from google_mobility
	where sub_region_1 = 'Tokyo'
	order by date desc;
Google Community Mobility ReportからSQL select文でデータを抽出する

　4838804レコードから400ミリ秒ほどでselect文を実行できました。緊急事態宣言が解除された東京ですが、まだ交通、職場、小売店などでは自粛の影響が残っているようです。

Ubuntu 21.04が待ち遠しい

DSC_0826
Ubuntu 21.04 Daily Build

　ASUSのノートPCを年明けにAmazonのセールで買いました。Ryzen 4600HとGTX 1650が載ったゲーミング仕様ですが、Linuxを入れる予定です。

　Linuxを入れるのはデータベースとデータ分析、Python機械学習が目的です。PostgreSQLは6コアを使ってくれますし、Tensorflow/Kerasなどディープラーニングのフレームワークもnvidia-smiなどの設定がうまく行けばGPUの並列処理を使ってくれるはずです。

　しかし今はまだWindows 10のまま。常用しているUbuntu 20.04のLinuxカーネルがAMD Ryzen 4000系のAPUに未対応だからです。

　カーネルだけ更新して世間のTipsが使えなくなるのも嫌なので、2021年4月リリース予定のUbuntu 21.04に合わせてOSを入れ替える予定です。

　写真はDaily BuildをUSBメモリから起動したところ。AMDへの対応が遅れがちなLinuxですが、Linusも今ではAMDのThreadripperを愛用しているとのこと。このノートでUbuntuが安定稼働することに期待しています。

　このブログでは、Ubuntu 21.04のインストールから各種環境構築まで一連の過程を記事として掲載しようと思っています。