LoginSignup
329
251

More than 3 years have passed since last update.

@noborus(Noboru Saito)

trdsqlというコマンドラインツールを作った

Last updated at Posted at 2017-08-15

trdsqlというコマンドラインツールを作りました。

Goで作ってます。
githubの trdsqlからダウンロード出来ます。
またLinux/Windows/macOSのバイナリもあります。

以下は、古くなっている内容もいくつかあります。
最新版に対応した、より詳細な内容は trdsql 目次 | Noboru Saito's page を参照して下さい。

これは何？

簡単に言えばCSV(TSV含む)やLTSVに対してSQLを実行できるツールです。

同様のツールが q や textql 等いくつかあります。

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名として扱うようになります。

test.csv
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と判定して処理されます。

test.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してみます。

color.csv
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を指定することが出来ます。

  • -driverに "postgres" 又は "mysql"を指定します。
  • -dsnはDBによって違うのでpostgres、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があったとして作者でまとめて表示したい場合があります。

sample.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への入力データとして使えます。

（このツールでなくても出来ますが:sweat_smile:、CSV、コマンドの出力、データベースの出力を使い分けずにSQLを書けば良いので自分では使ってます）

$ ps xu|trdsql -ih -od "\t"  -id " " \
"SELECT \"COMMAND\", \"%MEM\" as m FROM - ORDER BY m DESC LIMIT 10"|chart bar

（以下がブラウザに表示されます）

chart.png

329
251
7

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
329
251