このページは編集中です。 †

↑

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

【教科書：44-48p参照】

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

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

　データの量が多くなるほど、目的のものを取り出すのは、なかなか大変なはずなんですが、やっぱり実感はわきません。では、もう少し、具体的な話しをしましょう。今、あなたの手元には、卒業研究で自分が決定したミオシン遺伝子の部分塩基配列100件分がFASTA形式ファイルとして保存されています。１件１件のデータは1000塩基ぐらいなのですが、この配列の中から、ATTATAATという配列から始まる300bpぐらいだけを抜き出したいと考えています。どうやりますか？

　すでに皆さんが習得している方法だと、テキストエディタの検査機能を使うという手がありますね。K2EditorでFASTA形式ファイルを開いて、「検索」を選び、検索文字列にATTATAATを指定いて検索する。そうすると、画面上にATTATAATから始まる部分が強調表示されます。そこから500bpぐらいをマウスを動かして選択して、コピーして、別のウィンドウを開いてペーストして、また前の画面にもどって、この配列のアクセッション番号まで画面を動かして戻って、配列名やアクセッション番号を選択してコピーペーストして、。。。。あーめんどくさい。

Hairetsu1>
......ATTATAATGCTAAGATCGAA....
Hairetu2>
......................................
Hairetu3>
..ATTATAATGCTATGATCGCA....
....

上の赤字の部分をテキストエディタの検索機能を使って探し出し、コピーペースト

Hairetsu1>
ATTATAATGCTAAGATCGAA....
Hairetu3>
ATTATAATGCTATGATCGCA....
....

「検索」、「選択」、「コピー」、「ペースト」で新しいファイルは作れる。でも、面倒。

　テキストエディタでも、ワープロでも、検索はすごく便利な機能だけれど、自分が必要としている部分だけを取りだそうとすると、コピー・ペーストとも、かなり面倒な操作が必要になります（実感がわかない人は、上に書いた作業をやってみてください。途中で嫌になること請け合いです）。

　こういう作業を簡単にしてくれるのがデータベースです。先に挙げたDNAデータベースでも、Yahooでも、Amazonでも、いずれもリレーショナルデータベースというシステムを利用することで、ものすごく大量のデータから、一瞬にして必要な情報を検索し、抽出し、並び替えるという処理をおこなっています。この、

検索後の抽出と並び替え

というのが、テキストエディタやワープロには簡単にできないことです。データベースは、こういう面倒な処理を、大量のデータについて簡単に行えるという優れものです。大量データ処理に関しては、現時点における標準的なツールといっても良いでしょう。先日の調査結果によると、多くの皆さんが学生生活（＋研究生活）において、大量のデータを処理することになると考えているようです。そんなとき、データの抽出で、１件１件コピペするのも何か哲学的な意味を持った修行になるかもしれませんが、データベースシステムの扱いに慣れていれば、心と時間に余裕が生まれるでしょう。

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

プログラミング言語、HTMLに続いて３つめの言語ですが、安心してください。SQLはかなり英語の文章に近いので、直感的にも理解しやすいと思います。

↑

関連リンク †

↑

リレーショナルデータベースの実例 †

　それでは具体例をつかって説明してゆきましょう。「リレーショナル」という言葉を説明し出すと長くなるので、まず、実例をお見せします。押さえてほしいポイントは次の３点です。

簡単な例として、サークルの名簿と会費納入状況のデータベースを考えてみましょう。説明のためにはカラム名もかな・漢字表示をする方が良いのでしょうが、ここでは、キーボードからの入力の混乱を避けるために、カラムの名前は全て半角英数文字で表記します。

想定しているのは、次のようなデータです。

numbernamaeyubinjushotelgakunennyuukai2006_42006_52006_62006_7
1綿野泰行263-8522千葉市稲毛区弥生町1-33043-290-28183200404500500500500
2朝川毅守263-8522千葉市稲毛区緑が丘043-290-28182200504500500500
3梶田　忠263-8522千葉市稲毛区小仲台043-290-28181200606500

こういうデータなら、皆さんならどうやって管理しますか？

そうですね、エクセルで管理するという方法があります。では、ちょっとやってみましょう。

この表をエクセルで開いて、8月分の会費を集めたときは、2006_8という列（カラム）を手作業で一つ追加します。また、会費の未納状況を見るときには、納入状況を一つ一つ目で確かめます。納入された会費を集計するときには、行の右端でsumをとって、また、表の一番下でもsumをとります。e-mailもついでに管理しておこうとおもうと、列（カラム）を一つ追加するので、右のほうがますます見にくくなってしまいます。

しかも、この例では３件しかデータを示していませんが、サークルの会費なので、前の年のデータも有るはずです。なので、ワークシートはとても大きく、見にくくなりますね

こんな感じ：
　　　　住所等のデータ     2003年         2004年      2005年     2006年
名前（2003年入会）　　　●●●●●●●●●●●●●●●●●●●●
名前（2004年入会）　　　　　　　　　　　　●●●●●●●●●●●●●●●●
名前（2005年入会）　　　　　　　　　　　　　　　　　　●●●●●●●●●●
名前（2006年入会）　　　　　　　　　　　　　　　　　　　　　　　●●●●●

006年の入会者にとっては、左の方の空白のセルがムダになっています。それに、毎年毎年、このワークシートはどんどん右側に大きく伸びてゆくので、1年生と4年生の支払い状況を比較するのは難しそうです。このように、無計画にエクセルで集計表を作ってしまうと、目で見て、手でカラムを追加するというのが大変な作業になってしまうのです。

そこで、手と目に大変なこの作業を、頭とリレーショナルデータベースを使って解決してみましょう。

この表をリレーショナルデータベースに作り直す場合、まず、データの構造を変更します。この表には、

という２つの異なるデータが混在しています。そこで、この２つを区別します。また、どの年のどの月の会費を払うかは、会員の入会時期によっても異なるデータなので、会費納入のデータは１行ずつに、会員番号と納入月を追加して表すことにします。そうすると、データの構造は、次のようになります。さらに、会費は年ごとに集計することもあるでしょうから、日付は年と月の２つに分けておく方が使いやすくなります（データ構造をシンプルに使いやすくすることを、データの正規化といいます）。

データの構造がかなりすっきりしたのが分かるでしょうか？「でも、会員番号と納入月だけ出されても、誰のデータかわかんない。。。」という疑問が出そうですよね。それを解決するのが「リレーショナルデータベース」の「リレーション」（関連づけ）という機能です。

上の２つの表は、「会員番号でリレーションをとる（関連づける）」ことによって(下の例では、ついでに名前順・日付順に並び替えています）、

namenentukikaihi
綿野泰行200604500
綿野泰行200605500
綿野泰行200606500
綿野泰行200607500
朝川毅守200604500
朝川毅守200605500
朝川毅守200606500
朝川毅守200607500
梶田　忠200606500

という形で扱うことができるのです。

↑

SQLiteとは †

では、実際にデータベースシステムを操作してみましょう。ここではSQLiteというソフトを使います。SQLiteというのはフリーで使えるリレーショナルデータベースシステムです。他のデータベースシステム（例えばMySQLやPosgreSQL）ではユーザの設定やネットワークの設定など、面倒なことが多いのですが、SQLiteはダウンロードした実行ファイルをクリックするだけで、データベースを使うことができます。データベースのデータ本体も１つのファイルに保存されるので、コピーやバックアップが簡単にできるという優れものです。

とはいうものの、世間一般のパソコンユーザにはそれほど使わてはいません

コマンドラインからSQLという命令文を入力しなくてはならない
説明が英語

というのが敬遠されている理由でしょうが、Rの操作でコマンドラインに慣れた皆さんにとっては、それほど苦にはならないはずです。

↑

SQLiteの準備 †

　では、SQLiteを使う準備をします。以下のリンクから、SQLiteの実行形式ファイルをダウンロードしてください。

そうすると黒いウィンドウが開き、Rで使ったようなプロンプトが表示されます。これはWindowsXPというOSを動かすもとになっているDOSのコマンドプロンプト（キーボードから命令を入力する処理系）です。ここに文字を入力することで、コンピュータに命令を与えることができます。試しに下の用に入力してみると、ディレクトリの内容一覧が表示されます。

dir
↑

SQLiteの起動と終了 †

sqliteというフォルダにsqlite3.extが入っていることを確認できたら、とりあえず、SQLite3を起動して、ついでに終了の方法も覚えましょう。（手続き通りに終了しないとデータが消えてしまうことがあるので、要注意です）

↑

SQLiteの起動とデータベース ex1 の作成（作成済みのデータベースの場合、オープン） †

それではいよいよ、実際のデータを保存するデータベースを作りましょう。今回の例で作るデータベース以下のような構造になっています。

データベース：　　　ex1.db　（sqlite3を起動するとき、「sqlite3 データベース名」という命令で作成
    テーブル:       table1   (起動後（プロンプトはsqlite> ）SQL命令（create table...)を入力
    テーブル:       table2
    ※１つのデータベースは１つまたは複数のテーブルがあつまってできています。
      上で説明した１つ１つのエクセルの表はテーブルの一つ一つに相当します。

データベースを作るには、Windowsのコマンドプロンプトから、次のように入力します。

Z:\sqlite>sqlite3 ex1.db

sqlite3 ＜データベース名＞は、新しいデータベースを作ったり、もしすでに作成済みのデータベースがあれば、それを開く命令です。上の命令を入力すると、画面がSQLiteのコマンドプロンプト（sqlite>）に変わります。 (注意：このとき、ex1.dbというデータベースは、z:\sqlite\というフォルダの中にできています）

↑

コマンドラインからのSQL入力 †

ここから先は、実際にSQLを入力してデータベースの操作を行います。復習課題では、ここから先と同様の操作を課題用のデータファイルを使ってやってもらいます。

↑

テーブル tbl1の作成 †

先ほどの操作で、今はex1.dbという名前のデータベースに対していろんな命令を与える準備ができました。今から入力する命令は、ex1.dbというデータベースの中にテーブルを作る命令です。作成するテーブルには次のようなデータを入れようと思っています。

↑

テーブル tbl1 へのデータの一括登録 †

sqlite> copy tbl1 from 'z:\sqlite\ex1.txt';
sqlite > select * from tbl1;

同様に次のコマンドでtbl2を作成し、データを追加します。

sqlite> create table tbl2 (number, nen, tuki, kaihi);
sqlite> copy tbl2 from 'z:\sqlite\ex2.txt';
sqlite> select * from tbl2;
↑

キーを用いた２つのテーブルの連結表示 †

２つのテーブルにデータが入ったかどうかを、両者に共通するnumberというキーで結びつけて表示してみましょう。

sqlite> .header on
sqlite> select * from tbl1, tbl2 where tbl1.number=tbl2.number;

このコマンドでは、全てのカラムが表示されるのでみずらいですね。表示したいカラムを選んで、しかも、年ごとに支払金額を集計するには、次のような命令を与えます。

あるいは

↑

SQLiteの終了 †

sqlite> .q

上のように入力すれば、終了できます。

↑

補足説明 †

「こういうコマンドを考えるのは面倒くさいや。上の表をエクセルで、こんな風にして管理したって、同じことでしょ？」

numbernamaeyubinjushotelgakunennyuukaitukikaihi
1綿野泰行263-8522千葉市稲毛区弥生町1-33043-290-28183200404200604500
1綿野泰行263-8522千葉市稲毛区弥生町1-33043-290-28183200404200605500
1綿野泰行263-8522千葉市稲毛区弥生町1-33043-290-28183200404200606500
1綿野泰行263-8522千葉市稲毛区弥生町1-33043-290-28183200404200607500
2朝川守毅263-8522千葉市稲毛区緑が丘043-290-28182200504200604500
2朝川守毅263-8522千葉市稲毛区緑が丘043-290-28182200504200605500
2朝川守毅263-8522千葉市稲毛区緑が丘043-290-28182200504200606500
2朝川守毅263-8522千葉市稲毛区緑が丘043-290-28182200504200607500
3梶田　忠263-8522千葉市稲毛区小仲台043-290-28181200606200606500

はい。その通りです。 でも、次の２つの問題点があります。

  1. このテーブルのほとんどは、同じデータの繰り返しになっているので、ムダが多い。
  2. 住所などのデータに変更があったとき、関係のある全てのデータを変更しなければならないので、ムダが多い

ということです。こういうデータ構造でも問題はないのですが、あまりにもムダが多いので、２つに分けるのが良いということです。

↑

SQLiteの短所：インターフェースの使いにくさ †

　大量データの扱いに優れており、しかも、無料で使えるSQLiteですが、コンピュータに慣れていない人には非常に使いにくいだろうなと思う点があります。それは、データを追加したり、編集したりするためのインターフェースの貧弱さです。sqliteを立ち上げて、データを一つ追加するコマンドを入力すると、

sqlite> insert into tbl1 values('3', '梶田　忠', '263-8522', '千葉市稲毛区小仲台', '043-290-2818', '1', '200606');

などという、長く、しかも、シングルクォートがたくさんあってタイプミスを起こしやすそうな命令文を入力しなければなりません。検索・抽出がいかに素早くできても、データの追加にいちいちこういう複雑な命令文を考えなければならないのでは、困ってしまいます。

　実は、リレーショナルデータベースの説明に使うことをはじめに予定していたPostgreSQLでは、データ管理を簡単に行える洗練されたフリーソフトがあります。PpostgreSQLと同じくらい人気のあるMySQLでも、同様に使いやすい管理用のフリーソフトがあります。ところが、SQLiteでは、管理ソフトはあるにはあるのですが、利用できる漢字コードの問題、SQLiteのバージョンの問題など、授業で使用するにはいくつか適当ではない点があって、今回は利用しません（自分でSQLiteを使ってみようという人は、 SQLite3とSQLiteControlCenterの組み合わせを試してみると良いと思います）。

えーっ！？毎回毎回 insert tbl1 ナンタラカンタラ　なんてタイプするなんてムリっ！
編集するのにも update tbl1 ナンタラカンタラ　なんていちいちタイプしなければならないなんて、使えなさすぎっ！
そういう面倒なことをしなくちゃならないのだったら、SQLiteは実用的ではないのでは？

という非難の声が聞こえてきそうです。本当にSQLiteは使い物にならないのでしょうか？

皆さんがこれから出会うデータ処理の状況を考えてみて、データベースを常に立ち上げて管理する場面というのは、それほど沢山は無いと思います。それよりも、もっと多いのは、上の課題でやったように、複数の異なる表を組み合わせて、データを検索、抽出するという作業です。このような作業では、エクセル、テキストエディタ、SQLiteの組み合わせで、かなり複雑な処理を、ごく簡単に行うことができます。

↑

SQLite、エクセル、テキストエディタの合わせ技 †

↑

学生の成績一覧表の作成 †

　今手元に、国語、数学、英語の先生から提出された成績表があります。

さて、この４つの情報を統合して、学生ごとの成績を一覧表示する場合を考えてみましょう。こういうとき、SQLite、エクセル、テキストエディタの組み合わせで、次のような手順で操作を行えば、４つの表から目的のデータを抜き出すことができます。

  1. まず、成績ファイルを１つのテーブルにまとめます。（注：３つをそれぞれ別の表として扱っても良いのですが、この授業ではなるべく簡単な命令文を使いたいので、簡単にできることはエクセルで済ませてしまいます。３つをそれぞれ別の表として扱う方法は、次の授業のページで説明します。データベースの使い方としては、エクセルを使わずに処理する方が洗練されていますが、この授業では、エクセルの復習もしたいので、こういう処理を行います。）
    • エクセルで上の３つのファイルをそれぞれ開いて、新しい列に科目名を加えます。もし、英語のデータが 
        　　06S4061   42
  　　06S4028   44
      なら、 
        　　06S4061   42   eigo
  　　06S4028   44   eigo
にするわけです。この作業を国語、数学、英語の３教科のデータについて行います。
      • （学籍番号ごとに集計するだけなら、エクセルでもできますよね（だいぶ前にやりました））
  2. 次に、上で１つのファイルにまとめた３教科分のデータを、タブ区切りのテキストファイルに保存します。K2Editorで新しい書類を作って、そこに、上の３つのファイルの内容を、コピーペーストします。 
       06S4061   42   eigo
   .
   .
   06S4063   55   sugaku
   .
   .
   06S4066   66   kokugo
   .
   .
    のようなデータをK2Editorで作って、これを、ex3.txtというファイル名で、Z:\sqlite\に保存します。
  3. 次にSQLiteを立ち上げて、テーブルを２つ作ります。
    • １つは、先ほど作ったex3.txtの内容を入れるテーブルで、scoreという名前にします。このテーブルは 
            gakuseki    score    kamoku
      という列からできています。のデータベースを作る命令は
    • もう１つは、meibo.txtの内容を納めるテーブルで、meiboという名前にします。このテーブルは 
            gakuseki   namae   yomi
      という列からできています。
    • 実際の操作は次のようにします。コマンドラインから、SQLiteを起動して, ex3.dbという名前のデータベースを作ります。 
      Z:\sqlite>sqlite ex3.db
    • 次に、上で考えた２つのテーブルを作ります。 
      sqlite> create table score (gakuseki, score, kamoku);
sqlite> create table meibo (gakuseki, namae, yomi);
  4. ２つのテーブルに先ほど作ったex3.txtのデータと、meibo.txtのデータをそれぞれ読み込んで、gakusekiをキーにして関連づければ、それぞれの、氏名と科目の点数合計などを表示することがきます。
    • まず、それぞれのテーブルにデータを読み込みます。 
      sqlite> copy score from 'ex3.txt';
sqlite> copy meibo from 'meibo.txt';
    • これでデータが入ったはずです。確かめるには、select命令を使います 
      sqlite> select * from score;
sqlite> select * from meibo;
    • それでは、２つのテーブルをgakuseiで関連づけてみましょう。
      • sqlite> select * from meibo left outer join score on meibo.gakuseki = score.gakuseki order by yomi;
↑

命令文を知りたい時は †

　ここまでで説明した命令文についてもっと知りたいときは、

を見てください。

↑

第13回授業の課題 †

全ての課題は、http://bean.bio.chiba-u.jp/joho18/ に、「自分のID/13」という新しいページを作成し、これまでの提出例にならって、分かりやすく書き込むこと。あまりに読みにくい回答は減点します。ページの書式を変更するには、編集ページで、「テキスト整形のルールを表示する」をクリックすると、（長くて読みづらいですが）ページの書式設定のルールが表示されます。Pukiwikiでは、専用の書式指定で書き込みすると、サーバ側のプログラムがそれをHTMLタグに翻訳して、クライアントに送信してくれるというわけです。

↑

課題１．意見調査 †

　下の囲みの中にあるアンケートをコピー・ペーストして、「回答：」の後に答えを書き込むこと。

*第13回授業アンケート
**氏名：
**課題への回答
-今日（7月13日）の授業の進み方は？（はやい、丁度いい、おそい）
--回答：
-今日の授業の難しさはどう感じましたか（簡単すぎ　簡単　丁度いい　難しい　難しすぎ）：
--回答：
-難しいと答えた人は、特にどの点が難しかったですか？：
--回答：
-今日の授業は（よく分かった　分かった　分からなかった）：
--回答：
-分からないと答えた人は、特にどの点が分からなかったですか？：
--回答：
-今日の講義で理解できなかった用語があったら挙げてください：
--回答：
-これまでの講義を振り返って、でもう一度説明して欲しいテーマ、や追加説明が欲しい演習などを挙げてください（複数可）。
--回答：
↑

復習課題：リレーショナルデータベースについて †

　このページには先ほどの３科目のデータに加えて理科と社会のデータが添付されています。&ref(): File not found: "rika.txt" at page "授業/H19/情報処理/13";, &ref(): File not found: "shakai.txt" at page "授業/H19/情報処理/13";　この２つのファイルをダウンロードして、上と同様の処理を行いなさい。

  1. 上で準備したデータベースから、データを抽出します。データはあいうえお順に並べ替え、名前（namae）, 科目（kamoku）, 点数（score）のみ、最初の10件を表示させ、レポート提出ページにテキストとして書き込みなさい。（注：以下の提出課題全て、画像では貼り付けないこと。コマンドプロンプトで範囲選択してからコピー・ペーストしてください。行頭に半角空白を入れると、薄緑色のボックスの中に表示できます。）
    • 注：7月18日、上の緑色の部分を追記しました。最初の10件以外を貼り付けてあっても、構いません。 
      並び替えと10件だけ表示
 　order by yomi limit 10;
を最後に追加
例：　> select * from meibo, score where meibo.gakuseki = score.gakuseki order by yomi limit 10;
  　　のようにして使う
  2. meiboとscoreという２つのテーブルを単純連結して、あいうえお順に並び替え、学籍番号(gakuseki), 名前（namae）, 科目（kamoku）, 点数（score）最初の10件だけをテキストとしてレポート提出ページに書き込みなさい。 
    よく似た例：　sqlite> select * from meibo, score where meibo.gakuseki = score.gakuseki;
  3. meiboとscoreの２つを単純連結して、06S4053という学籍番号を持つ人の学籍番号、氏名、5科目の点数の順で表示させ、結果をレポート提出ページに書き込みなさい。特定の学籍番号の人のデータを表示させるには、　where score.gakuseki = '06S4053' を使います。whereの後の条件が２つ以上ある場合は、andでつなぎます。 
    例：　.....   where meibo.gakuseki = score.gakuseki and score.gakuseki = '06S4041'
  4. 全ての科目の合計点数を計算し、点数の高い順番に並び替えて、最初の10件を学籍番号、名前、合計点数の順に表示させ、レポートページにかき込みなさい。合計点数の計算には、授業の最初の例で使った group by を使います。
↑

.helpで表示される内容の説明（よく使うものだけを抜粋） †

#ヘルプと現在の設定を表示
   .help                  ヘルプの表示
   .show                  様々な項目の現在の設定値を表示
#プログラムの終了
   .exit                  プログラムの終了
   .quit                  プログラムの終了
#外部ファイルからのデータ取り込み・データ書き出し
   .import FILE TABLE     TABLEにFILEの内容をインポートする
   .output FILENAME       出力結果をFILENAMEという名前のファイルに保存
#外部ファイルでのデータ区切り文字を設定
   .separator STRING      ".mode"と".import"で使うファイルの区切り文字をSTRINGで指定されたものに設定
#画面に表示される出力文字列の設定
   .output stdout         出力結果をスクリーンに表示
   .header(s) ON|OFF      ヘッダーのオン・オフ切り替え
   .mode MODE ?TABLE?     テーブルごとに出力モードの切り替え。MODEは以下（一部のみ解説）のいずれか
                            csv      コンマ区切り（Comma-separated values）で出力
                            column   左揃えの列として出力
                            html     HTMLの<table>タグで出力
                            line     １行に１つの値
                            list     .separator で区切られた文字列を出力
                            tabs     タブ区切りの出力
   .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を実行