trdsqlというコマンドラインツールを作りました。
Goで作ってます。
githubの trdsqlからダウンロード出来ます。
またLinux/Windows/macOSのバイナリもあります。
以下は、古くなっている内容もいくつかあります。
最新版に対応した、より詳細な内容は trdsql 目次 | Noboru Saito's page を参照して下さい。
これは何？
簡単に言えばCSV(TSV含む)やLTSVに対してSQLを実行できるツールです。
trdsqlはPostgreSQLドライバとMySQLドライバを含んでいて実際にDBに接続することでSQLite以上の機能を使用できるようにしています。
PostgreSQLやMySQLの構文が使えるだけではなく、CSVファイルと実テーブルでJOINしたり、CSVファイルからテーブル作成といったことが簡単にできるようになります。
またCSVだけでなく以下のフォーマットに出力可能です。
- CSV
- LTSV
- ASCII Table
- MarkDown Table
- JSON
- Raw
- Vertical Format
基本的な使い方
基本的な使用方法は、SQLのテーブルの代わりにCSVファイルを指定してSQLを実行することです。デフォルトではCSVが出力されます。
$ trdsql "SELECT * FROM test.csv"
SQLが基本的にそのまま使えるので、条件指定／集約／並べ替え／SQL関数が使えます。Column指定はデフォルトではc1,c2,c3...と指定出来ます。
$ trdsql "SELECT c1,c2 FROM test.csv WHERE c1 > 2"
CSVファイルのヘッダーにColumn名がある場合は -ih を指定することでColumn名として扱うようになります。
id,name
1,Orange
2,Melon
3,Apple
$ trdsql -ih "SELECT name,id FROM test.csv WHERE name = 'Orange'"
LTSVの場合はラベルがColumn名になります。LTSVを対象とする場合は、「-iltsv」又は、拡張子が(ltsv/LTSV)ならば 「-ig」によりLTSVと判定して処理されます。
id:1 name:Orange price:50
id:2 name:Melon price:500
id:3 name:Apple price:100
出力方法はオプションで指定出来ます。「-oat」 はASCII Table出力です。
$ trdsql -ig -oat \
"SELECT price,name FROM testdata/test.ltsv ORDER BY CAST(price AS NUMERIC)"
+-------+--------+
| price | name |
+-------+--------+
| 50 | Orange |
| 100 | Apple |
| 500 | Melon |
+-------+--------+
※ 文字列以外として解釈させたい場合はSQLのCASTを使用します。
CSV,LTSVファイルを指定するだけでなく「標準入力」にも対応しています。その場合はテーブル名に「-」を指定します。 -id " " とした場合にスペース区切りになります。要素の前後のスペースは除去されるので、こんなことも出来ます。
$ ps|trdsql -id " " -ih -oat \
"SELECT pid,time,cmd FROM - LIMIT 3"
+-------+----------+--------+
| PID | TIME | CMD |
+-------+----------+--------+
| 9690 | 00:00:03 | zsh |
| 16897 | 00:00:00 | gocode |
| 17313 | 00:00:00 | atom |
+-------+----------+--------+
2つ以上のファイルをJOINすることも出来ます。先ほどのLTSVとCSVをJOINしてみます。
id,color
1,orange
2,red
3,green
4,yellow
$ trdsql -ig -ih -oat \
"SELECT t.id,t.name,c.color FROM test.ltsv AS t LEFT JOIN color.csv AS c ON (t.id = c.id)"
+----+--------+--------+
| id | name | color |
+----+--------+--------+
| 1 | Orange | orange |
| 2 | Melon | red |
| 3 | Apple | green |
+----+--------+--------+
以上は内部的にはSQLiteで動作していました。SQL構文はSQLiteで可能な構文に限られます。
trdsqlはドライバに PostgreSQL/MySQLを指定することが出来ます。
SQLiteではエラーになる構文でも...
$ trdsql -ih -ig -oat \
"SELECT t.id,t.name,c.color FROM test.ltsv AS t RIGHT JOIN color.csv AS c ON (t.id = c.id)"
2017/08/06 11:39:34 ERROR: SQL:RIGHT and FULL OUTER JOINs are not currently supported
[SELECT t.id,t.name,c.color FROM `test.ltsv` AS t RIGHT JOIN `color.csv` AS c ON (t.id = c.id)]
postgresドライバを使用すると実行できます。
$ trdsql -driver postgres -dsn "dbname=test" -ih -ig -oat \
"SELECT t.id,t.name,c.color FROM test.ltsv AS t RIGHT JOIN color.csv AS c ON (t.id = c.id)"
+----+--------+--------+
| id | name | color |
+----+--------+--------+
| 1 | Orange | orange |
| 2 | Melon | red |
| 3 | Apple | green |
| | | yellow |
+----+--------+--------+
残念ながらSQLiteは同梱されているので、何もなくても動作しますが、PostgreSQL/MySQLは動作しているデータベースサーバーが必要になります。
既存のデータベースに接続するとさらに、CSV/LTSVファイルとデータベースのテーブルをJOINすることが出来ます。
例えば fruits テーブルがあるとすると
$ psql test
test=# SELECT * from fruits ;
id | name
----+----------
1 | オレンジ
2 | メロン
3 | りんご
(3 rows)
$ trdsql -ih -driver postgres dsn="dbname=test" \
"SELECT t.id,t.name,c.name FROM test.csv AS t LEFT JOIN fruits AS c ON (t.id::integer = c.id)"
1,orange,オレンジ
2,melon,メロン
3,apple,りんご
のようなことが出来ます。
使用例
CSVファイルの集計
SQLには集約関数が揃っているので、集計処理に使用すると便利です。
以下の様なtest.csvがあるとします。
| id | name | price |
|---|---|---|
| 1 | Orange | 50 |
| 2 | Melon | 500 |
| 3 | Apple | 100 |
$ trdsql -ih "SELECT count(*) FROM test.csv"
3
$ trdsql -ih -omd "SEELCT max(price),min(price),sum(price),avg(price) FROM test.csv"
| max(price) | min(price) | sum(price) | avg(price) |
|---|---|---|---|
| 500 | 100 | 650 | 216.66666666666666 |
文字列の集約
以下の様なCSVがあったとして作者でまとめて表示したい場合があります。
作者,作品
宮沢 賢治,銀河鉄道の夜
夏目漱石,坊つちやん
夏目漱石,吾輩は猫である
宮沢 賢治,注文の多い料理店
夏目漱石,それから
標準ではありませんが、SQLの関数が用意されています。
SQLite,MySQL（GROUP_CONCATを使用）
$ trdsql -ih -omd "SELECT 作者,GROUP_CONCAT(作品) FROM sample.csv GROUP BY 作者"
| 作者 | GROUP_CONCAT(作品) |
|---|---|
| 夏目漱石 | 坊つちやん,吾輩は猫である,それから |
| 宮沢 賢治 | 銀河鉄道の夜,注文の多い料理店 |
PostgreSQL(配列にしてから文字列に変換）
$ trdsql -driver postgres -dsn "dbname=test"-ih -omd \
"SELECT 作者,array_to_string(array_agg(作品),',') FROM sample.csv GROUP BY 作者"
| 作者 | array_to_string |
|---|---|
| 夏目漱石 | 坊つちやん,吾輩は猫である,それから |
| 宮沢 賢治 | 銀河鉄道の夜,注文の多い料理店 |
Window関数、GROUPING SETS
PostgreSQLドライバを利用すればWindow関数やGROUPING SETSを使用することも出来ます。
$ trdsql -ih -omd -driver postgres -dsn "dbname=test" \
"SELECT name, price, sum(price::int) OVER (ORDER BY price::int) FROM test.csv "
| name | price | sum |
|---|---|---|
| Orange | 50 | 50 |
| Apple | 100 | 150 |
| Melon | 500 | 650 |
$ trdsql -ih -omd -driver postgres -dsn "dbname=test" \
"SELECT name, sum(price::int) FROM test.csv GROUP BY CUBE(name)"
| name | sum |
|---|---|
| Apple | 100 |
| Melon | 500 |
| Orange | 50 |
| 650 |
※ 上記の例は一つずつしかありませんが、Apple,Melon,Orange別で小計を出しつつ全体の総計を出しています。
テーブル作成・挿入
trdsql自体は実テーブルに保存する機能は備えていませんが、SQLの実行は可能です。そのため、以下の様にして実テーブルにすることが可能です。
テーブル作成
$ trdsql -ih -driver postgres -dsn "dbname=test" \
"CREATE TABLE test AS SELECT * FROM test.csv"
挿入
$ trdsql -ih -driver postgres -dsn "dbname=test" \
"INSERT INTO test SELECT id::integer,name FROM test.csv"
実はSQLiteファイルへの保存も可能です。
$ trdsql -driver "sqlite3" -dsn "test.sqlite" -ih \
"CREATE TABLE test AS SELECT * FROM test.csv"
出力変換
出力フォーマットを指定して変換出来ますので、変換ツールとしても使用出来ます。
また、テーブルとのJOINが可能だと書きましたが、CSVファイルを使わなくても実行は可能です。
そのため、単にデータベース内のテーブルを出力するためだけに使用することも出来ます。
$ trdsql -driver "postgres" -dsn "dbname=test" "SELECT * FROM test"
1,Orange
2,Melon
3,Apple
$ trdsql -oat -driver "postgres" -dsn "dbname=test" "SELECT * FROM test"
+----+--------+
| id | name |
+----+--------+
| 1 | Orange |
| 2 | Melon |
| 3 | Apple |
+----+--------+
$ trdsql -omd -driver "postgres" -dsn "dbname=test" "SELECT * FROM test"
| id | name |
|----|--------|
| 1 | Orange |
| 2 | Melon |
| 3 | Apple |
$ trdsql -ojson -driver "postgres" -dsn "dbname=test" "SELECT * FROM test"
[
{
"id": "1",
"name": "Orange"
},
{
"id": "2",
"name": "Melon"
},
{
"id": "3",
"name": "Apple"
}
]
$ trdsql -oltsv -driver "postgres" -dsn "dbname=test" "SELECT * FROM test"
id:1 name:Orange
name:Melon id:2
id:3 name:Apple
※ LTSV出力は列の出力順が不定になります。
$ trdsql -ovf -driver "postgres" -dsn "dbname=test" "SELECT * FROM test"
---[ 1]---------------------------------------------------------------
id | 1
name | Orange
---[ 2]---------------------------------------------------------------
id | 2
name | Melon
---[ 3]---------------------------------------------------------------
id | 3
name | Apple
データベースの挙動確認
無くても動作しますが、あらかじめconfig.jsonを作成しておくとデフォルトのデータベースを変更でき、データベースドライバの情報を設定しておくと -db オプションにより接続の切り替えが簡単に出来るようになります。
それにより SQLite、PostgreSQL、MySQLの挙動を確認することに利用できます。
例）
$ trdsql -db sdb "SELECT 0.01 + 0.23" #SQLite
0.24000000000000002
$ trdsql -db pdb "SELECT 0.01 + 0.23" #PostgreSQL
0.24
$ trdsql -db mdb "SELECT 0.01 + 0.23" #MySQL
0.24
$ trdsql -db sdb "SELECT log(2.0)" # SQLite
2017/08/15 17:50:34 ERROR: SQL:no such function: log
[SELECT log(2.0)]
$ trdsql -db pdb "SELECT log(2.0)" # PostgreSQL
0.30102999566398119521
$ trdsql -db mdb "SELECT log(2.0)" # MySQL
0.6931471805599453
※ ちなみに PostgreSQLのlog()は常用対数(MySQLのlog10()相当）で、MySQLのlog()は自然対数（PostgreSQLのln()相当）だそうです。
その他
その他のオプションはREADME.mdを参照して下さい。
上記の組み合わせにより psやdfをMarkDown Tableで出力したりといったことも出来ます。また、chartとの相性もよいので、chartへの入力データとして使えます。
（このツールでなくても出来ますが
、CSV、コマンドの出力、データベースの出力を使い分けずにSQLを書けば良いので自分では使ってます）
$ ps xu|trdsql -ih -od "\t" -id " " \
"SELECT \"COMMAND\", \"%MEM\" as m FROM - ORDER BY m DESC LIMIT 10"|chart bar
（以下がブラウザに表示されます）
