MySQLとSQLiteからCSVファイルにSQLを投げてみた
本エントリは以下のAdvent Calendar 18日目のクロスエントリです。
o
SQLite Advent Calendar 2022
o
MySQL Advent Calendar 2022
ほとんどのRDBMSでは、CSVファイルをデータベースにインポートする機能があります。
ただ、ちょっとCSVファイルを覗いてみる、しかもSQLを使って、ということになると、いちいちロードするのは大変!
一部のRDBMSには、CSVファイルを直接扱う機能があります。今回はSQLiteとMySQLについて紹介します。
SQLite
以前のブログでとりあげた仮想テーブルの機能を利用するとCSVファイルを直接扱えます。
前回紹介した以下のページはLinuxでの利用方法が紹介されていましたので、ここではWindowsで扱う方法を同様に紹介します。
SQLiteからCSVファイルにSQLを投げてみた
この記事同様に最新の本体ソースコードをダウンロードします。
2022年12月現在では、sqlite-amalgamation-3140100.zip (1.89 MiB) ではなく、
sqlite-amalgamation-3400000.zip(2.48 MiB)になります。
https://www.sqlite.org/download.html からダウンロードし、ソースコードを展開しておきます。
本体のコンパイルには、以前紹介した以下の記事のとおり行います。
Windows上で、SQLiteのGeopolyを使う(ということはMSVCでコンパイルするのねん)
メニューから開発コマンドプロンプト(Developper Command Prompt)を開き、以下のコマンドを発行します。
cl shell.c sqlite3.c -Fesqlite3.exe
今度はCSV用のDLLを作成します。同じディレクトリに以下のページからダウンロードします。
https://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/csv.c
ジャンプ先の「Download」ボタンを押してください。
ダウンロードされたCSV.cファイルを指定して、DLLとして作成(/LD)します。
cl /I. /LD csv.c
これでcsv.dllが作成されます。
/Iで.(カレントディレクトリ)を指定しないと、以下のエラーになりますのでご注意ください。
csv.c(42): fatal error C1083: include ファイルを開けません。'sqlite3ext.h':No such file or directory
参照先のブログにある(de.csv)を用意して、Windowsでも実際に使ってみましょう。
C:¥Users¥mekimura¥Downloads¥sqlite-amalgamation-3400000>sqlite3 test.db
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
sqlite> .load csv.dll
sqlite> CREATE VIRTUAL TABLE temp.foo USING csv(filename='de.csv');
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
参照先のブログにあるように読み込み専用のようです。
sqlite> insert into foo values('foo', 300);
Parse error: table foo may not be modified
それ以外も同様ですが、一点改善されており、headerが有効になります。
以下の例ではheaderを指定したので、一行目がカラム名としてあつかわれ、二行目からの読み込みになっています。
sqlite> CREATE VIRTUAL TABLE temp.foo2 USING csv(filename='de.csv',header);
sqlite> select * from foo2;
Hamburg|1739117
Munich|1260391
Cologne|963395
Frankfurt am Main|650000
Essen|593085
Stuttgart|589793
Dortmund|588462
Dusseldorf|573057
Bremen|546501
MySQL
MySQLのほうはもっと簡単で、デフォルトの構成でCSVストレージエンジンが用意されています。
16.4 CSV ストレージエンジン
testデータベースで以下のコマンドを発行します。
mysql> create table de(city TEXT,population INTEGER) engine=csv;
ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns
NULLは許容できないので、not nullをつけ、text型をvarcharに変更します。
mysql> create table de(city varchar(20) not null,population INTEGER not null) engine=csv;
Query OK, 0 rows affected (0.03 sec)
datadir/testを見ると以下のように三つのファイルができています。
2022/12/19 04:15 35 de.CSM
2022/12/19 04:15 0 de.CSV
2022/12/19 04:15 2,527 de_2140.sdi
# MySQL 8.0以降はこうなりますが、5.7以前はde_2140.sdiの代わりにde.FRMが生成されます。
このうちのde.csvを差し替えて、flush tables;すれば内容が反映されます。
mysql> select * from de;
+-------------------+------------+
| city | population |
+-------------------+------------+
| Berlin | 3426354 |
| Hamburg | 1739117 |
| Munich | 1260391 |
| Cologne | 963395 |
| Frankfurt am Main | 650000 |
| Essen | 593085 |
| Stuttgart | 589793 |
| Dortmund | 588462 |
| Dusseldorf | 573057 |
| Bremen | 546501 |
+-------------------+------------+
10 rows in set (0.03 sec)
CSVファイルの差し替えは以下のブログでも扱われています。
たった3秒でInnoDBのデータローディングが快適になるライフハック
SQLiteとは違って更新も可能です。
mysql> insert into de values('test',1000);
Query OK, 1 row affected (0.02 sec)
mysql> select * from de;
+-------------------+------------+
| city | population |
+-------------------+------------+
| Berlin | 3426354 |
| Hamburg | 1739117 |
| Munich | 1260391 |
| Cologne | 963395 |
| Frankfurt am Main | 650000 |
| Essen | 593085 |
| Stuttgart | 589793 |
| Dortmund | 588462 |
| Dusseldorf | 573057 |
| Bremen | 546501 |
| test | 1000 |
+-------------------+------------+
11 rows in set (0.00 sec)
ただ文字列は"(ダブルクォーテーション)付で扱われるため、実際に更新などするのであれば、文字列型は"付きであわせておきましょう。
Berlin,3426354
Hamburg,1739117
Munich,1260391
Cologne,963395
Frankfurt am Main,650000
Essen,593085
Stuttgart,589793
Dortmund,588462
Dusseldorf,573057
Bremen,546501
"test",1000