$ printf "id,name\n1,Ted\n2,Marjory\n3,Micah\n" > users.csv
$ printf "name,score\nFritz,90\nRainer,95.2\nFountainer,100\n" > scores.csv
$ cat > user_addresses.json <<'EOF'
[
  {"name": "Agarrah", "location": {"city": "Toronto", "address": { "number": 1002 }}},
  {"name": "Minoara", "location": {"city": "Mexico City", "address": { "number": 19 }}},
  {"name": "Fontoon", "location": {"city": "New London", "address": { "number": 12 }}}
]
EOF
$ cat > fields.json <<'EOF'
[
  {"field1": [1]},
  {"field1": [2]}
]
EOF
$ cat > api-results.json <<'EOF'
{
  "data": {
    "data": [
      {"id": 1, "name": "Corah"},
      {"id": 3, "name": "Minh"}
    ]
  },
  "total": 2
}
EOF
$ cat > orders.ndjson <<'EOF'
{"id": 1, "item": "keyboard", "qty": 2, "price": 49.9}
{"id": 2, "item": "mouse", "qty": 5, "price": 12.5}
{"id": 3, "item": "monitor", "qty": 1, "price": 219.0}
EOF
$ cat > query.sql <<'EOF'
SELECT name, score FROM {0} WHERE CAST(score AS REAL) > 90 ORDER BY CAST(score AS REAL) DESC
EOF

$ dsq --help
dsq (Version v0.23.0) - commandline SQL engine for data files

Usage:  dsq [file...] $query
        dsq $file [query]
        cat $file | dsq -s $filetype [query]

dsq is a tool for running SQL on one or more data files. It uses
SQLite's SQL dialect. Files as tables are accessible via "{N}" where N
is the 0-based index of the file in the commandline.

The shorthand "{}" is replaced with "{0}".

Examples:

    # This simply dumps the CSV as JSON
    $ dsq test.csv

    # This dumps the first 10 rows of the parquet file as JSON.
    $ dsq data.parquet "SELECT * FROM {} LIMIT 10"

    # This joins two datasets of differing origin types (CSV and JSON).
    $ dsq testdata/join/users.csv testdata/join/ages.json \
          "select {0}.name, {1}.age from {0} join {1} on {0}.id = {1}.id"

See the repo for more details: https://github.com/multiprocessio/dsq

$ dsq -p users.csv "SELECT * FROM {}"
+----+---------+
| id |  name   |
+----+---------+
|  1 | Ted     |
|  2 | Marjory |
|  3 | Micah   |
+----+---------+
(3 rows)

$ dsq --schema users.csv
{
  "kind": "array",
  "array": {
    "kind": "object",
    "object": {
      "id": {
        "kind": "scalar",
        "scalar": "string"
      },
      "name": {
        "kind": "scalar",
        "scalar": "string"
      }
    }
  }
}

$ dsq --schema user_addresses.json
{
  "kind": "array",
  "array": {
    "kind": "object",
    "object": {
      "location": {
        "kind": "object",
        "object": {
          "city": {
            "kind": "scalar",
            "scalar": "string"
          },
          "address": {
            "kind": "object",
            "object": {
              "number": {
                "kind": "scalar",
                "scalar": "number"
              }
            }
          }
        }
      },
      "name": {
        "kind": "scalar",
        "scalar": "string"
      }
    }
  }
}

$ dsq testdata/avro/test_data.avro --schema --pretty
Array of
  Object of
    birthdate of
      string
    cc of
      Varied of
        Object of
          long of
            number or
        Unknown
    comments of
      string
    ...
    title of
      string

$ cat query.sql
SELECT name, score FROM {0} WHERE CAST(score AS REAL) > 90 ORDER BY CAST(score AS REAL) DESC
$ dsq scores.csv -f query.sql | cat
[{"score":"100","name":"Fountainer"},
{"score":"95.2","name":"Rainer"}]

$ cat scores.csv | dsq -s csv "SELECT * FROM {} LIMIT 2" | cat
[{"name":"Fritz","score":"90"},
{"name":"Rainer","score":"95.2"}]

$ cat access.log | dsq -s text/nginxaccess "SELECT * FROM {}" | cat
[{"agent":null,"method":"GET","path":"/apache_pb.gif","referer":null,"remote":"127.0.0.1","size":"2326","time":"10/Oct/2000:13:55:36 -0700","user":"frank","code":"200","host":"-","http_x_forwarded_for":null},
{"time":"10/Oct/2000:14:02:11 -0700","agent":null,"code":"500","host":"-","http_x_forwarded_for":null,"path":"/api/order","referer":null,"remote":"192.168.1.5","method":"POST","size":"0","user":"-"},
{"path":"/index.html","remote":"10.0.0.8","size":"128","agent":null,"host":"-","http_x_forwarded_for":null,"method":"GET","referer":null,"time":"10/Oct/2000:14:10:05 -0700","user":"alice","code":"404"}]

$ dsq unknown.xyz "SELECT * FROM {}"
Unknown mimetype for file: unknown.xyz.
$ echo $?
1

$ time dsq testdata/userdata.parquet --cache "SELECT COUNT(1) FROM {}" | cat
[{"COUNT(1)":1000}]

real	0m0.022s
user	0m0.018s
sys	0m0.009s
$ time dsq testdata/userdata.parquet --cache "SELECT COUNT(1) FROM {}" | cat
[{"COUNT(1)":1000}]

real	0m0.026s
user	0m0.019s
sys	0m0.012s

$ dsq testdata/userdata.parquet -C "SELECT COUNT(1) FROM {}" | cat
Cache invalid, re-import required.
[{"COUNT(1)":1000}]

$ dsq testdata/userdata.parquet --cache-file "SELECT COUNT(1) FROM {}" | cat
/tmp/dsq-cache-f683ce9b49590888a5c9c24d8aa10013ad8ceadf.db

$ printf "SELECT COUNT(1) FROM {};\nSELECT name FROM {} WHERE id = 1;\n.exit\n" | dsq users.csv -i
Cache invalid, re-import required.
+----------+
| COUNT(1) |
+----------+
|        3 |
+----------+
(1 row)
+------+
| name |
+------+
| Ted  |
+------+
(1 row)
near ".": syntax error
$ echo $?
1

$ printf "SELECT COUNT(1) FROM {};\n" | dsq users.csv -i
table "t_0" already exists
$ echo $?
1

$ dsq scores.csv "SELECT * FROM {} ORDER BY score" | cat
[{"name":"Fountainer","score":"100"},
{"name":"Fritz","score":"90"},
{"name":"Rainer","score":"95.2"}]

$ dsq scores.csv --convert-numbers "SELECT * FROM {} ORDER BY score" | cat
[{"name":"Fritz","score":90},
{"name":"Rainer","score":95.2},
{"name":"Fountainer","score":100]}]

$ DSQ_CACHE=true dsq testdata/userdata.parquet "SELECT COUNT(1) FROM {}" | cat
[{"COUNT(1)":1000}]

$ DSQ_CONVERT_NUMBERS=true dsq scores.csv "SELECT * FROM {} ORDER BY score" | cat
[{"name":"Fritz","score":90},
{"score":95.2,"name":"Rainer"},
{"name":"Fountainer","score":100}]

$ DSQ_NO_SQLITE_WRITER=true dsq users.csv | cat
[{"id":"1","name":"Ted"},
{"id":"2","name":"Marjory"},
{"id":"3","name":"Micah"}]

$ ls -la /tmp/dsq-cache-*.db
-rw-r--r-- 1 root root 8192 Nov  3 03:18 /tmp/dsq-cache-6d9658d2e50acadd7b2060245fda9e94740dfb06.db
-rw-r--r-- 1 root root 4096 Nov  3 03:18 /tmp/dsq-cache-f683ce9b49590888a5c9c24d8aa10013ad8ceadf.db

$ sqlite3 /tmp/dsq-cache-f683ce9b49590888a5c9c24d8aa10013ad8ceadf.db ".tables"
t_0
$ sqlite3 /tmp/dsq-cache-f683ce9b49590888a5c9c24d8aa10013ad8ceadf.db ".schema t_0"
CREATE TABLE IF NOT EXISTS "t_0" ("Activated" BOOLEAN, "Company9" TEXT, "Created32At32" REAL, "Department" TEXT, "Email" TEXT, "PARGO_PREFIX_32323232City32" TEXT, "PARGO_PREFIX_3232Description" TEXT, "PARGO_PREFIX_32Name32" TEXT, "Phone32Number32" TEXT, "Profile32Photo" TEXT, "Routing32Number323232" REAL, "State" TEXT, "Street" TEXT, "Zip32Code32" TEXT);

$ dsq users.csv >> out2.json
write /dev/stdout: copy_file_range: bad file descriptor
$ echo $?
1

$ dsq users.csv | cat >> out2.json
$ dsq users.csv | cat >> out2.json
$ cat out2.json
[{"id":"1","name":"Ted"},
{"id":"2","name":"Marjory"},
{"id":"3","name":"Micah"}]
[{"id":"1","name":"Ted"},
{"id":"2","name":"Marjory"},
{"id":"3","name":"Micah"}]