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

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

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

これは何?

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

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

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によって違うのでpostgresmysql等を参照してください。

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


『 Go 』Article List
Category List

Eye Catch Image
Read More

Androidに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

AWSに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

Bitcoinに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

CentOSに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

dockerに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

GitHubに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

Goに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

Javaに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

JavaScriptに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

Laravelに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

Pythonに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

Rubyに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

Scalaに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

Swiftに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

Unityに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

Vue.jsに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

Wordpressに関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。

Eye Catch Image
Read More

機械学習に関する現役のエンジニアのノウハウ・トレンドのトピックなど技術的な情報を提供しています。コード・プログラムの丁寧な解説をはじめ、初心者にもわかりやすいように写真や動画を多く使用しています。