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

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

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

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

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

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

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

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

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

2つ以上のテーブル連結が必要な例と、エクセルによる操作に挑戦

まずは次のようなモデルケースを想定してみよう。

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

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

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

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

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

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

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

SQLite Managerの起動

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

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

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

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

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

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

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

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

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

SQLiteを使って、2つ以上のテーブルを連結して、データを抽出する方法に挑戦!! [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という名前のデータベースに、データを取り込んで、上の4つ科目のテーブルを作成してみよう

  1. SQLite ManagerのImport Wizardを起動する(メニューバーの「データベース」から「取り込み」を選択してもよい)
    -左側のパネルに4つのtableが出来上がれば、準備は完成だ。

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

ここから先は、簡単な英語を使って、データベースとお話しする。お話の内容(命令文)は、SQLite Managerの画面にあるSQL実行をクリックして、入力欄に命令を入力することで行う。
&ref(): File not found: "#15_6.jpg" at page "授業/H25/情報処理/14";
これから使う英語の意味をまとめておくと、

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

というものだ。

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

2つのテーブルを連結

それでは、いよいよ2つのテーブルをgakuseiで関連づけてみよう。今、2つのテーブル、meibokokugoを考えてみると、gakuseki(学籍番号)が共通していることが分かる。

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

そこで、select文で条件指定するときに、「学籍番号が共通するものだけを選べ」という命令を入れてみる。このとき、meiboの学籍番号は、meibo.gakusekiで、kokugoの学籍番号はkokugo.gakusekiで表される。