![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
大量データの一括処理：SQLiteによるリレーショナルデータベース入門 †【教科書：44-48p参照】 前回授業ではDNAデータベースを検索して、自分が必要とするデータを取り出し（検索・ダウンロード）、加工し（アラインメント）、目的とする解析（系統解析）を行いました。授業後の意見調査の結果でも、よく理解できたという人が多かったようです。理解しやすかったのは、皆さんが、インターネットを使って何かを検索し、データを取り出すという作業に慣れているせいだと思います。 授業で使ったDNAデータベースやYahoo、Amazonなどのサイトで、キーワードを入力して検索をかけた時、どうやってデータが取り出されているか想像したことはあるでしょうか？DNAデータベースだと、数千万件のデータ（配列にすると数百億！）、ちょっと実感がわかないほど大量のデータが処理されていますよね。 データの量が多くなるほど、目的のものを取り出すのは、なかなか大変なはずなんですが、やっぱり実感はわきません。では、もう少し、具体的な話しをしましょう。今、あなたの手元には、卒業研究で自分が決定したミオシン遺伝子の部分塩基配列100件分がFASTA形式ファイルとして保存されています。１件１件のデータは1000塩基ぐらいなのですが、この配列の中から、ATTATAATという配列から始まる300bpぐらいだけを抜き出したいと考えています。どうやりますか？ すでに皆さんが習得している方法だと、テキストエディタの検査機能を使うという手がありますね。K2EditorでFASTA形式ファイルを開いて、「検索」を選び、検索文字列にATTATAATを指定いて検索する。そうすると、画面上にATTATAATから始まる部分が強調表示されます。そこから500bpぐらいをマウスを動かして選択して、コピーして、別のウィンドウを開いてペーストして、また前の画面にもどって、この配列のアクセッション番号まで画面を動かして戻って、配列名やアクセッション番号を選択してコピーペーストして、。。。。あーめんどくさい。
テキストエディタでも、ワープロでも、検索はすごく便利な機能だけれど、自分が必要としている部分だけを取りだそうとすると、コピー・ペーストとも、かなり面倒な操作が必要になります（実感がわかない人は、上に書いた作業をやってみてください。途中で嫌になること請け合いです）。 こういう作業を簡単にしてくれるのがデータベースです。先に挙げたDNAデータベースでも、Yahooでも、Amazonでも、いずれもリレーショナルデータベースというシステムを利用することで、ものすごく大量のデータから、一瞬にして必要な情報を検索し、抽出し、並び替えるという処理をおこなっています。この、
というのが、テキストエディタやワープロには簡単にできないことです。データベースは、こういう面倒な処理を、大量のデータについて簡単に行えるという優れものです。大量データ処理に関しては、現時点における標準的なツールといっても良いでしょう。先日の調査結果によると、多くの皆さんが学生生活（＋研究生活）において、大量のデータを処理することになると考えているようです。そんなとき、データの抽出で、１件１件コピペするのも何か哲学的な意味を持った修行になるかもしれませんが、データベースシステムの扱いに慣れていれば、心と時間に余裕が生まれるでしょう。 データベースという言葉は、保存されたデータのまとまりそのものを指すことも、データを管理するシステムのことを指すこともあります。この授業でも特に厳密な定義はしません。授業で使うのは、リレーショナルデータベースというシステム（RDBS: Relational Data Base System）を無料で簡単に構築できるソフトウェア、SQLiteです。このソフトウェアを使って、実際のデータからデータベースを構築し、必要なデータを抽出することを学びます。その過程でSQL言語という、データベースとお話するための言葉を学びます。 プログラミング言語、HTMLに続いて３つめの言語ですが、安心してください。SQLはかなり英語の文章に近いので、直感的にも理解しやすいと思います。 関連リンク †
リレーショナルデータベースの実例 †それでは具体例をつかって説明してゆきましょう。「リレーショナル」という言葉を説明し出すと長くなるので、まず、実例をお見せします。押さえてほしいポイントは次の３点です。
簡単な例として、サークルの名簿と会費納入状況のデータベースを考えてみましょう。説明のためにはカラム名もかな・漢字表示をする方が良いのでしょうが、ここでは、キーボードからの入力の混乱を避けるために、カラムの名前は全て半角英数文字で表記します。 想定しているのは、次のようなデータです。
こういうデータなら、皆さんならどうやって管理しますか？ そうですね、エクセルで管理するという方法があります。では、ちょっとやってみましょう。 この表をエクセルで開いて、8月分の会費を集めたときは、2006_8という列（カラム）を手作業で一つ追加します。また、会費の未納状況を見るときには、納入状況を一つ一つ目で確かめます。納入された会費を集計するときには、行の右端でsumをとって、また、表の一番下でもsumをとります。e-mailもついでに管理しておこうとおもうと、列（カラム）を一つ追加するので、右のほうがますます見にくくなってしまいます。 しかも、この例では３件しかデータを示していませんが、サークルの会費なので、前の年のデータも有るはずです。なので、ワークシートはとても大きく、見にくくなりますね こんな感じ： 住所等のデータ 2003年 2004年 2005年 2006年 名前（2003年入会） ●●●●●●●●●●●●●●●●●●●● 名前（2004年入会） ●●●●●●●●●●●●●●●● 名前（2005年入会） ●●●●●●●●●● 名前（2006年入会） ●●●●● 006年の入会者にとっては、左の方の空白のセルがムダになっています。それに、毎年毎年、このワークシートはどんどん右側に大きく伸びてゆくので、1年生と4年生の支払い状況を比較するのは難しそうです。このように、無計画にエクセルで集計表を作ってしまうと、目で見て、手でカラムを追加するというのが大変な作業になってしまうのです。 そこで、手と目に大変なこの作業を、頭とリレーショナルデータベースを使って解決してみましょう。 この表をリレーショナルデータベースに作り直す場合、まず、データの構造を変更します。この表には、
という２つの異なるデータが混在しています。そこで、この２つを区別します。また、どの年のどの月の会費を払うかは、会員の入会時期によっても異なるデータなので、会費納入のデータは１行ずつに、会員番号と納入月を追加して表すことにします。そうすると、データの構造は、次のようになります。さらに、会費は年ごとに集計することもあるでしょうから、日付は年と月の２つに分けておく方が使いやすくなります（データ構造をシンプルに使いやすくすることを、データの正規化といいます）。
データの構造がかなりすっきりしたのが分かるでしょうか？「でも、会員番号と納入月だけ出されても、誰のデータかわかんない。。。」という疑問が出そうですよね。それを解決するのが「リレーショナルデータベース」の「リレーション」（関連づけ）という機能です。 上の２つの表は、「会員番号でリレーションをとる（関連づける）」ことによって(下の例では、ついでに名前順・日付順に並び替えています）、
という形で扱うことができるのです。 SQLiteとは †では、実際にデータベースシステムを操作してみましょう。ここではSQLiteというソフトを使います。SQLiteというのはフリーで使えるリレーショナルデータベースシステムです。他のデータベースシステム（例えばMySQLやPosgreSQL）ではユーザの設定やネットワークの設定など、面倒なことが多いのですが、SQLiteはダウンロードした実行ファイルをクリックするだけで、データベースを使うことができます。データベースのデータ本体も１つのファイルに保存されるので、コピーやバックアップが簡単にできるという優れものです。 とはいうものの、世間一般のパソコンユーザにはそれほど使わてはいません。なぜかというと コマンドラインからSQLという命令文を入力しなくてはならない 説明が英語 データの入出力が面倒(エクセルやエディタで前もって編集が必要） というのが敬遠されている理由でしょうが、Rの操作でコマンドラインに慣れた皆さんにとっては、それほど苦にはならないはずです。データの入出力が面倒なのは大問題ですが、SQLiteは、エクセルやテキストエディタと合わせて使うものと位置づけておけば良いでしょう。 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 へのデータの一括登録 †
キーを用いた２つのテーブルの連結表示 †２つのテーブルにデータが入ったかどうかを、両者に共通するnumberというキーで結びつけて表示してみましょう。 sqlite> .header on sqlite> select * from tbl1, tbl2 where tbl1.number=tbl2.number; このコマンドでは、全てのカラムが表示されるのでみずらいですね。表示したいカラムを選んで、しかも、年ごとに支払金額を集計するには、次のような命令を与えます。
あるいは
SQLiteの終了 †sqlite> .quit 上のように入力すれば、終了できます。 補足説明 †「こういうコマンドを考えるのは面倒くさいや。上の表をエクセルで、こんな風にして管理したって、同じことでしょ？」
はい。その通りです。 でも、次の２つの問題点があります。
ということです。こういうデータ構造でも問題はないのですが、あまりにもムダが多いので、２つに分けるのが良いということです。 先ほどの操作のまとめ †上で行った操作とコマンドをまとめておきます。 SQLの最後は ; （半角セミコロン）で終わる ドットコマンドの最初は . （半角ドット）で始まる （セミコロンは不要） 値は ' ' （シングルクォーツ）で囲む （※省略できる場合も多々ある）
SQLiteの短所：インターフェースの使いにくさ †大量データの扱いに優れており、しかも、無料で使えるSQLiteですが、コンピュータに慣れていない人には非常に使いにくいだろうなと思う点があります。それは、データを追加したり、編集したりするためのインターフェースの貧弱さです。sqliteを立ち上げて、データを一つ追加するコマンドを入力すると、 sqlite> insert into tbl1 values('3', '梶田 忠', '263-8522', '千葉市稲毛区小仲台', '043-290-2874', '1', '2006', '7');
などという、長く、しかも、シングルクォートがたくさんあってタイプミスを起こしやすそうな命令文を入力しなければなりません。検索・抽出がいかに素早くできても、データの追加にいちいちこういう複雑な命令文を考えなければならないのでは、困ってしまいます。 PostgreSQLなど、他のリレーショナルデータベースでは、データ管理を簡単に行える洗練されたフリーソフトがあります。PpostgreSQLと同じくらい人気のあるMySQLでも、同様に使いやすい管理用のフリーソフトがあります。ところが、SQLiteでは、管理ソフトはあるにはあるのですが、利用できる漢字コードの問題、SQLiteのバージョンの問題など、授業で使用するにはいくつか適当ではない点があって、今回は利用しません（自分でSQLiteを使ってみようという人は、SQLite3とSQLiteControlCenterの組み合わせを試してみると良いと思います）。 えーっ！？毎回毎回 insert tbl1 ナンタラカンタラ なんてタイプするなんてムリっ！ 編集するのにも update tbl1 ナンタラカンタラ なんていちいちタイプしなければならないなんて、使えなさすぎっ！ そういう面倒なことをしなくちゃならないのだったら、SQLiteは実用的ではないのでは？ という非難の声が聞こえてきそうです。本当にSQLiteは使い物にならないのでしょうか？ 皆さんがこれから出会うデータ処理の状況を考えてみて、データベースを常に立ち上げて管理する場面というのは、それほど沢山は無いと思います。それよりも、もっと多いのは、上の課題でやったように、複数の異なる表を組み合わせて、データを検索、抽出するという作業です。このような作業では、エクセル、テキストエディタ、SQLiteの組み合わせがものを言います。 SQLiteとエクセルやテキストエディタの合わせ技 †学生の成績一覧表の作成をエクセルやテキストエディタで行う †今手元に、国語、数学、英語の先生から提出された成績をまとめた書類があります。
さて、この４つの情報を統合して、学生ごとの成績を一覧表示する場合を考えてみましょう。こういうときエクセル、テキストエディタの組み合わせで、次のような手順で操作を行えば、４つの表から目的のデータを抜き出すことができます。
命令文を知りたい時は †ここまでで説明した命令文についてもっと知りたいときは、
第13回授業の課題 †
課題．意見調査 †下の囲みの中にあるアンケートをコピー・ペーストして、「回答：」の後に答えを書き込むこと。 *第13回授業アンケート **氏名： **課題への回答 -今日（7月12日）の授業の進み方は？（はやい、丁度いい、おそい） --回答： -今日の授業の難しさはどう感じましたか（簡単すぎ 簡単 丁度いい 難しい 難しすぎ）： --回答： -難しいと答えた人は、特にどの点が難しかったですか？： --回答： -今日の授業は（よく分かった 分かった 分からなかった）： --回答： -分からないと答えた人は、特にどの点が分からなかったですか？： --回答： -今日の講義で理解できなかった用語があったら挙げてください： --回答： -これまでの講義を振り返って、でもう一度説明して欲しいテーマや追加説明が欲しい演習などを挙げてください（複数可）。 --回答： 復習課題：リレーショナルデータベースについて † このページには先ほどの３科目のデータに加えて理科と社会のデータが添付されています。
ドットコマンドの説明（よく使うものだけを抜粋） †#ヘルプと現在の設定を表示
.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を実行
|
ex1.txt
WS000009.JPG
WS000006.JPG
