授業/H18/情報処理/13

大量データの一括処理：SQLiteによるリレーショナルデータベース入門 †

【教科書：44-48p参照】

　前回授業ではDNAデータベースを検索して、自分が必要とするデータを取り出し（検索・ダウンロード）、加工し（アラインメント）、目的とする解析（系統解析）を行いました。授業後の意見調査の結果でも、よく理解できたという人が多かったようです。理解しやすかったのは、皆さんが、インターネットを使って何かを検索し、データを取り出すという作業に慣れているせいだと思います。

　ところで、授業で使ったDNAデータベースやYahoo、Amazonなどのサイトで、キーワードを入力して検索をかけた時、どうやってデータが取り出されているか想像したことはあるでしょうか？DNAデータベースだと、数千万件のデータ（配列にすると数百億！）、ちょっと実感がわかないほど大量のデータが処理されていますよね。

　データの量が多くなるほど、目的のものを取り出すのは、なかなか大変なはずなんですが、やっぱり実感はわきませんよね。では、もう少し、具体的な話しをしましょう。今、あなたの手元には、先週ダウンロードしたヒト・ゴリラ・チンパンジーのミトコンドリアDNAの全長の配列５つがFASTA形式ファイルとして保存されています。１件１件のデータは1万6千塩基という、けっこう大きなデータでした。このファイルの中から、ATTATAATというキー配列から始まる500bpぐらいだけを抜き出したいと考えています。どうやりますか？

　すでに皆さんが習得している方法だと、テキストエディタの検査機能を使うという手がありますね。K2EditorでFASTA形式ファイルを開いて、「検索」を選び、検索文字列にATTATAATを指定いて検索する。そうすると、画面上にATTATAATから始まる部分が強調表示されます。そこから500bpぐらいをマウスを動かして選択して、コピーして、別のウィンドウを開いてペーストして、また前の画面にもどって、この配列のアクセッション番号まで画面を動かして戻って、アクセッション番号を選択してコピーして、。。。。あーめんどくさい。

　テキストエディタでも、ワープロでも、検索はすごく便利な機能だけれど、自分が必要としている部分だけを取りだそうとすると、コピー・ペーストとも、かなり面倒な操作が必要になります（実感がわかない人は、上に書いた作業をやってみてください。途中で嫌になること請け合いです）。

　そこで登場するのがデータベースです。先に挙げたDNAデータベースでも、Yahooでも、Amazonでも、いずれもリレーショナルデータベースというシステムを利用することで、ものすごく大量のデータから、一瞬にして必要な情報を検索し、抽出し、並び替えるという処理をおこなっています。この、

というのが、テキストエディタやワープロには簡単にできないことです。データベースは、こういう面倒な処理を、大量のデータについて簡単に行えるという、大量データ処理に関しては、現時点における標準的方法といって良い物です。先日の調査結果によると、多くの皆さんが学生生活（＋研究生活）において、大量のデータを処理することになると考えているようです。そんなとき、データの抽出で、１件１件コピペするのも一つの手ですが、データベースシステムの扱いに慣れていれば、時間も短縮できるし、データの整理も簡単にできます。。

データベースという言葉は、保存されたデータのまとまりそのものを指すことも、データを管理するシステムのことを指すこともあります。この授業でも特に厳密な定義はしません。授業で使うのは、リレーショナルデータベースというシステム（RDBS: Relational Data Base System）を無料で簡単に構築できるソフトウェア、SQLiteです。このソフトウェアを使って、実際のデータからデータベースを構築し、必要なデータを抽出することを学びます。その過程でSQL言語という、データベースとお話するための言葉を学びます。最後に、DNAデータベースなどがどうやってインターネット経由での検索を可能にしているのかを体験するために、、仮想ウェブサーバのXitamiとスクリプト言語であるPHPを使って、ローカルホスト(localhost）上にネットワークデータベースを立ち上げます。この技術を覚えると、自分の作ったデータベースを全世界に公開したり、逆に、全世界の人からデータを収集して新たなデータベースを構築するなど、いろんなことに使えます。

関連リンク †

• SQLite http://www.sqlite.org/
  • PHP4からSQLiteを呼び出す ダイナミックライブラリ http://pecl4win.php.net/list.php/4_4
    • php_sqlite.dll
• SQLite　SQL日本語リファレンス：http://www.net-newbie.com/sqlite/lang.html
  • コマンドチュートリアル（日本語）：http://www.net-newbie.com/sqlite/sqlite.html
• rakutoネット：　SQLiteの使用例と解説：http://study.rakuto.net/php/command/
• PHPマニュアル　http://jp.php.net/manual/ja/index.php　PHPの使い方がとてもよく分かる。プログラム例が豊富なので、とても助かる！

• SQLite CC http://www.takke.jp/soft/sqliteccj.html

リレーショナルデータベースの実例 †

　では、具体例をつかって説明してゆきましょう。「リレーショナル」という言葉を説明し出すと長くなるので、まず、実例をお見せします。押さえてほしいポイントは次の３点です。

• １行ごとにデータを並べた表を２つ（以上）作ります
• ２つ（以上）の表全てで、１つのカラム（列）には同じデータが入っています
• 共通するデータで全ての表を結びつけ、１つの表のようにして扱うことができます

簡単な例として、サークルの名簿と会費納入状況のデータベースを考えてみましょう。説明のためにはカラム名もかな・漢字表示をする方が良いのでしょうが、ここでは、キーボードからの入力の混乱を避けるために、カラムの名前は全て半角英数文字で表記します。

想定しているのは、次のような表です。

こういう簡単なデータなら、皆さんならどうやって管理しますか？

そうですね、エクセルで管理するという方法があります。では、ちょっとやってみましょう。

この表をエクセルで開いて、8月分の会費を集めたときは、2006_8という列（カラム）を手作業で一つ追加します。また、会費の未納状況を見るときには、納入状況を一つ一つ目で確かめます。納入された会費を集計するときには、行の右端でsumをとって、また、表の一番下でもsumをとります。e-mailもついでに管理しておこうとおもうと、列（カラム）を一つ追加するので、右のほうがますます見にくくなってしまいます。

しかも、この例では３件しかデータを示していませんが、サークルの会費なので、前の年のデータも有るはずです。なので、ワークシートはとても大きく、見にくくなりますね

こんな感じ：
　　　　住所等のデータ     2003年         2004年      2005年     2006年
名前（2003年入会）　　　●●●●●●●●●●●●●●●●●●●●
名前（2004年入会）　　　　　　　　　　　　●●●●●●●●●●●●●●●●
名前（2005年入会）　　　　　　　　　　　　　　　　　　●●●●●●●●●●
名前（2006年入会）　　　　　　　　　　　　　　　　　　　　　　　●●●●●

どうです？2006年の入会者にとっては、左の方の空白のセルがムダですよね。それに、毎年毎年、このワークシートはどんどん右側に大きく伸びてゆくので、目で見て、手でカラムを追加するというのが大変だと思えてきたでしょうか？

では、手と目に大変なこの作業を、頭とリレーショナルデータベースを使って解決してみましょう。

この表をリレーショナルデータベースに作り直す場合、まず、データの構造を変更します。この表には、

• 会員の住所等個人情報
• 会員の会費納入状況

という２つの異なるデータが混在しているので、データが扱いにくくなっています。そこで、この２つを区別します。また、どの年のどの月の会費を払うかは、会員の入会時期によっても異なるデータなので、会費納入のデータは１行ずつに、会員番号と納入月を追加して表すことにします。そうすると、データの構造は、次のようになります。さらに、会費は年ごとに集計することもあるでしょうから、日付は年と月の２つに分けておく方が使いやすくなります。このように、データ構造をシンプルに使いやすくすることを、データの正規化といいます。

• 会員の住所等個人情報

  number	namae	yubin	jusho	tel	gakunen	nyukainen	nyukaituki
  1	綿野泰行	263-8522	千葉市稲毛区弥生町1-33	043-290-2874	3	2004	04
  2	朝川守毅	263-8522	千葉市稲毛区緑が丘	043-290-2874	2	2005	04
  3	梶田　忠	263-8522	千葉市稲毛区小仲台	043-290-2874	1	2006	04

• 会員の会費納入状況

  number	nen	tuki	kaihi
  1	2006	04	500
  1	2006	05	500
  1	2006	06	500
  1	2006	07	500
  2	2006	04	500
  2	2006	05	500
  2	2006	06	500
  2	2006	07	500
  3	2006	06	500

データの構造がかなりすっきりしたのが分かるでしょうか？「でも、会員番号と納入月だけ出されても、誰のデータかわかんない。。。」という疑問が出そうですよね。それを解決するのが「リレーショナルデータベース」の「リレーション」（関連づけ）という機能です。

上の２つの表は、「会員番号でリレーションをとる（関連づける）」ことによって、

という形で扱うことができるのです。

SQLiteの準備 †

　まず、SQLiteを使う準備をします。前回ダウンロードが成功していれば、デスクトップにsqlite-2.8.17というフォルダができているはずです。これをマイドキュメントにコピーして、フォルダ名を

sqlite

に変更してください。

もう一つ、前回ダウンロードしたファイル、php_sqlite.dllがありましたよね。ダウンロードしていない人は、上のリンクからダウンロードしてください。ダウンロードしたファイルはデスクトップにできているでしょうから、それを、Z:\php\dll\　というフォルダにコピーしてください。（このファイルはXitamiからPHP経由でSQLiteを操作するためのファイルなので、SQLiteの使用には、通常は必要ありません。）

コマンドラインからのSQL入力 †

さあ。では、実際の操作をやってみましょう。復習課題では、ここから先と同様の操作を課題用のデータファイルを使ってやってもらいますので、手順を十分に理解してください。

sqliteの使用準備 †

1. まず、コマンドプロンプトを起動します。ウィンドウズのスタートメニューから、「すべてのプログラム／アクセサリ／コマンドプロンプト」を選んで下さい。そうすると黒いウィンドウが開き、Rで使ったようなプロンプトが表示されます。これはWindowsXPというOSを動かすもとになっているDOSのコマンドプロンプト（キーボードから命令を入力する処理系）です。
2. まず最初に下のように入力して、カレントディレクトリ(現在、自分が閲覧しているディレクトリのこと）をZ:\sqlite に変更してください。そうすると、プロンプトのところが、Z:\sqlite> に変わります。

   > cd z:\sqlite
   Z:\sqlite>

   そうしたら、sqliteという名前のプログラムを立ち上げます。このプログラムでデータベースの作成、テーブルの作成、編集、などができます。

データベース ex1 の作成　（作成済みのデータベースの場合、オープン） †

Z:\sqlite>sqlite ex1.db

と入力してみましょう。sqlite ＜データベース名＞で、新しいデータベースを作ったり、既存のデータベースに接続することができます。上の命令を入力すると、画面は次のようにかわります。

(注意：このとき、ex1.dbというデータベースは、z:\sqlite\というフォルダの中にできています）

テーブル tbl1の作成 †

ex1.dbという名前のデータベースを作ったら、その中にテーブルを２つ作ります。その場合、

create table ＜テーブル名＞ (カラム名, カラム名);

という命令を使います。今の場合は、次のように入力します。

Z:\sqlite>sqlite ex1.db
   SQLite version 2.8.17
   Enter ".help" for instructions
   sqlite> create table tbl1 (number, namae, yubin, jusho, tel, gakunen, nyukainen, nyukaituki);

この、テーブル作成の部分が、SQLiteなどのコマンドラインを利用するデータベースで最も面倒な部分です。命令はけっこう長いし、入力まちがいがあると、うまく動きません。でも、間違ってしまった場合、↑キーでもう一度命令を呼び出して編集できますし、作成したテーブルを削除してもう一度やり直したい場合は、

sqlite> drop table tbl1;

とやれば先ほど作ったtbl1というテーブルを消すことができます。

テーブル tbl1 へのデータの一括登録 †

sqlite> copy tbl1 from 'z:\sqlite\ex1.txt';
sqlite > select * from tbl1;

同様に次のコマンドでtbl2を作成し、データを追加します。

sqlite> create table tbl2 (number, nen, tuki, kaihi);
sqlite> copy tbl2 from 'z:\sqlite\ex2.txt';
sqlite> select * from tbl2;

キーを用いた２つのテーブルの連結表示 †

２つのテーブルにデータが入ったかどうかを、両者に共通するnumberというキーで結びつけて表示してみましょう。

sqlite> .header on
sqlite> select * from tbl1, tbl2 where tbl1.number=tbl2.number; 

このコマンドでは、全てのカラムが表示されるのでみずらいですね。表示したいカラムを選んで、しかも、年ごとに支払金額を集計するには、次のような命令を与えます。

• sqlite> select namae, nen, sum(kaihi) from tbl1, tbl2 where tbl1.number=tbl2.number group by nen, namae;

あるいは

• sqlite> select namae, nen, sum(kaihi) from tbl1 left outer join tbl2 on tbl1.number = tbl2.number group by nen, namae;

  namae|nen|sum(kaihi)
  梶田　忠|2006|500
  朝川守毅|2006|2000
  綿野泰行|2006|2000

SQLiteの終了 †

sqlite> .q

上のように入力すれば、終了できます。

補足説明 †

「こういうコマンドを考えるのは面倒くさいや。上の表をエクセルで、こんな風にして管理したって、同じことでしょ？」

はい。その通りです。 でも、次の２つの問題点があります。

1. このテーブルのほとんどは、同じデータの繰り返しになっているので、ムダが多い。
2. 住所などのデータに変更があったとき、関係のある全てのデータを変更しなければならないので、ムダが多い

ということです。こういうデータ構造でも問題はないのですが、あまりにもムダが多いので、２つに分けるのが良いということです。

SQLiteの短所：インターフェースの使いにくさ †

　大量データの扱いに優れており、しかも、無料で使えるSQLiteですが、コンピュータに慣れていない人には非常に使いにくいだろうなと思う点があります。それは、データを追加したり、編集したりするためのインターフェースの貧弱さです。sqliteを立ち上げて、データを一つ追加するコマンドを入力すると、

sqlite> insert into tbl1 values('3', '梶田　忠', '263-8522', '千葉市稲毛区小仲台', '043-290-2874', '1', '200606');

などという、長く、しかも、シングルクォートがたくさんあってタイプミスを起こしやすそうな命令文を入力しなければなりません。検索・抽出がいかに素早くできても、データの追加にいちいちこういう複雑な命令文を考えなければならないのでは、困ってしまいます。

　実は、リレーショナルデータベースの説明に使うことをはじめに予定していたPostgreSQLでは、データ管理を簡単に行える洗練されたフリーソフトがあります。PpostgreSQLと同じくらい人気のあるMySQLでも、同様に使いやすい管理用のフリーソフトがあります。ところが、SQLiteでは、管理ソフトはあるにはあるのですが、利用できる漢字コードの問題、SQLiteのバージョンの問題など、授業で使用するにはいくつか適当ではない点があって、今回は利用しません（自分でSQLiteを使ってみようという人は、 SQLite3とSQLiteControlCenterの組み合わせを試してみると良いと思います）。

えーっ！？毎回毎回 insert tbl1 ナンタラカンタラ　なんてタイプするなんてムリっ！
編集するのにも update tbl1 ナンタラカンタラ　なんていちいちタイプしなければならないなんて、使えなさすぎっ！
そういう面倒なことをしなくちゃならないのだったら、SQLiteは実用的ではないのでは？

という非難の声が聞こえてきそうです。本当にSQLiteは使い物にならないのでしょうか？

皆さんがこれから出会うデータ処理の状況を考えてみて、データベースを常に立ち上げて管理する場面というのは、それほど沢山は無いと思います。それよりも、もっと多いのは、上の課題でやったように、複数の異なる表を組み合わせて、データを検索、抽出するという作業です。このような作業では、エクセル、テキストエディタ、SQLiteの組み合わせで、かなり複雑な処理を、ごく簡単に行うことができます。

SQLite、エクセル、テキストエディタの合わせ技 †

学生の成績一覧表の作成 †

　今手元に、国語、数学、英語の先生から提出された成績表があります。

• kokugo.txt, sugaku.txt, eigo.txt これらの成績表はタブ区切りのテキストファイルになっており、学籍番号と成績がそれぞれ書かれています。また、名簿ファイル：meibo.txtには、学籍番号、名前、よみがなが書かれています。学生毎に選択している科目が違うので、３人の先生から集めた情報を１つにまとめるのは大変そうです。

さて、この４つの情報を統合して、学生ごとの成績を一覧表示する場合を考えてみましょう。こういうとき、SQLite、エクセル、テキストエディタの組み合わせで、次のような手順で操作を行えば、４つの表から目的のデータを抜き出すことができます。

1. まず、成績ファイルを１つのテーブルにまとめます。（注：３つをそれぞれ別の表として扱っても良いのですが、この授業ではなるべく簡単な命令文を使いたいので、簡単にできることはエクセルで済ませてしまいます。３つをそれぞれ別の表として扱う方法は、次の授業のページで説明します。データベースの使い方としては、エクセルを使わずに処理する方が洗練されていますが、この授業では、エクセルの復習もしたいので、こういう処理を行います。）
   • エクセルで上の３つのファイルをそれぞれ開いて、新しい列に科目名を加えます。もし、英語のデータが

       　　06S4061   42
       　　06S4028   44

     なら、

       　　06S4061   42   eigo
       　　06S4028   44   eigo
     にするわけです。この作業を国語、数学、英語の３教科のデータについて行います。

     • （学籍番号ごとに集計するだけなら、エクセルでもできますよね（だいぶ前にやりました））
2. 次に、上で１つのファイルにまとめた３教科分のデータを、タブ区切りのテキストファイルに保存します。K2Editorで新しい書類を作って、そこに、上の３つのファイルの内容を、コピーペーストします。

      06S4061   42   eigo
      .
      .
      06S4063   55   sugaku
      .
      .
      06S4066   66   kokugo
      .
      .

   のようなデータをK2Editorで作って、これを、ex3.txtというファイル名で、Z:\sqlite\に保存します。
3. 次にSQLiteを立ち上げて、テーブルを２つ作ります。
   • １つは、先ほど作ったex3.txtの内容を入れるテーブルで、scoreという名前にします。このテーブルは

           gakuseki    score    kamoku

     という列からできています。のデータベースを作る命令は
   • もう１つは、meibo.txtの内容を納めるテーブルで、meiboという名前にします。このテーブルは

           gakuseki   namae   yomi

     という列からできています。
   • 実際の操作は次のようにします。コマンドラインから、SQLiteを起動して, ex3.dbという名前のデータベースを作ります。

     Z:\sqlite>sqlite ex3.db

   • 次に、上で考えた２つのテーブルを作ります。

     sqlite> create table score (gakuseki, score, kamoku);
     sqlite> create table meibo (gakuseki, namae, yomi);

4. ２つのテーブルに先ほど作ったex3.txtのデータと、meibo.txtのデータをそれぞれ読み込んで、gakusekiをキーにして関連づければ、それぞれの、氏名と科目の点数合計などを表示することがきます。
   • まず、それぞれのテーブルにデータを読み込みます。

     sqlite> copy score from 'ex3.txt';
     sqlite> copy meibo from 'meibo.txt';

   • これでデータが入ったはずです。確かめるには、select命令を使います

     sqlite> select * from score;
     sqlite> select * from meibo;

   • それでは、２つのテーブルをgakuseiで関連づけてみましょう。
     • sqlite> select * from meibo left outer join score on meibo.gakuseki = score.gakuseki order by yomi;

命令文を知りたい時は †

　ここまでで説明した命令文についてもっと知りたいときは、

• SQLite　SQL日本語リファレンス：http://www.net-newbie.com/sqlite/lang.html

を見てください。

第13回授業の課題 †

全ての課題は、http://bean.bio.chiba-u.jp/joho18/ に、「自分のID/13」という新しいページを作成し、これまでの提出例にならって、分かりやすく書き込むこと。あまりに読みにくい回答は減点します。ページの書式を変更するには、編集ページで、「テキスト整形のルールを表示する」をクリックすると、（長くて読みづらいですが）ページの書式設定のルールが表示されます。Pukiwikiでは、専用の書式指定で書き込みすると、サーバ側のプログラムがそれをHTMLタグに翻訳して、クライアントに送信してくれるというわけです。

• 提出期限：7月19日水曜正午（下記課題全て）
  • 提出期限を過ぎたものでも、点数を半分にするなどで評価しています。

課題１．意見調査 †

　下の囲みの中にあるアンケートをコピー・ペーストして、「回答：」の後に答えを書き込むこと。

*第13回授業アンケート
**氏名：
**課題への回答
-今日（7月13日）の授業の進み方は？（はやい、丁度いい、おそい）
--回答：
-今日の授業の難しさはどう感じましたか（簡単すぎ　簡単　丁度いい　難しい　難しすぎ）：
--回答：
-難しいと答えた人は、特にどの点が難しかったですか？：
--回答：
-今日の授業は（よく分かった　分かった　分からなかった）：
--回答：
-分からないと答えた人は、特にどの点が分からなかったですか？：
--回答：
-今日の講義で理解できなかった用語があったら挙げてください：
--回答：
-これまでの講義を振り返って、でもう一度説明して欲しいテーマ、や追加説明が欲しい演習などを挙げてください（複数可）。
--回答：

復習課題：リレーショナルデータベースについて †

　このページには先ほどの３科目のデータに加えて理科と社会のデータが添付されています。rika.txt, shakai.txt　この２つのファイルをダウンロードして、上と同様の処理を行いなさい。

• 解説：

  ・２つのファイルをダウンロードして、エクセルに読み込み、rikaとshakaiというカラムを追加します
  ・カラムを追加したデータをK2Editorの新規書類にコピーして、ex4.txt（何でもよい）という名前でセーブします。
  ・SQLiteを立ち上げて、国語、数学、英語の成績が入っている ex3.db というデータベースを開きます
     Z:\sqlite>sqlite ex3.db
  ・ex3.dbに理科と社会のデータを追加します。
    ＜上でやったcopyで始まる命令文を使う＞
  ＊＊ここまでで準備完了

• 課題：（全部で４題あります）

1. 上で準備したデータベースから、データを抽出します。データはあいうえお順に並べ替え、名前（namae）, 科目（kamoku）, 点数（score）のみ、最初の10件を表示させ、レポート提出ページにテキストとして書き込みなさい。（注：以下の提出課題全て、画像では貼り付けないこと。コマンドプロンプトで範囲選択してからコピー・ペーストしてください。行頭に半角空白を入れると、薄緑色のボックスの中に表示できます。）
   • 注：7月18日、上の緑色の部分を追記しました。最初の10件以外を貼り付けてあっても、構いません。

     並び替えと10件だけ表示
      　order by yomi limit 10;
     を最後に追加
     例：　> select * from meibo, score where meibo.gakuseki = score.gakuseki order by yomi limit 10;
       　　のようにして使う

2. meiboとscoreという２つのテーブルを単純連結して、あいうえお順に並び替え、学籍番号(gakuseki), 名前（namae）, 科目（kamoku）, 点数（score）最初の10件だけをテキストとしてレポート提出ページに書き込みなさい。

   よく似た例：　sqlite> select * from meibo, score where meibo.gakuseki = score.gakuseki; 

3. meiboとscoreの２つを単純連結して、06S4053という学籍番号を持つ人の学籍番号、氏名、5科目の点数の順で表示させ、結果をレポート提出ページに書き込みなさい。特定の学籍番号の人のデータを表示させるには、　where score.gakuseki = '06S4053' を使います。whereの後の条件が２つ以上ある場合は、andでつなぎます。

   例：　.....   where meibo.gakuseki = score.gakuseki and score.gakuseki = '06S4041'

4. 全ての科目の合計点数を計算し、点数の高い順番に並び替えて、最初の10件を学籍番号、名前、合計点数の順に表示させ、レポートページにかき込みなさい。合計点数の計算には、授業の最初の例で使った group by を使います。