CSV と TSV ファイルを SQL で操作する [trdsql]
trdsql とは
CSV (カンマ区切り) ファイルや TSV (タブ区切り) ファイルを SQL 言語で操作できたら非常に便利ですよね。そんな要望を満たしてくれるツールが trdsql というツールです。このツールがあればわざわざ EXCEL でファイルを 開いてデータをフィルターして・・・という手間なく、CSV/TSV ファイルを検索したりすることが出来ます。
trdsql のインストール
Windows、Mac OS、Linux 向けにパッケージが用意されていますので、下記 URL にアクセスしてお使いの OS に応じたパッケージをダウンロードしてインストールします。https://github.com/noborus/trdsql/releases
テストデータの用意
では trdsql を実際に試してみたいと思いますが、まずテスト用の CSV ファイルを用意します。ここでは以下のようなカンマ区切りの CSV ファイルを用意しました。また CSV ファイルには列ヘッダーも付けました (id,name,age)。
$ cat sample.csv id,name,age 1,foo,99 2,bar,17 3,hoge,29 4,pikachu,65 5,tarou,24
CSV ファイルに対して SQL を実行
それでは実際に先に作成した CSV ファイルに対して trdsql を使って SQL で問い合わせを実行してみましょう。なおテーブル名は CSV ファイル名をそのまま使います。この CSV ファイルにはヘッダー行がありますので、"-ih" というオプションが必要ですが、もしヘッダー行がなければ "-ih" オプションは不要です。
まず最初に CSV ファイルを全件取得してみます。
$ trdsql "SELECT * FROM sample.csv" id,name,age 1,foo,99 2,bar,17 3,hoge,29 4,pikachu,65 5,tarou,24
次に "id = 3" のレコードだけを取得してみます。
$ trdsql -ih "SELECT * FROM sample.csv WHERE id = 3" 3,hoge,29
そして LIKE を使って name 列に "o" が含まれているレコードを問い合わせてみます。
$ trdsql -ih "SELECT * FROM sample.csv WHERE name LIKE '%o%'" 1,foo,99 3,hoge,29 5,tarou,24
今度は id を降順に並べ替えてみます。
$ trdsql -ih "SELECT * FROM sample.csv ORDER BY id DESC" 5,tarou,24 4,pikachu,65 3,hoge,29 2,bar,17 1,foo,99
そして最後に集計関数である SUM を使って age 列の合計を算出してみましょう。
$ trdsql -ih "SELECT SUM(age) FROM sample.csv" 234
このようにとても簡単に CSV ファイルに対して SQL を実行でき、集計することも出来ます。
複数の CSV ファイルを結合してみる
では今度は複数の CSV ファイルのでーたを結合して SQL を使って問い合わせてみます。 以下のような２つのファイルを作成しました。sample.csv ファイルには id, name, age (id、名前、年齢) という３列があります。
またもう１つの sample_address.csv ファイルには id, address (id、住所) という２列があります。２つのファイル間で id 列の値は共通しています。
$ cat sample.csv id,name,age 1,foo,99 2,bar,17 3,hoge,29 4,pikachu,65 5,tarou,24
$ cat sample_address.csv id,address 1,Tokyo 2,Osaka 3,Kyoto 4,Kanagawa 5,Saitama
それでは２つのファイルを結合した結果のデータを取得してみます。先にも記載した通りテーブル名は CSV ファイル名になっています。
$ trdsql --ih \ "SELECT s.id, s.name, s.age, sa.address \ FROM sample.csv s JOIN sample_address.csv sa \ ON (s.id = sa.id)" 1,foo,99,Tokyo 2,bar,17,Osaka 3,hoge,29,Kyoto 4,pikachu,65,Kanagawa 5,tarou,24,Saitama
このツールを使うことによって EXCEL を使う必要もなく、またわざわざ CSV ファイルをデータベースに投入して SQL を実行しなくても簡単にデータを選択したり集計、複数の CSV ファイルを結合して問い合わせを実行することが可能となっています。
区切り文字を変更する
また trdsql のオプションの１つである "-od" を使ってタブ区切りにすることも可能です。$ trdsql -ih -od "\t" "SELECT * FROM sample.csv" 1 foo 99 2 bar 17 3 hoge 29 4 pikachu 65 5 tarou 24
データ出力フォーマットを変更する
このツールは更に複数のデータ出力フォーマットに対応しており、次のように JSON 形式、アスキー形式、さらには Markdown の表形式で結果を出力することが出来ます。- -ojson : JSON 形式
- -oat : ASCII テーブル形式
- -omd : Markdown 表形式
[JSON 形式]
$ trdsql -ih -ojson "SELECT * FROM sample.csv"
[
{
"age": "99",
"id": "1",
"name": "foo"
},
{
"age": "17",
"id": "2",
"name": "bar"
},
{
"age": "29",
"id": "3",
"name": "hoge"
},
{
"age": "65",
"id": "4",
"name": "pikachu"
},
{
"age": "24",
"id": "5",
"name": "tarou"
}
]
[アスキー表形式 (ASCII Table)]
$ trdsql -ih -oat "SELECT * FROM sample.csv"
+----+---------+-----+
| id | name | age |
+----+---------+-----+
| 1 | foo | 99 |
| 2 | bar | 17 |
| 3 | hoge | 29 |
| 4 | pikachu | 65 |
| 5 | tarou | 24 |
+----+---------+-----+
[Markdown 表形式]
$ trdsql -ih -omd "SELECT * FROM sample.csv"
| id | name | age |
|----|---------|-----|
| 1 | foo | 99 |
| 2 | bar | 17 |
| 3 | hoge | 29 |
| 4 | pikachu | 65 |
| 5 | tarou | 24 |
標準入力から受け取ったデータを処理する
trdsql は更にファイルに保存された CSV ファイルだけでなく、標準入力からパイプ(|)で受け取ったデータも SQL で処理できます。$ cat sample.csv | trdsql -ih -oat "SELECT name FROM -" +---------+ | name | +---------+ | foo | | bar | | hoge | | pikachu | | tarou | +---------+
この機能を使えば、様々な入力を基にデータを処理できますね！
PostgreSQL データベースの機能を使う
trdsql の更に凄いところは、他のデータベースエンジンの力を借りて CSV ファイルを処理できるところです。では早速 trdsql で PostgreSQL の機能を借りて CSV ファイルに対して問い合わせを実行してみましょう。
ここでは PostgreSQL の md5() 関数を使って名前を MD5 関数でハッシュ化してみます。加えて PostgreSQL のバージョン情報も取得してみましょう [SELECT version()]。
$ trdsql -driver postgres \ -dsn "postgres://user:password@127.0.0.1:5432/dbname?sslmode=disable" \ -ih -oat \ "SELECT id, name, age, md5(name) FROM sample.csv" +----+---------+-----+----------------------------------+ | id | name | age | md5 | +----+---------+-----+----------------------------------+ | 1 | foo | 99 | acbd18db4cc2f85cedef654fccc4a4d8 | | 2 | bar | 17 | 37b51d194a7513e45b56f6524f2d51f2 | | 3 | hoge | 29 | ea703e7aa1efda0064eaa507d9e8ab7e | | 4 | pikachu | 65 | 9ce44f88a25272b6d9cbb430ebbcfcf1 | | 5 | tarou | 24 | 1d9a68c08eaec4927173af57683f05d9 | +----+---------+-----+----------------------------------+ $ ./trdsql -driver postgres \ -dsn "postgres://user:password@127.0.0.1:5432/dbname?sslmode=disable" \ -ih -oat \ "SELECT version()" +--------------------------------+ | version | +--------------------------------+ | PostgreSQL 8.2.19 on | | i686-pc-linux-gnu, compiled by | | GCC gcc (GCC) 4.1.2 20080704 | | (Red Hat 4.1.2-52) | +--------------------------------+
ちゃんと PostgreSQL データベースが持つ md5() 関数が使われて name 列の値が MD5 ハッシュ化されました。また PostgreSQL サーバーのバージョン情報も取得できています。
MySQL データベースの機能を使う
今度は trdsql で MySQL の機能を借りて CSV ファイルに対して問い合わせを実行してみましょう。ここでは MySQL の md5() 関数を使って名前を MD5 関数でハッシュ化してみます。加えて MySQL のバージョン情報も取得してみましょう [SELECT version()]。
$ trdsql -driver mysql \ -dsn "username:password@tcp(127.0.0.1)/dbname" \ -ih -oat \ "SELECT id, name, age, md5(name) FROM sample.csv" +----+---------+-----+----------------------------------+ | id | name | age | md5(name) | +----+---------+-----+----------------------------------+ | 1 | foo | 99 | acbd18db4cc2f85cedef654fccc4a4d8 | | 2 | bar | 17 | 37b51d194a7513e45b56f6524f2d51f2 | | 3 | hoge | 29 | ea703e7aa1efda0064eaa507d9e8ab7e | | 4 | pikachu | 65 | 9ce44f88a25272b6d9cbb430ebbcfcf1 | | 5 | tarou | 24 | 1d9a68c08eaec4927173af57683f05d9 | +----+---------+-----+----------------------------------+ $ trdsql -driver mysql \ -dsn "username:password@tcp(127.0.0.1)/dbname" \ -ih -oat \ +-----------+ | version() | +-----------+ | 5.5.57 | +-----------+
このツールを使えばデータ可能は非常に楽になること間違いなしです。それに加えて高度な機能が必要であれば PostgreSQL や MySQL データベースエンジンの力を借りることも出来ます！
なお trdsql ツールの詳細は下記本家 URL で確認することが出来ます。
https://github.com/noborus/trdsql
