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

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

[Go](https://golang.org/)で作ってます。
githubの [trdsql](https://github.com/noborus/trdsql)からダウンロード出来ます。
また[Linux/Windows/macOSのバイナリ](https://github.com/noborus/trdsql/releases)もあります。

以下は、古くなっている内容もいくつかあります。
最新版に対応した、より詳細な内容は [trdsql 目次 | Noboru Saito's page](https://noborus.github.io/ja/trdsql/) を参照して下さい。

## これは何？

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

同様のツールが [q](https://github.com/harelba/q) や [textql](https://github.com/dinedal/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が出力されます。

```sh
$ trdsql "SELECT * FROM test.csv"
```

SQLが基本的にそのまま使えるので、条件指定／集約／並べ替え／SQL関数が使えます。Column指定はデフォルトではc1,c2,c3...と指定出来ます。

```sh
$ trdsql "SELECT c1,c2 FROM test.csv WHERE c1 > 2"
```

CSVファイルのヘッダーにColumn名がある場合は -ih を指定することでColumn名として扱うようになります。

```test.csv
id,name
1,Orange
2,Melon
3,Apple
```

```sh
$ 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出力です。

```sh
$ 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 " " とした場合にスペース区切りになります。要素の前後のスペースは除去されるので、こんなことも出来ます。

```sh
$ 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
```

```sh
$ 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](https://www.postgresql.jp/document/9.6/html/libpq-connect.html#libpq-connstring)、[mysql](https://github.com/go-sql-driver/mysql#dsn-data-source-name)等を参照してください。

SQLiteではエラーになる構文でも...

```sh
$ 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ドライバを使用すると実行できます。

```sh
$ 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 テーブルがあるとすると

```shell-session
$ psql test
test=# SELECT  * from fruits ;
 id |   name   
----+----------
  1 | オレンジ
  2 | メロン
  3 | りんご
(3 rows)
```

```shell-session
$ 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 |

```sh
$ trdsql -ih "SELECT count(*) FROM test.csv"
3
```

```sh
$ 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を使用）

```shell-session
$ trdsql -ih -omd "SELECT 作者,GROUP_CONCAT(作品) FROM sample.csv GROUP BY 作者"
```

|   作者    |         GROUP_CONCAT(作品)         |
|-----------|------------------------------------|
| 夏目漱石  | 坊つちやん,吾輩は猫である,それから |
| 宮沢 賢治 | 銀河鉄道の夜,注文の多い料理店      |

PostgreSQL(配列にしてから文字列に変換）

```shell-session
$ 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を使用することも出来ます。

```shell-session
$ 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 |


```shell-session
$ 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の実行は可能です。そのため、以下の様にして実テーブルにすることが可能です。

テーブル作成

```shell-session
$ trdsql -ih -driver postgres -dsn "dbname=test" \
"CREATE TABLE test AS SELECT * FROM test.csv"
```

挿入

```shell-session
$ trdsql -ih -driver postgres -dsn "dbname=test" \
"INSERT INTO test SELECT id::integer,name FROM test.csv"
```

実はSQLiteファイルへの保存も可能です。

```shell-session
$ trdsql -driver "sqlite3" -dsn "test.sqlite" -ih \
"CREATE TABLE test AS SELECT * FROM test.csv"
```

### 出力変換

出力フォーマットを指定して変換出来ますので、変換ツールとしても使用出来ます。
また、テーブルとのJOINが可能だと書きましたが、CSVファイルを使わなくても実行は可能です。
そのため、単にデータベース内のテーブルを出力するためだけに使用することも出来ます。

```shell-session
$ trdsql -driver "postgres" -dsn "dbname=test" "SELECT * FROM test"
1,Orange
2,Melon
3,Apple
```

```shell-session
$ trdsql -oat -driver "postgres" -dsn "dbname=test" "SELECT * FROM test"
+----+--------+
| id |  name  |
+----+--------+
|  1 | Orange |
|  2 | Melon  |
|  3 | Apple  |
+----+--------+
```

```shell-session
$ trdsql -omd -driver "postgres" -dsn "dbname=test" "SELECT * FROM test"
| id |  name  |
|----|--------|
|  1 | Orange |
|  2 | Melon  |
|  3 | Apple  |
```

```shell-session
$ trdsql -ojson -driver "postgres" -dsn "dbname=test" "SELECT * FROM test"
[
  {
    "id": "1",
    "name": "Orange"
  },
  {
    "id": "2",
    "name": "Melon"
  },
  {
    "id": "3",
    "name": "Apple"
  }
]
```

```shell-session
$ trdsql -oltsv -driver "postgres" -dsn "dbname=test"  "SELECT * FROM test"
id:1	name:Orange
name:Melon	id:2
id:3	name:Apple
```

※ LTSV出力は列の出力順が不定になります。

```shell-session
$ 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](https://github.com/noborus/trdsql/blob/master/config.json.sample)を作成しておくとデフォルトのデータベースを変更でき、データベースドライバの情報を設定しておくと -db オプションにより接続の切り替えが簡単に出来るようになります。

それにより SQLite、PostgreSQL、MySQLの挙動を確認することに利用できます。

例）

```shell-session
$ 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
```

```shell-session
$ 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](https://github.com/noborus/trdsql/blob/master/README.md)を参照して下さい。

上記の組み合わせにより psやdfをMarkDown Tableで出力したりといったことも出来ます。また、[chart](https://github.com/marianogappa/chart)との相性もよいので、chartへの入力データとして使えます。

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

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

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

![chart.png](https://qiita-image-store.s3.amazonaws.com/0/18555/9b6a8caf-e00e-f74a-328d-cf45e6d97301.png)