SQLiteからCSVファイルにSQLを投げてみた

データ分析

#SQLite

#CSV

#データベース

quiver

2016.08.16

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

SQLite3.14(円周率πと同じですね!)が2016/08/08にリリースされました。リリースノートによると、CSV仮想テーブルに対応したとありましたので、早速使ってみました。

Added the CSV virtual table for reading RFC 4180 formatted comma-separated value files.

この機能を使うと、CSV形式のログファイルにSQLで問い合わせるといったことが簡単にできます。

仮想テーブルについて

仮想テーブル(Virtual Table)はSQLiteのデータベースで管理されていないデータを仮想的に管理されているように見せるテーブルです。仮想テーブルで管理されているデータは、通常のテーブルのデータと同じように SQL インターフェースで操作できます。

SQLite では

などが仮想テーブルを使って実現されています。

CSV 仮想テーブルについて

SQLite の CSV 仮想テーブルは SQLite の標準構成(amalgamation)には含まれていません。 SQLite には実行時にライブラリを読み込む機能が備わっているため、CSV 仮想テーブル機能を共有ライブラリとしてコンパイルし、実行時に有効にするのが良いでしょう。

検証環境について

以下では Amazon Linux 2016.03.03(64 bit) を使い SQLite 3.14 と CSV 仮想テーブル機能を検証します。

インストール

EC2 の起動

Amazon Linux 2016.03.03 の EC2 を立ち上げます。 AMI は amzn-ami-hvm-2016.03.3.x86_64-gp2 を利用しました。

パッケージを最新にし、コンパイルに必要なライブラリをインストールします。

$ sudo yum update -y
$ sudo yum groupinstall -y "Development Tools"

SQLite のコンパイル

共有ライブラリのコンパイル時にヘッダーファイルが必要なため、SQLite 3.14 をソースからコンパイルします。

ソースコードとして sqlite-amalgamation-3140100.zip (1.89 MiB) を利用します。

https://www.sqlite.org/download.html からダウンロードします。

$ curl -O https://www.sqlite.org/2016/sqlite-amalgamation-3140100.zip
$ unzip sqlite-amalgamation-3140100.zip
$ cd sqlite-amalgamation-3140100
$ gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread

ファイル sqlite3 が作成されていることを確認します

$ ./sqlite3 --version
3.14.1 2016-08-11 18:53:32 a12d8059770df4bca59e321c266410344242bf7b

CSV 仮想テーブルのコンパイル

CSV 拡張テーブル用ファイルは別ファイルになっているため、ダウンロードして、共有ライブラリとしてコンパイルします。

ファイルは https://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/csv.c にあるため、ここからローファイルをダウンロードします。

$ curl -o csv.c https://www.sqlite.org/src/raw/ext/misc/csv.c?name=816a3715356e4210dae2d242057745e937050896 
$ gcc -g -fPIC -shared csv.c -o csv.so -I.

SQLite3 のソースコードがなければ、ヘッダーファイルが見つからず、次の様なエラーが発生します。ご注意下さい。

$ gcc -g -fPIC -shared csv.c -o csv.so -I.
csv.c:42:24: fatal error: sqlite3ext.h: No such file or directory
 #include <sqlite3ext.h>
                        ^
compilation terminated.

CSV 仮想テーブルを使う

最後に CSV 仮想テーブルを使ってみましょう。

検証用CSVファイル

以下の様に、ヘッダー行がなく、都市名と人口だけが記載されたCSVファイル(de.csv)を用意します。

Berlin,3426354
Hamburg,1739117
Munich,1260391
Cologne,963395
Frankfurt am Main,650000
Essen,593085
Stuttgart,589793
Dortmund,588462
Dusseldorf,573057
Bremen,546501

CSV 仮想テーブルへのロード

CSV 仮想テーブルの有効化

カレントディレクトリにはプログラム sqlite3 と共有ライブラリ csv.so があるものとします。

$ ./sqlite3 test.db
SQLite version 3.14.1 2016-08-11 18:53:32
Enter ".help" for usage hints.
sqlite> .load ./csv

CSV 仮想テーブルへのロード

CSV ファイル de.csv のデータを仮想テーブル foo にロードします。

sqlite> CREATE VIRTUAL TABLE temp.foo USING csv(filename='de.csv');

仮想テーブルの中を覗いてみましょう。

スキーマを指定しなかった場合、1カラム目から c0, c1, ..., という cNNN 形式のカラム名が自動的に振られます。

sqlite> .schema foo
CREATE VIRTUAL TABLE temp.foo USING csv(filename='de.csv');
sqlite> pragma table_info(foo);
0|c0|TEXT|0||0
1|c1|TEXT|0||0
sqlite> SELECT * FROM foo;
Berlin|3426354
Hamburg|1739117
Munich|1260391
Cologne|963395
Frankfurt am Main|650000
Essen|593085
Stuttgart|589793
Dortmund|588462
Dusseldorf|573057
Bremen|546501

CSVファイルのスキーマを指定する

USING csv の中の schema 引数に CREATE TABLE 文でスキーマを定義することもできます。

sqlite> CREATE VIRTUAL TABLE temp.bar USING csv(filename='de.csv', schema = "create table x(city TEXT,population INTEGER)");
sqlite> pragma table_info(bar);
0|city|TEXT|0||0
1|population|INTEGER|0||0
sqlite> select city, population from bar where population > 1000000;
Berlin|34263540
Hamburg|1739117
Munich|1260391

CSVファイルで利用するカラム数を指定する

デフォルトではCSVファイルの1行目を元に仮想テーブルのスキーマが確定されます。 CSVファイルの先頭Nカラムだけを仮想テーブルにロードしたい場合は、USING csv の中の columns 引数にカラム数を指定します。例えば、CSVファイルの1カラム目までを利用する場合、columns=1 となります。

sqlite> CREATE VIRTUAL TABLE temp.piyo USING csv(filename='de.csv', columns=1);
sqlite> pragma table_info(piyo);
0|c0|TEXT|0||0

注意点

一時テーブルとして仮想テーブルを作成する

仮想テーブルは一時テーブルとして作成することが多いかと思います。

通常のテーブルであれば、SQLite は CREATE TEMP TABLE 文で一時テーブルを作成できます。

仮想テーブルを作成するCREATE VIRTUAL TABLE には一時テーブル版の CREATE TEMP VIRTUAL TABLEが存在しないため、テーブル名を temp. で接頭します。

CSV ファイルが更新されたら?

仮想テーブルのため、CSVファイルが更新されると、SELECT 結果も影響を受けます。

たとえば、CSV ファイルを先頭3行だけ残して削除します。

$ sed -ie '4,$d' de.csv
$ cat de.csv
Berlin,34263540
Hamburg,1739117
Munich,1260391

SELECT 結果は次のように変わりました

sqlite> select * from foo;
Berlin|34263540
Hamburg|1739117
Munich|1260391
sqlite>

CSV 仮想テーブルの更新はできない

現時点では、CSV 仮想テーブルはリードオンリーのようです

sqlite> insert into foo values('foo', 300);
Error: table v2 may not be modified

リリースノートにも"Added the CSV virtual table for reading RFC 4180 formatted comma-separated value files." と writing には触れられていません。

CSV ファイルの先頭行をスキップできない

ソースコードのコメント欄を読むと

/*
** Parameters:
...
**    header=YES|NO              First row of CSV defines the names of
**                               columns if "yes".  Default "no".
...
** If header=YES, then the first row is skipped.
*/

というように、CSV ファイルのヘッダー行をスキップするパラメーターがあるように見受けられるのですが、実際の header=YESとすると、CSVファイルは1行も読み込まれません。ドキュメントページ The CSV Virtual Table の "2. Recognized Arguments" には headerパラメーターへの言及がないので、実装中なのかもしれません。

CSV インポート機能

CSV 仮想テーブルとは別に CSV インポート・エクスポート機能があります。この機能は CSV 形式でテーブルにデータをインポート・エクスポートするものです。

あくまで CSVファイルとテーブル間でデータ操作をするインターフェースでしかなく、仮想テーブルとは関係ありません。

参考までに、CSV 形式のデータ(de.csv)をSQLite のテーブル(baz)にロードするには以下の様にします。

sqlite> .mode csv
sqlite> CREATE TABLE baz(a, b);
sqlite> .import de.csv 
sqlite> select * from baz;
Berlin,3426354
Hamburg,1739117
Munich,1260391
Cologne,963395
"Frankfurt am Main",650000
...