size(16){編集中です！};大量データの一括処理：SQLiteによるリレーショナルデータベース入門 †

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

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

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

検索後の抽出と並び替え

と

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

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

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

また、授業の後半では、普段皆さんが使っているインターネットとウェブページの仕組みを解説して、自分でも簡単なウェブページを作成・公開できるようになってもらう。

今日の授業は普段よりも盛りだくさんだが、前半は挑戦、後半は習得という点に注意して、授業に集中してほしい。

↑

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

↑

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

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

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

とはいうものの、世間一般のパソコンユーザにはそれほど使われていない。なぜかというと

コマンドラインからSQLという命令文を入力しなくてはならない
説明が英語
データの入出力が面倒(エクセルやエディタで前もって編集が必要）

だろう。ただ、Rの操作でコマンドラインに慣れた皆さんにとっては、それほど苦にはならないはずだし、データの入出力が面倒なのは大問題だが、SQLiteは、エクセルやテキストエディタと合わせて使うものと位置づけておけば良いだろう。

↑

SQLiteのインストール †

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

↑

関連リンク †

↑

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

↑

3. SQLという言語を使って、データベースとお話ししてみる †

このとき知っているべき英語

select  from  where
↑

SQLiteを使って何をやるか？：　最終目標は２つのテーブルの連結 †

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

モデルケース：　あなたは参加者が2000人ぐらいの学会の会計をやることになった。参加者はウェブから氏名や所属などの参加登録データを送信する。送信されたデータはエクセルの表として保存されている。また、参加費は銀行振り込みで支払われる。振り込みデータもエクセルの表として保存されている。
右の表をダウンロードしてエクセルで開いてみよう。&ref(): File not found: "#13_table.xls" at page "授業/H21/情報処理/13";
（この表に記述されているデータは、全て架空のものです)
２つのシート（下の方にあるタブ）　「Web登録データ」と「銀行振り込み」が入っている。
会計係のあなたは、２つの表を比較して、参加費未納者や、振り込み金額間違いのリストを作らなければならない。また、メールアドレスを抜き出して、督促状を送らなければならない。

さて、どうすれば良いだろうか？

このケースでは、２つの表がる。

ウェブ登録で得られた参加者のデータ：　氏名、よみがな、所属、メールアドレス（後にmeiboと呼ぶ表） 銀行から得られた振り込みリスト：　日付、振り込み人氏名（カナ）、振り込み金額（後にfurikomiと呼ぶ表）

手作業で解決するならば、それぞれの表をエクセルで１つのシートにまとめ、氏名で並び替え、上から順に、会費未納の人はいないか（あるいは、２回振り込んでしまっている人がいないか）、振り込み金額が間違っている人がいないかをチェックする。
しかし、この例で用いた100件のデータでもかなり大変な作業になる。これが2000件とか1万件とかのデータを対象に手作業でやるとなると、、、、ちょっと気が遠くなってしまう。

こういうときこそ、データベースソフトウェアであるSQLiteの出番だ。まず答えだけを言ってしまうと、SQLiteを使って２つのテーブルを連結して、２つを比較する次の命令を与えれば、上の作業をコンピュータにやらせることができる。

select name, email, shozoku, date, amount from web left outer join ginko on name_kana = ginko_name;
では、２つのテーブルが連結できるところまで、話しを進めてみよう。全体の流れは次のようになる。

1. エクセルの表のデータをSQLiteのテーブルに移す
　1-1. SQLiteを起動し、webとginkoというテーブルを作成する
　　create table web (name, name_kana, shozoku, email);
　　create table ginko (date, ginko_name, amount);
　1-2. エクセルのデータをK2Editorにコピーし、タブ区切りテキストにしておく
　　web.txt, ginko.txtという２つのファイルを作る
　1-3. SQLiteで、webとginkoというテーブルに、web.txt, ginko.txtの内容をそれぞれインポートする
　　import 'z:\sqlite\web.txt' web
　　import 'z:\sqlite\ginko.txt' ginko
2. SQLiteの２つのテーブルで、ヨミガナが同じデータ同士を連結する
　2-1. select ... left outer join ...という命令で２つのテーブルを連結
　　select name, email, shozoku, date, amount from web left outer join ginko on name_kana = ginko_name;
　2-2. 得られた結果をK2Editorにコピーし、空白の連続をタブに変換して、エクセルで閲覧

上のような操作で２つのテーブルを連結することで、次のような表が得られ、Web登録をしたのに銀行振り込みしていない人がいるかどうかや、間違った金額を支払っている人がどれくらいいるかなどを知ることができる。

nameemailshozokudateamount
市川トミ子06S4080@gmail.com東北大・院・理2008-05-09(Fri)6000
越川和彦06S4010@gmail.com東大院・理学系・生物科学2008-05-16(Fri)7000
宇山智哉06S4009@gmail.com東邦大・医・生物2008-05-16(Fri)6000
宇山秀樹06S4008@gmail.com東邦大・理・生物
坂場三男06S4064@gmail.com東北大・院・生命科学2008-05-16(Fri)6000

というテーブルが得られることを期待している。上をみると、2番目の人は払いすぎ、4番目の人は払っていないことがわかる。

↑

SQLiteの起動：コマンドプロンプトから起動し、テーブルを作成 †

エクセルにあるデータをSQLiteで処理するには、まず、SQLiteを起動してテーブルを作成し、エクセルのデータを入れなければならない。

  エクセルの表のデータをSQLiteに移す
 そのために、SQLiteを立ち上げ、エクセルの表に対応するテーブルを作る
データベース名：　　　ex1.db　（コマンドプロンプトから「sqlite3 データベース名」で作成
    テーブル:       web   (起動後（プロンプトはsqlite> ）SQL命令（create table...)を入力
    テーブル:       ginko  (起動後（プロンプトはsqlite> ）SQL命令（create table...)を入力
    ※１つのデータベースは１つまたは複数のテーブルがあつまってできている
      エクセルのそれぞれの表は、データベースのテーブルのそれぞれに相当

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

Z:\sqlite>sqlite3 ex1.db

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

↑

先ほどの操作のまとめ †

上で行った操作とコマンドをまとめておこう。

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

命令文を知りたい時は †

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

以上、駆け足でSQLiteの使い方を見てきたが、データベースはどのパソコンでも使えるし、大量データの一括処理という意味では、これに勝る方法は無いのだが、一般ユーザーにはあまり使われていないのが現状だろう。皆さんも、今回の挑戦で行った操作をなんとなくで良いから覚えておき、今後のデータ処理に役立てて欲しい。

↑

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

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

ウェブページの仕組み [smile] †

↑

ウェブページで考えるインターネットのこちらとあちら †

slide11-1.gif
　普段皆さんがいつも使っているインターネット。非常に便利なもので、インターネットの普及前後で、世界は変わったとすらいわれている。ここで、インターネットがどのようなコンピュータで成立しているかを少し考えてみよう。
インターネットで使われている通信ケーブルのこちら側（ローカルといいます）には、皆さんが使っているコンピュータがある。ケーブルのあちら側（リモートといいます）にもコンピュータがあることは、おぼろげながら理解しているだろう。このとき、こちら側のコンピュータをクライアント、あちら側のコンピュータをサーバ（＊サーバーともいう。コンピュータ関係の人は割と、最後の長音記号を省略することが多い）と呼ぶ。
では、ここで質問。

あなたが普段使っているPukiwikiのレポート提出ページの置き場所は、サーバ？それともクライアント？
Pukiwikiのページで見出しを青色にしたり太字にしたりする処理をするのは、サーバ？それともクライアント？
レポート提出ページに貼った画像はサーバから直接画面に表示されるか、クライアントに保存されてから表示されるか？

この質問に全部答えられれば、たぶん、ネットのこちらとあちらを正しく理解できているだろうと思う。

↑

URLってなに？ †

　インターネットを介してリモートのサーバにアクセスするときには、URLを使って場所の指定をする。最近はとても一般的に使われるようになったURLという言葉、一度ぐらいは聞いたことがあるだろう。URLとは Uniform Resource Locatorの略で、インターネット上にある情報（resource）の場所を表す(locate)、統一的な(uniform)記述方式だ。例えば、今見ているページのURL:

http://bean.bio.chiba-u.jp/lab/index.php

というの示している内容は、

http://　この後に書かれている情報にどうやってアクセスするかという手段をしめすところ。
         httpはWebサーバとクライアント(Webブラウザなど)がデータを送受信するのに用いる通信手段(プロトコル）
         （HyperText Transfer Protocol)
         　皆さんが他に使う可能性がある通信手段は、
         httpsは暗号化されたhttp
　　ftp ファイル転送のためのプロトコル (File Transfer Protocol)　など
bean.bio.chiba-u.jp　://の直後に来る文字列はウェブサーバのドメイン名。ドメイン名というのは、サーバに着けられた
　　　　　　　　　　　名前。jpは日本を、chiba-uは千葉大を、bioは生物学科の、beanはサーバ（機械）に着けられた名前
                      サーバ１台の住所を指定するのに、国名、県名、市町村を書くようなもの
/lab/index.php　：　ドメイン名の後に来るのは、ディレクトリ名やファイル名。/  /で挟まれた部分は、一番最後の文字列で
　　　　　　　　　　示されたファイル名が入っているディレクトリを示しており、パスと呼ばれる。

　つまり、上のURLは、全部で、

bean.bio.chiba-u.jpというサーバの中のlabというディレクトリにあるindex.phpにhttpで接続しなさい

ということを表している。インターネットを介した情報へのアクセスのほとんどは、URLによるアドレス指定が必要。

http://bean.bio.chiba-u.jp/lab/　というドメイン名を使った書き方をしなくても、 http://133.82.xxx.xxx/lab/ というアクセス指定をしても同じページが表示される。実は、インターネットを介してアクセスできるコンピュータの１台１台は、固有のIPアドレスという、0から255までの数字が４つ並んだ名前を持っている。でも、数字じゃ分かりにくいので、上のURLで説明したドメイン名という理解しやすい文字列に変換して使っている。IPアドレスは数字を見ても分かるように個数が限られており、大切な資産になっている。なお、上の演習問題にある www.chiba-u.jp のIPアドレスは、172.26.1.133 だ。

↑

ウェブページは１つのファイル。サーバ上にウェブページ公開用ディレクトリに置かれている †

　さて、http://172.26.1.133/　にアクセスすると、千葉大学のホームページが表示される。

上の例だと、

千葉大のウェブサーバは(www.chiba-u.jp, 172.26.1.133)というアドレスで番地指定されている
このサーバに対して、http:接続しろという情報がインターネットを介して流れてきて、
しかも、どのファイルに接続しろという情報が書かれていなかったので
index.htmlというページを表示した

ということだ。試しに、次の接続をやってみよう。

↑

ウェブページはHTML(Hyper Text Markup Language)という言語で書かれている †

皆さんが普段みているウェブページには、文字の大きさを様々に変えて分かりやすく表示してあったり、色や配置が工夫されているものも多い。こういう文字の書式やレイアウトは、HTMLという言語を使って指定されている。ここで理解して欲しいのは、次の２点。

1. ウェブページの本体は、HTMLという書式で書かれたテキストファイルで、ウェブサーバに置かれている
2. タグ（< > で囲まれた書式指定用の命令）に従って、ブラウザが書式や画像の配置などを決めている

この授業では、どういうタグを使えばどのようなウェブページが作れるかということは、詳しくは説明しない。ウェブページでのいろんな表現手段に関しては、ウェブ上にとても丁寧で分かりやすい説明があるので、下のサイトを見て、必要に応じて勉強してほしい。非常に詳しい解説が載っているので、独学でも大丈夫。

↑

簡単なHTML書類の作成 [smile] †

　でも、せっかくなので、これからとても簡単なウェブページを作成してみる。ウェブページの本体はテキストファイルなので、K2Editorを使って作成してみよう。

↑

自分のホームページを公開しよう [smile] †

では、今作成したウェブページを実際にインターネットで公開してみましょう。千葉大学で使っている教育用計算機システムでは、学生個人が作ったウェブページを簡単にインターネット公開することができる（公序良俗に反するようなものは公開しないこと。また、著作権にも注意）。

ここまででウェブページを介した情報伝達の基本の解説は終わり。今回の復習課題の一つはHTMLファイルを作成して、自分のウェブサイトで公開すること。詳しい内容は、課題のところを見てみよう。

↑

第13回授業の課題 †

↑

課題１．意見調査 †

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

課題2（復習）：SQLiteへの挑戦の結果 †

↑

課題3（予習）：ウェブページの作成と公開 †