JSON, CSV, Excel, Parquet 等の様々なデータ形式に SQL クエリを実行できる dsq コマンドが便利なのでメモしておく。

はじめに

dsq コマンドは JSON, CSV, Excel, Parquet などの様々なデータ形式に対して SQL クエリを実行できるツール。

対応しているデータ形式

dsql は以下のように多くのデータ形式に対応している。

データ形式	ファイル拡張子	Mime タイプ
CSV	csv	text/csv
TSV	tsv, tab	text/tab-separated-values
JSON	json	application/json
Newline-delimited JSON	ndjson, jsonl	application/jsonlines
Concatenated JSON	cjson	application/jsonconcat
ORC	orc	orc
Parquet	parquet	parquet
Avro	avro
YAML	yaml, yaml	application/yaml
Exel	xlsx, xls	application/vnd.ms-excel
ODS	ods	application/vnd.oasis.opendocument.spreadsheet
Apache Error Logs	NA	text/apache2error
Apache Access Logs	NA	text/apache2access
Nginx Access Logs	NA	text/nginxaccess

類似のツールとの比較

詳細は公式サイトを見てほしいが、q コマンドや textql コマンド等の類似ツールと比べると、対応しているフォーマットがあきらかに多い。

dsq は後発のツールということもあり、既存の類似ツールに対して以下のような優位性があるようだ。

対応しているデータ形式が多い
処理速度が速い

バージョン

このエントリーで使っている dsq のバージョン。

$ dsq --version
dsq 0.21.0

インストール方法

Mac は brew でインストールすると楽。

$ brew install dsq

Mac 以外は (releases)https://github.com/multiprocessio/dsq/releases からバイナリを取得し、パスが通っているディレクトリに配置する。たとえば Linux はこんな感じ。

$ curl -LO https://github.com/multiprocessio/dsq/releases/download/0.21.0/dsq-linux-x64-0.21.0.zip
$ unzip dsq-linux-x64-0.21.0.zip
$ sudo mv dsq /usr/local/bin/dsq

最新版を使いたいときは go install でインストールする。

$ go install github.com/multiprocessio/dsq@latest

基本的な使い方

ファイルに対して SQL クエリを実行する

dsq <ファイル> <SQL クエリ> のように処理対象のファイルを指定して実行する。 SQL クエリの中の {} についてはあとで説明する。

$ cat employee.json | jq
[
  {
    "employee_id": 100,
    "department_id": 1,
    "name": "Alice"
  },
  {
    "employee_id": 200,
    "department_id": 1,
    "name": "Bob"
  },
  {
    "employee_id": 300,
    "department_id": 2,
    "name": "Carol"
  }
]

$ dsq employee.json "select * from {} where employee_id = 100"
[{"department_id":1,"employee_id":100,"name":"Alice"}]

標準入力に対して SQL クエリを実行する

dsq -s <ファイルタイプ> <SQL クエリ> のように実行すると、標準入力に対して SQL クエリを実行することができる。

$ cat employee.json | dsq -s json "select * from {} where employee_id = 100"
[{"department_id":1,"employee_id":100,"name":"Alice"}]

クエリ結果を整形する

dsq はデフォルトではクエリ結果の JSON は整形せずに出力する。

これは実行効率を重視しての挙動のようだが、もしクエリ結果を整形したいときは jq と組み合わせればよい。

$ cat employee.json | dsq -s json "select * from {} where employee_id = 100" | jq
[
  {
    "department_id": 1,
    "employee_id": 100,
    "name": "Alice"
  }
]

あるいは、dsq の -p, --pretty を指定するとクエリ結果をテーブル表示することもできる。

$ cat employee.json | dsq -s json --pretty "select * from {} where employee_id = 100"
+---------------+-------------+-------+
| department_id | employee_id | name  |
+---------------+-------------+-------+
|             1 |         100 | Alice |
+---------------+-------------+-------+
(1 row)

複数のファイルに対して SQL クエリを実行する

複数のファイルを JOIN する

dsq は複数のファイルに対して SQL クエリを実行し、JOIN できる。

$ cat department.json | jq
[
  {
    "department_id": 1,
    "name": "Sales department"
  },
  {
    "department_id": 2,
    "name": "Marketing department"
  }
]

$ dsq --pretty employee.json department.json \
    "select {0}.name as 'employee_name', {1}.name as 'department_name' from {0} join {1} on {1}.department_id = {0}.department_id"
+----------------------+---------------+
|   department_name    | employee_name |
+----------------------+---------------+
| Sales department     | Alice         |
| Sales department     | Bob           |
| Marketing department | Carol         |
+----------------------+---------------+
(3 rows)

SQL クエリからは {N} のようにしてテーブル (読み込み元のファイル) を指定できる。 N は 0 から始まり、dsq の引数に指定したファイルの順番に対応している。

N を省略すると {0} を指定したものとして処理される。つまり、以下の SQL クエリは先ほどの SQL クエリと同じ意味になる。

$ dsq --pretty employee.json department.json \
    "select {}.name as 'employee_name', {1}.name as 'department_name' from {} join {1} on {1}.department_id = {}.department_id"
+----------------------+---------------+
|   department_name    | employee_name |
+----------------------+---------------+
| Sales department     | Alice         |
| Sales department     | Bob           |
| Marketing department | Carol         |
+----------------------+---------------+
(3 rows)

SQL クエリ内のテーブルにはエイリアスを付けることもできる。エイリアスを使うと先ほどの SQL クエリは以下のように書くことができる。

$ dsq --pretty employee.json department.json \
    "select e.name as 'employee_name', d.name as 'department_name' from {0} as e join {1} as d on d.department_id = e.department_id"
+----------------------+---------------+
|   department_name    | employee_name |
+----------------------+---------------+
| Sales department     | Alice         |
| Sales department     | Bob           |
| Marketing department | Carol         |
+----------------------+---------------+
(3 rows)

データ形式が異なるファイルを JOIN する

dsq はデータ形式が異なるファイル同士も処理できる。

たとえば、以下の例では JSON と CSV に対して SQL クエリを実行し、JOIN している。

$ cat department.csv
"department_id","name"
1,"Sales department"
2,"Marketing department"

$ dsq --pretty employee.json department.csv \
    "select {0}.name as 'employee_name', {1}.name as 'department_name' from {0} join {1} on {1}.department_id = {0}.department_id"
+----------------------+---------------+
|   department_name    | employee_name |
+----------------------+---------------+
| Sales department     | Alice         |
| Sales department     | Bob           |
| Marketing department | Carol         |
+----------------------+---------------+
(3 rows)

任意のデータ形式から JSON ヘの変換

dsq はデフォルトではクエリ結果を JSON で出力するので、任意のデータ形式から JSON ヘの変換フィルターとして使うことができる。

$ dsq department.csv "select * from {}"
[{"name":"Sales department","department_id":"1"},
{"department_id":"2","name":"Marketing department"}]

dsq は SQL クエリを省略すると select * from {} が指定されたものとして動作するので、先ほどの実行例は以下のように短くすることができる。

$ dsq department.csv
[{"department_id":"1","name":"Sales department"},
{"department_id":"2","name":"Marketing department"}]

オブジェクト内にネストしている配列に SQL クエリを実行する

SQL クエリを実行したいオブジェクトの配列がオブジェクト内にネストしている場合、{N, 'path'} または {N, "path"}のようにテーブルを指定する。

$ cat employee-2.json | jq
{
  "data": {
    "employees": [
      {"employee_id": 100, "department_id": 1, "name": "Alice"},
      {"employee_id": 200, "department_id": 1, "name": "Bob"},
      {"employee_id": 300, "department_id": 2, "name": "Carol"}
    ]
  }
}

$ dsq --pretty employee-2.json "select * from {0, 'data.employees'} where employee_id = 100"
+---------------+-------------+-------+
| department_id | employee_id | name  |
+---------------+-------------+-------+
|             1 |         100 | Alice |
+---------------+-------------+-------+
(1 row)

テーブルに {} を指定すると {0} を指定したものとして処理されるので、先ほどの SQL クエリは以下のように短くできる。

$ dsq --pretty employee-2.json "select * from {'data.employees'} where employee_id = 100"
+---------------+-------------+-------+
| department_id | employee_id | name  |
+---------------+-------------+-------+
|             1 |         100 | Alice |
+---------------+-------------+-------+
(1 row)

ネストしているオブジェクトから値を取得する

dsq がクエリできるのはスカラー値のみで、オブジェクト全体は取得できない。したがって、下記のようなコマンドを実行すると「カラムがない」旨を示すメッセージが表示される。

$ cat employee-3.json
[
  {
    "employee_id": 100,
    "department_id": 1,
    "name": {"first": "Alice", "last": "Smith"}
  },
  {
    "employee_id": 200,
    "department_id": 1,
    "name": {"first": "Bob", "last": "Johnson"}
  },
  {
    "employee_id": 300,
    "department_id": 2,
    "name": {"first": "Carol", "last": "Williams"}
  }
]

$ dsq --pretty employee-3.json 'select name from {} where employee_id = 100'
no such column: name

以下のようにスカラー値へのパスを指定すると、オブジェクトのフィールドの値を取得できる。

$ dsq --pretty employee-3.json 'select "name.first", "name.last" from {} where employee_id = 100'
+------------+-----------+
| name.first | name.last |
+------------+-----------+
| Alice      | Smith     |
+------------+-----------+
(1 row)

ネストしている配列から値を取得する

dsq が内部で利用している SQLite は JSON 演算子をサポートしており、下記のようにネストしている配列から任意の値を取得することができる (正確には、dsq が内部で利用している datastation が SQLite を利用している)

$ cat fields.json
[
  {"field1": [1]},
  {"field1": [2]},
]

$ dsq fields.json "SELECT field1->0 FROM {}" | jq
[
  {
    "field1->0": "1"
  },
  {
    "field1->0": "2"
  }
]

正規表現のサポート

SQL クエリ内で正規表現を使うことができる。

$ dsq --pretty employee.json 'select * from {} where name regexp "^[AB]"'
+---------------+-------------+-------+
| department_id | employee_id | name  |
+---------------+-------------+-------+
|             1 |         100 | Alice |
|             1 |         200 | Bob   |
+---------------+-------------+-------+
(2 rows)

その他

カラムの出力順

--pretty を指定する場合、カラムはアルファベット順に出力される。

$ dsq --pretty employee.json 'select name, department_id, employee_id from {} where employee_id = 100'
+---------------+-------------+-------+
| department_id | employee_id | name  |
+---------------+-------------+-------+
|             1 |         100 | Alice |
+---------------+-------------+-------+
(1 row)

--pretty を指定しない場合、カラムは select 句で指定した順に出力される。カラムを順序付けして出力する場合は jq --sort-keys と併用するとよい。

$ dsq employee.json 'select department_id, name, employee_id from {} where employee_id = 100'
[{"department_id":1,"name":"Alice","employee_id":100}]

$ dsq employee.json 'select department_id, name, employee_id from {} where employee_id = 100' | jq --sort-keys
[
  {
    "department_id": 1,
    "employee_id": 100,
    "name": "Alice"
  }
]

スキーマの推測

--schema を指定するとファイルのスキーマを推測して出力する。

$ dsq --schema employee.json
{
  "kind": "array",
  "array": {
    "kind": "object",
    "object": {
      "department_id": {
        "kind": "scalar",
        "scalar": "number"
      },
      "name": {
        "kind": "scalar",
        "scalar": "string"
      },
      "employee_id": {
        "kind": "scalar",
        "scalar": "number"
      }
    }
  }
}

入力ファイルのキャッシュ

-C, --cache を指定すると入力ファイルをキャッシュするようになり、キャッシュ済みのファイルへの SQL クエリが大幅に高速化される。 -C, --cache を指定する代わりに環境変数で DSQ_CACHE=true を設定しておくこともできる。

$ dsq some-large-file.json --cache 'SELECT COUNT(1) FROM {}'

$ export DSQ_CACHE=true
$ dsq some-large-file.json 'SELECT COUNT(1) FROM {}'

REPL

-i, --interactive を指定すると REPL が起動し、対話的に SQL クエリを実行できる。

$ dsq --interactive employee.json
dsq> select count(1) from {}
+----------+
| count(1) |
+----------+
|        3 |
+----------+
(1 row)

dsq> select * from {} where employee_id = 100;
+---------------+-------------+-------+
| department_id | employee_id | name  |
+---------------+-------------+-------+
|             1 |         100 | Alice |
+---------------+-------------+-------+
(1 row)

dsq> exit
bye

CSV と TSV ファイル内の数値の変換

CSV と TSV ファイル内のフィールドは、デフォルトではすべて文字列として扱われる。

したがって、下記の CSV のように score が実際は数値であっても、score でソートすると辞書順にソートされてしまう。

$ cat scores.csv
name,score
Fritz,90
Rainer,95
Fountainer,100

$ dsq scores.csv 'select score from {} order by score'
[{"score":"100"},
{"score":"90"},
{"score":"95"}]

-n, --convert-numbers を指定すると、数値 (整数と浮動小数点数) を自動検出して、文字列から数値に変換する。

$ dsq --convert-numbers scores.csv 'select score from {} order by score'
[{"score":90},
{"score":95},
{"score":100}]

-n, --convert-numbers を指定する代わりに環境変数で DSQ_CONVERT_NUMBERS=true を設定しておくこともできる。

$ export DSQ_CONVERT_NUMBERS=true
$ dsq scores.csv 'select score from {} order by score'
[{"score":90},
{"score":95},
{"score":100}]

なお、数値 (整数と浮動小数点数) の自動検出 & 文字列から数値への変換を有効にすると、一部の最適化が無効になる。

参考サイト

GitHub - multiprocessio/dsq: Commandline tool for running SQL queries against JSON, CSV, Excel, Parquet, and more.