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

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

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

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

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

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

というのが、データベースには簡単にできて、テキストエディタやワープロには簡単にできないことだ。
おそらく、今現在の時点でデータベースを使うことはあまりないだろうが、この技術を知っておくと、将来的にいろんな得をする場面に出会うことは間違い無い(あるいは、授業を聞いておくだけでも、楽をすることができるチャンスに気づくことができる)。さらに、自由に使えるようになれば、心と時間に余裕が生じるだろう。

前回のフィードバックで皆さんに尋ねた、これまでに自分が扱ったことのある最大のデータ件数と、将来扱うことになることが予測されるデータ件数をグラフにしてみ。これをみただけで、将来的にデータ件数がすごく増えると予想していることが分かる。
&ref(): File not found: "#15_1.jpg" at page "授業/H24/情報処理/15";  #15_2.jpg

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

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

  • 1. リレーショナルデータベース(SQLite)を使える環境を作る:FireFoxによるアドオンの使用
  • 2. SQLという言語を使って、データベースとお話しする
    • SQLiteを使って、情報の検索・抽出・並び替えを行う方法を習得する
  • 3. データベースにおけるテーブルの概念を理解する
  • 4. SQLを用いて、複数のデータテーブルを連結して、必要なデータだけを抽出する方法に挑戦する

1. リレーショナルデータベース(SQLite)を使える環境を作る:FireFoxによるアドオンの使用 [smile]

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

昨年までの授業では、自分のパソコンにフリーソフトウェアSQLiteをインストールして、コマンドラインからデータベースを操作していた。
ちょっとデモしてみるが、コマンドラインからの

  • 実行
  • データの保存・利用

という点は、なかなか皆さんには馴染みの無いことらしく、あきらめてしまう人も多かったようだ。そこで今年からは、いつも使っているFireFoxにデータベース利用機能のアドオンを追加して、SQLiteを利用することにする。

なお、皆さんが大学で使っているコンピュータにはAccessというデータベースソフトも入っているが、無料で、自分のパソコンでも使えるという点を重視して、フリーソフトウェアであるSQLiteを使うことにした。

FireFoxでのSQLite Managerのインストール

  • FireFoxを立ち上げる
  • Google等でSQLite Managerを検索し、インストール
  • FireFoxを再起動

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

SQLite Managerの起動

  1. FireFoxのツールメニューから、SQLite Managerを起動する
    #15_3.jpg
  2. データベースディレクトリの指定:  画面の右上の方にある「ディレクトリ」をクリックして、データベースのデータファイルを保存するディレクトリ(フォルダーと同じ意味)を指定する
    #15_4.jpg
  3. データベースの作成: SQLiteのデータベースは、1つのファイルに全てのデータや設定が保存される。サンプルデータの解析に使うseisekiという名前のデータベースを作成してみよう。
    • FireFoxのメニューバーにある「データベース」から、「新しいデータベース」を選択し、データベース名の入力欄でseisekiと入力する。
      #15_5.jpg
      これで準備は整った。

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

SQLでは先ほど作ったデータベースファイルの中に、複数のテーブルを作成することで、データの操作を行う。

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

であることを、理解しておこう。例えば、エクセルを使うと、次のようなテーブルとして表せる。
#13_1.gif
また、

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

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

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

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

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

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

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

テーブル作成の準備

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

テーブルテーブル名カラム
名簿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 ManagerのImport Wizardを起動する(メニューバーの「データベース」から「取り込み」を選択してもよい)
    -左側のパネルに4つのtableが出来上がれば、準備は完成だ。

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

ここから先は、簡単な英語を使って、データベースとお話しする。お話の内容(命令文)は、SQLite Managerの画面にあるSQL実行をクリックして、入力欄に命令を入力することで行う。
#15_6.jpg
これから使う英語の意味をまとめておくと、

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

というものだ。

命令の形式さえ守っていれば、コンピュータは内容を分かってくれるので
変更できるところは自由に変更して楽しんでみよう
  • 使用例: selectの簡単な文章:
    書式: select <カラム名,,> from <テーブル名,,> where <カラム名>='<値>' order by <カラム名>;
  • では、次の命令を入力して、データベースとお話ししてみよう。また、命令文を自分で考えて少しずつかえて、結果を見てみよう。
    • select * from meibo; : 「meiboというテーブルから全てのレコード(注: *は全てのレコードを表す)表示しなさい
    • select * from meibo order by yomi;  :「meiboというテーブルから全てのレコードを、yomiで並び替えて表示」
    • select gakuseki, namae from meibo; :「meiboというテーブルから、gakusekiとnamaeだけを表示しなさい」

2つのテーブルを連結

それでは、いよいよ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;
  • 同様にして3つ以上のシートからもデータを抽出できる。また、order byを使って並び替えも可能。
  • さらに発展的な連結(非常に便利な連結方法)
    先ほどの連結方法で得られたレコード数は、97件だった。でも、実は、名簿には、100名分のデータが入っている。3名は国語の試験を受けていないため、成績が表示されなかったわけだ。
    select * from meibo, kokugo where meibo.gakuseki=kokugo.gakuseki
     ↑ この命令文だと、
    kokugo.gakuseki(国語のファイルの学籍番号)が存在している場合だけ、データが表示される
    しかし、それでは困る場合もある。成績集計の上では、受験の有無にかかわらず、名簿に載っている全ての人に対して点数をつける必要があるからだ。そこで、SQLに left outer join on ... という命令を追加する。これは、「左側のテーブルにあるものは全て表示し、対応するデータがあるものは右側のデータも表示するというものだ」。この命令を使うことで、「meiboの内容は全て表示し、受験されている場合だけ、kokugoの点数も表示させる」ということが可能になる。
    • select meibo.gakuseki, meibo.namae, kokugo.score from meibo left outer join kokugo on meibo.gakuseki=kokugo.gakuseki
  • 今度は100件のデータが全て表示されており、国語の受験が無い人については下の例の様に、点数が表示されていない
    06S4030  岩井文男  45
    06S4031  岩藤俊幸  
    06S4032  紀谷昌彦  46
    このとき、国語を受験しなかった人の点数はNULL(無)になっている。
  • 次に、国語の点数の高い順番で、結果をソートして表示させてみよう
    • select meibo.gakuseki, meibo.namae, kokugo.score from meibo left outer join kokugo on meibo.gakuseki=kokugo.gakuseki order by kokugo.score desc 

第15回授業の課題


添付ファイル: filesugaku.txt 1215件 [詳細] filemeibo.txt 1532件 [詳細] filekokugo.txt 1206件 [詳細] fileeigo.txt 1209件 [詳細] file#15_6.jpg 792件 [詳細] file#15_5.jpg 780件 [詳細] file#15_4.jpg 791件 [詳細] file#15_3.jpg 770件 [詳細] file#15_2.jpg 767件 [詳細]

Last-modified: 2015-05-13 (水) 16:45:09 (3497d)