CSV と TSV ファイルを SQL で操作する [TextQL]
TextQL とは
CSV (カンマ区切り) ファイルや TSV (タブ区切り) ファイルを SQL 言語で操作できたら非常に便利ですよね。そんな要望を満たしてくれるツールが TextQL というツールです。このツールがあればわざわざ EXCEL でファイルを 開いてデータをフィルターして・・・という手間なく、CSV/TSV ファイルを検索したりすることが出来ます。
TextQL のインストール
Ubuntu をお使いの場合は既にパッケージが存在しますので、そのまま以下のようにしてインストール可能です。$ apt-cache search textql textql - execute SQL against structured text like CSV or TSV $ sudo apt install textql
また Mac OS の場合は以下のようにしてインストールできます。
$ brew install textql
テストデータの用意
では TextQL を実際に試してみたいと思いますが、まずテスト用の 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 ファイルに対して TextQL を使って SQL で問い合わせを実行してみましょう。なおテーブル名は CSV ファイル名として扱うことが出来ます。この CSV ファイルにはヘッダー行がありますので、"-header" というオプションが必要ですが、もしヘッダー行がなければ "-header" オプションは不要です。
まず最初に CSV ファイルを全件取得してみます。
$ textql -header -sql "SELECT * FROM sample" sample.csv 1,foo,99 2,bar,17 3,hoge,29 4,pikachu,65 5,tarou,24
次に "id = 3" のレコードだけを取得してみます。
$ textql -header -sql "SELECT * FROM sample WHERE id = 3" sample.csv 3,hoge,29
そして LIKE を使って name 列に "o" が含まれているレコードを問い合わせてみます。
$ textql -header -sql "SELECT * FROM sample WHERE name LIKE '%o%'" sample.csv 1,foo,99 3,hoge,29 5,tarou,24
今度は id を降順に並べ替えてみます。
$ textql -header -sql "SELECT * FROM sample ORDER BY id DESC" sample.csv 5,tarou,24 4,pikachu,65 3,hoge,29 2,bar,17 1,foo,99
そして最後に集計関数である SUM を使って age 列の合計を算出してみましょう。
$ textql -header -sql "SELECT SUM(age) FROM sample" 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 ファイル名になっています。
$ textql --header \ -sql "SELECT s.id, s.name, s.age, sa.address \ FROM sample s JOIN sample_address sa \ ON (s.id = sa.id)" \ sample.csv sample_address.csv 1,foo,99,Tokyo 2,bar,17,Osaka 3,hoge,29,Kyoto 4,pikachu,65,Kanagawa 5,tarou,24,Saitama
このツールを使うことによって EXCEL を使う必要もなく、またわざわざ CSV ファイルをデータベースに投入して SQL を実行しなくても簡単にデータを選択したり集計、複数の CSV ファイルを結合して問い合わせを実行することが可能となっています。
区切り文字を変更する
また TextQL のオプションの１つである "-output-dlm" を使ってタブ区切りにすることも可能です。$ textql -header -output-dlm=tab -sql "SELECT * FROM sample" sample.csv 1 foo 99 2 bar 17 3 hoge 29 4 pikachu 65 5 tarou 24
sqlite データベースを作成する
TextQL の更に凄いところは、CSV ファイルに対して実行した SQL の結果を基に、そのまま sqlite データベースを作成できるんです。では早速 TextQL の機能を使って簡単に sqlite データベースを作成してみましょう。
"-save-to" というオプションに続けて作成したい sqlite データベースの名前を与えてあげると、同じく指定した SQL の実行結果を基に sqlite データベースを作成してくれます。
$ textql -header -save-to ./sample.db -sql "SELECT * FROM sample" sample.csv 1,foo,99 2,bar,17 3,hoge,29 4,pikachu,65 5,tarou,24
SELECT の結果が画面に表示されていますが、同時に sample.db という sqlite データベースも作成されています。では早速作成された sqlite データベースの中身を見てみましょう。
$ sqlite3 sample.db "SELECT * FROM sample" 1|foo|99 2|bar|17 3|hoge|29 4|pikachu|65 5|tarou|24
このように SELECT の結果を基に sqlite データベースにもレコードが登録されているのがわかります。
このツールを使えばデータ可能は非常に楽になること間違いなしです。
なお TextQL ツールの詳細は下記本家 URL で確認することが出来ます。
https://github.com/dinedal/textql
