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

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

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

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

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

複数のデータ(テーブル)を結合

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

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

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

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

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

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

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

SQLiteのインストール

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

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

    次に、

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

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

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

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

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

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

コマンドプロンプト

というものだ。

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

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

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

>

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

>notepad

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

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

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

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

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

などという命令を、データベースに英語で与えるものだ。

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

  • filemeibo
    ダウンロードしたファイルを、上で作ったsqliteというフォルダの中に入れておこう。
    ファイルエクスプローラーを立ち上げて、マウスを使って、 meiboを
    マイドキュメントの下のsqliteというフォルダ
    に入れておこう。

SQLite3の起動~

  1. カレントディレクトリの移動: 
    コマンドプロンプトで、自分が今いる閲覧している場所の中を、もう一度見てみよう。
    > dir
    そうすると、ここには先ほどだるンロードした、meiboというデータベースファイルは入っていないことが分かる。
    そこで、まず、自分がコマンドプロンプトから閲覧しているディレクトリを 次の命令を使って、Z:\sqlite に変更する。そうすると、プロンプトのところが、Z:\sqlite> に変わる。
    > cd z:\sqlite
    Z:\sqlite>
    ついでに、dirと打って、sqlite3.exemeiboというファイルが入っていることを確認しておこう。
     Z:\sqlite>dir
  2. sqlite3を使ってmeiboデータベースを開く
    ここまで準備ができたら、
    >sqlite3  meibo
    と入力すると、SQLiteが起動し、先ほどダウンロードしたmeiboというデータベースファイルが開かれる

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

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

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

というものだ。

命令の形式さえ守っていれば、コンピュータは内容を分かってくれるので
変更できるところは自由に変更して楽しんでみよう
  • 使用例: selectの簡単な文章:
    書式: select <カラム名,,> from <テーブル名,,> where <カラム名>='<値>' order by <カラム名>;
    注意命令文の最後は必ず ;(セミコロン)で終わらなければならない。

    もしも、

    sqlite> select * from rigaku
    とセミコロンを抜かして入力してしまうと
      ...>
    と表示される。そんなときは、セミコロンを1つだけ入力すると、命令が実行される。
       ...>;
  • では、次の命令を入力して、データベースとお話ししてみよう。また、演習問題を解くための命令文を、自分で考えてみよう。
    • select * from rigaku; : 「rigakuというテーブルから全てのレコード(注: *は全てのレコードを表す)表示しなさい
    • select shimei, shoku from rigaku; :「testというテーブルから、nameとpositionだけを表示しなさい」
    • select shimei, shoku from rigaku where gakka='生物学'; : 「testというテーブルから、genderの内容が'f'のレコードだけについて、nameとpositioを表示しなさい
  • なお、今使っている 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. 生物学科の教員の氏名と職名と講座名を、講座ごとに選び出し、職名ごとに並べる

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

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

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

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

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

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

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

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

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

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

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

テーブル作成の準備

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

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

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

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

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

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

  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. それでは、2つのテーブルをgakuseiで関連づけてみよう。今、2つのテーブル、meibokokugoを考えてみると、gakuseki(学籍番号)が共通していることが分かる。
    meibo (gakuseki, namae, yomi);
    kokugo (gakuseki,score);
    そこで、select文で条件をしていするときに、「学籍番号が共通するものだけを選べ」という命令を入れてみる。このとき、meiboの学籍番号は、meibo.gakusekiで、kokugoの学籍番号はkokugo.gakusekiで表される。
    • select * from meibo, kokugo where meibo.gakuseki=kokugo.gakuseki;
      これだと、2つのテーブルの全てのカラムが表示されるので、表示させるカラムを指定する。
    • select meibo.gakuseki, meibo.namae, kokugo.score from meibo, kokugo where meibo.gakuseki=kokugo.gakuseki;
  7. 同様にして3つ以上のシートからもデータを抽出できる。また、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. 2つのテーブルを連結して表示: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     1行に1つの値
                            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回授業の課題

  • 提出期限:7月15日水曜正午

課題1.意見調査

  1. http://bean.bio.chiba-u.jp/joho/index.php?joho21に、「自分のID」/13 という新しいページを作成し、下の囲みの中にあるアンケートをコピー・ペーストして、「回答:」の後に答えを書き込むこと。
*第13回授業・基本課題 
**氏名:
**課題への回答
-今日の授業の進み方は?(はやい、丁度いい、おそい)
--回答:
-今日の授業の難しさはどう感じましたか(簡単 丁度いい 難しい):
--回答:
-難しいと答えた人は、特にどの点が難しかったですか?:
--回答:
-今日の授業は(分かった 半分ぐらいは分かった 分からなかった):
--回答:
-分からないと答えた人は、特にどの点が分からなかったですか?:
--回答:
-今日の講義で分からなかった用語があったら挙げてください:
--回答:
-授業に関する要望・質問があったらなんでもどうぞ:
--回答:
-課題2の答え
--問1:
--問2:
--問3:
-課題3の答え:
--問1:
--問2:
--問3:

課題2(復習):SQLiteへの挑戦の結果

授業で準備したデータベース(seiseki)には、4つのテーブル(meibo, kokugo, sugaku, eigo)が入っています。このデータベースを用いて、以下の問題に答えなさい。なお、以下の提出課題全て、画像では貼り付けないこと。
コマンドプロンプトで範囲選択してから、行頭に半角空白を入れて、コピー・ペーストしてください。(薄緑色のボックスの中に表示できます。)

  • 問1: meiboとkokugoのテーブルを用いて、 名前と国語の点数を表示させなさい。ただし、データは名前のあいうえお順に並べ替え、最初の10件を表示させること。
    • なお、 並び替えた後10件だけ表示 するには、
        order by yomi limit 10;
      を最後に追加
      例: > select * from meibo, score where meibo.gakuseki = score.gakuseki order by yomi limit 10;
          のようにして使う
  • 問2: 4つのテーブル全てを用いて、学籍番号、名前と国語、数学、英語のそれぞれの点数を表示させなさい。ただし、データは学籍番号で並び替え、最初の10件だけをテキストとしてレポート提出ページに提出すること。
    • ポイント:
      ・3つ以上のテーブルからデータを抽出するとき、
       select .... from meibo, kokugo, eigo, ... のように、fromの後にテーブル名をカンマ区切りで並べる
       where meibo.gakuseki=kokugo.gakuseki and meibo.gakuseki=sugaku.gakuseki and ...
        のように、whereの後に=で作られた条件式を、andでつないで並べる。
       注:whereで指定できる条件は2つのテーブル間でしか作ることができない。
         where meibo.gakuseki=kokugo.gakuseki=sugaku.gakuseki という書き方はできない
  • 問3: 全ての科目の合計点数を計算し、点数の高い順番に並び替えて、最初の10件を学籍番号、名前、合計点数の順に表示させ、レポートページにかき込みなさい。
    ヒント:例えば、数学と国語の合計点を表示させるには、
    select sugaku.score+kokugo.score from sugaku, kokugo where sugaku.gakuseki=kokugo.gakuseki;
    と書きます。合計点の高い順に並び替えるには、最後に、
    order by sugaku.score+kokugo.score desc  を追加します

課題3(予習):ウェブページとHTML

  • 問1:あなたが普段みているウェブページの本体はテキストファイルです。でも、ウェブページの画面には写真などが表示されています。文字情報だけのはずのテキストファイルで写真が表示できるのはどうしてでしょうか?
    • a. ウェブページは写真をファイル内に保存できる、特殊な形式のテキストファイルである。
    • b. ウェブブラウザがテキストファイルの指定に従って、別の場所に保存された写真を画面上に表示している
    • c. ウェブサーバというものがテキストファイルと写真の入った画像ファイルを合成して発信している
  • 問2:皆さんの課題の採点結果閲覧ページは、PostgreSQLというデータベースシステムと、PHPというプログラミング言語が使われています。自分の成績ページを表示させるときに、どのような事が起きているでしょうか?
    • a. インターネットにアクセスしているコンピュータに暗号化された全員分のデータがダウンロードされ、ブラウザの持っている機能で自分のデータだけ解読され、表示されている。
    • b. アクセス先のウェブサーバーがデータベースサーバーからあなたのデータを取り出し、1回1回、あなた専用のウェブページを作成している。
    • c. 教員が毎回、課題の採点後に、全員分の返却用ページを作成し、ウェブサーバーにアップロードしている。アカウントとパスワードを入力すると、成績データのページが納められたディレクトリに入った自分専用のページが表示される。
  • 問3:あなたが普段見ているウェブページは、HTMLという書式で作られており、タグと呼ばれるマークで、下線や太字を指定したり、他のページへのリンクを指定したりできます。HTML書類の中で、次のようなタグで指定された部分がありました。この部分は、ブラウザではどのように表示されるでしょうか?
    <font size="16"><i><b>HTMLの世界にようこそ!</b></i></font>
  • 評価:
    • HTMLについて自分で調べて予習したかどうか
    • どの処理がサーバー側で行われ、どの処理がローカル(自分が操作しているコンピュータ)で行われているかを理解しているかどうか
    • 補足追加: size="16" のところは間違い。sizeの指定は 1-7しかできない。

添付ファイル: file成績集計表.doc 1720件 [詳細] file名簿.doc 3568件 [詳細] filesugaku.txt 1279件 [詳細] filemeibo.txt 1862件 [詳細] filemeibo 1894件 [詳細] filekokugo.txt 1192件 [詳細] file#13_1.gif 1286件 [詳細] fileeigo.txt 1235件 [詳細]

Last-modified: 2015-05-13 (水) 16:42:58 (3302d)