軽量スクリプトで手軽に仕事をこなすことに慣れている人にとって「RDBMS なんて要らない!」などと感じるものかもしれない。 ここでは必要性を感じない理由をその立場を想定しつつ考察し、 実際には RDBMS+SQL が軽量スクリプトとも相性よく機能することを示す。
リレーショナルデータベースシステムについて 筆者が最初に学んだのは1991年頃であったと思う。 そのときに感じたのは「重厚長大」というイメージで、 同時に思い浮かんだのが次の言葉だった。
割鷄焉用牛刀
鶏を割くに焉んぞ牛刀を用いん
既に awk や Perl などでテキストファイルのまま処理する有用性を見出していた当時の筆者にとって RDBMS はあまりに巨大で、目の前の諸問題を扱うには大袈裟すぎると感じた。 学習の題材として出てくる例の買い物リストや、科目データベースなどどれも「Perl だってできるさ」と思えてしまったことが災いして、 「学ぶ」だけで終わり「使う」には至らなかった。
当時の RDBMS は、そして現在でもほとんどの RDBMS はクライアント/サーバ方式で 分散した多数のクライアントからの問い合わせに応じられる設計となっている。 現在ではソフトウェアのパッケージシステムの発達で、RDBMS の導入と設定は手軽になったが、 それでも認証等の初期設定にはそれなりの手間が必要である。 「テキストファイルから sed/grep」で済む手間と比べると圧倒的にハードルは高い。
「牛刀割鷄」は、些細なものに大きな人・道具を使うのは無駄 という故事だが、これには続きがある。 もっと大きな場所を治めるに適した人材であると自分が持ち上げられたことに気づかぬ子游は、 よいものを学べば徳の高い人にも、 そうでない人にもそれぞれ違った効用があるという主旨の答えをした。 IT風に言い換えるなら、スケーラビリティの高い高性能なものは 小さな問題にもうまく機能する、といったところだろうか。 これもまたもっともであるが、些細なデータ処理に RDBMS を使うには、あまりに負担が大きいのも確かである。 そんな時代が長く続いたが、SQLite の登場が「牛刀割鷄の続き」を手軽に体験できるものとした。
SQLite はこれまでの RDBMS で当然と思われているのとは対極の性質を持つ。
これにともない、それまでの RDBMS と比べて以下のような制約が出ることになる。
クライアント/サーバ方式と違い単一ホストですべての処理を行なうため、 あらゆる処理のスループットは1台のホストの性能で律速される。もっとも、 現在のハードウェア性能は高く、著名な Web サイト等で働くデータベースでない限り性能限界に達する状況は考えにくい。
SQLite自身は247バイト(=128TiB)まで扱える設計だが、 データベース全体を単一ファイルに格納するため、このサイズが OS で利用するファイルシステムの制約を受ける。
https://www.sqlite.org/whentouse.html にはこれらの詳細が書かれているが、かなり控えめな表現である。 実際のところどの程度「使える/使えない」のか、 筆者の個人的使用感にもとづくものだが記してみたい。
ファイルにアクセスできる権限があれば、SQLite のデータベースに誰でもアクセスできる。 もちろんパスワードは要求されない。他の RDBMS では一つのシステム内に複数のデータベースを持て、 さらにデータベースごとにユーザとパスワードを設定できる。 SQLiteにこれがないことを不安視する向きもあるが、逆に 「ユーザ+パスワード」方式は安全なのだろうか考えてみたい。
まず対話的に利用する場合。クライアント/サーバ方式の RDBMS は、利用者がログインしているホストと データベースサーバが動いているホストは別の場合もある。 そのため、データベースにつなぐのに別途パスワードを要求して然りである。 逆にSQLiteの場合は、利用者ログインホストにあるデータベースファイルにアクセスすることになるので、 利用者がログイン権を得ていればもはや再認証は必要ないと考えて然りである。
次に非対話的に利用する場合、たとえばブログサーバや CMS などの Web アプリケーションサービスでアクセスするデータベースの場合がこれに該当する。 この場合、アプリケーションプログラムがデータベースにアクセスできるよう なんらかの形でユーザ/パスワードの組み合わせを埋め込んでおく必要がある。 つまるところそのアプリケーションプログラムの起動は、 同時にデータベースへのアクセス許可を自動的に与えているのに等しい。 SQLiteではどうかというと、そもそもパスワードがないのでアプリケーションプログラム起動がデータベースへのアクセス許可を意味することは同等である。ただし、SQLite の場合は稼動プロセスの UID で書き込めるデータベースファイルはすべて書き込みできるため、Web サーバプロセスの稼動UIDと同じファイルはすべて読み書きできる。 このため、1つのアプリケーションプログラムに任意の SQL 文を送り込める状態になっていると(SQLインジェクション)、その Web サーバ上で稼動する他の Web アプリケーション用のデータベースも破壊されうる。 これを防ぐには以下のような対策が考えられる。
消極的だが悪影響が及ぶ先そのものをなくす。 ホストの仮想化技術やサービスのコンテナ化技術が豊富な今日では 現実的な方法だろう。
SQLite データベースを利用するプログラムが複数あったら、 それぞれ違う UID で動くようにする。Apache のsuexec(※1)、 あるいは wrapper プログラム、sudo などを用いてその UID に変更してからアプリケーションプログラムを動かすことで、 同一ホストで動く別のサービスへの影響は最小化できる。
Webブラウザでのアクセスが情報の参照だけで新規データの入力が 発生しないのであれば、SQLite データベースファイルを Web サーバプロセスのUIDとは別の所有者にして書き込みできない状態にする。
※1 suEXECサポート: https://httpd.apache.org/docs/2.4/suexec.html
いずれにせよ、Web サーバ等のプロセスにデータベースアクセスを許しつつ想定される被害を最小限にするには それぞれのセキュリティモデルを熟知している必要がある。SQLite によるネットワークサーバ運用で安全性が確保できる知識がなければ クライアント/サーバ方式での安全確保は困難だろうし、逆もまた真である。
処理性能では商用データベースに譲るのは確かである。 しかしながら現実的に使えるのはどの程度の規模なのか、 それが普段扱っているデータ量をカバーしうるものなのかが気になるところだろう。 限界は利用環境、データの性質、設計のよしあしによって大きく変わるので 一概に言えないが、直観に任せてざっくりいうなら、1GB 前後、もしくはファイルコピーが30秒程度で終わるデータ総量が目安となる。 この目安はテーブル設計を練ることで何倍にも大きくなる。
そうは言っても実際にどの程度の性能かピンと来ず、その程度なら sed や grep などのフィルタコマンドの組み合わせで行けるのでは、 と考える可能性も考慮して1GB超のデータを処理する例を次の質問項目で示した。
grep コマンドの検索性能は高く、 ファイルを読み込むだけの処理とほぼ同じ時間で検索結果を返す。 テキストファイルでデータを保持しておいて、sed/awk/grep/sort などを駆使するシェルスクリプトを作れば、 たいていの検索システムは高速なものが簡単に構築できる。 ただしテキストフィルタはストリームを先頭から読むため、 処理時間はテータサイズにリニアに比例する。 ではデータの参照・更新の両方を必要とするシステムで、 どの程度のデータ量が現実的な処理速度が得られる上限なのだろうか。
本稿執筆時点で、筆者が利用できる計算機のうち、 クロック的にあまり速くないものを選んで、 実用的な速度で結果が得られると判断できる水準を、フィルタコマンドでの処理と、 SQLite を利用した処理を比較する形で探ってみた経過を示す。
実験はNetBSD7/amd64を用い以下の環境で行なった。
| CPU | Intel(R) Xeon(R) CPU E5-2620 v2 @ 2.10GHz |
|---|---|
| 主記憶 | 40960MB |
| ファイルシステム | NetBSD FFSv2 1GBのシーケンシャルWRITE約13秒(78.8MB/s) |
サンプルデータとして、日本郵便のサイトで入手できる 全国の郵便番号(CSV形式)の「読み仮名データの促音・拗音を小書きで表記するもの」 http://www.post.japanpost.jp/zipcode/dl/kogaki-zip.html を用いた(2016年1月取得)。
なお、以下の手順ではCSVファイルをそのままのデータ構造で利用し、 grep 等のフィルタコマンドで処理しやすいものとした。 そのため、RDBに格納するデータとしては冗長な構造となっている。
UTF-8変換し改行をLFのみにし、ダブルクォートを除去する。
nkf -d -w KEN_ALL.CSV | tr -d \" > ken.csv
12万件以上あるが約17MBとgrepにとってもSQLite3にとっても「小さい」ので、 12800バイトのダミーデータをすべての行の末尾に付ける。以下で用いる xxd コマンドは、バイナリ列を16進数文字列並びに変換するフィルタコマンドで vim に付属する。vim が導入されているシステムであれば xxd が利用できる。なお、xxd は続く章でも利用する。また gfold と記したのは GNU 版 fold コマンドで、GNU版が標準のシステムでは fold のみで起動する。
dd if=/dev/urandom | xxd -p | tr -d '\n' \
| gfold -b -w 12800 | sed `wc -l < ken.csv`q \
| paste -d, ken.csv - > large.csv
wc large.csv
123909 306851 1602346668 large.csv
続いて同じデータでSQLite3データベースを作成する。元の郵便番号 CSV は15カラムだが、末尾にランダム文字列格納するカラム random を加えて、全16カラムのテーブルとする。
sqlite3 zip.sq3
CREATE TABLE zip(x0401, zip5, zip7, prefkana, citykana, townkana,
pref, city, town, multi, koaza, chome, mcover, modify, modreason,
random);
作成したCSVファイル large.csv をインポートする。
.mode csv
.import large.csv zip
SELECT count(*) FROM zip;
123909
.quit
CSVファイル、データベースファイルのサイズは以下のようになった。
ls -l large.csv zip.sq3
-rw-r--r-- 1 yuuji wheel 1602346668 Jan 8 16:47 large.csv
-rw-r--r-- 1 yuuji wheel 1650753536 Jan 8 16:47 zip.sq3
ls -lh large.csv zip.sq3
-rw-r--r-- 1 yuuji wheel 1.5G Jan 8 16:47 large.csv
-rw-r--r-- 1 yuuji wheel 1.5G Jan 8 16:47 zip.sq3
重要な数値を表で示しておく。
| データ件数 | 123909 (約12万) |
|---|---|
| テキストファイルの大きさ | 1.5GB(1528MB) |
| SQLite3ファイルの大きさ | 1.5GB(1574MB) |
| 1件あたりの大きさ | 平均約12932バイト(12.6KB) |
まず読み取り性能について。 約12万件から町名検索で2件のみ該当する検索をしてみた。 ランダム文字列により結果出力が長大になるため、80桁で打ち切っている。
time grep '東泉町' large.csv | colrm 80
06204,998 ,9980013,ヤマガタケン,サカタシ,ヒガシイズミチョウ
40202,836 ,8360821,フクオカケン,オオムタシ,ヒガシイズミマチ
grep '東泉町' large.csv 1.36s user 2.32s system 99% cpu 3.680 total
colrm 80 0.00s user 0.00s system 0% cpu 3.678 total
grepの処理時間(total)は3.68秒であった。
続いてSQLite3。町名はtownカラムにあるのでLIKEで検索した。
time sqlite3 zip.sq3 \
"SELECT * FROM zip WHERE town LIKE '%東泉町%';" | colrm 80
06204|998 |9980013|ヤマガタケン|サカタシ|ヒガシイズミチョウ
40202|836 |8360821|フクオカケン|オオムタシ|ヒガシイズミマチ
sqlite3 zip.sq3 ... 0.49s user 1.43s system 99% cpu 1.918 total
colrm 80 0.00s user 0.00s system 0% cpu 1.917 total
SQLite3 の処理時間は1.92秒であった。「町名検索」といいつつ grep の方は行全体から検索するのでその分不利かもしれないが、 だからといってカラム位置を指定する複雑な正規表現を指定するのが有利とはいえない。 いずれにせよどちらも10の0乗オーダーの値であるので有意な差はないと言って よいだろう。今回はたまたまSQLite3の方が速かったが、 データがもうすこし小さい場合は、コマンドそのものの起動に時間を要しない grep の方が total 時間は小さくなる傾向にある。もし、正直に「町名のみで検索」 するならawkのようなフィールド指向のものを利用する。
time gawk -F, '$9 ~ /東泉町/{print $4,$5,$6}' large.csv
ヤマガタケン サカタシ ヒガシイズミチョウ
フクオカケン オオムタシ ヒガシイズミマチ
gawk -F, '$9 ~ ... 5.37s user 2.46s system 99% cpu 7.830 total
GNU awk のコマンドを gawk で示した。
もっとも、これが SQLite3 より遅いのは当然で、 データベースではフィールド分割のコストを CSV インポートのときに先払いしているのに対し、 awk では実行時に検索だけでなくフィールド分割もしているからである。 その都度分割している awk が8秒弱で処理できるのはむしろ褒めるべきと言ってよい。
続いてデータの更新が必要な場合を考える。
テキストファイルの更新は、更新結果をテンポラリファイルに落としてから 元ファイルに上書きコピーする必要があり、実用上はさらに 同時アクセスが来たときに壊れないようロックする手順が必要だがここでは省き、 GNU sed の -i オプションに上書き更新を任せる単純な例で示す。 行に「東泉町」が含まれる行の、最後のフィールドを "yes" に書き変える置換処理をする。
GNU sed のコマンドを gsed で示した。 GNU版が標準のシステムでは sed のみで起動する。
time gsed -i '/東泉町/s/,[^,]*$/,yes/' large.csv
gsed -i ... 6.86s user 15.21s system 33% cpu 1:05.43 total
1分強で、これはおおむねこの環境で 1.5GB のファイルをコピーする時間(25秒前後)の2倍強である。
いっぽう、SQLiteで同様の更新をする。町名に「東泉町」が含まれるなら random カラムを "yes" に更新する。
time sqlite3 zip.sq3 \
"UPDATE zip SET random='yes' WHERE town LIKE '%東泉町%';"
sqlite3 zip.sq3 ... 0.44s user 1.58s system 98% cpu 2.051 total
2秒強で完了した。この2秒はほとんどがWHEREの絞り込みの所要時間である。 町名検索を高速化させるようにインデックスを作成し、インデックスが活きるよう絞り込み条件を LIKE でなく = に変えると更新の速度も圧倒的に向上する。
sqlite3 zip.sq3 "CREATE INDEX zip_town ON zip(town);"
time sqlite3 zip.sq3 \
"UPDATE zip SET random='yes' WHERE town='東泉町';"
sqlite3 zip.sq3 ... 0.00s user 0.15s system 84% cpu 0.173 total
10倍程度速度が向上した。
ここまでの結果から分かることは、データの参照が主体で、 更新頻度のあまりないシステムであればどちらも応答速度に不満はないが、 フィルタコマンドを駆使したものの方が手軽さにおいて有利である。 逆に、更新もそこそこ発生するシステムであり、 データ量の単調増加が見込まれるものであればもはや RDBMS を使わない選択肢はない。
ここまでの議論に当てはまるような状況で、RDBMS の導入を検討する段階だとする。先述のように SQLite はコマンド1つですべて揃うので導入コストがほぼゼロに等しい。 気軽に利用できる選択肢だが、同時に次のような不安もある。
より本格的な RDBMS の導入が必要になったとき、それらは SQLite と違う文法なので覚えるのが二度手間になったりするのではないか。
新しいものを覚えるときにはこのような不安はつねに付きまとうが、 多くの場合これは杞憂に過ぎない。1つのものをマスターする頃には 他の類似システムのちょっとした違いもすぐに理解できるようになっている。 SQLに関しては「覚えるべき文法」が用途ごとに分かれている。 ユーザの概念やそれに対するデータベースのアクセス権限を制御する DCL(Data Control Language)は SQLite では必要ないので、仮に将来乗り換えるとしても覚え直しの必要がない。 また、データ構造を決める DDL(Data Definition Languege) と データの出し入れを行なう DML(Data Manipulation Language) に関しては標準SQLを意識して覚えればこれまた乗り換え先で困らない。 本書は、言語としての機能の小さなシェルスクリプトからの RDB 利用ということで、 SQLite の独自関数・機能を用いている部分があるが、 それらはHTMLへの変換機能など、データの入出力に関する部分である。 SQLite のみの機能はその旨記すようにした。
Perl/Ruby/Python/JavaScript といったスクリプト言語でのプログラミング既に慣れている場合、 リストやハッシュを利用したデータ構造に当てはめる術に長けていることだろう。 それらは格納対象を階層的・木構造的に表現することができる。それに慣れたあとで RDB の表構造を見ると、 反射的に冗長感と、再帰との相性の悪さを感じるのではないだろうか。 さらに正規化や結合操作の説明などに壁を感じて、いわゆる NoSQL と呼ばれるデータベースエンジンに目が向くかもしれない。
ここで、本稿の標題どおり「シェルスクリプト」の視点から考える。 結論からいうとシェルスクリプトと相性のよいのは NoSQL より RDBMS の方である。 それはどの RDBMS も、CUI のコマンドラインインタフェースを用意しており、 標準入出力の利用で完全な SQL 問い合わせが可能だからである。さらに、 CUI ツールでデータ更新を行なう場合も、データベース更新時の排他処理等が もれなく行なわれるため、シェルスクリプトではデータの完全性保証を 一切心配しなくてよい。さらに、標準SQLがあることからも分かるように 問い合わせ言語が規格化されており、これから十年以上先でも通用する。 いわゆる NoSQL エンジンは、高水準言語からの利用を想定しているものがほとんどで 種々の言語から利用するAPIがライブラリとして供給されるものである。
導入前の RDBMS に対する不安の一つに、 データファイルがテキストファイルでないことがある。 テキストファイルなら less などでデータを直接見られる安心感がある。 JSONやYAMLに代表される構造化されたテキストフォーマットはその点で有利だが、 NoSQL の問題と同様、以下のような点がシェルスクリプトからの利用の障害となる。
テキストファイルであることはメリットでもありデメリットでもある。 どちらを採用するかで迷った場合、 データ更新時の排他処理もあまり考慮する必要がなく、 サイズが十分に小さく同時更新に対する排他処理などを考えなくてよいなら構造化テキストフォーマット、 そうでない場合は SQLite の方が総合的なメリットが大きいと言える。
yuuji@koeki-u.ac.jp