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

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

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

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

大量のデータの検索・抽出・並び替え

と

複数のデータ（テーブル）を結合

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

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

↑

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

↑

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

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

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

↑

SQLiteのインストール †

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

↑

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

↑

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

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

コマンドプロンプト

というものだ。

↑

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

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

ウィンドウズのスタートメニューから、「すべてのプログラム／アクセサリ／コマンドプロンプト」を選ぶ。
WS000009.JPG
そうすると黒いウィンドウが開き、Rで使ったようなプロンプト

>

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

>notepad

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

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

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

では、今から、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　　：　　・・・という条件で

というものだ。

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

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

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

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

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

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

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

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

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

↑

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

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

↑

テーブル作成の準備 †

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

テーブルテーブル名カラム
名簿meibogakuseki, namae, yomi
国語kokugogakuseki,score
数学sugakugakuseki,score
英語eigogakuseki,score
↑

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

  1. タブ区切りテキストファイルの準備
    データをSQLiteで使える形にするには、タブ区切りテキストファイルの形にしておかなければならない。そこで、名簿とそれぞれの科目を、K2Editorを使って、タブ区切りテキストファイルとして保存する。それぞれ、 
    meibo.txt
kokugo.txt
sugaku.txt
eigo.txt
    という名前で保存しておく。名簿の方はもともとタブ区切りになっているが、点数の方は空白で区切られているし、行頭に空白が入っているので、次の正規表現検索置換をして、テキストファイルにするのを忘れないように。 
    検索文字列: ^ +　　置換文字列＜指定しない＞
検索文字列:  +　　置換文字列: ¥t
↑

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

つぎに、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のデータも表示させる
↑

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

#ヘルプと現在の設定を表示
   .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を実行
↑

第13回授業の課題 †

↑

課題１．意見調査 †

↑

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

↑

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