授業/H22/情報処理/13

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

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

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

自分の実験で沢山のデータを処理する場合も、データの量が多くなるほど目的のものを取り出すのが大変になってきて、１つ１つ手作業で処理するということは難しくなる。そんなとき役に立ってくれるのが「データベース」だ。先週使ったDNAデータベースでも、Yahooでも、Amazonでも、いずれもリレーショナルデータベースというシステムを利用することで、塩基配列、生物情報、文献情報などのものすごく大量のデータから、一瞬にして必要な情報を検索し、抽出し、並び替えるという処理をおこなっている。この、

と

というのが、データベースには簡単にできて、テキストエディタやワープロには簡単にできないことだ。
おそらく、今現在の時点でデータベースを使うことはあまりないだろうが、この技術は知っておいて、絶対に損はない。将来的に自由に使えるようになれば、心と時間に余裕が生じるだろう。

せっかく情報処理を勉強するのだから、こういう便利な方法があるということを知っておかなければ勿体ないので、今日の授業の前半部分ではとにかく、データベースを使って簡単なデータ処理に挑戦してみる。

第13回授業の獲得目標： †

• 1. リレーショナルデータベース（SQLite）をインストールする
• 2. コマンドプロンプトの利用法と、コンピュータにおけるディレクトリ構造を修得する
• 3. SQLという言語を使って、データベースとお話しする
  • SQLiteを使って、情報の検索・抽出・並び替えを行う方法を習得する
• 4. データベースにおけるテーブルの概念を理解する
• 5. SQLを用いて、複数のデータテーブルを連結して、必要なデータだけを抽出する方法に挑戦する

1. リレーショナルデータベース（SQLite）をインストールする †

データベースという言葉は、保存されたデータのまとまりそのものを指すことも、データを管理するシステムのことを指すこともある。この授業でも特に厳密な定義はしない。授業で使うのは、リレーショナルデータベースというシステム（RDBS: Relational Data Base System）を無料で簡単に構築できるソフトウェア、SQLiteだ。このソフトウェアを使って、実際のデータからデータベースを構築し、必要なデータを検索・抽出・並び替えすることを学ぶ。その過程でSQL言語という、データベースとお話するための言葉を使う。

まずはリレーショナルデータベースシステムを利用するために必要なフリーソフトウェアSQLiteをインストールする。これは予習課題でやって貰っているはずだ。なお、皆さんが大学で使っているコンピュータにはAccessというデータベースソフトも入っているが、自分のパソコンでも無料で使えるという点を重視して、この授業ではSQLiteを使うことにした。SQLiteはダウンロードした実行ファイルをクリックするだけで、データベースを扱うことができるし、データベースのデータ本体が１つのファイルに保存されるので、コピーやバックアップが簡単にできるという特徴を持っている。

SQLiteのインストール †

では、以下のリンクから、SQLiteの実行形式ファイルをダウンロードしよう。

• SQLite3 WindowsXP用実行ファイル http://www.sqlite.org/sqlite-3_6_16.zip
  ダウンロードが成功すればデスクトップにあるsqlite-3_6_16というフォルダの中にsqlite3.exeというファイルができているはず。

  次に、

  マイドキュメント　フォルダの下に
   sqlite  という名前のフォルダを作って
    sqlite3.exe   をその中に移動する。

  ※この操作は別にやらなくてもSQLiteは動くが、後々データベースファイルがどこにあるのか探すのが面倒なので、あらかじめ入れ場所を作った。
  今後、SQLiteを使うときには、

  データやデータベースは全て、　マイドキュメント/sqlite　というフォルダに入れて使うことにする

• これでインストールは完了した。

SQLiteについて自習する場合は、以下のサイトを参照してみよう †

• SQLite http://www.sqlite.org/
  • マニュアルの日本語訳：　http://www.3rd-impact.net/Document/SQLite/Translation/Current/
  • SQLite3 Windows用実行ファイル http://www.sqlite.org/sqlite-3_4_0.zip
• 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/
  • SQLite CC http://www.takke.jp/soft/sqliteccj.html
    • 日本語表示も可能なSQLite用GUI

2. コマンドプロンプトの利用法と、コンピュータにおけるディレクトリ構造を修得する †

さて、いよいよSQLiteを使ってデータベースを操作するのだが、このとき必要になるのが、

コマンドプロンプト

というものだ。

「すべてのプログラム／アクセサリ／コマンドプロンプト」を起動 †

まずコマンドプロンプトというソフトウェアを起動しよう。

ウィンドウズのスタートメニューから、「すべてのプログラム／アクセサリ／コマンドプロンプト」を選ぶ。

そうすると黒いウィンドウが開き、Rで使ったようなプロンプト

>

が表示される。これはWindowsXPというOS（オペレーションシステムという、コンピュータの基本ソフト）に、キーボードから命令を入力する処理系だ。普段、皆さんが使っている処理は、「マウスをクリックすることでコンピュータに命令を与えている」わけだが、コマンドプロンプトでは、「文字を入力することで、コンピュータに命令を与える」。試しに下の囲みのように

>notepad

と入力すると、WindowsXPの「メモ帳」が起動する。 つまり、文字を入力することでコンピュータに命令を与えたわけだ。
実は、マウスをクリックしてコンピュータに命令を与えるインターフェース（ヒトと機会を繋ぐもの）は、コンピュータのハードウェアやOSに依存する部分が多いので、ソフトウェアを開発するプログラミングがそれだけ面倒になる。でも、今見ているコメンドプロンプトは、文字で命令を与えるので、そういう面倒な部分のプログラミングが必要ない。そのため、コマンドプロンプトを採用する方が、ソフトウェアの開発にかかる手間が減るわけだ。

演習：　コマンドプロンプトで命令を入力して、フォルダ（または、ディレクトリと言う）の中身を表示してみよう
> dir

3.データベースとお話しよう：　 SQLという言語の利用 †

では、今から、SQLiteを使って、データベースとお話ししてみる。
「お話し」などという言い方をすると、なんだか曖昧に聞こえるかもしれないが、これからやるのは、

「データベースから生物学科の先生の名前だけを抜き出して下さい」
　とか、
「データベースから教授の名前だけを抜き出して下さい」

などという命令を、コンピュータが理解できるように単純化した英語でデータベースに伝えることだ。

サンプルデータベースの準備 †

• meibo（平成21年時点の名簿）
  ダウンロードしたファイルを、上で作ったsqliteというフォルダの中に入れておこう。
  ファイルエクスプローラーを立ち上げて、マウスを使って、 meiboを

  マイドキュメントの下のsqliteというフォルダ

  に入れておこう。

SQLite3の起動~ †

1. カレントディレクトリの移動：　
   コマンドプロンプトで、自分が今いる閲覧している場所の中を、もう一度見てみよう。

   > dir

   そうすると、ここには先ほどダウンロードした、meiboというデータベースファイルは入っていないことが分かる。
   そこで、まず、自分がコマンドプロンプトから閲覧しているディレクトリを 次の命令を使って、Z:\sqlite に変更する。そうすると、プロンプトのところが、Z:\sqlite> に変わる。

   > cd z:\sqlite
   Z:\sqlite>

   ついでに、dirと打って、sqlite3.exeとmeiboというファイルが入っていることを確認しておこう。

    Z:\sqlite>dir

   （もし、sqlite3.exeとmeiboの２つのファイルが中ったら、ファイルエクスプローラーを使って再度移動させること）
2. sqlite3を使ってmeiboデータベースを開く：
   ここまで準備ができたら、

   >sqlite3  meibo

   と入力すると、SQLiteが起動し、先ほどダウンロードしたmeiboというデータベースファイルが開かれる

２つのファイルの役割を再確認 †

1. sqlite3.exe　　データベースシステム（プログラム）の本体
2. meibo　テスト用のサンプルデータベースファイル（この演習用に作成したもの）
　　　　（今はとりあえず、こちらで準備したデータベースファイルを使う。ファイルの作成の仕方は、後で学ぶ）

SQLiteを使って、データベースとお話しする †

ここから先は、簡単な英語を使って、データベースとお話しする。これから使う英語の意味をまとめておくと、

select  　　：選び出して表示しなさい
from  〜　　：　〜というデータベースから
　where　　：　　・・・という条件で

というものだ。

命令の形式さえ守っていれば、コンピュータは内容を分かってくれるので
変更できるところは自由に変更して楽しんでみよう

• 使用例：　selectの簡単な文章：

  書式：　select ＜カラム名,,＞　from ＜テーブル名,,＞ where ＜カラム名＞='＜値＞' order by ＜カラム名＞;

  注意： 命令文の最後は必ず ;（セミコロン）で終わらなければならない。

  もしも、

  sqlite> select * from rigaku

  とセミコロンを抜かして入力してしまうと

    ...>

  と表示される。そんなときは、セミコロンを1つだけ入力すると、命令が実行される。

     ...>；

• では、次の命令を入力して、データベースとお話ししてみよう。また、演習問題を解くための命令文を、自分で考えてみよう。
  • select * from rigaku;　：　「rigakuというテーブルから全てのレコード(注： *は全てのレコードを表す）表示しなさい
  • select * from rigaku order by shoku;　　：「rigakuというテーブルから全てのレコードを、shokuで並び替えて表示」
  • select shimei, shoku from rigaku;　：「testというテーブルから、nameとpositionだけを表示しなさい」
  • select shimei, shoku from rigaku order by gakka, shoku; : 「rigakuというテーブルから、shimeiとshokuを、gakkaとshokuで並び替えて表示」
  • select shimei, shoku from rigaku where gakka='生物学';　：　「rigakuというテーブルから、shimeiとshokuを、gakkaが生物学のものだけを表示」
  • select * from rigaku limit 10; : 「rigakuというテーブルから、全ての項目を、10件だけ表示」

• なお、今使っている meiboというデータベースの構造は、次のようになっている。 　meio データベースの構造：

  含まれるテーブル：　rigaku
  テーブル rigaku　のカラム　shimei,　heya,　gakka,　kouza,　shoku,　kenmu,　kenmu_flag
  
  このように、データベースの中にどういうテーブルが入っているか知りたいときは、
  sqlite> .tables
  と入力すると、　rigaku　と表示される。また、rigakuの構造を知りたいときは、
  sqlite> .schema rigaku
  と入力すると、rigakuというテーブルのカラム名（列の名前）が（　）の中に表示される。
  CREATE TABLE rigaku (shimei,heya,gakka,kouza,shoku,kenmu,kenmu_flag);

• では、次の演習問題をやってみよう

  1. 千葉大学理学部生物学科の教員全員を、分野（kouza）ごとに並び替えてで選び出す
  2. 生物学科の教員の氏名と職名と講座名を、講座ごとに選び出し、職名ごとに並べる
  3. 理学部の教授の氏名だけを10件だけ表示させる

データベースにおけるテーブルの概念を理解する †

いま使っている meiboというデータベースには、rigakuというテーブルが入っていることは上でも述べた。
ここで、

テーブル　というのは　表　のこと

であることを、理解しておこう。先ほどの rigaku というテーブルは、エクセルを使うと、次のようなテーブルとして表せる。

また、

１つのデータベースファイルの中には、複数のテーブルをしまっておける
（エクセルで、複数のワークシートを使うようなもの）

では、エクセルを使わずに、SQLiteを使ってテーブルを操作することの利点は何か？それは、

大量のデータを含んだ、　複数のテーブルを、　１つの命令で簡単に処理できること

だろう。何千、何万ものデータが入ったテーブルを、エクセルで開いて、並べ替えたり、データを抽出したりするのがどれだけ大変なことか、想像してみるといいだろう。

SQLiteを使って、２つ以上のテーブルを連結して、データを抽出する方法に挑戦！！ †

これから実際にSQLiteを使って何ができるかを体験してもらう。目標とするのは、次のようなケースを簡単にSQLiteで簡単に処理することだ。

• モデルケース（自分が学校の先生になったことを想像してみる）： 今手元に、国語、数学、英語の先生から提出された成績をまとめた書類がある。
  成績集計表.doc
  この成績表には学籍番号と成績がそれぞれ書かれている。これからあなたのクラスの学生全員の、成績の集計をしたい。生徒の名簿は、
  名簿.doc
  という名簿のファイルに、「学籍番号、名前、よみがな」が保存されている。
  さて、学生毎に選択している科目が違うのだが、３人の先生から集めた情報を１つ集計するにはどうすればいいだろうか？
  （※じつは、この程度の数のデータなら、エクセルでもわりと簡単に集計できる＜SQLiteを使うと、この数百〜数千倍の規模のデータでも、簡単に処理できる＞）
  
  

テーブル作成の準備 †

まず、テーブルを確認しておく。テーブルには、上の例で示したようなカラム名が必要になる。それぞれ、タイプしやすいようなカラム名にしておくといいだろう。

データをデータベースに入れるために、タブ区切りテキストファイルを準備 †

1. タブ区切りテキストファイルの準備
   データをSQLiteで使える形にするには、タブ区切りテキストファイルの形にしておかなければならない。そこで、名簿とそれぞれの科目を、K2Editorを使って、タブ区切りテキストファイルとして保存する。それぞれ、

   meibo.txt
   kokugo.txt
   sugaku.txt
   eigo.txt

   という名前で保存しておく。名簿の方はもともとタブ区切りになっているが、点数の方は空白で区切られているし、行頭に空白が入っているので、次の正規表現検索置換をして、テキストファイルにするのを忘れないように。

   検索文字列: ^ +　　置換文字列＜指定しない＞
   検索文字列:  +　　置換文字列: ¥t

• うまく出来ない場合は、次の４つのファイルを、右クリックしてダウンロードして使おう。
  meibo.txtkokugo.txtsugaku.txteigo.txt

データベースとテーブルの作成、テキストファイルからのデータの取り込み †

つぎに、seisekiという名前でデータベースを作成して、上の４つのテーブルを作成して、データを取り込んでみよう

1. コマンドラインから、SQLiteを起動して, seisekiという名前のデータベース作る

   Z:\sqlite>sqlite3 seiseki

2. 最初に　タブモードにしておく 重要
   SQLiteを立ち上げたときには、データの区切り文字は | になっているので、これをタブに変更しておく。忘れがちなので注意.

   sqlite> .mode tab
   

3. 上で考えた4つのテーブルを作る

   sqlite> create table meibo (gakuseki, namae, yomi);
   sqlite> create table kokugo (gakuseki,score);
   sqlite> create table sugaku (gakuseki,score);
   sqlite> create table eigo (gakuseki,score);

   • このとき使ったのが create table　＜テーブル名＞　（　＜カラム名のリスト＞）;という命令。新しいテーブルは、この命令で作る。
4. 作成したテーブルに、先ほど作ったex3.txtのデータと、meibo.txtのデータをそれぞれ読み込む。

   sqlite> .import meibo.txt meibo
   sqlite> .import kokugo.txt kokugo

   これを、他の科目のについても繰り返す

   sqlite> .import sugaku.txt sugaku
   sqlite> .import eigo.txt eigo

   • 先ほどの

     .mode tab

     や、今行った

     .import ＜テキストファイル名＞　＜テーブル名＞

     というコマンドは、ドットコマンドと言って、データベースのシステムと、データベース以外のファイルの間でデータのやり取りをする命令。一番最後に;（セミコロン）は不要なので、注意。
5. これでデータが入ったはず。確かめるには、select命令を使う

   sqlite> select * from meibo;
   sqlite> select * from kokugo;

6. それでは、２つのテーブルをgakuseiで関連づけてみよう。今、２つのテーブル、meiboとkokugoを考えてみると、gakuseki（学籍番号）が共通していることが分かる。

   meibo (gakuseki, namae, yomi);
   kokugo (gakuseki,score);

   そこで、select文で条件をしていするときに、「学籍番号が共通するものだけを選べ」という命令を入れてみる。このとき、meiboの学籍番号は、meibo.gakusekiで、kokugoの学籍番号はkokugo.gakusekiで表される。
   • select * from meibo, kokugo where meibo.gakuseki=kokugo.gakuseki;
     これだと、２つのテーブルの全てのカラムが表示されるので、表示させるカラムを指定する。
   • select meibo.gakuseki, meibo.namae, kokugo.score from meibo, kokugo where meibo.gakuseki=kokugo.gakuseki;
7. 同様にして３つ以上のシートからもデータを抽出できる。また、order byを使って並び替えも可能。

データの保存と終了 †

プログラムを正常に終了すると、データは自動的にデータベースファイルに保存される。

sqlite>.quit

これを忘れると、データは消えてしまうので、注意。

ここまでの操作のまとめ †

上で行った操作とコマンドをまとめておこう。

SQLの最後は ; （半角セミコロン）で終わる
ドットコマンドの最初は . （半角ドット）で始まる　（セミコロンは不要）
値は ' ' （シングルクォーツ）で囲む　（※省略できる場合も多々ある）

1. SQLiteの起動とデータベースを作成

   Z:\sqlite> ''sqlite3 ex1.db''

   • 意味：　ex1.dbというデータベースファイルを作成（または既存の場合は開く）。OSのコマンドラインから入力された。場所はsqliteフォルダの中
2. テーブルの作成:SQL: create table

   create table web (name, name_kana, shozoku, email);

   • 意味：name, name_kana, shozoku, email という4つのカラムを持つwebというテーブルを作成
3. 外部ファイルの区切り文字を指定:ドットコマンド: .mode

   .mode tab

   • 意味：入出力に使われるファイルでは、カラム同士はタブで区切られていると設定する
4. 外部データのインポート：ドットコマンド .import

   .import 'z:\sqlite\web.txt' web

   • 意味：sqliteフォルダに入っているweb.txtをテーブルwebに読み込み
5. テーブル内容の表示：SQL: select

    select * from web;

   • 意味：webの全てのカラム（*で示されている）を選択して表示
6. ２つのテーブルを連結して表示：SQL: select

   select name, email, shozoku, date, amount from web left outer join ginko on name_kana = ginko_name;

   • 意味：webとginkoからname, email, shozoku, date, amount を表示する。但し、webのデータは全部表示させ、ヨミガナが一致するデータがあるものについてだけ、ginkoのデータも表示させる

• それぞれのSQL命令の使い方の例は、以下を参考にしよう。
  • SQLite コマンドの文法（英語）http://www.sqlite.org/lang.html
  • やや古いが上記の日本語説明 http://www.net-newbie.com/sqlite/lang.html
  • rakutoネット：　SQLコマンド　http://study.rakuto.net/php/command/
  • PostgreSQLの日本語ドキュメント：http://www.postgresql.jp/document/pg824doc/html/sql-commands.html
    • 全てがSQLiteで使えるわけでは無いが、createとかselectの例は参考になる

ドットコマンドの説明（よく使うものだけを抜粋） †

#ヘルプと現在の設定を表示
   .help                  ヘルプの表示
   .show                  様々な項目の現在の設定値を表示
#プログラムの終了
   .exit                  プログラムの終了
   .quit                  プログラムの終了
#外部ファイルからのデータ取り込み・データ書き出し
   .import FILE TABLE     TABLEにFILEの内容をインポートする
   .output FILENAME       出力結果をFILENAMEという名前のファイルに保存
#外部ファイルのデータ区切り文字を設定
   .mode MODE ?TABLE?     入力・出力ファイルの区切り文字切り替え。以下（一部のみ解説）のいずれか
                            csv      コンマ区切り（Comma-separated values)
                            column   左揃えの列
                            html     HTMLの<table>タグ
                            line     １行に１つの値
                            list     .separator（下の指定） 指定された文字で区切る
                            tabs     タブ区切り
   .separator STRING      ".mode"と".import"で使うファイルの区切り文字をSTRINGで指定されたものに設定
#画面に表示される出力文字列の設定
   .output stdout         出力結果をスクリーンに表示
   .header(s) ON|OFF      ヘッダーのオン・オフ切り替え
   .nullvalue STRING      STRING で指定された文字列を NULL（ヌル・データ無しのこと）のところで表示
   .width NUM NUM ...    　"column" modeのときのカラムの幅を指定
#テーブル一覧とテーブルの設定表示
   .tables ?PATTERN?      PATTERNにマッチするテーブルを一覧
   .schema ?TABLE?        TABLEが作られた時のCREATE命令を表示
   .dump ?TABLE? ...      テーブルの内容をSQLで表示
#その他
   .prompt MAIN CONTINUE  標準プロンプトを入れ替える
   .read FILENAME         FILENAMEに書かれたSQLを実行

Macユーザーへのコメント †

Macユーザの人はsqlite3をダウンロードする必要はありません。Macには最初から、sqliteが入っています。

使い方 †

• 1. Finderから、「移動／アプリケーション／ユーティリティー／ターミナル.app」を開く
  コマンドプロンプトに似た画面が開く
• 2. この画面で は$の右側に、コマンドを打ち込むようになっている。試しに

  ls

  と入力すると、ホームディレクトリの一覧が表示される
  ホームディレクトリは、どれかのフォルダを開いたときに、左側のカラムに家のマークで示されているディレクトリ。課題のファイルはここに入れるといい。
• 3. このページから、meibo.txt, sugaku.txt, kokugo.txt, eigo.txt　をダウンロードして、ホームディレクトリに入れる
• 4. 重要　MacとWindowsでは、文字コードが違うので、ここで文字コードと改行コードの変更が必要
  • Mac用のテキストエディタ, mi（http://mimikaki.net/）をダウンロードして、インストール。
  • miで先ほどダウンロードしたファイルを開く
  • 画面上部に　文字コードと改行コードが

    Shift_JIS     CR+LF(Windows)

    と表示されているので、これをMacのターミナル（UNIX)用に変更する

    UTF-8  LF(UNIX)

• 5. 後の操作は、Windowsと同じ。ターミナルで

  sqlite3 seiseki

  と入力すると、seisekiという名前のあたらしいデータベースが作られる。

Macは最初からUNIX系のソフトウェアが入っているので、とても便利ですね。

第13回授業の課題 †

• 提出期限：7月21日水曜正午
  以下2題とも-http://bean.bio.chiba-u.jp/moodleから提出して下さい。

課題１．意見調査 †

課題2（復習）：SQLiteを使ったデータベースの操作 †

注意：　班ごとのプロジェクト発表は、7月29日の授業時間に行います †