バイオインフォマティクス解析のためのSQLite入門

SQLite3 はリレーショナルデータベース管理システムのひとつ。サーバクライアントモデルではなく、データベースがファイルになっています。ライセンスがパブリックドメインであることもあり、組み込みDBとしてよく使われます。iPhone の位置情報などのデータを格納しているのも SQLite3 です。ウェブアプリケーションのフレームワークである、Ruby on Rails では標準の組み込みDBとして使われています。また中規模のデータベースならMySQLなどと比較しても遜色ない速度で利用できます。

バイオ業界でも、マイクロアレイやNGSデータを格納する際によく利用されており、Bioconductor の annotation package に含まれるデータも SQLite3 のデータベースになっています。

インストール

Mac OS X には標準でインストールされており、設定なしでターミナルから利用できます。Linuxでもほとんどのディストリビューションでパッケージマネジメントシステムでインストール可能です。Windows は知りません。

使いかた

SQLite3 が正常にインストールされていれば、以下のようにして対話型のクライアントを起動することができます。

sqlite3

データベースを作成するには (ここでは expressions.sqlite という名前のデータベースを作成する)

sqlite3 expressions.sqlite

とする。

テーブルを作りスキーマを確認する。スキーマとはテーブルの定義のこと。

sqlite> create table transcripts(id varchar(10), sample varchar(10), fpkm real);
sqlite> .schema
CREATE TABLE transcripts(id varchar(10), sample varchar(10), fpkm real);
sqlite>

データを格納する。

sqlite> insert into transcripts values("Nanog",  "ESC", 1000);
sqlite> insert into transcripts values("Pou5f1", "ESC", 1500);

データを検索する

sqlite> select * from transcripts;
Nanog|ESC|1000.0
Pou5f1|ESC|1500.0
sqlite> select * from transcripts where id = "Nanog";
Nanog|ESC|1000.0
sqlite> select fpkm from transcripts where id = "Nanog";
1000.0

ヘルプと終了は以下の通り。

sqlite> .help
sqlite> .exit

ローカルディレクトリに expressions.sqlite が作成されています。次回利用したい場合は、

sqlite3 expressions.sqlite

とすればよいだけです。

様々な bulk 処理

バイオインフォマティクスでは、データを機器や公的データベースから入手し、一度にデータベースに登録するのが主です。ここではその方法について解説します。

まず、スキーマをテキストファイルに書いておいて一気にテーブルを作成します。スキーマファイル expressions.schema を作成します。これはテキストエディタで作ります。中身は以下の通りです。

RNA-seq で gene と isoform レベルの遺伝子発現のデータを格納することを想定しています。テーブル名は英語の複数形で作りましょう。

DROP TABLE IF EXISTS genes;
CREATE TABLE genes (
  id      varchar(10),
  sample  varchar(10),
  fpkm    varchar(10)
);

DROP TABLE IF EXISTS isoforms;
CREATE TABLE isoforms (
  id      varchar(10),
  sample  varchar(10),
  fpkm    varchar(10)
);

このスキーマにあわせてデータベースをテーブルを作成します。

sqlite3 expressions.sqlite < expressions.schema

確認してみましょう。データベースに接続します。

sqlite3 expressions.sqlite

スキーマを確認します。

sqlite> .schema
CREATE TABLE genes (
  id      varchar(10),
  sample  varchar(10),
  fpkm    varchar(10));
CREATE TABLE isoforms (
  id      varchar(10),
  sample  varchar(10),
  fpkm    varchar(10)
);

次に、カンマ区切りテキストファイル(CSV)からテーブルを作成してみます。まずデータが含まれるCSVは以下のようになっています。

expressions.isoforms.csv

Nanog,ESC,1000
Pou5f1,ESC,1500

expressions.genes.csv

Nanog,ESC,1200
Pou5f1,ESC,1700

この2つのファイルをそれぞれ、isoforms, genes というテープルに取り込みます。

sqlite3 expressions.sqlite
sqlite> .separator ,
sqlite> .import expressions.isoforms.csv isoforms
sqlite> .import expressions.genes.csv    genes

試しに検索してみます。

sqlite> select fpkm from transcripts where id = "Nanog";

insert vs. bulk

データを1行ずつ insert/update するのと、bulk処理で入力する方法がありますが、どちらが良いのでしょうか? バイオインフォマティクスの解析の場合は、1行ずつデータを更新する機会は少なく、一度テキストファイルに出力された データをデータベースに取り込むことのほうが多いでしょう。この場合は、1行ずつ処理するより、bulk のほうが 圧倒的に速度が速いです。

例として sailfish から出力されたテキストファイルを SQLite3 に取り込んでみます。

1行ずつ insert していく方法の場合

Ruby と sqlite3-ruby を利用して、1行ずつ insert してみます。

time ruby bin/sailfish2sqlite.rb
ruby bin/sailfish2sqlite.rb  10.04s user 24.18s system 49% cpu 1:09.23 total

ファイルから取り込む場合

time sqlite3 results/sailfish_0.9/sailfish.salite3 < bin/sailfish2sqlite.sql
sqlite3 results/sailfish_0.9/sailfish.salite3 < bin/sailfish2sqlite.sql  0.54s user 0.07s system 98% cpu 0.619 total

以上の結果より、bulk処理のほうが、100倍以上速いことがわかります。